VBA 即时窗口与应用程序运行时有何不同？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/350227/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How does the VBA immediate window differ from the application runtime?
提问by Jon Artus
I've encountered a very strange bug in VBA and wondered if anyone could shed some light?
我在 VBA 中遇到了一个非常奇怪的错误，想知道是否有人可以解释一下？
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.
这是为了获取我传入的项目的行。在某些情况下（虽然我无法确定确切的时间），对 Match 函数的调用会发生奇怪的事情。
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.
即查找工作，并且 lMyRow 获得分配给它的值。如果我让该语句在实际代码中执行，则 lMyRow 的值为 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!
这看起来很奇怪！我不明白在立即窗口中执行某事如何成功分配值，其中相同的调用，在程序执行的同一点，当它在代码中正常运行时，可以给出值 0！
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.
我唯一能想到的是它是一些奇怪的转换，但是如果我分配的变量是 int、double 甚至是 string，我会得到相同的行为。
I don't even know where to begin with this - help!!
我什至不知道从哪里开始 - 救命！！
回答by Will Rickards
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.
立即窗口和正常代码运行之间的唯一区别是范围。即时窗口中的代码在当前应用程序范围内运行。如果当前没有任何东西在运行，这意味着一个全局范围。放入 VBA 函数时的代码仅限于函数作用域。
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.
如果您的 vba 代码模块顶部没有 Option Explicit，则应该添加它。
回答by Dick Kusleika
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
您没有分配函数名称，因此该函数将始终返回零（如果您期望的是 Long）。看来你应该有
makeTheLookup = lMyRow
makeTheLookup = lMyRow
at the end of your function.
在你的函数结束时。
回答by CABecker
I don't know if you are still looking at this or not but I would have written it this way:
我不知道你是否还在看这个，但我会这样写：
Function makeTheLookup(vItemID As Variant, rngMyRange as Range)as Long
makeTheLookUp = WorksheetFunction.Match(vItemID, rngMyRange.Columns(1), 0)
End Function
回答by Jon Artus
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!
是否有任何简单的方法可以将函数上下文中的范围对象与立即窗口上下文中的范围对象进行比较并判断它们是否不同？鉴于该范围是一种引用类型，感觉就像我应该能够比较两个指针，但我不知道如何在 VBA 中做到这一点！
I'm using Excel 2007 by the way, although I'm not sure if that makes any difference.
顺便说一下，我正在使用 Excel 2007，但我不确定这是否有任何区别。
回答by Devdatta Tengshe
As will has mentioned above, It most definitely seems like a problem of scope. Or an Obscure Bug.
正如上面提到的，这绝对是一个范围问题。或者一个模糊的错误。
I would try to use Debug.printstatements, as well as Watch, and see if they match up, and take it from there.
我会尝试使用Debug.print语句和 Watch，看看它们是否匹配，然后从那里获取。
回答by Dirk Vollmar
I cannot reproduce the problem with Excel 2007.
我无法重现 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?
这听起来可能很愚蠢，但您确定 Match 函数的所有参数在您的宏和直接窗口中都相同吗？也许范围对象已更改？
