Excel 2010 VBA-将变量的值用作另一个变量 [英] Excel 2010 VBA - Use Value of variable as another variable

问题描述

这是我在编写VBA代码时遵循的一种做法. 我通常将工作表名称(不带空格)设置为变量名称. 例如，我有一个工作簿，其中包含以下名称的3个工作表

This is a practice I follow while writing VBA code. I usually set worksheet names without spaces into variable names. For example I have a workbook with 3 worksheets with following names

1-控件

2-60 W状态

3-60 W状态Pvt Tbl

3 - 60 W Status Pvt Tbl

我通常通过以下方式将工作表名称设置为工作表变量:

I usually set worksheets names as worksheet variables in the following way:

Set wb = thisworkbook    
Set wsControl = wb.sheets("Control")
Set ws60WStatus = wb.sheets("60 W Status")    
Set ws60WStatusPvtTbl = wb.sheets("60 W Status Pvt Tbl")

现在，我想使它具有动态意义，我的代码应在工作簿中的所有工作表中循环，并将工作表名称(不带空格)存储到变量名称中，例如ws *

Now I want to make this dynamic meaning my code should loop through all the worksheets in a workbook and store the worksheet names without spaces into variable names like ws*

到目前为止，我有以下代码:

So I have the following code so far:

Sub GetwsNames()
Set wb = thisworkbook
Dim wsNames()
i = 0 
'Loop through the workbook and store the names as wsName in an array
For each ws in worksheets
    Redim Preserve wsNames(i)
    Var1 = ws.Name
    Var2 = Replace(ws.Name," ", "")
    Var3 = "ws" & Var2
    wsNames(i) = Var3
    i = i + 1
Next

'Loop through array wsNames() and assign each element to corresponding workbook names
'For example
'Set wsControl = wb.sheets("Control")
'Set ws60WStatus = wb.Sheets("60 W Status")
'Set ws60WStatusPvtTbl = wb.sheets("60 W Status Pvt Tbl")

End Sub

我想知道是否有任何方法可以实现这一目标.我们可以将变量的值用作另一个变量吗?

I would like to know if there is any way to achieve this. Can we use value of a variable as another variable?

推荐答案

您已经可以从工作表集合中按名称引用工作表.

You can already reference sheets by name from the worksheets collection.

Worksheets("Sheet 1")

如果您想通过修改后的名称引用它们，则创建数组或字典将非常有用.我建议使用字典.它将允许您按键引用项目.在此示例中，我使用修改后的名称作为键，并使用工作表对象作为项目.

If you instead want to reference them by your modified names, creating an array or dictionary will work great. I suggest using a dictionary. It will allow you to reference items by key. In this example I use the modified name as the key and use the worksheet object as the item.

Dim myWorksheets As Scripting.Dictionary
Set myWorksheets = New Scripting.Dictionary
Dim ws As worksheet
For Each ws In ThisWorkbook.Worksheets
    Dim modifiedName As String
    modifiedName = "ws" & Replace(ws.Name, " ", "")
    myWorksheets.Add modifiedName, ws
Next
'You can now reference the worksheets by the modified names, through the dictionary
myWorksheets("wsControl").Cells(1, 1) = "Hi"
myWorksheets("ws60WStatus").Cells(1, 1) = "there"
myWorksheets("ws60WStatusPvtTbl").Cells(1, 1) = "world."

请注意，您将需要添加对MS Scripting运行时的引用才能使用字典. 要添加参考，请转到工具"->参考"并选中其复选框.

Note that you will need to add a reference to MS Scripting runtime in order to use dictionaries. To add the reference, go to Tools->References and check its box.

这篇关于Excel 2010 VBA-将变量的值用作另一个变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！