Creating Variables Programmically

[Guest]

I was wondering if there is a way to create variables programmically. I know
the following code isn't correct, but hopefully it will help demonstrate what
i am trying to do. I want to go through a list, that will have different
numbers of items in it each time (which is why i can't just create a defined
number of variables) and create a variable for each item, that i can use
someplace else. Specifically i want to be able to copy things from one
workbook to the next without having to declare a variable for each item
manually.

In the code below, is there a way to change the "Variable.i" variable name
to actually become Variable1, Variable2, etc.....

thanks for you help


Sub CreateVariables
Dim i as Integer

For i = 1 to 15
Dim Variable.i as integer
Variable.i = Cells(i,1).value
Next i

End Sub

 

[Guest]

You need to use a dynamic array - unless you know ahead of time how large the
count in the For ... Next statement will be. We'll keep it simple and say
that you do. You can post back if you need to use a dynamic array that
allows you to keep adding new elements to it as you encounter them.

Sub CreateVariables()
Dim myArray(1 to 15) as Integer ' or other type as needed
Dim i As Integer

For i = 1 To 15
myArray(i) = Cells(i,1).Value
Next

End Sub

 

[Guest]

This is perfect. Thanks!

You need to use a dynamic array - unless you know ahead of time how large the
count in the For ... Next statement will be. We'll keep it simple and say
that you do. You can post back if you need to use a dynamic array that
allows you to keep adding new elements to it as you encounter them.

Sub CreateVariables()
Dim myArray(1 to 15) as Integer ' or other type as needed
Dim i As Integer

For i = 1 To 15
myArray(i) = Cells(i,1).Value
Next

End Sub

 

[JE McGimpsey]

And if you don't need to explicitly Type the variable:

Public Sub CreateVariables()
Dim myArray As Variant
Dim i As Long

With ActiveSheet
myArray = .Range(.Cells(1, 1), _
.Cells(.Rows.Count, 1).End(xlUp)).Value
End With

For i = 1 To UBound(myArray, 1)
Debug.Print i, myArray(i, 1)
Next i
End Sub

 

[Guest]

You're welcome.

Here's a little tip that may also help - you could control the number of
times the loop executes using the bounds of the array itself. This way if
you either declare the array 'oddly' as Dim myArray(27 to 44), or if you
simply change the size of it later because the amount of data changes, all
you have to do is change the dimension of the array and all else falls in
line:

For i = LBound(myArray) To UBound(myArray)
myArray(i) = Cells(i, 1).Value
Next

Also guarantees you don't try to read into array elements that don't exist.

 

[Guest]

To correct a potential error: that suggestion would work without change with
an array dimensioned like (1 to 15), but if it were an oddly dimensioned
array, like the (27 To 42) example, then you'd have to modify the Cells(i,
1).Value to adjust i to get to the proper cell, like Cells(i-26,1).Value