How to create dynamic variable names VBA

by

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

More Related Contents:

  1. delete Autofilter Field resullt using the header Name
  2. Somehow it’s not copying [closed]
  3. VBA macro that search for file in multiple subfolders
  4. Getting ScriptControl to work with Excel 2010 x64
  5. Count and Highlight keywords within phrases
  6. Transpose a range in VBA
  7. Protecting cells in Excel but allow these to be modified by VBA script
  8. Saving excel worksheet to CSV files with filename+worksheet name using VB [duplicate]
  9. Detect merged cells in VBA Excel with MergeArea
  10. Using VBA code, how to export Excel worksheets as image in Excel 2003?
  11. Parse HTML content in VBA
  12. How do I name a spreadsheet automatically, by referencing two cells?
  13. Excel VBA Delete Rows
  14. VBA EXCEL To Prompt User Response to Select Folder and Return the Path as String Variable [duplicate]
  15. Should I turn .CutCopyMode back on before exiting my sub procedure?
  16. VBA: How to delete filtered rows in Excel?
  17. Determine whether user is adding or deleting rows
  18. Removing the password from a VBA project
  19. Excel VBA Formula German/French/Italian/Russian/Dutch/Foreign Function
  20. Iterate over slicer via VBA and select a single item each time
  21. Loop through Excel Sheets
  22. Return a range from A1 to the true last used cell
  23. How to add items to a combobox in a form in excel VBA?
  24. VBA Password Input with Cancel Function
  25. Calling regasm without administrative rights for COM interop in Excel VBA
  26. Is there a way for DIR(path) in VBA to handle strings longer than 260?
  27. Start VBA macro when editing a cell
  28. VBA Run-time error 438 appears when “paste” runs
  29. If Not function proceeds when value *is* the one specified
  30. Copy an entire worksheet to a new worksheet in Excel 2010

Leave a Comment