VBA UDF changes the values ​​on all sheets. How to limit yourself to one?

Question

I created a UDF that works on one sheet. The problem occurs with multiple sheets. If I have a formula on several sheets, and if I (re) upload it to one sheet, it also changes the result on ALL other sheets.

Why is this happening? I do not use ActiveWorksheet or Active Cell or the like.

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 sheet specified for any of the three times when you use the Range.Cells property , so the parent sheet is set to ActiveSheet by default . This can be fixed with With ... End With statement , which provides a link to a worksheet for 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 

Inside With ... End With, all Cells are links like .Cells to show that the parent sheet is the one mentioned in With ... End With.

You do not need to explicitly compare the ISERROR or VBA worksheet function IsError to True . He already knows whether it is True or False.

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

---

¹ _{Settings Require declaration of variables in VBE tools ► Parameters ► The editor's properties page will place Option Explicit at the top of each newly created code sheet. This will avoid stupid coding errors, such as spelling errors, and also affect the use of the correct variable type in the variable declaration. Variables created on the fly without declaration are a variant / object type. Using Option Explicit is widely considered "best practice."}