EXCEL VBA: How To: Deal with Empty Dynamic Arrays ???

[Webtest]

Esteemed Forum Participants and Lurkers:
===============================
Excel 2003 VBA

I have a workbook generated by others where I am trying to classify the sheets into Dynamic Arrays of Sheet Names. Let's say (for example) there are TWO types of sheets where the type is determined by the sheet name (there are actually several types). The problem is that not all types will necessarily be populated, and the number of sheets of any type can range from none to about 10. Scanning the sheet names is trivial, but kicking off the arrays, and determining at the end whether or not they are populated is the bugger. It sure would be nice to have an "arrayname.Count" function

I know I can easily use a boolean flag for each array, but do I need to resort to that?
In the following, 'FAIL' means "subscript out of range error":

Option Explicit
Sub Array_Junk()
    Dim str_TypeA() As String
    Dim str_TypeB() As String
    Dim strTemp As String
    Dim aSheet As Worksheet
    
    'Scan the Sheets to determine the type for each one
    For Each aSheet In ActiveWorkbook.Sheets
        'This external function just returns the sheet type as a character
        strTemp = Function_to_type_Sheet(aSheet)
        'Add the Sheet name to the correct array
        Select Case strTemp
            Case "A"
                ReDim Preserve str_TypeA(UBound(str_TypeA) + 1)  '<< FAILS IF EMPTY
                str_TypeA(UBound(str_TypeA)) = aSheet.Name
            Case "B"
                ReDim Preserve str_TypeB(UBound(str_TypeB) + 1)  '<< FAILS IF EMPTY
                str_TypeB(UBound(str_TypeB)) = aSheet.Name
        End Select
    Next aSheet
    
    'Now, how do I get the Count of the array elements ... if the array is EMPTY!
    MsgBox "Type A Sheets: " & (UBound(str_TypeA) + 1)  '<< FAILS IF EMPTY
    MsgBox "Type B Sheets: " & (UBound(str_TypeB) + 1)  '<< FAILS IF EMPTY

End Sub

I was just wondering how you great and wonderful professional analysts would code this before I hack it!

[malik641]

I'm not an expert with Arrays (...yet ) but see if this is what you were looking for. I decided to use the On Error Resume Next line, and checked if there was an error:

Option Explicit
 
Sub Array_Junk()
On Error Resume Next 'Added this
 
Dim str_TypeA() As String
Dim str_TypeB() As String
Dim strTemp As String
Dim aSheet As Worksheet
 
'Scan the Sheets to determine the type for each one
For Each aSheet In ActiveWorkbook.Sheets
    'This external function just returns the sheet type as a character
    strTemp = Function_to_type_Sheet(aSheet)
    'Add the Sheet name to the correct array
    Select Case strTemp
        Case "A"
            ReDim Preserve str_TypeA(UBound(str_TypeA) + 1)  '<< FAILS IF EMPTY
            If Err <> 0 Then ReDim str_TypeA(0)              'Added this
            str_TypeA(UBound(str_TypeA)) = aSheet.Name
        Case "B"
            ReDim Preserve str_TypeB(UBound(str_TypeB) + 1)  '<< FAILS IF EMPTY
            If Err <> 0 Then ReDim str_TypeB(0)              'Added this
            str_TypeB(UBound(str_TypeB)) = aSheet.Name
    End Select
    
Err = 0     'Added this
Next aSheet
 
'Now, how do I get the Count of the array elements ... if the array is EMPTY!
MsgBox "Type A Sheets: " & (UBound(str_TypeA) + 1)  '<< FAILS IF EMPTY
MsgBox "Type B Sheets: " & (UBound(str_TypeB) + 1)  '<< FAILS IF EMPTY
 
End Sub

Not sure if this is the best way, but it seemed to work for me.

[WorkHorse]

ReDim Preserve str_TypeA(UBound(str_TypeA) + 1) '<< FAILS IF EMPTY

I was just wondering how you great and wonderful professional analysts would code this before I hack it!

For dynamic arrays, you should always redimension the array to zero before you start using it. After your variable declartions, use

ReDim str_TypeA(0)
ReDim str_TypeB(0)

The arrays wil no longer be empty and will resolve your failure problems.