Hàm tự tạo IsReference

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Trong Excel phiên bản cao có hàm ISFORMULA(reference) nhưng máy em không dùng Excel cao
Nhờ mợi người viết code cho hàm này, với điều kiện, Ô tham chiếu là công thức thì chả về là 1, ngược lại là 0
- Điều kiện kèm theo:
+ Ô Công thức là ô có các hàm, công thức tham chiếu đến địa chỉ ô khác: =A2; =A2*100; =sum(A3:A12); =Dongia!B1
+ Ô không có công thức là ô có hằng số: 1.000; 2000; =2*1000; =pi()
 

File đính kèm

leonguyenz

Thành viên mới
Thành viên BQT
Moderator
Tham gia ngày
2 Tháng tám 2010
Bài viết
4,538
Được thích
7,950
Điểm
610
Nơi ở
Bình Dương
Trong Excel phiên bản cao có hàm ISFORMULA(reference) nhưng máy em không dùng Excel cao
Nhờ mợi người viết code cho hàm này, với điều kiện, Ô tham chiếu là công thức thì chả về là 1, ngược lại là 0
- Điều kiện kèm theo:
+ Ô Công thức là ô có các hàm, công thức tham chiếu đến địa chỉ ô khác: =A2; =A2*100; =sum(A3:A12); =Dongia!B1
+ Ô không có công thức là ô có hằng số: 1.000; 2000; =2*1000; =pi()
Bạn tìm hiểu thuộc tính Range.HasFormula.
 

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Bạn tìm hiểu thuộc tính Range.HasFormula.
Vâng em có tìm hiểu qua nhưng vẫn chưa thỏa được điều kiện; Khi ô có giá trị: =1+2; =pi() thì vẫn trả về giá trị TRUE; em muốn khi có công thức nhưng là hằng số không có tham gia của địa chỉ ô khác thì trả về giá trị FALSE
 

File đính kèm

leonguyenz

Thành viên mới
Thành viên BQT
Moderator
Tham gia ngày
2 Tháng tám 2010
Bài viết
4,538
Được thích
7,950
Điểm
610
Nơi ở
Bình Dương
Vâng em có tìm hiểu qua nhưng vẫn chưa thỏa được điều kiện; Khi ô có giá trị: =1+2; =pi() thì vẫn trả về giá trị TRUE; em muốn khi có công thức nhưng là hằng số không có tham gia của địa chỉ ô khác thì trả về giá trị FALSE
Thử code sau:
Mã:
Function IsFormula(cell_ref As Range) As Boolean
    IsFormula = InStr(cell_ref.FormulaR1C1, "[") > 0
End Function
 

HeSanbi

Thành viên tích cực
Tham gia ngày
24 Tháng hai 2013
Bài viết
1,308
Được thích
1,199
Điểm
560
Bạn thử sử dụng hàm dưới đây:

và bạn cũng có thể tự viết code thêm điều kiện để xác định là Công thức bằng cách thêm "And" và điều kiện phủ định.

-------
PHP:
Function IsFormula(ByVal Reference As Range) As Boolean
  If Reference(1, 1).HasFormula Then
    Dim Text As String
    Text = VBA.Mid(Reference(1, 1).Formula , 2)
    IsFormula = Not VBA.IsNumeric(Text) And Not Text Like "[pP][iI]()"  _
                        And Not Text Like """*"
  End If
End Function
 

HeSanbi

Thành viên tích cực
Tham gia ngày
24 Tháng hai 2013
Bài viết
1,308
Được thích
1,199
Điểm
560
Cảm ơn anh leonguyenz như này là ổn rồi
------------

Có thật là ổn không bạn ! nếu vậy thì bạn cứ tiếp tục sử dụng, còn không hãy nghe lời khuyên dưới đây.

leonguyenz đã viết nhầm hàm kiểm tra xem ô có chứa dấu ngoặc vuông ( [ ) hay không.


A1 = "[" -> lỗi sai kết quả
A1 = =Sum(1 + 1) -> lỗi sai kết quả
A1 = =PI() -> vì không có dấu [ nên trả kết quả đúng
 

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Cảm ơn anh leonguyenz như này là ổn rồi
------------

Có thật là ổn không bạn ! nếu vậy thì bạn cứ tiếp tục sử dụng, còn không hãy nghe lời khuyên dưới đây.

leonguyenz đã viết nhầm hàm kiểm tra xem ô có chứa dấu ngoặc vuông ( [ ) hay không.


A1 = "[" -> lỗi sai kết quả
A1 = =Sum(1 + 1) -> lỗi sai kết quả
A1 = =PI() -> vì không có dấu [ nên trả kết quả đúng
Cảm ơn HeSanbi
Đúng là có một số chỗ sẽ không thỏa mãn điều kiện thật, bản thân mình cũng chưa viết rõ điều kiện; Khi dùng địa chỉ tuyệt đối, =$B$3; =$B5; =B$3; =Dongia!$B$1 thì của anh leonguyenz cho kết quả không như ý muốn thật
Mục đích của mình là kiểm tra xem trong công thức, hàm có liên kết sang các địa chỉ ô khác không, nếu có trả về True, còn lại FALSE
Mình cũng đang test theo của bạn các trường hợp khác xem sao.
Theo Code của bạn HeSanbi thỏa mãn gần hết các điều kiện mà mình cần, còn 2 trường hợp nữa chưa đúng điều kiện:
ô có công thức: =2*3+5; =sum(1+1); Các ô này không liên kết sang các ô khác nên không phải là công thức mình cần trả kết quả là FALSE
V2 của anh leonguyenz
V3 của HeSanbi,
Mong Hai anh có thể sửa giúp mình lại cho đúng điều kiện với
 

File đính kèm

Lần chỉnh sửa cuối:

ppc0312

whom?
Tham gia ngày
2 Tháng tư 2008
Bài viết
583
Được thích
278
Điểm
735
........
Đúng là có một số chỗ sẽ không thỏa mãn điều kiện thật, bản thân mình cũng chưa viết rõ điều kiện; Khi dùng địa chỉ tuyệt đối, =$B$3; =$B5; =B$3; =Dongia!$B$1 thì của anh leonguyenz cho kết quả không như ý muốn thật
Mục đích của mình là kiểm tra xem trong công thức, hàm có liên kết sang các địa chỉ ô khác không, nếu có trả về True, còn lại FALSE
Vậy sửa lại như sau
Mã:
Function IsFormula(cell_ref As Range) As Boolean
    IsFormula = InStr(cell_ref.FormulaR1C1, "[") > 0 Or InStr(cell_ref.Formula, "$") > 0
End Function
Tuy thế tên hàm nên là IsNoReferrence() thì đúng nghĩa hơn (hay IsNoRef())
Mã:
Function IsNoRef(cell_ref As Range) As Boolean
    IsNoRef= InStr(cell_ref.FormulaR1C1, "[") > 0 Or InStr(cell_ref.Formula, "$") > 0
End Function
 
Lần chỉnh sửa cuối:

HeSanbi

Thành viên tích cực
Tham gia ngày
24 Tháng hai 2013
Bài viết
1,308
Được thích
1,199
Điểm
560
Cảm ơn HeSanbi
Đúng là có một số chỗ sẽ không thỏa mãn điều kiện thật, bản thân mình cũng chưa viết rõ điều kiện; Khi dùng địa chỉ tuyệt đối, =$B$3; =$B5; =B$3; =Dongia!$B$1 thì của anh leonguyenz cho kết quả không như ý muốn thật
Mục đích của mình là kiểm tra xem trong công thức, hàm có liên kết sang các địa chỉ ô khác không, nếu có trả về True, còn lại FALSE
Mình cũng đang test theo của bạn các trường hợp khác xem sao.
Theo Code của bạn HeSanbi thỏa mãn gần hết các điều kiện mà mình cần, còn 2 trường hợp nữa chưa đúng điều kiện:
ô có công thức: =2*3+5; =sum(1+1); Các ô này không liên kết sang các ô khác nên không phải là công thức mình cần trả kết quả là FALSE
V2 của anh leonguyenz
V3 của HeSanbi,
Mong Hai anh có thể sửa giúp mình lại cho đúng điều kiện với
----------

Không liên kết đến ô khác thì làm sao Hàm lại có tên IsFormula


----------
PHP:
Function IsReference(ByVal Reference As Range) As Boolean
  If Reference(1, 1).HasFormula Then
    Static RE As Object
    If RE Is Nothing Then
      Set RE = VBA.CreateObject("VBScript.RegExp")
      With RE
        .Pattern = "(?:^|[,!(=\s])((?:\$?[A-z]{1,3}\$?\d+(?::\$?[A-z]{1,3}\$?\d+)?|" & _
                   "\$?[A-z]{1,3}:\$?[A-z]{1,3}|\$?\d+:\$?\d+))(?:$|[\s,)])"
        .Global = True
      End With
    End If
    IsReference = RE.test(Reference(1, 1).Formula)
  End If
End Function

Function IsLikeConstants(ByVal Reference As Range) As Boolean
  If Reference(1, 1).HasFormula Then
    Dim Text As String
    Text = VBA.Mid(Reference(1, 1).Formula , 2)
    IsLikeConstants = Not VBA.IsNumeric(Text) And Not Text Like "[pP][iI]()"  _
                        And Not Text Like """*"
  End If
End Function
 
Lần chỉnh sửa cuối:

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Cảm ơn các bạn nhiều!
Vâng mình chưa có kinh nghiệm diễn đạt mục đích cần đạt được.
Đúng là tên hàm nên đặt là IsReference hoặc IsNoRef;
Bản thân Excel đời cao đã có hàm ISFORMULA(reference) và hàm =ISREF(reference) nhưng 2 hàm này không như ý mình muốn cần
Em muốn thay tiêu đề cho phù hợp nhưng không sửa được, Mong bác nào qua sửa cái tiêu đề thành: Hàm tự tạo IsReference
 
Lần chỉnh sửa cuối:

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
----------

Không liên kết đến ô khác thì làm sao Hàm lại có tên IsFormula


----------
PHP:
Function IsReference(ByVal Reference As Range) As Boolean
  If Reference(1, 1).HasFormula Then
    Static RE As Object
    If RE Is Nothing Then
      Set RE = VBA.CreateObject("VBScript.RegExp")
      With RE
        .Pattern = "(?:^|[,!(=\s])((?:\$?[A-z]{1,3}\$?\d+(?::\$?[A-z]{1,3}\$?\d+)?|" & _
                   "\$?[a-z]{1,3}:\$?[a-z]{1,3}|\$?\d+:\$?\d+))(?:$|[\s,)])"
        .Global = True
      End With
    End If
    IsReference = RE.test(Reference(1, 1).Formula)
  End If
End Function

Function IsLikeConstants(ByVal Reference As Range) As Boolean
  If Reference(1, 1).HasFormula Then
    Dim Text As String
    Text = VBA.Mid(Reference(1, 1).Formula , 2)
    IsLikeConstants = Not VBA.IsNumeric(Text) And Not Text Like "[pP][iI]()"  _
                        And Not Text Like """*"
  End If
End Function
Mình kiểm tra thấy Code này sẽ không đúng cho trường hợp thực hiện các toán tử với các địa chỉ Ô: =B2+B3
=B4*D4, =$B$2+B3
Các trường hợp này đều là tham chiếu đến các ô khác nên trả về TRUE mới đúng
Với bài #9 thì kết quả của ppc0312 thì Ok
 

huuthang_bd

Chuyên gia GPE
Tham gia ngày
10 Tháng chín 2008
Bài viết
7,236
Được thích
8,206
Điểm
860
Nơi ở
TP.HCM
Mình kiểm tra thấy Code này sẽ không đúng cho trường hợp thực hiện các toán tử với các địa chỉ Ô: =B2+B3
=B4*D4, =$B$2+B3
Các trường hợp này đều là tham chiếu đến các ô khác nên trả về TRUE mới đúng
Với bài #9 thì kết quả của ppc0312 thì Ok
Nếu ô có công thức vầy thì sao
Mã:
="[$"
Chưa kể công thức tham chiếu đến name
 

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Nếu ô có công thức vầy thì sao
Mã:
="[$"
Chưa kể công thức tham chiếu đến name
Ô có công thức =$B$3; =B$3; =$B3 theo Code bài #9 vẫn được anh huuthang_bd
hay em chưa hiểu tham chiếu dạng ="[$"
Tham chiếu đến name thì không ổn: Ví dụ =Heso với Heso là name của ô H2
 

phuocam

Thành viên mới
Tham gia ngày
16 Tháng năm 2013
Bài viết
2,437
Được thích
3,245
Điểm
560
Công thức này đơn giản, chưa tính tham chiếu đến Name
PHP:
Function IsReference(ByVal Cell As Range) As Boolean
    IsReference = Cell.Formula <> Cell.FormulaR1C1
End Function
 

huyyeu99

Thành viên chính thức
Tham gia ngày
21 Tháng sáu 2008
Bài viết
86
Được thích
57
Điểm
680
Hỏi thiệt bạn cho mình biết mực đích làm gì được hông tòmo quá.
 

KhanhChi29

Thành viên chính thức
Tham gia ngày
27 Tháng chín 2014
Bài viết
77
Được thích
17
Điểm
370
Tuổi
31
Nơi ở
HaNoi
Hỏi thiệt bạn cho mình biết mực đích làm gì được hông tòmo quá.
Bên mình nhập và xuất, các đơn giá có đơn giá gốc, đơn giá link từ ô khác, giá gốc có tính toán bằng công thức hoặc nhập số chết, (Hằng số), mục đích mình lọc ra những ô có tham chiếu và ô không có tham chiếu để kiểm soát giá đầu vào.
Bài đã được tự động gộp:

Công thức này đơn giản, chưa tính tham chiếu đến Name
PHP:
Function IsReference(ByVal Cell As Range) As Boolean
    IsReference = Cell.Formula <> Cell.FormulaR1C1
End Function
Vâng cảm ơn bạn, cũng may bên mình ít dùng name, nếu dung name sẽ sót mất trường hợp này,
 
Lần chỉnh sửa cuối:

quanghai1969

Thành viên gạo cội
Tham gia ngày
21 Tháng hai 2009
Bài viết
5,638
Được thích
7,053
Điểm
860
Nơi ở
Thuận An, Bình Dương
Trong Excel phiên bản cao có hàm ISFORMULA(reference) nhưng máy em không dùng Excel cao
Nhờ mợi người viết code cho hàm này, với điều kiện, Ô tham chiếu là công thức thì chả về là 1, ngược lại là 0
- Điều kiện kèm theo:
+ Ô Công thức là ô có các hàm, công thức tham chiếu đến địa chỉ ô khác: =A2; =A2*100; =sum(A3:A12); =Dongia!B1
+ Ô không có công thức là ô có hằng số: 1.000; 2000; =2*1000; =pi()
 
Lần chỉnh sửa cuối:
Top Bottom