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
– Anurag Singh, Commented Mar 20, 2015 at 21:36
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
– Greg, Commented Mar 20, 2015 at 21:44
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
– Anurag Singh, Commented Mar 20, 2015 at 21:50
Have a look here: windowssecrets.com/forums/showthread.php/… — Maciej Los
– Maciej Los, Commented Mar 20, 2015 at 22:20

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

0

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.

answered Mar 20, 2015 at 22:33

Greg

4264 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Anurag Singh Over a year ago

Thanks a lot Greg!! This works perfectly. You are life saver... although can we avoid using myworksheets("Name").Cells(1,1) and use just Name.Cells instead as I will be referencing to these objects a lot. But thanks a lot for your help!@

Greg Over a year ago

No because we do not have variables just named name. The dictionary name could be shortened... d("name").cells...

Collectives™ on Stack Overflow

Excel 2010 VBA - Use Value of variable as another variable

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related