For Each Array()

[bgcogen]

Hi.
This is a VBA program in Excel, but I hope someone could still help me??!!

I've got an array that contains 6 Strings.
I declare it as follows:

Dim MyArray As String
MyArray = Array(BG1, BG2, BG3, BG4, BG5, CP1)


I also have a block of code that performs a certain task. I want the program to perform this task based upon all 6 strings i.e.


For Each [element in the array]

Do this
Do that

Next [element in the array]

But I'm just getting some weird variable error!


Any ideas anyone????

Thanks,
D

 

[Drevlin]

Your problem probably isn't with the Array coding. The problem more then likely is the variables you are passing to the code. What type of information are you holding in the Array? Cell addresses or actual variables? What type of calculations are you making with the variables?

 

[bgcogen]

The Array is holding a group of 6 strings.

The calculations are just for changing the values in cells.

D

 

[Drevlin]

First I apologize for not seeing the line that read:
Dim MyArray as String

As far as your problem goes. I believe the reason your having trouble is because the Array() function uses Variant data type.

What you were trying to do is place an array inside of a string variable. This of course won't work. The variant datatype accepts Arrays and thus:
Dim MyArray as Variant
MyArray = Array(BG1, BG2, BG3, BG4, BG5, CP1)

would work (technically).

I'm not EXACTLY sure on this but I believe that excel is reading BG1 as a range (and so prints "" when you: Debug.Print MyArray(0) ). In order to make this work the way you want you should try this:

Dim MyArray(5) As String
MyArray(0) = "BG1"
MyArray(1) = "BG2"
MyArray(2) = "BG3"
MyArray(3) = "BG4"
MyArray(4) = "BG5"

For x = 0 To 5
Debug.Print MyArray(x)
Next x


I hope this helps.