arrays

[Hemant_india]

hi
i have several arrays defined with dim statement
e.g. dim a(),dim b(),dimc() and so on
is it posssble to redim these arrays using for...each statement?
thanks

 

[Bob Phillips]

For Each ... 'whatever
Redim Preserve array_name(1 To new_upper_bound)
'do something
Next

--
---
HTH

Bob

__________________________________________
UK Cambridge XL Users Conference 29-30 Nov
http://www.exceluserconference.com/UKEUC.html

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Hemant_india]

hi bob
actually i am thinking of considering all arrays as collection of objects
for each obj in collection...and so on

 

[Chip Pearson]

I think the only way to do a For Each would be to store the arrays in a
Collection and loop through that.

Dim Coll As New Collection


Sub AAA()
Set Coll = Nothing
Dim V As Variant
Dim Arr1()
Dim Arr2()
Dim Arr3()
Coll.Add Arr1
Coll.Add Arr2
Coll.Add Arr3

For Each V In Coll
ReDim V(1 To 3)
Next V
End Sub


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Alan Beban]

I think the only way to do a For Each would be to store the arrays in a
Collection and loop through that.

Dim Coll As New Collection


Sub AAA()
Set Coll = Nothing
Dim V As Variant
Dim Arr1()
Dim Arr2()
Dim Arr3()
Coll.Add Arr1 '<--------Error message
Coll.Add Arr2
Coll.Add Arr3

For Each V In Coll
ReDim V(1 To 3)
Next V
End Sub

I get an "Object variable or With block variable not set" error message
where indicated.

Alan Beban

 

[Rick Rothstein \(MVP - VB\)]

You should really be using the Option Explicit statement in your code
modules... then you would have gotten a "Variable Not Defined" error with
the Set Coll statement highlighted and that might have led you to the fact
that you did not include the

Dim Coll As New Collection

statement where Chip showed it in his posted code. Try adding that statement
in the (General)(Declarations) section of the code window and see if that
solves your problem.

By the way, to make the VBA editor automatically include the Option Explicit
statement, click Tools/Options on the VBA editor menu bar, select the Editor
tab on the dialog box that appears and put a check mark next to "Require
Variable Declaration", then OK out of the dialog box.

Rick

 

[Hemant_india]

allen
sorry for answering this late
alen(arr,1) returns number of elements in the first dimension of array-arr

 

[Hemant_india]

thanks Chip
thnk u very much
--
hemu

You should really be using the Option Explicit statement in your code
modules... then you would have gotten a "Variable Not Defined" error with
the Set Coll statement highlighted and that might have led you to the fact
that you did not include the

Dim Coll As New Collection

statement where Chip showed it in his posted code. Try adding that statement
in the (General)(Declarations) section of the code window and see if that
solves your problem.

By the way, to make the VBA editor automatically include the Option Explicit
statement, click Tools/Options on the VBA editor menu bar, select the Editor
tab on the dialog box that appears and put a check mark next to "Require
Variable Declaration", then OK out of the dialog box.

Rick

 

[Alan Beban]

You should really be using the Option Explicit statement . . . Try adding that
statement in the (General)(Declarations) section of the code window and
see if that solves your problem. . . .
Rick

Thanks; it highlights the underlying problem. I now get an error message
"Subscript out of range" where indicated when I check to see if the
redimming was successful. It seems that in the loop, when V is redimmed,
the corresponding item of the collection is unaffected.

Alan Beban

 

[Chip Pearson]

It was a mistake (inadequate testing) on my part. You'd need to add the
Redim'd array back into the Collection are remove the original element. All
said and done, it is likely all overkill simply to For Each on a set of
arrays.

Dim Coll As New Collection

Sub AAA()
Set Coll = Nothing ' clear out the collection
Dim V As Variant
Dim N As Long
Dim Arr1() As Long ' declare some dynamic arrays
Dim Arr2() As Long
Dim Arr3() As Long
Coll.Add Arr1 ' add them to the collection
Coll.Add Arr2
Coll.Add Arr3
For N = 1 To Coll.Count
V = Coll(N)
ReDim V(1 To 3) ' redim each array 1 to 3
V(1) = N * 10 ' enter some easiliy identiful values
V(2) = N * 100
V(3) = N * 1000
Coll.Add V, before:=N ' insert newly redim'd array back to
collection
Coll.Remove N + 1 ' remove original array
Next N

''''''''''''''''''''''''''''''''''
' Confirmation of correct results.
''''''''''''''''''''''''''''''''''
For N = 1 To Coll.Count
V = Coll(N)
Debug.Print "================="
Debug.Print "ARRAY: " & N, " ALLOCATED: " & IsArrayAllocated(V)
Debug.Print "VALUES OF ELEMENTS:"
Debug.Print V(1), V(2), V(3)
Next N
End Sub


Function IsArrayAllocated(A As Variant) As Boolean
'''''''''''''''''''''''''''''''''''''''''''''''''''''
' Ensure that a dynamic array is actually allocated.
'''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
IsArrayAllocated = Not IsError(LBound(A)) And LBound(A) <= UBound(A)
End Function



And don't get started on the old "Ranges As Arrays" bit. Ranges are not
arrays.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Alan Beban]

And don't get started on the old "Ranges As Arrays" bit. Ranges are not
arrays.

No, no. You somehow got it in your mind that I disagree with you on
that; I don't! I mentioned before about how irritating it was to me
(many years ago)) that IsArray(iVar) returned True when iVar was not an
array, but a range. But because it does, it is often necessary to
provide code to confirm that one is dealing with a true array and not a
multi-cell range that IsArray treats as an array.

(You may be thinking of my erstwhile suggestion that Ranges are
Collections, which you find almost as attractive as the idea that ranges
are arrays )

Regards,
Alan