How does the VBA immediate window differ from the application runtime?

Question

I've encountered a very strange bug in VBA and wondered if anyone could shed some light?

I'm calling a worksheet function like this:

Dim lMyRow As Long
lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)

This is intended to get the row of the item I pass in. Under certain circumstances (although I can't pin down exactly when), odd things happen to the call to the Match function.

If I execute that line in the immediate window, I get the following:

lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)
?lMyRow
10

i.e. the lookup works, and lMyRow gets a value assigned to it. If I let that statement execute in the actual code, I lMyRow gets a value of 0.

This seems very odd! I don't understand how executing something in the immediate window can succeed in assigning a value, where the same call, at the same point in program execution can give a value of 0 when it runs normally in code!

The only thing I can think of is that it's some odd casting thing, but I get the same behaviour taking if the variable to which I'm assigning is an int, a double, or even a string.

I don't even know where to begin with this - help!!

Answer 1

Hi Jon,

I cannot reproduce the problem with Excel 2007.

This was the code I used:

Sub test()

Dim vItemID As Variant
Dim lMyRow As Long
Dim rngMyRange As Range

    Set rngMyRange = ActiveWorkbook.Sheets(1).Range("A1:Z256")

    vItemID = 8
    lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)

    Debug.Print lMyRow

End Sub

It may sound stupid but are you sure that all parameters of the Match function are the same in your macro and in the immediate window? Maybe the range object has changed?

Answer 2

The only difference between the immediate window and normal code run is the scope. Code in the immediate window runs in the current application scope. If nothing is currently running this means a global scope. The code when put in a VBA function is restricted to the function scope.

So my guess is that one of your variables is out of scope.

I would put a breakpoint in your function on that line and add watches to find out which variable is not set.

And if you don't have Option Explicit at the top of your vba code module, you should add it.

Answer 3

Thanks for the answers guys - I should have been slightly more specific in the way I'm making the call below:

Function makeTheLookup(vItemID As Variant, rngMyRange as Range)

Dim lMyRow As Long
lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)

End Function

The odd thing is, I'm passing the two parameters into the function so I can't see any way they could be different inside and outside of the function. That said, I'm still entirely clueless as to what's causing this, particularly since it's a really intermittent problem

Is there any easy way of comparing the range object in the function context to the range object in the Immediate window context and telling if they're different? Given that range is a reference type, it feels like I should just be able to compare two pointers, but I've got no idea how to do that in VBA!

I'm using Excel 2007 by the way, although I'm not sure if that makes any difference.

Answer 4

As will has mentioned above, It most definitely seems like a problem of scope. Or an Obscure Bug.

I would try to use Debug.print statements, as well as Watch, and see if they match up, and take it from there.

Answer 5

You're not assigning the function name so that function will always return zero (if you're expecting a Long). It seems you should have

makeTheLookup = lMyRow

at the end of your function.