How to tell if an array is initialized for For Each loops?

Question

I'm trying to use For Each with Arrays. But am stumbling in some cases. My solutions feel cumbersome.
When passing an array into another function, sometimes that array may not be initialized.

I've approached this in a couple different ways. First way, I dimension or ReDim it to 0. Dim myString(0) AS String.

So, if I have a Class clsCustomer. I can do this:
Public Function PrintCustomerNames(ByRef Customers() as clsCustomer)
Dim Customer as Variant
For Each Customer in Customers
     Debug.Print Customer.Name
Next
End Function

In the case of dimensioning to 0, the For Each loop itself will initialize. But I will get an error when trying to use Customer in the loop, because there is no object at Customers(0). But I can't seem to check if Customers(0) has an object reference at index 0. Thus, error handling soon to takes over like the Blob in that 1958 movie.

The second way, I created the a global function below to check for initialization of arrays:
Public Function modArrayExtensions_IsArrayInitialized(ByRef arr As Variant) As Boolean
On Error GoTo Error_Handler
Dim Dimensioned As Long
Dimensioned = UBound(arr)
'' If code gets here, the array is initialized.
modArrayExtensions_IsArrayInitialized = True
Exit Function
Error_Handler:
If Err.Number = 9 Then modArrayExtensions_IsArrayInitialized = False
End Function

But checking if the array is initialized before using an array every time seems excessive. And still means extra conditions may be required when it is discovered that the array is not initialized. Is there a simpler way to do this?

Answer 1

HTHP -

I can see a couple point that might help.

A) The passed variable, Customers() may be Nothing

B) As you stated, Customers(0) may be nothing, but Customers(1) might be clsCustomers.

Here's how I would approach it using your example

Public Function PrintCustomerNames(ByRef Customers() as clsCustomer)
 Dim Customer as variant    ' As you are more confident, use "as clsCustomer" instead of Variant
 If isarray(Customers) then   
    For Each Customer in Customers
         if not customer is nothing then
            Debug.Print Customer.Name
         end if
    Next
 end if
 End Function

---

-MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

Answer 2

The VBA complier requires "For Each variable on arrays must be Variant". I usually declare the variable twice, Once as the correct object type(ie clsCustomer), then as variant. During compile, I comment out the custom Type and leave the Variant code.

My big issue with this approach is that the array being passed into the function may not be initialized. For instance, lets say some code calls a function to get an array before passing it to another function.

Like: Public Function GetCustomersWithOpenBalance() AS clsCustomer()
But, lets assume all customers have paid in full and own nothing. This function then passes an uninitialized array to PrintCustomerNames function. In this case, the loop itself will not initialize because the array was not initialized. This is why I was initializing arrays with empty values at index 0 in the first place.

Although I see your point that an array indexes should be allowed to point to uninitialized objects. In which case thank you for showing how to handle those cases. But what should be done if the array passed in is not initialized itself? Thus, the loop will not initialize. Do I have to precede every single For Each with a custom method to check if the array is initialized before attempting giveing control to For Each loops? And if so, how does my approach to that look?

Answer 3

It looks like your last post was removed MainSleuth? Anyways,
I completely agree with you on using Collections, or Dictionaries. The Collection type in VBA is almost as tedious as an array, and lacks InteliSense, and I don't know how to cast from one type to another in VBA(is that possible?). To create decent collection workflow is like creating a framework to create a framework when I'm already trying to model the business domain, it all seems to get really obscured.

I did try the IsArray() method. It does not return False if the array is not initialized. It returns True as long as the alias is declared as an Array. This has its merits! But it DOES do something very curious.

This will throw an exception, the loop will not initialize:
Dim Customers() As clsCustomer
For Each Customer In Customers
If Not Customer Is Nothing Then
Debug.Print Customer.Name
End If
Next

However, just the mere use of IsArray() method before that loop, and it will work!!:
This will throw an exception, the loop will not initialize:
Dim Customers() As clsCustomer
Debug.Print IsArray(Customers)
For Each Customer In Customers
If Not Customer Is Nothing Then
Debug.Print Customer.Name
End If
Next

Answer 4

Yes, I killed it because as you stated, when you test IsArray(Customers())and leave it empty, it returns true, so your point is quite valid. That's why I mentioned Recordsets, collections, Dictionaries.

I suppose you could do the thing I despise and it appears that you do as well - deliberately force/trap the error:

dim lngCustTopBound as long
On error resume next
lngCustTopBound=UBound(Customers)
if err<>0 then
for/each


...

else
  debug.Print "Everybody is paid up."
end if

---

-MainSleuth You've Got It, Use It! Engineering, Science, Statistics Solutions http://ToolSleuth.com

Answer 5

Like: Public Function GetCustomersWithOpenBalance() AS clsCustomer()
But, lets assume all customers have paid in full and own nothing. This function then passes an uninitialized array to PrintCustomerNames function. In this case, the loop itself will not initialize because the array was not initialized. This is why I was initializing arrays with empty values at index 0 in the first place.

Hi HTHP,

In many of comparable situations I retrieve a comma-separated string instead on an array. Nice thing is that that string can be used in combination with the IN component in a sql-string. When passed to the next routine, if the string is a ZLS, then nothing is done, else it is converted to an array using the Split function.

Another way to test if an array is initialized could be:

Function Is_init(cur_array) As Boolean
  Dim x As Integer
  
  On Error GoTo fout
  x = UBound(cur_array)
  Is_init = True
fout:
End Function

Imb.

Answer 6

Imb. I use that approach in most simple cases. Here, however, I'm really concerned with objects a bit more complex than primatives(string, int, bool, and the like). In which case I cannot use a delimited string unless I want to attempt the ridiculous feat of writing a library of code to serialize all of my custom classes.

Yes MainSleuth, I'm attempting to trap the error. But as I pointed out, its incredible how just the use of IsArray() initialized the variable for use in the For Each loop. Although its a side effect, and I could risk it not happening in a future version of MS Access, I think its cleaner than forcing/trapping the error. Just debug.print IsArray(Customers). Its ugly, but relatively cleaner than forcing an error.

Answer 7

Hi HTHP,

you had mentioned that,"Although its a side effect, and I could risk it not happening in a future version of MS Access, I think its cleaner than forcing/trapping the error. Just debug.print IsArray(Customers). Its ugly, but relatively cleaner than forcing an error."

I don't understand why you think that debug.print is better then error handling?

does it mean , when every time you run the code you will check in the immediate window that array is initialized or not.

Regards

Deepak 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

Are you asking if I actually plan to check the immediate window with my own eyes? God no. Debug.Print is just coincidental. One could just as easily use the IsArray() method in any context they like. Set the result to a variable, use If Then condition, anything.

Error handling in this case would require the code to force the error, code to trap and check the error(because it could be some other unexpected error), and then more code handle the results of the error before handing control back. I admit its a bid obscure, but a single comment could explain the intention of the IsArray() call. The effect of it initializes the object for use in the For Each loop, no extra code, no handling unexpected errors, no disruption in the control flow of the code.

For improved transparency, and so another developer can understand, I could write a descriptively named global wrapper function that used IsArray(), and document there what it is being used for, and its pros/cons. Then I wouldn't have to leave comments all over the code. I'm not saying its not "better", but with my limited VBA experience it feels cleaner in my opinion. It may be the convention to use VBA's library to achieve error handling. But, in effect(perhaps not using VBA semantics), this IS a form of error handling.

Answer 9

Hi HTHP,

from your last reply , it looks like you want to follow that approach.

if that solves your issue then mark your last post as an answer and help us to close this thread.

Regards

Deepak

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].