0

I have written a code that is able to generate a variable string that matches another variable name. But when I try to use that uppermost variable as cell value it pastes the variable name but not it's value.

So I have something like:

Public General as string
General = 10

and at some point I end up with a variable

dim CurrentValue as string 
CurrentValue = General

Where I mean to get that "10" but not "General" when I paste that CurrentValue as cell value, so I wonder if it's possible to do and if so then how could I do that?

Eduards
  • 68
  • 2
  • 20
  • 1
    Use a Scripting.Dictionary. – BigBen Sep 02 '21 at 13:43
  • Sorry, but could you be more clear? – Eduards Sep 02 '21 at 13:43
  • 1
    Use a dictionary. Add an item with a key of "General" and value of 10. Then you can look up "General" from the dictionary and return 10. – BigBen Sep 02 '21 at 13:44
  • I mean I have a module where I have all the public variables outside the subroutine and their set values in public subroutine – Eduards Sep 02 '21 at 13:46
  • `CurrentValue = General` will set CurrentValue to 10... – Vincent G Sep 02 '21 at 13:47
  • @Vincent G Of course it will but I have a dynamic string for CurrentValue that gets generated to match one of the hundreds of public variables names – Eduards Sep 02 '21 at 13:48
  • 1
    `"General"` (a String that looks like a variable) is not the same as `General` (a variable that is a String). Your current setup won't work. So use a dictionary with all your current public variables and their corresponding values. – BigBen Sep 02 '21 at 13:49
  • How do I write that? – Eduards Sep 02 '21 at 13:50
  • 1
    https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/dictionary-object – BigBen Sep 02 '21 at 13:51
  • 4
    A dictionary is a very old and very common object in most programming languages. Definitely something worth familiarizing yourself with. It's worth noting that anytime you think "Variable variables" or "Variable variable names" you are headed hard and fast towards a very bad time. The answer is almost always "Use an array", or in this case "Use a dictionary". – JNevill Sep 02 '21 at 14:00
  • 1
    Possibly helpful, too: [Type statement](https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/type-statement) as well as [Enum statement](https://learn.microsoft.com/de-de/office/vba/language/reference/user-interface-help/enum-statement) as well as Chick Pearson's great site at [Enum Variable Type](http://www.cpearson.com/Excel/Enums.aspx) - @Eduards – T.M. Sep 02 '21 at 14:23

1 Answers1

1

If you put your Global in the ThisWorkbook module (a worksheet module would also work) you could do something like this:

Option Explicit

Public General As Long

Function GetVar(varName As String)
    GetVar = CallByName(Me, varName, VbGet)
End Function

Then in a regular module:

Sub Tester()
    ThisWorkbook.General = 10
    Debug.Print ThisWorkbook.GetVar("General") ' >> 10
End Sub

In general though, a use case like this really signals a need to rethink things...

Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • 2
    FYI - do not take my example as an endorsement of what you're doing here... Referring to variables by their names as strings is generally a code smell.... – Tim Williams Sep 02 '21 at 16:04
  • That last sentence needs bolded, highlighted, underlined, italicized... – BigBen Sep 02 '21 at 16:14