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
– Patrick Lepelletier, Commented Jul 4, 2016 at 7:51

Shai Rado · Accepted Answer · 2016-07-03 09:17:33Z

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 3, 2016 at 9:17

answered Jul 3, 2016 at 7:42

Shai Rado

33.7k7 gold badges32 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

arcadeprecinct Over a year ago

I thought ThisWorkbook was specifically about not taking different values but always be the workbook the code is in.

Shai Rado Over a year ago

@arcadeprecinct you are right, just need to fix the reference to the Sht object

arcadeprecinct Over a year ago

ah I missed the missing sheet reference in the next lines. So the problem is that ActiveSheet which is used by Cells is changing.

Shai Rado Over a year ago

@arcadeprecinct feel free to +1 :)

arcadeprecinct Over a year ago

Your solution is probably the right fix but tbh I'm not convinced by the explanation

|

user1016274 · Accepted Answer · 2016-07-03 09:35:45Z

0

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.

answered Jul 3, 2016 at 9:35

user1016274

4,2992 gold badges25 silver badges20 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

7 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

7 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related