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.
-
Possible duplicate of reference variable name in excel vbaGSerg– GSerg2017-09-19 16:38:49 +00:00Commented Sep 19, 2017 at 16:38
Add a comment
|
1 Answer
A variable is a symbol. A string literal is a value. VBA has no built-in reflection 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.
