如何创建动态变量名VBA [英] How to create dynamic variable names VBA

问题描述

我正在尝试根据单元格中的值在VBA中创建一个动态数量的变量。
本质上我想要的结果是像$ code> Team1，Team2 ...到TeamX 。
非常感谢任何帮助

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

推荐答案

p>字典在这种情况下可能会有帮助，它是为脚本设计的，而不会让你创建动态变量，字典的项目是动态的，可以像变量一样具有相似的用途。

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"

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

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

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

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

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

字典的键必须是整数或字符串，但值可以是任何数据类型（包括数组，甚至 Object 数据类型，如 Collection ， Worksheet ，应用程序，嵌套字典等，使用 Set 关键字），所以例如你可以指出工作簿中的工作表：

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

这篇关于如何创建动态变量名VBA的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！