Hàm tự tạo IsReference

Liên hệ QC

KhanhChi29

Thành viên chính thức
Tham gia
27/9/14
Bài viết
92
Được thích
22
Nghề nghiệp
Giáo Viên
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

  • Ham ISFORMULA(reference).xls
    23.5 KB · Đọc: 13
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.
 
Upvote 0
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

  • Ham ISFORMULA(reference) V1.xls
    33 KB · Đọc: 4
  • Kiem tra cong thuc.png
    Kiem tra cong thuc.png
    10.5 KB · Đọc: 11
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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

  • Ham ISFORMULA(reference) V2.xls
    34.5 KB · Đọc: 3
  • Ham ISFORMULA(reference) V3.xls
    35 KB · Đọc: 3
Lần chỉnh sửa cuối:
Upvote 0
........
Đú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:
Upvote 0
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:
Upvote 0
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:
Upvote 0
----------

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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
Hỏi thiệt bạn cho mình biết mực đích làm gì được hông tòmo quá.
 
Upvote 0
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:
Upvote 0
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:
Upvote 0
Web KT
Back
Top Bottom