Can I Automatically Generate Variable Names?

Hi There,

I am trying to set short names for sheets automatically using an array containing the short and full names for each sheet.
e.g. instead of having to do this for each individual sheet name:

Set BW = Workbooks(MyName).Worksheets("BobsWorkoutSheet")

I want to do something like this:

Set AllTheSheetsArr(i,1) = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))

I want to then be able to refer to the sheet later on by "BW". Unfortunately the 2nd line of code above would require me to refer to it as "AllTheSheetsArr(1,1)"!
So really, I am wanting to know if I can name the variables automatically.

I have looked online for something like this before, and have either not managed to search for the correct phrase or it is not something that is possible. either way, I thought the best way to find out for certain was to post and see if anyone can help!

Also, you'll see that I add to the array a "1" if the sheet name exists and "-1" if it does not. This is to avoid an error if the sheet name was called and it doesn't exist!

Thank you for reading!

Code is below, including the long way that I have to set the sheetnames using Select Case...

Function WorksheetExists(sName As String) As Boolean
    WorksheetExists = Evaluate("ISREF('" & sName & "'!A1)")
End Function



Sub DefineVariableNamesForSheets()
'=======================================================================================================================
'#Throughout my program, I want to be able to check against the table to see if a particular sheet exists or not Using the WorksheetExists Function above.
'#I have created an array that will store in: Column1- The abbreviate name of each sheet; Column2- The Full name of each sheet; Column3- If the sheet currently exists in the workbook.
'#I want to know if it is possible to set the short names in a smart way using names from the array rather than the way I have had to do it below! For example if there are 100 possible sheetnames
'#surely there must be a smareter way of doing it (without having to change the actual sheet names!)
'=======================================================================================================================
MyName = ThisWorkbook.Name
Set InitialSheet = ActiveSheet

    '=======================================================================================================================
    '#Make a safety so it won't try to alter any sheets that don't exist!
    '=======================================================================================================================
    Dim sName As String    '#Use this to pass the Full Worksheet name to "WorksheetExists" function to check if the sheet exists or not!
    
    '#Enter All Sheets to be processed into a list! (Use Split command!) - Use Short Sheet Names (BW), Long or Full Sheet Names (BobsWorkoutSheet) and combine into the AllTheSheetsArr array!
    Dim AllTheSheetsShort
    AllTheSheetsShort = "BW|DW|SW|AW|BrW|CW|IW|MW|StW|AlW|AnW|AiW"
    AllTheSheetsShort = Split(AllTheSheetsShort, "|", -1, vbTextCompare)
    
    Dim AllTheSheetsLong
    AllTheSheetsLong = "BobsWorkoutSheet|DavesWorkoutSheet|StevesWorkoutSheet|AndysWorkoutSheet|BriansWorkoutSheet|CharlesWorkoutSheet|IainsWorkoutSheet|MarysWorkoutSheet|StephaniesWorkoutSheet|AlexsWorkoutSheet|AnthonysWorkoutSheet|AidensWorkoutSheet"
    AllTheSheetsLong = Split(AllTheSheetsLong, "|", -1, vbTextCompare)
    
    '#Col 1 = Short names;  Col 2 = Full Names;  Col 3 = Check if Sheets exist!
    Dim AllTheSheetsArr()
    ReDim AllTheSheetsArr(UBound(AllTheSheetsShort) + 1, 3)
    AllTheSheetsArr(0, 1) = "Short Sheet Name"
    AllTheSheetsArr(0, 2) = "Full Sheet Name"
    AllTheSheetsArr(0, 3) = "Sheet Exists?"
    
    For i = 1 To UBound(AllTheSheetsArr)
        AllTheSheetsArr(i, 1) = AllTheSheetsShort(i - 1)
        AllTheSheetsArr(i, 2) = AllTheSheetsLong(i - 1)
        sName = AllTheSheetsArr(i, 2)
        If WorksheetExists(sName) = True Then AllTheSheetsArr(i, 3) = 1 Else: AllTheSheetsArr(i, 3) = -1
    Next i
    '=======================================================================================================================
    '#END   Make a safety so it won't try to alter any sheets that don't exist!
    '=======================================================================================================================
    
    For i = 1 To UBound(AllTheSheetsArr)
    
        If AllTheSheetsArr(i, 3) = 1 Then
    
            '# I want to replace this Case so that all 12 Variables can be generated from the array
            '#e.g. Set AllTheSheetsArr(i,1).name = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
            Select Case i
                Case Is = 1
                    Set BW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 2
                    Set DW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 3
                    Set SW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 4
                    Set AW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 5
                    Set BrW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 6
                    Set CW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 7
                    Set IW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 8
                    Set MW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 9
                    Set StW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 10
                    Set AlW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 11
                    Set AnW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
                Case Is = 12
                    Set AiW = Workbooks(MyName).Worksheets(AllTheSheetsArr(i, 2))
             End Select
       
        End If
        
    Next i

End Sub

No, you can't, not automatically anyway. There's potentially a bit of a hacky way through automating the VBA IDE if you only need to do it once, but wouldn't really be distributable to others. You could use a collection to shorten things somewhat so you'd end up with something like:

AllSheets("BW").Cells(1,1).Value2 = "Kyle"

But I don't think it adds a great deal

Thanks Kyle,

Thanks for your reply.
That's disappointing news!

I'll leave this thread open for a while in the hope that someone knows something that we don't! :o)

What's the problem with select case? If you have more variables, you'll have to add them manually whichever way you do it.

Another approach is to look a what you're actually doing with the variables once you have them.

Ps I don't really see why:

AllSheets("BW").Cells(1,1).Value2 = "Kyle"

Is much more arduous than:

BW.Cells(1,1).Value2 = "Kyle"

Post updated

Hi Kyle,

Apologies if I seemed dismissive of your suggestion - that was not the intention.

To explain a little - the example I have given was a generic one. In reality, I have created a program for work which requires users to move between various sheets depending on what they are doing. I am using the sheet names to move around and to extract relevant data depending on the request.

If I have misunderstood what you have written, then apologies, but it is quicker to write

bw.cells(1,1)= "Kyle"

than

AllSheets("BW").cells(1,1) = "Kyle"

For the specific task in which I am aiming this at, it is for resizing the various sheets that users will enter data into dependant on the device they are using e.g. Tablet type 1, Tablet type 2, PC type 1, etc.
Not all sheets are available in all modes, so rather than have to check each time whether the specific sheet is in existence , I just wanted a quick way to protect against an error (without having to add another On error resume function).

There is nothing wrong with the Case Select method - I am not dissing it! I agree that I'd have to add them in manually anyway, but being able to write them in on one or a few lines and split them rather than writing say 100 individual cases.

In short, I was wanting to know if it was possible to automate for extending my (limited) understanding of VBA. Anything that requires a hack so it couldn't be replicated on other machines would not be considered a solution!

I hope that is a little clearer as to me request and objective from posting? :o)

It's not a hack, just a little hacky and faffy. Have a look here: http://www.cpearson.com/excel/vbe.aspx. It allows you to write VBA with code, essentially automate the IDE like you would Excel, however there are a few gotchas that limit things a bit that may or may not be relevant:

1. It's not going to work on a mac
2. An extra permission needs to be set in Excel which is off by default for security reasons, to use the code users would need to enable this - in some corporate environments it can't be enabled
3. It's permanent, you're adding code into modules, there's no undo. You have to delete manually (or through more code), this starts to become problematic when you add more variables, for example you need to check to see if they exist (or delete them all and recreate them)
4. It resets the VBA project

For the specific task in which I am aiming this at, it is for resizing the various sheets that users will enter data into dependant on the device they are using e.g. Tablet type 1, Tablet type 2, PC type 1, etc.
Not all sheets are available in all modes, so rather than have to check each time whether the specific sheet is in existence , I just wanted a quick way to protect against an error (without having to add another On error resume function).

I don't really understand how what you're looking for helps here, are you just wanting to do this due to the speed of typing out bw vs say as("bw")?

Hi Kyle,

Thank you - I will look into that at some point when I have the time to invest! Looks a little hair raising, especially if a virus checker can delete the modules dependent on how it sees the vba writing behaviour!

As I said, the main goal was to find out if it is possible to automatically assign variables without naming them directly e.g. instead of having to type

set bw =

they can be set indirectly using a reference from an array. e.g.

set array(I,2).name =

where

 array(I,2) = bw

and I can then refer to the sheet using bw rather than array(I,2).

The bigger picture was expanding my understanding of VBA's capabilities - not just about saving a few keystrokes. There are some amazing solutions that are on this forum - a one/two line piece of code that saves dozens of lines - so I wondered if this would be a similar thing!

I was/am looking for a solution that does not require tinkering, but for now I shall use the longer method for my current project and look into the method above at some point in the near future!

Thanks again for your expansion. Sorry if it's not particularly clear what I'm after!

Or if you wanted a check built in, something like:

Sub test()
    Debug.Print "aa = "; s(aa).Name
    Debug.Print "bb = "; s(bb).Name
    Debug.Print "cc = "; s(cc).Name
    Debug.Print "dd = "; Not s(dd) Is Nothing
End Sub


Public Function s(sn As cd) As Worksheet
    
    Dim sheetArray()    As String
    sheetArray = Split(sheetNames, "|")
    
    On Error Resume Next
    Set s = ThisWorkbook.Worksheets(sheetArray(sn))
    On Error GoTo 0
End Function

Hi Kyle,

Apologies for my ignorance, but I am unfamiliar with enum (having looked up the definition, it is a collection e.g. according to wiki...

"the four suits in a deck of playing cards may be four enumerators named Club, Diamond, Heart, and Spade, belonging to an enumerated type named suit. If a variable V is declared having suit as its data type, one can assign any of those four values to it."

I tried adding this into my code to see exactly what it is doing, but there is an error coming up regarding "s" function. Could you please elaborate a little more on what this is doing as I do not use debug.print although I have seen the result in other code that has incorporated it... Once again, probably something I should be using!

I have assumed that the two code snippets are independent - have I misunderstood and I am actually meant to use them in combination?

Kind Regards,

John

Thanks Kyle.

Ok, so I can see that the sheet names are printed to the immediate window as you show. I can extend this by writing the full sheet name into Const sheetNames "|" and the shortened version into PublicEnum .. (Does cd stand for collection then?)
I can use Debug.Print to print the short and full length sheet names to the immediate window for all of the sheet names as you have demonstrated..

So how would I then select a sheet e.g.

cc.select

?

I am not sure that I understand

HTML Code: 

Where would the sheetcodes autodropdown from or during?
I am uncertain what compile time validation means in vba context....

Kind Regards,

John