0

Is it possible to name an array variable using a different variable? For example, if I define variable "i" as an integer with a value equal to the number of columns I've used it a sheet:

Sub varNameTest

    Dim i, j, As Integer
    i = ActiveSheet.UsedRange.Columns.Count
    ...

Is it possible to then establish "i" number of arrays named something like myArray1 through i? Possibly something like:

For j = 1 to i
    Dim (myArray & j())
Next i

Though this example immediately above is incorrect syntax, I'm just trying illustrate what I'm trying to do.

edit: so to be more clear, using the above example, say I have 4 sheets in a workbook. The variable i would then be 4, and I would have some code that generates myArray1(), myArray2(), myArray3() and myArray4().

AnthonyJS
  • 153
  • 1
  • 3
  • 10
  • AFAIK, I don't think so. What you can do is create an array instead of creating the variable at runtime. – L42 Jan 16 '15 at 23:09
  • Your edit doesn't really clarify the question. It might help to explain what you then want to do with these "named" arrays. – Tim Williams Jan 20 '15 at 16:01

2 Answers2

1

You can create an array of arrays (though your question is a little unclear..)

Sub MyArrays()

Dim arrays()
Dim arr
Dim i, j

    i = 5 'e.g.
    ReDim arrays(1 To i)

    For j = 1 To i
        arr = Array()
        ReDim arr(1 To j)
        arrays(j) = arr
    Next j
    'reference an array by its position in "arrays"
    Debug.Print UBound(arrays(3))

End Sub
Tim Williams
  • 154,628
  • 8
  • 97
  • 125
  • Your edit doesn't really clarify why it's important to create named variables: typically this is not required (besides not really being possible in VBA) and there are better approaches to acheive the same end. – Tim Williams Jan 20 '15 at 17:17
0

Yes.

Dim i(5) As Integer

In VBA you can then access elements from i(0) to i(5).

Based on your edited question, the answer is no. You must explicitly define each variable in your code.

The other option would be to write code that writes your code - a form of code generation. Effectively that lets you write very long and complex code by repeating code "templates". But I don't think this would help in your case.

Enigmativity
  • 113,464
  • 11
  • 89
  • 172