Về vấn đề cập nhật cho hàm tự tạo khi dữ liệu thay đổi

[ndu96081631]

Tôi xây dựng hàm SUMIF trên nhiều sheet, code như sau:

Option Explicit
Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
  Dim Sh As Worksheet
  Application.Volatile
  For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> ActiveSheet.Name Then
      SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), _
                DK, Sh.Range(VungKQ.Address))
    End If
  Next Sh
End Function

Code không có vấn đề, nhưng khi dử liệu ở các sheet thay đổi, tôi phải bấm F9 thì kết quả của hàm mới cập nhật
Tôi muốn hỏi: Có cách nào để hàm tự cập nhật mà không cần bấm F9 không?

 

[cadafi]

Em thử bỏ dòng IF đi thì Code chạy OK! Nhưng kết quả sẽ lấy luôn sheet hiện hành! Không biết có cách nào loại trừ Sheet hiện hành mà không dùng IF không?
[highlight=vb]
Option Explicit
Public Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
Dim Sh As Worksheet
Application.Volatile
For Each Sh In ThisWorkbook.Worksheets
SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), _
DK, Sh.Range(VungKQ.Address))
Next Sh
End Function
[/highlight]

 

[boyxin]

Tôi xây dựng hàm SUMIF trên nhiều sheet, code như sau:

Option Explicit
Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
  Dim Sh As Worksheet
  Application.Volatile
  For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> ActiveSheet.Name Then
      SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), _
                DK, Sh.Range(VungKQ.Address))
    End If
  Next Sh
End Function

Code không có vấn đề, nhưng khi dử liệu ở các sheet thay đổi, tôi phải bấm F9 thì kết quả của hàm mới cập nhật
Tôi muốn hỏi: Có cách nào để hàm tự cập nhật mà không cần bấm F9 không?

Công thức không tự cập nhật do nguyên nhân của dòng này:

If Sh.Name <> ActiveSheet.Name Then ...

gây ra lỗi circular (có sự chồng chéo giãu các vùng trong formula). Sửa lại thành

If Sh.Name <> "Main" Then ...

thì mọi vấn đề lại đâu vào đấy cả (tự cập nhật luôn)

Viết thế này cho gọn

Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Variant
  Dim Sh As Worksheet, Temp As Variant
With Application
    .Volatile
    For Each Sh In ThisWorkbook.Worksheets
        If Sh.Name <> "Main" Then
            Temp = Temp + .SumIf(Sh.Range(VungDK.Address), DK, Sh.Range(VungKQ.Address))
        End If
    Next Sh
    SumIfMSh = Temp
End With
End Function

 

[ndu96081631]

Em thử bỏ dòng IF đi thì Code chạy OK! Nhưng kết quả sẽ lấy luôn sheet hiện hành! Không biết có cách nào loại trừ Sheet hiện hành mà không dùng IF không?
[highlight=vb]
Option Explicit
Public Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
Dim Sh As Worksheet
Application.Volatile
For Each Sh In ThisWorkbook.Worksheets
SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), _
DK, Sh.Range(VungKQ.Address))
Next Sh
End Function
[/highlight]

Lấy luôn sheet hiện hành cũng được, nhưng mình sợ nhất là bị Circular ---> Vì thế mới tránh sheet hiện hành
Mặc khác trên thực tế thì sheet Main này sẽ dùng để tổng hợp, đương nhiên sẽ không cộng chính nó
Khó nhỉ?

. Sửa lại thành

If Sh.Name <> "Main" Then ...

thì mọi vấn đề lại đâu vào đấy cả (tự cập nhật luôn)

Hàm tự tạo mà Boyxin, đâu thể sửa kiểu đó ---> Còn gì là tính tổng quát

 

[boyxin]

Lấy luôn sheet hiện hành cũng được, nhưng mình sợ nhất là bị Circular ---> Vì thế mới tránh sheet hiện hành
Mặc khác trên thực tế thì sheet Main này sẽ dùng để tổng hợp, đương nhiên sẽ không cộng chính nó
Khó nhỉ?

Hàm tự tạo mà Boyxin, đâu thể sửa kiểu đó ---> Còn gì là tính tổng quát

Vậy thì đặt điều kiện trong IF để tránh SUMIF cả vùng chứa kết quả là hết lỗi Circular
Chính nó là nguyên nhân gây ra lỗi không cập nhật

Hoặc để vùng kết quả không trùng với vùng cần tính trong công thức thì bỏ hẳn IF ...

 

[cadafi]

Vậy anh thử code sau xem, hình như lâu lâu phải có Code rác nó mới chịu!
[highlight=vb]
Public Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
Dim Sh As Worksheet
Application.Volatile
For Each Sh In ThisWorkbook.Worksheets
If Sh.Name = ActiveSheet.Name Then
SumIfMSh = SumIfMSh
Else: SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), _
DK, Sh.Range(VungKQ.Address))
End If
Next Sh
End Function
[/highlight]

Sorry em nhầm, code này ra kết quả trật lất, kỳ vậy ta!

 

[vbavn]

[highlight=vb]
Public Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
Dim Sh As Worksheet
...
Application.Volatile
...
End Function
[/highlight]

Xin chú ý cho, nếu chúng ta dùng
[highlight=vb]
Application.Volatile
[/highlight]
cho UF, thì bạn phải chú ý đến việc hàm này được dùng nhiều trong workbook của bạn hay không? Vì nếu dùng nhiều thì "bạn sẽ cảm thấy chán" nếu bạn để chế độ Calculation là Auto.

Vbavn

 

[ndu96081631]

Xin chú ý cho, nếu chúng ta dùng
[highlight=vb]
Application.Volatile
[/highlight]
cho UF, thì bạn phải chú ý đến việc hàm này được dùng nhiều trong workbook của bạn hay không? Vì nếu dùng nhiều thì "bạn sẽ cảm thấy chán" nếu bạn để chế độ Calculation là Auto.

Vbavn

Vậy xin bạn gợi ý cho cách sửa lại, hoặc làm thành 1 function mới cũng được

 

[hoangvuluan]

Bạn test thử hàm sau xem sao:

Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
  Dim Sh As Worksheet, aRng As Range
  Application.Volatile
  Set aRng = Application.Caller
  For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> aRng.Parent.Name Then
      SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), DK, Sh.Range(VungKQ.Address))
    End If
  Next Sh
End Function

 

[ndu96081631]

Bạn test thử hàm sau xem sao:

Function SumIfMSh(VungDK As Range, DK As Variant, VungKQ As Range) As Double
  Dim Sh As Worksheet, aRng As Range
  Application.Volatile
  Set aRng = Application.Caller
  For Each Sh In ThisWorkbook.Worksheets
    If Sh.Name <> aRng.Parent.Name Then
      SumIfMSh = SumIfMSh + WorksheetFunction.SumIf(Sh.Range(VungDK.Address), DK, Sh.Range(VungKQ.Address))
    End If
  Next Sh
End Function

Ai chà... cái vụ Application.Caller này hơi bị ngon à nha ... Cảm ơn nhiều lần...
Nhưng mà chưa được hiểu mấy (dù đã F1)
Tôi để ý thấy rằng: Sau khi cho Application.Caller vào thì có thể bỏ luôn Application.Volatile nó vẫn cập nhật tốt
Bạn có thể "diển nôm" sơ sơ về cái Caller này tí được không?

 

[hoangvuluan]

Application.Caller

Ai chà... cái vụ Application.Caller này hơi bị ngon à nha ... Cảm ơn nhiều lần...
Nhưng mà chưa được hiểu mấy (dù đã F1)
Bạn có thể "diển nôm" sơ sơ về cái Caller này tí được không?

Caller Property
Thuộc tính Caller trả lại thông tin về cách thức hàm (UDF) được gọi.
Nếu hàm được nhập vào 1 ô (hoặc một Range - nếu UDF là hàm mảng) thì giá trị trả lại chính là địa chỉ của ô (hoặc Range) mà hàm xuất hiện.
Ví dụ, tại B2 nhập UDF thì Caller trả lại chính ô B2, thông qua Range này, ta xác định được thuộc tính Parrent của nó (chính là Sheet chứa B2), nếu ta lặp lại việc dùng Parrent thì nhận được tham chiếu đến WorkBook chứa B2.

(Ngoài ra, 1 hàm UDF vẫn có thể được sử dụng ở đâu đó trong code, không nhất thiết phải dùng trong 1 ô để tính toán như vẫn thường dùng - chẳng hạn, có thể nó được gọi để tính từ các Sub, như Autpen...)

Bổ sung về Volatile:

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

Đặc điểm khi dùng volatile với giá trị True, đó là hàm sẽ được cập nhập bất kỳ khi nào có sự cập nhật xuất hiện. Ngược lại, khi có giá trị False thì hàm chỉ cập nhật khi có sự thay đổi giá trị của các đối số của hàm (ví dụ, với hàm tính tổng có ĐK như trên, thì khi có sự thay đổi ở các nguồn dữ liệu mà hàm có tham chiếu đến thì hàm đã tự cập nhật, không cần dùng Volatile).
Hơn nữa, thuộc tính này chỉ có hiệu lực khi hàm được sử dụng như 1 hàm tính toán trong sheet (nghĩa là không được gọi bới các Sub, hoặc Function khác trong Code).


Xem thêm Help!

Thân!

 

[Trojan]

Cái này đúng là hay đấy. Tôi cũng gặp vấn đề này mà không xử lý được. Cảm ơn anh em GPE.