[RESOLVED] ExcelVBA: How To: Determine if Array is not Initialized ???

[Webtest]

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

I have an array that holds a list of error files (if any). I Dim the array at the start of the Sub (Dim NoStore() As Integer), I Redim it "Preserve" each time I add a new item, and then at the end I generate a report of the errors. The problem I run into is that often there are NO errors, and the following line gives me a "Subscript Out of Range" error:

For i = 0 To UBound(NoStore)

What is a clean and elegant way to test to see if the Array "NoStore()" has no items in it? I have tried IsEmpty, IsNull, and a few other things, but all without success.

I sincerely appreciate any and all comments, suggestions, and assistance.

[dmaruca]

I've never found anything for that. I've always used something like this...

Function Initialized(val) As Boolean
On Error GoTo errHandler
    Dim i
    
    If Not IsArray(val) Then GoTo exitRoutine
    
    i = UBound(val)
    
    Initialized = True
exitRoutine:
    Exit Function
errHandler:
    Select Case Err.Number
        Case 9 'Subscript out of range
            GoTo exitRoutine
        Case Else
            Debug.Print Err.Number & ": " & Err.Description, _
                "Error in Initialized()"
    End Select
    Debug.Assert False
    Resume
End Function

[Webtest]

Thanks dmaruca ...

I was afraid that there would not be a clean elegant funtion. I followed your lead, but simplified things ... it still is dirty and inelegant ...

On Error GoTo NotInitialized
    'Process the Array Items
    For i = 0 To UBound(NoStore)
        On Error GoTo 0
        'Process Array Items Here ...
        '    ...
    Next i
NotInitialized:
    On Error GoTo 0
    'Continue with function

Thanks for your assistance.

[dmaruca]

Just use the function. Error trapping is handled there.

If Initialized(NoStore) Then
    For i = 0 To UBound(NoStore)
        'Process Array Items Here ...
        '    ...
    Next i
End If

Have a good day.

[Webtest]

dmaruca:

Yes, I understood that ... I just didn't really want to add your whole function to my application. I probably will end up doing that anyway though. While we're at it, what is the function of "Debug.Assert False" As far as I can tell, that doesn't do anything. In the "Case Else" statement, could you use "Error Err.Number" to report the error with a system popup instead of the Debug.Print statement ???

[dmaruca]

Debug.Assert False is like a hard-coded breakpoint in VBA. Read more here:

http://en.wikipedia.org/wiki/Assertion_(computing)

Debug.Print, Assert, and resume is my typical debugging type code. I don't like annoying things that pop-up and you gotta click OK. You can use anything you like and "Error Err.Number" would be just fine.

[anhn]

This is a way to check in-line:

    Dim NoStore() As Long
    Dim n As Long
    Dim i As Long
    
    '... ...
    On Error Resume Next
    n = LBound(NoStore)
    If Err = 9 Then
        MsgBox "Array not initialized"
        Exit Sub '-- Function
    ElseIf Err > 0 Then
        '... ...
    End If
    On Error GoTo 0
    
    For i = 0 To n
        '...
    Next

Edit: A very useful topic from CPearson: Functions For VBA Arrays
Another quite old thread by Merri: http://www.vbforums.com/showthread.php?t=375341

[Webtest]

Thanks dmaruca and anhn. I guess this issue is resolved now. I did modify dmaruca's function a little and included it and a test routine below.

Option Explicit
'Module:  IsInitializedArray
'20080620.1116    Arthur Du Rea    (c) Copyright 2008    All rights reserved
'Based on work by "dmaruca", "anhn", and others.   http://www.vbforums.com/showthread.php?t=528009

'===================================================================================================
'Test an Array as a Parameter to see if it is Initialized
'    Returns TRUE if the UBound of the Array is Greater than or Equal to Zero (Array IS initialized)
'    Returns FALSE if the Array has never been dimensioned (Redim) - reading UBound generates Error
'
'Passes any other problem or error (eg. Parameter is NOT an array) to the System Error Reporter
'
'The only drawback to this function is that if there is an error OTHER than an array initialization
'  problem, the debugger will ALWAYS point to the "Err.Raise" line at the end of this function,
'  because that is where the "Error" is actually sent to the System.
'
Function IsInitializedArray(ByRef anArray) As Boolean
    
    'Trap the system error exception where the Array is NOT initialized
    On Error GoTo ErrorProc
        'Check to see if the Array has a valid Upper Bound (i.e. It IS initialized)
        If UBound(anArray) >= 0 Then IsInitializedArray = True
    'Reset the Error Exception Handler to the SYSTEM error handler
    On Error GoTo 0
    'Go Back to the Calling process
    Exit Function
    
'UBound(anArray) Error Exception Handler
ErrorProc:
    'System Error #9 means that there are no available subscripts for this array (NOT initialized)
    If Err.Number = 9 Then
        'Report that the Array Parameter is NOT Initialized and do a normal continuation
        IsInitializedArray = False: On Error GoTo 0: Exit Function
    Else
        'Debug.Print Err.Number, Err.Source, Err.Description
        'Debug.Print Err.HelpFile
        'Debug.Print Err.HelpContext
    
        'Pass the UNKNOWN Error Parameters to the System Error Reporter
        'object.Raise number, source, description, helpfile, helpcontext
        Err.Raise Err.Number, Err.Source, Err.Description, Err.HelpFile, Err.HelpContext
    End If
    
End Function

'TEST the Function ...
Sub TEST_IsInitializedArray()
    Dim myArray() As Integer  'An Array to TEST
    Dim tStr As String        'A String is 'an array of characters'?
    
    'ReDim myArray(0)
    'Check to see if the Array "myArray" has ever been initialized
    If IsInitializedArray(myArray) Then
        Debug.Print """myArray"" IS Initialized"
    Else
        Debug.Print """myArray"" is NOT initialized"
    End If
    
    tStr = "TEST"
    'tStr is NOT an array ... it will correctly pop up a system error report
    'but the debugger will end up in the "IsInitializedArray" function.
    If IsInitializedArray(tStr) Then Debug.Print "tStr is Initialized"

End Sub

One can actually find out where the source of the error by looking in the Locals window at the various processes in the dropdown tool at the right of the Process name edit box. There will be a green arrow at the instruction that called the function.

Thanks again ... you guys are GREAT!

[dmaruca]

Wow, you put me in the comments. I'm honored! My name will live on eternally!