Excel User Defined Function evaluates to zero if another workbook is opened

Question

I have created a user defined function (UDF) in Excel VBA, which picks up contents from 2 columns and evaluates to a result. The UDF evaluates correctly when no other workbooks are open, but changes to zero when any other workbooks are opened simultaneously. I think the issue is in the first few steps, where I read the input:

Set Sheet = ThisWorkbook.Worksheets(inputSheet)
For i = 0 To numrows
    array_multi(i, 0) = Cells(inputRow1 + i, inputCol1)
    array_multi(i, 1) = Cells(inputRow2 + i, inputCol2)
Next

Can someone help me resolve the issue here? Let me know if you require more details.

Answer 1

In your code you use the Sheet which you never use when assigning values to your array.

Set Sheet = ThisWorkbook.Worksheets(inputSheet)
array_multi(i, 0) = Cells(inputRow1 + i, inputCol1)
array_multi(i, 1) = Cells(inputRow2 + i, inputCol2)

Try using the Sht.Cells to make sure your array reads the values from the right worksheet and workbook.

Set Sht = ThisWorkbook.Worksheets(inputSheet)
For i = 0 To numrows
    array_multi(i, 0) = Sht.Cells(inputRow1 + i, inputCol1)
    array_multi(i, 1) = Sht.Cells(inputRow2 + i, inputCol2)
Next