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

Answer 1

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'.}