Solved

Test to determine if variant is an array

Posted on 2012-04-07
6
364 Views
Last Modified: 2012-04-09
I am working in Access 2010.
I need to determine if a variant is an array. I put together the following function:

Public Function IsArray(varIn As Variant) As Boolean
   Dim i As Integer
   On Error GoTo ExitFunction
   i = UBound(varIn)
   IsArray = True
ExitFunction:
   On Error GoTo 0
End Function

It appears to work. Is there some unforeseen circumstance I'm not thinking of where this procedure wouldn't work?
Is there a better way to determine if a variant is an array?
0
Comment
Question by:shambalad
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
6 Comments
 
LVL 50

Accepted Solution

by:
Gustav Brock earned 500 total points
ID: 37820684
IsArray exists already as a native function of VBA. But you may need this test:
Public Function IsReDimmed(ByRef varArrayToCheck) As Boolean

' Checks a dynamic array to see if it has been ReDim'med.
' This is to prevent errors before applying LBound() or UBound().
'
' Returns False if varArrayToCheck is dynamic and
' never has been ReDim'med or just has been erased.
'
' 2001-08-11. Cactus Data ApS, CPH. Gustav Brock.

  Dim booIsReDimmed As Boolean
  
  On Error GoTo Err_IsReDimmed
   
  If IsArray(varArrayToCheck) = True Then
    ' Check if varArrayToCheck is ReDim'med.
    ' LBound() will fail if varArrayToCheck not has been ReDim'med.
    booIsReDimmed = LBound(varArrayToCheck) Imp True
    ' No error raised; varArrayToCheck is ReDim'med.
  Else
    ' varArrayToCheck is not an array.
  End If
  
  IsReDimmed = booIsReDimmed
  
Exit_IsReDimmed:
  Exit Function
  
Err_IsReDimmed:
  Select Case Err
    Case 9
      ' Subscript (array index) out of range.
      ' varArrayToCheck is dynamic and not ReDim'med.
    Case Else
      ' Other error.
  End Select
    ' Exit function returning False.
  Resume Exit_IsReDimmed

End Function

Open in new window

/gustav
0
 
LVL 7

Author Comment

by:shambalad
ID: 37820707
>  IsArray exists already as a native function of VBA
Yeah, it's 2:46AM on a Sunday morning and I probably should shut this computer down and go to sleep. It's a good thing I don't take myself too seriously...
Thanks for the IsReDimmed function. It will be useful.
Good night.
Todd
0
 
LVL 7

Author Closing Comment

by:shambalad
ID: 37820708
Thanks again.
th
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 50

Expert Comment

by:Gustav Brock
ID: 37820984
You are welcome!

/gustav
0
 
LVL 7

Author Comment

by:shambalad
ID: 37822348
Gustav -
I must say your perspective never ceases to intrigue me. I have been looking over the IsReDimmed function you posted last night and I have a question about one of the lines of code:

booIsReDimmed = LBound(varArrayToCheck) Imp True

I'm trying to discern your rationale behind adding the 'Imp True' piece. If I understand correctly how the Imp function works, when the second expression is True, the result is always True. In other words, this line of code should always return True. Would it be correct to say that the intent here is to ensure a valid 'True' value is always returned (as opposed to simply some non-zero result) so long as the LBound function does not kick out an exception? Or is there more to this than meets the eye?
Thanks,
Todd
0
 
LVL 50

Expert Comment

by:Gustav Brock
ID: 37822605
Good question. I forgot myself, and Imp is not carried forward to the never languages.

The catch is Null Imp True:

True Imp True = True
False Imp True = True
Null Imp True = True

But that is not important here. The goal is that if LBound does not fail, the expression should in any case - no matter if LBound returns zero (False) or some other number (True) return True.

The rationale behind the function is, that if a dynamic array has not been ReDim'med or has been erased, functions like LBound() and UBound() will fail. This is so basic that one could believe code could be found all over the net, but no. On the contrary, the question is raised again and again in the user groups how to check this.

There is - as far as I can see - no way to find out except to apply xBound() on the array and see if it fails. Very primitive.

Note that the array to test is not passed as an array but as a variant. This is to allow for the array to be declared as whatever type you like. Doing so, you will have to check if the variable is an array but that is very easy to do.


Another method I just looked up is suggested by A. D. Tejpal using the IsError function (which I never have used):
Public Function Fn_IsArrayInitialized(ByVal varArray As Variant) As Boolean
    On Error Resume Next
    Fn_IsArrayInitialized = Not IsError(UBound(varArray) >= 0)
End Function

Open in new window

It seems to do exactly the same as mine but a bit simpler.

/gustav
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question