reference variable name in excel 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

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

This worked in Excel 2010

variable1 = [variable1].Value

VBA treats [variable1] (with brackets) as a variant that references the named cell.

-mmh