Results 1 to 15 of 15
  1. #1
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Undimensioned Arrays (VBA 2000)

    I am certain that I've seen and used a single statement to determine whether or not a dynamic array is currently dimensioned.
    You guessed it; I cannot find it. Any help would be most appreciated.

    TIA
    Regards
    Don

  2. #2
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Don

    <font color=blue><<< I am certain that I've seen and used a single statement to determine whether or not a dynamic array is currently dimensioned. >>> </font color=blue>

    Could that be Dim MyArray()

    <font color=blue><<< You guessed it; I cannot find it. Any help would be most appreciated. >>></font color=blue>

    Your question is very vague, so I am not sure I am understanding it perfectly to answer it <img src=/S/sorry.gif border=0 alt=sorry width=15 height=15> <img src=/S/drop.gif border=0 alt=drop width=23 height=23> Could you check if the first and last array elements have values in them? Even Null, that means you have that much memory space allocated... <img src=/S/duck.gif border=0 alt=duck width=23 height=23>

    Maybe you can tell us what you are trying to do? <img src=/S/confused.gif border=0 alt=confused width=15 height=20>

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  3. #3
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Thanks for the interest Wassim
    Consider the following:
    <pre>Option Explicit


    Public Sub Demo4Wassim()
    Dim myArray() As String
    Dim oSht As Worksheet
    ' |
    ' |
    ' Many lines of codewhich may or may not have
    ' invoked any of the next 3 statements any number
    ' of times:
    ReDim myArray(0)
    ReDim Preserve myArray(UBound(myArray) + 1)
    Erase myArray()
    ' |
    ' |
    For Each oSht In ActiveWorkbook.Sheets
    ReDim Preserve myArray(UBound(myArray) + 1)
    myArray(UBound(myArray)) = oSht.Name
    Next

    End Sub
    </pre>


    The final ReDim statement will fail if;
    <UL><LI>the array has been erased without subsequently being redimensioned; or
    <LI>the array has never been redimensioned. [/list]I am looking for a statement that will preclude this failure with a snippet similar to:
    <pre> If myArray Is Null Then
    ReDim myArray(0)
    Else
    ReDim Preserve myArray(UBound(myArray) + 1)
    End If
    </pre>

    Regards
    Don

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Undimensioned Arrays (VBA 2000)

    I think you need to add an error handler to the code, and trap error 9 (subscript out of range) separately:

    Public Sub Demo4Don()
    Dim myArray() As String
    On Error GoTo ErrHandler
    MsgBox UBound(myArray)
    ReDim myArray(0)
    MsgBox UBound(myArray)
    ReDim Preserve myArray(UBound(myArray) + 1)
    MsgBox UBound(myArray)
    Erase myArray()
    MsgBox UBound(myArray)

    ExitHandler:
    Erase myArray
    Exit Sub

    ErrHandler:
    Select Case Err
    Case 9
    MsgBox "Array not valid.", vbExclamation
    Resume Next
    Case Else
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Select
    End Sub

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Undimensioned Arrays (VBA 2000)

    Its not one statement, but does this work for you:

    <code>
    Dim MyArray() As Long
    Dim lUB As Long
    lUB = -2147483648#
    On Error Resume Next
    lUB = UBound(MyArray)
    On Error GoTo 0
    If lUB = -2147483648# Then
    MsgBox "Array is not dimensioned."
    Else
    MsgBox "array upper bound is " & lUB
    End If
    </code>
    Legare Coleman

  6. #6
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Don, the only reason that I know that you will get an error is an undimensioned array, so you can shorten Legare's code to:
    <pre> On Error Resume Next
    i = UBound(s)
    If Err Then MsgBox "Not Dimensioned"
    On Error GoTo 0
    </pre>


    For functions like split that return a variant containing an array, you need to actually reference the first element or test UBound for -1:
    <pre> Dim v As Variant, v0 As Variant, i As Long
    v = Split("")
    On Error Resume Next
    v0 = v(0)
    If Err Then MsgBox "Not Dimensioned"
    On Error GoTo 0
    </pre>

    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  7. #7
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Undimensioned Arrays (VBA 2000)

    Another alternative is to initially declare myArray as a variant, rather than as an undimensioned array. Then, each time you would otherwise have used Erase myArray(), you can instead use myArray = Empty. And then you can use IsEmpty(myArray) as your test, rather than having to test for an undimensioned array.

    Note that initially declaring myArray as a variant doesn't prevent you from ReDim-ing it as an array of a particular type. Sample code:
    <pre> Dim myArray
    ReDim myArray(0) As String
    myArray = Empty
    If IsEmpty(myArray) Then
    ReDim myArray(0) As String
    Else
    ReDim Preserve myArray(UBound(myArray) + 1)
    End If</pre>


  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Undimensioned Arrays (VBA 2000)

    You'd also have to set myArray to Empty after erasing it, for

    Erase myArray

    will not make myArray Empty. (One of Don's goals was to be able to determine if the variable had been erased without being subsequently redimensioned)

  9. #9
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Undimensioned Arrays (VBA 2000)

    My post suggested that he use myArray = Empty instead of, rather than in addition to, Erase myArray(). Would there be any reason to Erase first?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Undimensioned Arrays (VBA 2000)

    You're correct, I misunderstood your post. If you set the variable to Empty, you don't need to erase.

  11. #11
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Thank you for that Legare.
    Now I remember; I had code very similar to what you offered, and made a function out of it. This allowed me to simplify the main body of code where this situation arose frequently throughout.
    It will certainly be easier now to adapt your package than locate what I coded before.
    Thanks again for the support.
    Regards
    Don

  12. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Thank you Hans.
    your reference to Error 9 caused the penny to drop for me (please see my response to Legare). My memory is starting to embarrass me
    Thanks again,
    Regards
    Don

  13. #13
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Thank you Sam.
    Regards
    Don

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    Steve
    I have just re-read your approach. I think that it is exactly what I need. A tip of the fedora to you Sir. I think there's more than a touch of elegance in that code.

    Thanks again.
    Regards
    Don

  15. #15
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Undimensioned Arrays (VBA 2000)

    > declare myArray as a variant ... use IsEmpty(myArray) as your test.

    Sweet, plus it even works for v = Split("") v is returned Empty. Thanks, Steve!
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

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