Results 1 to 3 of 3

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

Threaded View

  1. #1

    Thread Starter
    Frenzied Member
    Join Date
    May 2004
    Location
    Carlisle, PA
    Posts
    1,045

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

    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":
    Code:
    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!
    Last edited by Webtest; Nov 22nd, 2005 at 10:52 AM.
    Blessings in abundance,
    All the Best,
    & ENJOY!

    Art . . . . Carlisle, PA . . USA

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width