VBA UDF changes values on ALL sheets. How to limit to one?

Question

I have made a UDF that works on a single sheet. The problem occurs with multiple sheets. If I have the formula on multiple sheets, and if I (re-)load it on one sheet, it changes the output in ALL other sheets, too.

Why does this happen? I am not using ActiveWorksheet or Active Cell or similar.

Function customreturn(security As Range, datacheck As Range) As Variant

    Dim row_num As Integer
    Dim row_num2 As Integer
    Dim price1 As Double
    Dim price2 As Double
    Dim perfo As Double
    Dim blank_end As Boolean

    row_num = security.Row
    col_num = security.Column
    row_num2 = row_num + 1
    col_num2 = datacheck.Column

    If WorksheetFunction.IsError(datacheck.Value) = True Then
        customreturn = "No data"
    Else
        price1 = Cells(row_num, col_num).Value

        Do While WorksheetFunction.IsError(Cells(row_num2, col_num2).Value) = True
            row_num2 = row_num2 + 1
        Loop

        price2 = Cells(row_num2, col_num).Value

        perfo = price1 / price2 - 1

        customreturn = perfo
    End If
End Function

It reloads the formula, meaning it recalculates? Or it recalculates, and uses the same information across all sheets, instead of using the data on the sheet the formula is in? (...does that make sense?). It might have something to do with [Volatility](http://www.mrexcel.com/forum/excel-questions/271165-udf-volatile-vs-not.html) — BruceWayne, Feb 12 '16 at 15:11
1) If I load the formula in one sheet manually 2) It uses the same value across all other sheets - yes — sandboxj, Feb 12 '16 at 15:15
Also, try adding a worksheet variable (`Dim ws as Worksheet`, `Set ws = Sheets(security.parent.name)`, then just add `ws.` before all uses of `Cells()`. That will make sure the data is coming from the sheet `security` is on. — BruceWayne, Feb 12 '16 at 15:17

score 5 · Accepted Answer · edited May 23 '17 at 12:31

There is no parent worksheet specified for any of the three times you use the Range.Cells property so the parent worksheet is defaulted to the ActiveSheet property. This can be rectified with a With ... End With statement that provides a worksheet reference to one of the range parameters' Range.Parent property.

Function customreturn(security As Range, datacheck As Range) As Variant
    Dim row_num As Long, row_num2 As Long, col_num As Long, col_num2 As Long
    Dim price1 As Double, price2 As Double, perfo As Double
    Dim blank_end As Boolean

    row_num = security.Row
    col_num = security.Column
    row_num2 = row_num + 1
    col_num2 = datacheck.Column

    With security.Parent
        If IsError(datacheck) Then
            customreturn = "No data"
        Else
            price1 = .Cells(row_num, col_num).Value

            Do While IsError(.Cells(row_num2, col_num2))
                row_num2 = row_num2 + 1
            Loop

            price2 = .Cells(row_num2, col_num).Value

            perfo = price1 / price2 - 1

            customreturn = perfo
        End If
    End With
End Function

Within the With ... End With, all of the Cells are references as .Cells to show that the parent worksheet is the one referred to in the With ... End With.

You don't have to explicitly compare the worksheet's ISERROR or VBA's IsError function to True. It already knows if it is True or False.

It was pointed out (thanks BruceWayne) that you had two undeclared variables, col_num and col_num2. This can be avoided by adding Option Explicit¹ to the top of each code sheet in the declarations area.

¹ _{Setting Require Variable Declaration within the VBE's Tools ► Options ► Editor property page will put the Option Explicit statement at the top of each newly created code sheet. This will avoid silly coding mistakes like misspellings as well as influencing you to use the correct variable type in the variable declaration. Variables created on-the-fly without declaration are all of the variant/object type. Using Option Explicit is widely considered 'best practice'.}

So I was onto something when suggesting to make explicit the sheet? Yay! Nice to know I'm on the right path in UDFs (I'm still learning them). Nice explanation! (FYI there are some undeclared variables, `col_num` and `col_num2`). I'd also recommend to add `Option Explicit` anytime in VBA. — BruceWayne, Feb 12 '16 at 15:21
Yes, that seems to have been the issue as best as I can tell at this point; the OP can confirm. Now-a-days, every time I see `Cells` and not `.Cells` alarm bells go off in my head. — , Feb 12 '16 at 15:25
... and thanks for noting the undeclared vars; I've amended my answer to reflect that. — , Feb 12 '16 at 15:30
Yes, thanks. I can confirm that this works. I have also added Option Explicit as you have mentioned. — sandboxj, Feb 12 '16 at 15:31

VBA UDF changes values on ALL sheets. How to limit to one?

1 Answers1