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