Recalculating Excel VBA Function on one sheet disrupts other sheets

Question

I made a function that counts the number of items in a given month.

Column A is the month, and Column B is the number of items in that month.

Cell B1 has:

=countItems(A1)

Excel data:

Excel data

Code:

Function countItems(month)
    Application.Volatile
    Dim count As Integer
    If Not (month = 0) Then
        count = 0
        Do While Not (Cells(month.row + count + 1, 3) = 0)
            count = count + 1
        Loop
        countItems = count
    Else
        countItems = ""
    End If
End Function

I dragged the formula down from B1 to B500 and it works properly for every month. The formula returns nothing if there is no month in the corresponding A cell.

I have multiple sheets using the same formula on similarly-structured data sets. Whenever the values in column B update for this Sheet 1, the other sheets will change too. However, Sheet 2 will update using Column C from Sheet 1.

If I have Sheet 2 recalculate, Sheet 1 will update using Column C from Sheet 2.

The function counts the number of items in a given month by checking how far down it can read in Column C before it finds the blank cell, indicating that the month is over. Sheet 2 has 1 item in the first month, but it will still return 3 due to Sheet 1 having 3 items (counts Row 1 through 3 and stops at Row 4). The second month of Sheet 2 begins on Row 3. But since the function is reading Column C from Sheet 1, it will run into the blank cell after counting 1 more item (counts Row 3 and stops at Row 4). Therefore no matter how many items are in Sheet 2 Month 2, it will return 1.

The function always uses the correct Column A, and only displays a number in Column B where there is date in Column A.

The consequence is that only 1 sheet can have the correct values, and doing that disrupts the other sheets.

I cannot solve this at the moment because I am new to VBA.

I have thought of making all of the function's cell references include a self-reference to the current cell's sheet, but I don't know how to do that and I don't know if it would work.

Edit: I couldn't make it work this way, but Application.Caller.Offset() with relative cell position worked as a solution. I am still wondering if there is a way to use absolute cell position though.

The sheets are not grouped together.

I believe the issue lies with the Cells property you are using. If no worksheet is specified, Cells will use the Activesheet meaning when you are recalculating within Sheet 2, the formulas in Sheet 1 will now take the referenced cell in Sheet 2 (the Activesheet) as opposed to the referenced cell in Sheet 1. — Jordan
I found a method of solving the problem. Instead of using Cells with row/column position, I used Application.Caller.Offset() and made all values relative to the cell that calls the function. It works without a problem, but I still wonder if there is a way to use absolute cell position to do the job. — Dylan Wang

user3598756 user3598756 · Accepted Answer · 2016-06-08T09:10:35

it's because there's a "time-space shift" between the range passed to the function and the range "felt" as the "caller" one by the function

you can see this behavior by modifying the function code as follows

Function countItems(month)
    Application.Volatile
    Dim count As Integer

    Dim r As Range
    Dim p As Variant, a As Variant

    Set r = Application.Caller '<~~ retrieve the actual "calling cell" of the current function "instance"
    p = r.Parent.Name
    a = r.Address

    MsgBox p & " vs " & month.Parent.Name & vbCrLf & a & " vs " & month.Address '<~~ compare the function "calling cell" vs the "passed cell"

    If Not (month = 0) Then
        count = 0
        Do While Not (Cells(month.Row + count + 1, 3) = 0)
            count = count + 1
        Loop
        countItems = count
    Else
        countItems = ""
    End If
End Function

and you'll see msgboxs prompts showing you differences between the function "calling cell" and "passed cell" addresses and/or sheets

so to avoid this behavior you could rely on the "calling range only", like follows:

Option Explicit

Function countItems(month)
    Application.Volatile
    Dim r As Range

    Set r = Application.Caller '<~~ retrieve the actual "calling cell" of the current function "instance"

    'the "calling cell" is the one with the called function in its formula, i.e. in column "B" as per your data structure. then ...
    If Not IsEmpty(r.Offset(, -1)) Then '<~~ ... the column with dates are one column to the left, and  ...
        Do While Not IsEmpty(r.Offset(countItems + 1, 1)) '<~~ ...  the values to be checked for are one column to the right
            countItems = countItems + 1
        Loop
    End If
End Function

Recalculating Excel VBA Function on one sheet disrupts other sheets

1 Answers