vba 在VBA中通过变量传递给子
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16217537/
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
Passing through variable to sub in VBA
提问by user2320210
This is probably very basic, but I have tried so many things and the always give errors.
这可能是非常基本的，但是我尝试了很多事情并且总是出错。
Basically what I'm trying to do is run a procedure every time a cell gets changed in Excel. So I'm using Sub Worksheet_Change(ByVal Target As Range). All works well, but in this procedure, I'm calling another sub procedure several times.
基本上我想要做的是每次在 Excel 中更改单元格时运行一个程序。所以我使用 Sub Worksheet_Change(ByVal Target As Range)。一切正常，但在此过程中，我多次调用另一个子过程。
I want to reuse the 'Target' value in this procedure, but for some reason, I can't find how to do it. I tried placing 'Public rTarget As Range' and do a 'rTarget = Target' at the beginning of the procedure. But the rTarget stays empty when I call the sub procedure.
我想在这个过程中重用“目标”值，但由于某种原因，我找不到如何去做。我尝试将“公共 rTarget 作为范围”并在程序开始时执行“rTarget = Target”。但是当我调用子过程时 rTarget 保持为空。
How do I make this work? I'm now adding Target as one of the variables to to subroutine, but that just looks stupid.
我如何使这项工作？我现在将 Target 作为变量之一添加到子程序中，但这看起来很愚蠢。
Thanks!
谢谢！
回答by John Bustos
Private Sub Worksheet_Change(ByVal Target As Range)
MySub Target
End Sub
Sub MySub(ByVal Target As Range)
' Your sub code goes here and can work with the Target Range from the Worksheet_Change Event
End Sub
回答by Dick Kusleika
Passing Target as a variable is how you should do it. You want to keep your variables in the tightest scope possible and passing them as arguments is the tightest.
将 Target 作为变量传递是您应该如何做的。您希望将变量保持在尽可能小的范围内，并将它们作为参数传递是最紧凑的。
If you're passing the variable to a bunch of different procedures in the same module, it may make sense to have a module-level variable. That looks like this
如果您将变量传递给同一模块中的一堆不同过程，那么拥有一个模块级变量可能是有意义的。看起来像这样
Private mrTarget As Range
Private Sub Worksheet_Change(ByVal Target As Range)
Set mrTarget = Target
MySub
End Sub
Private Sub MySub()
Debug.Print mrTarget.Address
End Sub
Based on your questions, it's just one sub that gets called repeatedly. In that case, I would stick with passing it as an argument.
根据您的问题，这只是一个被反复调用的子程序。在那种情况下，我会坚持将它作为参数传递。
