Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    arrray - validate (2002 xp)

    Hi all

    I'm trying to figure out how to test an array that is empty, one that hasn't been filled by code, with this function there is something in the array, and still it returnes false.
    When I encounter it being I get "Subscipt out of range" cause it is out of range

    Is there a workaround to test an array to see if it has been filled?

    Thanks in advance,
    Darryl.

    Public Function valray(myarr As Variant) As Boolean
    On Error Resume Next
    If LBound(myarr) = UBound(myarr) Then
    valray = False
    Exit Function
    Else
    valray = True
    End If
    End Function

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: arrray - validate (2002 xp)

    Works fine for me!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: arrray - validate (2002 xp)

    Your function would return False for an array of one element (since LBound = UBound for a one-element array).

    Does this do what you want?

    Public Function valray(myarr As Variant) As Boolean
    valray = Not IsEmpty(myarr)
    End Function

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: arrray - validate (2002 xp)

    Hi Hans,

    Your function also returns true for any non-array variable, I think this does the trick:

    <pre>Public Function valray(myarr As Variant) As Boolean
    Dim lTemp As Long
    On Error Resume Next
    lTemp = UBound(myarr)
    valray = (Err.Number = 0)
    End Function
    </pre>

    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Re: arrray - validate (2002 xp)

    Yes, if you want to exclude non-array variables, that's better.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: arrray - validate (2002 xp)

    I would, because I typically use this function to determine whether a variant variable contains an array. (I use variants a lot when getting data from sheets).
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    4 Star Lounger
    Join Date
    May 2004
    Location
    Toronto, Ontario, Canada
    Posts
    425
    Thanks
    5
    Thanked 0 Times in 0 Posts

    Re: arrray - validate (2002 xp)

    Thank you all

    Darryl.

Posting Permissions

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