Excel VBA中的引用变量名称 [英] reference variable name in excel vba

问题描述

我在电子表格列中有很多变量名.这些变量已定义，并在我项目中的模块中具有值.

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

推荐答案

在运行期间，无法通过VBA在名称中请求变量.在编译期间，所有变量名都被删除，并且在运行时，仅使用内存位置来引用变量.另外，如果在子对象中声明了该变量，则在执行该子对象时它仅存在.如果您稍后尝试访问它，则其他位置将使用它的内存位置.

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 only exists while that sub is being executed. If you try to access it later, something else will be using its memory location.

唯一的方法是在模块级别声明所有变量，然后具有一个显式将变量名映射到这些变量的函数:

The only way to do this is to declare all the variables at module level, and then have a function which explicitly maps 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

但是，当您走这条路线时，如果您需要访问VBA中的变量，则不能只说variable1，您需要使用如下代码:

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:

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

这篇关于Excel VBA中的引用变量名称的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！