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

    Array Subscripts (VBA/Word & Excel/2003)

    When calling a procedure, I am passing an array as an argument.

    In the called procedure, I have need to know how many subscripts the array was dimensioned with.

    I can achieve this with statements along the lines of :
    <pre>Dim subs As Long
    Dim bound As Long
    subs = 0
    On Error Resume Next
    Do
    bound = UBound(myArray, subs + 1)
    If Err Then
    On Error GoTo 0
    Exit Do
    End If
    subs = subs + 1
    Loop
    </pre>


    or by passing the information along as an additional argument.

    Is there a simpler way to extract the data?

    T.I.A.
    Regards
    Don

  2. #2
    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: Array Subscripts (VBA/Word & Excel/2003)

    I'm a bit VB-rusty at the moment, but it wasn't that long ago I spent some time being array-obsessed. My GetArrayDimensions function uses the same error-triggering technique as your code, and I'm pretty confident there isn't a more straightforward way.

    For what it's worth, my loop counts backward from 2, and my function precedes the loop with the following:
    <pre> If IsEmpty(TargetArray) Then
    GetArrayDimensions = 0
    GoTo ExitLabel
    ElseIf Not IsArray(TargetArray) Then
    GetArrayDimensions = 0
    GoTo ExitLabel
    End If</pre>


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

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Thanks St3333ve
    I overlooked the precaution against someone sending an inappropriate object to my procedure.
    Regards
    Don

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Don,
    Purely as a matter of interest, what kind of procedure is it, if it takes an array of any number of dimensions?
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Hi Rory
    I'm using this in a Sort procedure and am rejecting anything over two dimensions.
    Regards
    Don

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Oh, OK, that makes sense. So all you need is to check that it is an array and then check the boundary of dimensions 2 and 3.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Agreed with one observation:
    <hr> check the boundary of dimensions 2 and 3 .<hr>
    Perhaps I have the terminology wrong; but didn't you mean "dimensions 1 and 2"?
    Regards
    Don

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Subscripts (VBA/Word & Excel/2003)

    Nope - if it's an array, you know it's got one boundary, so you don't need to check 1; you want it to have 2, so you need to check that; and if it's got 3 (or more) it's invalid, so you test for that. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Array Subscripts (VBA/Word & Excel/2003)

    As always;
    Thank you Rory.
    Regards
    Don

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Subscripts (VBA/Word & Excel/2003)

    It was just an observation - you're probably better off with a generic function to return the number of dimensions anyway.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Array Subscripts (VBA/Word & Excel/2003)

    I'm coming to realize that developing generic functions takes a great deal more thought and care than developing one for a very specific use. But does it ever pay off in the longer term through re-use.
    Regards
    Don

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Array Subscripts (VBA/Word & Excel/2003)

    I couldn't agree more! The only trouble sometimes is finding the extra time to write a good generic function when you have a deadline and something quick and dirty will do... <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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