CODEWITHSUNDEEP
vbaexcel
KishorePalli
KishorePalli

Reputation: 21

how to pass variable name through cell reference in vba

actually I want to pass variable name through cell reference in sheet. If that variable is present in the declared list of the module then corresponding value should come into the reference cell. can we acheive it anyway? If yes ,Please guide me. It will be really helpful. Thanks in advance.

Upvotes: 1

Views: 695

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

A variable is a symbol. A string literal is a value. VBA has no built-in capabilities, there's no way you can cleanly get a variable by name and print its value.

What you can do, is have a Dictionary with the names as keys, and the values as, well, values:

Private Foo As Long
Private Bar As Long
Private Fizz As Long
Private Buzz As Long
Option Explicit

Public Function GetValueByName(ByVal name As String) As Long    
    With New Scripting.Dictionary
    'initialize every time, so the values are always up-to-date
        .Add "Foo", Foo
        .Add "Bar", Bar
        .Add "Fizz", Fizz
        .Add "Buzz", Buzz
        GetValueByName = .Item(name)
    End With
End Function

Allow me to seriously question the need/requirement for accessing programmatic symbols by name via a worksheet function though: there's a reason it's not supported.

Upvotes: 1

Related Questions