Excel VBA unify two variables

I have a this code:

Public nrSheet As Integer
Public sheetName As Variant

Sub serials()

a = 1
nrSheet = ThisWorkbook.Sheets.Count
For i = 2 To nrSheet
    sheetName = Sheets(i).Name
    a = a + 1
Next
End Sub

a开发者_C百科nd I want to have something like

sheetName & a = Sheets(i).Name

and the result should be:

sheetName1 = "Sheet1"

sheetName2 = "Sheet2"

How can I do that since is not working with & or +

Thank you

---

You meant:

sheetName = Sheets(i).Name & a

[edit]

No you don't, sorry. :) What you describe is impossible, but you are actually looking for arrays. With arrays, you can have a variable that is a list, much like the Sheets collection you're already using.

So you can write:

sheetName(a) = Sheets(i).Name & a

and then use sheetName(1) where ever you need the first sheet name.

Here I found a tutorial on using arrays in Excel VBA.

---

You can use a "for each Loop" to loop through every sheet. Using a "Scripting.Dictionary" instead of an array, could save you some headaches.

Here is another approach for your needs:

Public sheetName As Scripting.Dictionary

Sub serials()
    Dim mySheet As Worksheet

    Set sheetName = New Scripting.Dictionary

    For Each mySheet In ThisWorkbook.Worksheets
        sheetName.Add mySheet.Name, "Here you can add another variable/value" 'Dictionary requiers allways a Key (first argument) and a Item(second argument)
    Next mySheet

    Set sheetName = Nothing 'Put this line at the end of your procedure.
End Sub

To retrieve your values, you only need: sheetName.Keys(n). Where n is the index number. To test if a value is in the dictionary, just use sheetName.Exists(Name). Where Name is the name of the sheet.

To make the dictionary available, go to Extras->Reference and check the "Microsoft Scripting Runtime" option.

Here you can get more info about the Dictionary Object.

---

This is exactly what Arrays and other collections were created for, but they go one better in that you don't need to have more than one variable.

Arrays
Arrays in VB are usually declared as follows:

Dim sheetName(1 to 10) As String

In the case above we've specified that we want indexes between 1 and 10, so we could access these by specifying something like:

Debug.Print sheetName[5]

to output the fifth element. If you don't know how many items you can expect in the array you have to declare it as follows:

Dim sheetName() As String

And then later on you'll need to declare how big the item is using:

ReDim sheetName(1 to 10)

Note that if you've got an array and you want to add more items to it, you'll need to manage resizing this yourself.
Say we have between 1 and 10 units, and then later you want to make it bigger:

ReDim Preserve sheetName(1 to 20)

Where the preserve keyword is used to make sure we don't lose the existing data.

Fortunately some other options have been included for managing these without having to worry about increasing the size manually.

You can then get the array data out by doing something like the following:

Dim sheetName(1 To 10) As String
sheetName(1) = "One"
sheetName(2) = "Two"
Dim i As Integer
For i = 1 To 10
    Debug.Print sheetName(i)
Next i

Note that in this situation we've declared there to be 10 sheets, despite the fact that we've only declared two of them, so this will rather stupidly go through and print out every one of the 10 sheet names, 8 of which will be blank.

Another option here is to use For Each, so we don't need to worry about a counter.

Dim sheetName(1 To 10) As String
sheetName(1) = "One"
sheetName(2) = "Two"
Dim sheet As Variant
For Each sheet In sheetName
    Debug.Print sheet
Next sheet

This will, unfortunately, have the same problem in that it will print out every one of the 10 sheet names.

Fortunately, VB has some built in collections that takes away some of these problems.

Collections
Visual Basic has built-in Collections. You can declare a collection as follows:

Dim sheetName As Collection
Set sheetname = New Collection

Note that here we need to use the Set and New keywords to create the collection, because this is a complex type.
You now have a collection that you can add to by doing the following:

sheetName.Add "Your Sheet Name"

Taking the above code examples a step further, we could do the following:

Dim sheetName As Collection
Set sheetName = New Collection
sheetName.Add "One"
sheetName.Add "Two"
Dim sheet As Variant
For Each sheet In sheetName
    Debug.Print sheet
Next sheet

And now, notice that we don't have to worry about how many elements there are in the Collection. We can add to it as much as we want and the re-sizing will be handled.

Collections can also be accessed using index numbers like arrays, so we can still do the following if you want to:

Dim i As Integer
For i = 1 To 2
    Debug.Print sheetName(i)    ' where this is the sheetName Collection
Next i

Collections also allow you to define a key to refer to the sheetName items with. Say we still want to use another variable to keep track of the sheets, and this is what will get you closest to what you were originally asking for in your question, although hopefully by now you've seen that there isn't really any need to do so:

Dim sheetName As Collection
Set sheetName = New Collection
sheetName.Add "One", "Sheet" & "1"    ' Note that I have only separated the
sheetName.Add "Two", "Sheet" & "2"    ' Sheet & the 1 for illustrative purposes.
Dim i As Integer
For i = 1 To 2
    Debug.Print sheetName("Sheet" & i)
Next i

And finally, for completeness, CaBieberach mentioned the Scripting.Dictionary in their answer.

Dictionary
A Dictionary isn't actually a part of standard VB, but can be included easily. The steps to do this are to include a reference to the 'Microsoft Scripting Runtime' by going to Tools, References, and Adding a tick into 'Microsoft Scripting Runtime'.
Once you've done that you can create a Dictionary as follows:

Dim sheetName As Scripting.Dictionary
Set sheetName = New Scripting.Dictionary
sheetName.Add "Sheet" & "1", "One"  ' Note that these are reversed. The Key is first,
sheetName.Add "Sheet" & "2", "Two"  ' the Item is second.
Dim sheet As Variant
For Each sheet In sheetName
    Debug.Print sheet
Next sheet

Dictionaries can be used almost identically to a collection, but must have a key to go with each item, and this can be used for a very quick at look-up, so in the example above where we have the Item listed as One, we can:

Debug.Print sheetName("Sheet1")

And get an output of "One"

Recommendations
If you're just storing a few sheet names and don't require a very fast lookup I wouldn't bother using a Scripting.Dictionary, in my opinion a Collection will suffice and you won't need to remember to included non-standard references.

Use a For Each to iterate through the items - that way you don't have to keep track of an extra Integer for no reason.