Assign a variable value to a variable key

Question

I need to create variables on the fly in Excel VBA, as for example users type Name and Age inside variable NAME and AGE. Suppose user type PETER in NAME and 34 in AGE, I want VBA to create a variable PETER with contents 34. This can be done easily in PHP.

I was able to make part of it works:

Person = Cells(10, 1).Value 'suppose it is "PETER"
Age = Cells(11, 1).Value    'suppose it is "34"

Creating the Variable

Names.Add Name:=Person, RefersTo:=Age

Retrieving it

Debug.Print Person              ' prints PETER
Debug.Print Evaluate(Person)    ' prints 34
Debug.Print PETER               ' Variable PETER does not exist

The above doesn't help me, since it is the same as creating a Variable "NAME" with contents PETER, when evaluated shows 34. I need Variable PETER with contents 34.

The problem is;

PERSON="PETER"
AGE="34"
Name.Add Name:=PERSON RefersTo:=Age

creates a variable PERSON=34, not PETER=34.

Name.Add Name:=Evaluate(Person) ... does not work.

Any help please?

Update from 03/22/2019 1:38pm

Following some tips and discoveries, Dictionary is what solve my problem.

Sub T1() 
  Dim MAGIC As New Scripting.Dictionary 
  AnyVar = "R10"               'sets [AnyVar] = "R10"
  Magic(AnyVar) = 25           'sets [R10] = "25" 
  Magic("ARTEN") = "R10"       '[ARTEN] points to R10
  Magic("R10") = 33            'sets [R10] = "33"
  N5 = Magic("ARTEN")          'sets N5 = "R10"
  N6 = Magic(N5)               'sets N6 = [R10] (33)
  Debug.Print N5 & " = " & N6 
End Sub

It printed "R10 = 33", exactly what I was willing to have. So now, with dictionary I can create variables on fly and find or change their values at easy.

To create a new entry on Magic (dictionary):

Magic.Add key:="name", Item:=33

If a text between quotes, the new entry will be that text. If without quotes, it assumes it is a variable containing the name/value.

V25 = "foo"
V26 = 33
Magic.Add key:=V25, Item:=V26

Will create an entry name "foo" with contents 33.

To change any existent entry, just

V25 = "foo"
V26 = 33
V29 = "fee"
V30 = "faa"
Magic(V25) = V26                '[foo] = 33
Magic("foo") = 38               '[foo] = 38
Magic(V29) = 39                 '[fee] = 39
Magic(V25) = Magic(V29) + 1     '[foo] = [fee]+1 = 40 
dictionary(V30) = V25           '[faa] = [V25] = "foo"
debug.print Magic(V30)          'prints "foo"
debug.print Magic(Magic(V30))   'prints 40
V40 = "Paul"
V41 = "Smith"
Magic(V40) = V41
debug.print Magic("Paul")       'print Smith

If entry name "foo" exists, it will change the contents to the contents of V26, if the entry does not exist, it creates it.

So, you don't need to use the formal way to create an entry.

Magic.Add key:=V25, Item:=V26

You could perhaps use a dictionary, eg https://stackoverflow.com/questions/38254337/how-to-create-dynamic-variable-names-vba ? — SJR, Mar 21 '19 at 17:32

Storax · Answer 1 · 2019-03-21T19:19:39.277

1

You could also use a simple class named cPerson

Option Explicit

Public pName As String
Public pAge As Long

An example might look like that

Option Explicit

Sub TestIt()
Dim dict As New Scripting.Dictionary
Dim person As cPerson
Dim key As Variant

    Set person = New cPerson
    With person
        .pName = "Peter"
        .pAge = 34
        dict.Add .pName, person
    End With


    Set person = New cPerson
    With person
        .pName = "John"
        .pAge = 43
        dict.Add .pName, person
    End With

    For Each key In dict.Keys
    With dict(key)
        Debug.Print .pName, .pAge
    End With
    Next

    Debug.Print dict("Peter").pAge
    Debug.Print dict("John").pAge

End Sub

Update Within Excel you need to set a reference to the VB script run-time library.

To reference this library, load the Visual Basic Editor (ALT+F11)
Select Tools > References from the drop-down menu
A listbox of available references will be displayed
Tick the check-box next to 'Microsoft Scripting Runtime'
The full name and path of the scrrun.dll file will be displayed below the listbox
Click on the OK button.

edited Mar 21 '19 at 19:19

answered Mar 21 '19 at 17:44

Storax

11,158
3
16
33

It gives me Compile error, "User Defined Type not defined", on the "Dim dict As New Scripting.Dictionary" – Wagner Lipnharski Mar 21 '19 at 18:23
1

Yes, of course, it does. You need to add a reference to MS Scripting library. Do you know ho to do that? – Storax Mar 21 '19 at 18:48
Yes, I followed the "Update" above and checkmarked the 'Microsoft Scripting Runtime' on Tools-Reference, but now the error is "User-defined type not define", on the line "Dim person As cPerson". The checkmarked selections on References are: Visual Basic for Applications, Microsoft Excel 15.0 Object Library, OLE automation, Microsoft Office 15.0 Object Library and Microsoft Scripting Runtime. Thanks for any help. – Wagner Lipnharski Mar 21 '19 at 19:21
1

Do you know how to add a class to a VBA project and name it cPerson? Look [here](https://excelmacromastery.com/vba-class-modules/#Creating_a_Simple_Class_Module) – Storax Mar 21 '19 at 19:37
1

You could edit your original post and update it accordingly. – Storax Mar 22 '19 at 15:43
Storax, thank you for the suggestion to edit the original post, I just did it. – Wagner Lipnharski Mar 22 '19 at 19:26

Assign a variable value to a variable key

1 Answers1