CODEWITHSUNDEEP
arraysvba
VBA_kid
VBA_kid

Reputation: 65

VBA Define Variable names from members of an array

Good morning,

I am using VBA and my problem in brief is that i would like to have a bit of code that will set up a new variable with the name of every member of an array.

Background: The initial array that is being created by reading an XML file taking the information in all the child noes as the value of each member of the array. So for example. If i had some XML such as:

<dimensions>
   <dimension>
      dim1
   </dimension>
   <dimension>
      dim2
   </dimension>
   <dimension>
      dim3
   </dimension>
</dimensions>

My code so far generates and array, lets call it my_array() with 3 members:

my_array(0)=dim1
my_array(1)=dim2
my_array(2)=dim3

My Issue: What i would then like to be able to do is to loop through all the members of my_array()and produce new variables named after each member. So as a result in this simple case end up with 3 new variables defined dim1, dim2, dim3.

My attempt so far:

dim i as integer
for i=0 to Ubound(my_array)
   dim my_array(i) as string
next i

I was hoping this would define a new variable each time the loop runs. As the number of members of my_array depends on the file being read I don't know how many variables will need to be defined or I could just predefined them for use later.

Is what i want possible, am I thinking of this in the correct way?

The next step is that instead of defining simple variables each loop would create a new dictionary for use later, but that's an upgrade for a later date.

Many thanks

Upvotes: 2

Views: 345

Answers (1)

John Coleman
John Coleman

Reputation: 51998

If you don't want to script the VBA editor (which is a lot of work compared to using dictionaries) but you still want to automatically generate a lot of Dim statements for later use, you could write a sub which generates the dim statements as strings and then prints them to the Immediate Window:

Sub test()
    Dim i As Long
    Dim my_array As Variant
    Dim dims As String
    
    my_array = Array("dim1", "dim2", "dim3")
    
    For i = 0 To UBound(my_array)
        dims = dims & "Dim " & my_array(i) & " As String" & vbCrLf
    Next i
    Debug.Print dims
End Sub

Output looks like:

enter image description here

You would still need to manually copy/paste into the editor, but this might save a bit of typing. Obviously this isn't a very robust solution, but every now and then I have found it useful to generate repetitive code by writing a helper sub that creates the code as a string and prints it to the immediate window.

Upvotes: 1

Related Questions