Excel 2010 VBA - Use Value of variable as another variable

Question

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 - Control

2 - 60 W Status

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")

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?

Yes I would like to know if there is any way I can achieve this. Can I use the value inside a variable and use it as another variable — Anurag Singh
I do not know of a way to name variables dynamically, nor any reason to. Your array which stores the names seems good. You could use the same array to also store pointers to worksheet objects. — Greg
I would like to extract the element from the array that is the sheet name and use it as a worksheet object. Is there a way to do this? — Anurag Singh
Have a look here: windowssecrets.com/forums/showthread.php/… — Maciej Los

Greg Greg · Accepted Answer · 2015-03-20T22:33:22

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

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 - Use Value of variable as another variable

1 Answers