How does the closest VBA window differ from the application runtime?
I ran into a very strange error in VBA and wondered if anyone could shed some light?
I am calling the worksheet function like this:
Dim lMyRow As Long lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)
This is for getting the string of the element I am entering. Under certain circumstances (although I can't figure out exactly when), odd things happen with the call to Match.
If I run this line in the immediate window, I get this:
lMyRow = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0) ?lMyRow 10
i.e. the lookup is performed and lMyRow gets its assigned value. If I let this statement execute in real code, then lMyRow gets the value 0.
It seems very strange! I don't understand how doing something in the immediate window can succeed in assigning a value, when the same call at the same point in the program execution can give the value 0 when it works fine in code!
The only thing I can think of is that this is some kind of weird thing, but I get the same behavior if the variable I assign to is an int, a double, or even a string.
I don't even know where to start - help !!
source to share
The only difference between a direct window and a normal code run is scope. The code in the immediate window runs in the current application area. If nothing is currently running, it means the global scope. Code entered into a VBA function is limited to the scope of the function.
So, I'm guessing one of your variables is out of scope.
I would put a breakpoint in your function on that line and add a watch to see which variable is not set.
And if you don't have Option Explicit at the top of your vba code module, you must add it.
source to share
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
This may sound silly, but are you sure that all the parameters of the Match function match in your macro and in the immediate window? Maybe the range object has changed?
source to share
Thanks for the answers guys - I should have been a little more specific on how I make 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 fuzzy thing is that I am passing two parameters to a function, so I cannot see how they can be different inside and outside the function. However, I still have absolutely no idea what is causing this, especially since it is a really intermittent problem
Is there an easy way to compare a range object in the context of a function with a range object in the Immediate context window and indicate if they are different? Given that the range is a reference type, it seems to me that I can just compare the two pointers, but I don't know how to do that in VBA!
I'm using Excel 2007 by the way, although I'm not sure if that matters.
source to share