vba excel vba中的引用变量名
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18336006/
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
reference variable name in excel vba
提问by user2697166
I have a load of variable names in a spreadsheet column. These variables are defined and have values in the modules I have in my project.
我在电子表格列中有大量变量名称。这些变量在我的项目中的模块中定义并具有值。
I want to have a code that references the variable name in a spreadsheet, and returns the value that it have in the module and pastes the value in another spreadsheet i.e
我想要一个代码来引用电子表格中的变量名称，并返回它在模块中的值并将该值粘贴到另一个电子表格中，即
Sub code()
dim variable1 as integer
variable1 = 2
End sub
sheet 1: cell A1: variable1
Sub code2()
sheet(2).range("a1").value = sheet(1).range("a1").value
end sub
sheet 2: cell A1: 2
回答by Chel
There is no way to ask for a variable by name in VBA during runtime. During compilation all variable names are stripped away, and at runtime the variables are referenced just with memory locations. Also, if the variable is declared within a sub, it onlyexists while that sub is being executed. If you try to access it later, something else will be using its memory location.
在运行时，无法在 VBA 中按名称请求变量。在编译期间，所有变量名都被删除，在运行时变量只用内存位置引用。此外，如果变量是在 sub 中声明的，则它仅在执行该 sub 时存在。如果您稍后尝试访问它，其他东西将使用其内存位置。
The only way to do this is to declare all the variables at module level, and then have a function which explicitlymaps variable names to these variables:
唯一的方法是在模块级别声明所有变量，然后使用一个函数将变量名显式映射到这些变量：
Private variable1 As Integer
Sub code()
variable1 = 2
End Sub
Sub code2()
Sheets(2).Range("a1").Value = VariableByName(Sheets(1).Range("a1").Value)
End Sub
Function VariableByName(VarName As String) As Variant
Select Case VarName
Case "variable1": VariableByName = variable1
End Select
End Function
Actually, your best option is to forget about using variables and use names instead:
实际上，您最好的选择是忘记使用变量并改用名称：
Sub code()
Names.Add "variable1", 2, Visible:=False
End Sub
Sub code2()
Sheets(2).Range("a1").Value = Evaluate(Sheets(1).Range("a1").Value)
End Sub
But when you go that route, if you need to access the variable in VBA you can't just say variable1, you need to use code like this:
但是当你走那条路时，如果你需要访问 VBA 中的变量，你不能只说variable1，你需要使用这样的代码：
Sub code3()
Dim variable1 As Integer
variable1 = Evaluate("variable1") 'bring it into a normal variable
variable1 = variable1 * 2 'now you can use it like a normal variable
Names("variable1").RefersTo = variable1 'need to save it when you're done
End Sub
回答by mmh
This worked in Excel 2010
这在 Excel 2010 中有效
variable1 = [variable1].Value
VBA treats [variable1] (with brackets) as a variant that references the named cell.
VBA 将 [variable1]（带括号）视为引用命名单元格的变体。
-mmh
-mmh
