How to create dynamic variable names VBA

Question

I am trying to create a dynamic number of variables in VBA based on the value in a cell. Essentially what I'd like to end up with is something like Team1, Team2... to TeamX. Any help is greatly appreciated

Dim i, x As Integer
Set x = Range("J4").Value
Dim Team(1 To x) As String
Dim Manager(1 To x) As String
Range("A3").Select
For i = 1 To x
Dim Team(i) As Integer

you are thinking about an [array](https://msdn.microsoft.com/en-us/library/wak0wfyt.aspx) — litelite, Jul 07 '16 at 20:10
I am not familiar with dictionaries; could they help in this situation? — reggie86, Jul 07 '16 at 20:12
[Create Dynamic Variable names](https://stackoverflow.com/questions/55295843/can-i-set-the-name-of-variable-with-textbox-vba-excel) — Siddharth Rout, Mar 22 '19 at 10:26

David Zemens · Accepted Answer · 2016-07-08T13:35:32.853

A dictionary would probably help in this case, it's designed for scripting, and while it won't let you create "dynamic" variables, the dictionary's items are dynamic, and can serve similar purpose as "variables".

Dim Teams as Object
Set Teams = CreateObject("Scripting.Dictionary")
For i = 1 To x
    Teams(i) = "some value"
Next

Later, to query the values, just call on the item like:

MsgBox Teams(i)

Dictionaries contain key/value pairs, and the keys must be unique. Assigning to an existing key will overwrite its value, e.g.:

Teams(3) = "Detroit"
Teams(3) = "Chicago"
Debug.Print Teams(3)  '## This will print "Chicago"

You can check for existence using the .Exist method if you need to worry about overwriting or not.

If Not Teams.Exist(3) Then
    Teams(3) = "blah"
Else:
    'Teams(3) already exists, so maybe we do something different here

End If

You can get the number of items in the dictionary with the .Count method.

MsgBox "There are " & Teams.Count & " Teams.", vbInfo

A dictionary's keys must be integer or string, but the values can be any data type (including arrays, and even Object data types, like Collection, Worksheet, Application, nested Dictionaries, etc., using the Set keyword), so for instance you could dict the worksheets in a workbook:

Dim ws as Worksheet, dict as Object
Set dict = CreateObject("Scripting.Dictionary")
For each ws in ActiveWorkbook.Worksheets
    Set dict(ws.Name) = ws
Next

score 2 · Answer 2 · answered Jul 07 '16 at 20:17

This will get you started. But before you start I recommend watching these WiseOwlTutorials tutorial on Youtube:

    Dim i, x As Integer
    x = Range("J4").Value
    Dim Team() As Integer
    Dim Manager() As String

    ReDim Team(1 To x) As Integer
    ReDim Manager(1 To x) As String

    Range("A3").Select


    For i = 1 To x

        Team(i) = i

    Next

How to create dynamic variable names VBA

2 Answers2

Linked