vba 如何创建动态变量名VBA

How to create dynamic variable names VBA

提问by reggie86

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

我正在尝试根据单元格中的值在 VBA 中创建动态数量的变量。基本上我想最终得到的是类似的东西Team1, Team2... to TeamX。任何帮助是极大的赞赏

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

回答by David Zemens

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 keywill 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 .Existmethod if you need to worry about overwriting or not.

.Exist如果您需要担心覆盖与否，您可以使用该方法检查是否存在。

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 .Countmethod.

您可以使用该.Count方法获取字典中的项目数。

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 Objectdata types, like Collection, Worksheet, Application, nested Dictionaries, etc., using the Setkeyword), so for instance you could dict the worksheets in a workbook:

字典的键必须是整数或字符串，但值可以是任何数据类型（包括数组，甚至Object数据类型，如Collection、Worksheet、Application、嵌套字典等，使用Set关键字），例如，您可以在工作簿：

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

回答by reggie86

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

这会让你开始。但在您开始之前，我建议您在 Youtube 上观看这些 WiseOwlTutorials 教程：

• Selecting Cells (Range, Cells, Activecell, End, Offset)
• Worksheets, Charts and Sheets
• Variables
• Arrays

• 选择单元格（范围、单元格、活动单元格、结束、偏移）
• 工作表、图表和表格
• 变量
• 数组

    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