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.

`Cells()` must be referenced in the sheet where they are supposed to be, or the values will be read in the `Activesheet`. — Patrick Lepelletier, Jul 04 '16 at 07:51

Shai Rado · Accepted Answer · 2016-07-03T09:17:33.010

2

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

edited Jul 03 '16 at 09:17

answered Jul 03 '16 at 07:42

Shai Rado

33,032
6
29
51

I thought `ThisWorkbook` was specifically about not taking different values but always be the workbook the code is in. – arcadeprecinct Jul 03 '16 at 08:52
@arcadeprecinct you are right, just need to fix the reference to the `Sht` object – Shai Rado Jul 03 '16 at 08:56
ah I missed the missing sheet reference in the next lines. So the problem is that `ActiveSheet` which is used by `Cells` is changing. – arcadeprecinct Jul 03 '16 at 09:02
@arcadeprecinct feel free to +1 :) – Shai Rado Jul 03 '16 at 09:06
Your solution is probably the right fix but tbh I'm not convinced by the explanation – arcadeprecinct Jul 03 '16 at 09:13
@arcadeprecinct If the `Cells()` declaration is not assigned a specific reference sheet (ie `Sht.Cells()`), it will automatically be assigned to the active sheet which can cause any number of problems. This issue is most commonly seen when using the Worksheet.Range() command where Range is filled by Cells. Even though the Range has been assigned to the Worksheet, the subsequent Cells declarations will be assigned to the active sheet, which will throw an error since a Range cannot be defined by cells on another sheet – RGA Jul 04 '16 at 07:39
@RGA yes I know, my problem was that it originally said that `ThisWorkbook` was changing depending on what's active. – arcadeprecinct Jul 04 '16 at 07:50

score 0 · Answer 2 · answered Jul 03 '16 at 09:35

The Cells object refers to the ActiveWorkbook.ActiveSheet unless qualified. You could use

With 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 i
End With

As Shai's answer is 100% right please accept and upvote his answer.

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

2 Answers2