How to Automatically update cells in Excel that uses a particular VBA Code?

Question

I have a sheet in Excel with cells that uses this VBA macro (using two parameters, it count the number of occurrence of arg#1 in the range of Arg#2 accross all sheets, and return the total count):

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
Dim wbcs As Long, rng As Range, addr As String
For Each s In Worksheets
    addr = InRange.Address
    Set rng = s.Range(addr)

    wbcs = wbcs + Application.WorksheetFunction.CountIf(rng, SearchFor)
Next s
WBCountString = wbcs
End Function

The macro is used as a function in a cell as shown below, which uses cells located in A:A as parameters:

When I change a A:A cell value, the value of the cell that uses the script do not change, and I have to click on the formula and press "Enter" for the value to be updated.

I tried using this function in "ThisWorkbook", I get a "Change" message box but the cells in F:F do not update or change value:

Private Sub Workbook_SheetChange(ByVal Sh As Object, _
 ByVal Source As Range)
 ' runs when a sheet is changed
    MsgBox "Change."
    Application.Calculate
End Sub

How can I make so that the cells that uses this macro updates every time any of the cells A:A on any of the sheet of the workbook are updated?

I tried using it but it did not work, and I do not understand how to apply it to this problem. I have seen pages such as this one but was not able to apply it to my problem (a function that is called in particular Excel cells). Would you have an example code that apply to this specific problem? E.g. How to mention to update the cells that uses the function? — Alexandre Willame
I am recommending the workbook-level event, which occurs when any sheet is changed. You used the worksheet-level event. — BigBen
I did use the code in the URL I provided in the "ThisWorkbook" section of VBAProject, but I do not know how to apply it to this specific problem, as I cannot just call the WBCountString function in a "workbook_sheetchange" function, as it takes arguments that are specified in the Excel sheet in Column F, and are different for every cell. Would you have an example code that apply to this specific problem? E.g. How to mention to update the cells that uses the function? — Alexandre Willame
You could restrict the calculation to the range in question. — BigBen

Ricardo Diaz Ricardo Diaz · Accepted Answer · 2020-02-10T18:50:50

I refactored some of the code so the variables are easier to understand

Key here is add the Application.Volatile so it's recalculated in each workbook change.

Note that if the workbook is heavy in formulas (this approach will slow your workbook calculation)

Code:

Public Function WBCountString(SearchFor As String, InRange As Range) As Long
    Dim targetSheet As Worksheet
    Dim lookupRange As Range

    Dim counter As Long

    Dim lookupAddress As String

    For Each targetSheet In Worksheets
        lookupAddress = InRange.Address
        Set lookupRange = targetSheet.Range(lookupAddress)

        counter = counter + Application.WorksheetFunction.CountIf(lookupRange, SearchFor)

    Next targetSheet

    WBCountString = counter

    ' Recalculate with every change in workbook
    Application.Volatile

End Function

Let me know if it works

How to Automatically update cells in Excel that uses a particular VBA Code?

1 Answers