Solved: Problem with array

[NewDaddy]

Sub ColumnHeaders()
Dim myArray As Variant
Dim myCount As Integer
'Fill array
myArray = Array("Name", "Address", "Phone", "Email")
'Empty array
With Worksheets("Sheet1")
For myCount = 1 To UBound(myArray)
.Cells(1, myCount).Value = myArray(myCount)
Next myCount
End With
End Sub

Hi all

Attempting to understand arrays and have come up with the above example.

It works fine except it is missing the first value, "Name" so what I get is
Cell - A1, B1, C1
Value - Address, Phone, Email

For some reason it is ignoring "Name"?
I am not sure what is happening here?! I have tried a few things like changing the dimension? to myCount = 1 to 4, but this causing an error as it seems to only find 3 in the array.

Also can someone explain UBound(myArray) is doing? I have looked up UBound in the help files but cannot understand and I cant find a reference to UBound in my VBA book.

Can someone explain this in pain english?

Thank you very much.

Cheers
J

[Bob Phillips]

Unless you have explicitly added



Option Base 1


arrays start at 0 not 1. You should either start your index at 0, or better, cater for all possibilities



Dim myArray As Variant
Dim myCount As Integer
'Fill array
myArray = Array("Name", "Address", "Phone", "Email")
'Empty array
With Worksheets("Sheet1")
For myCount = LBound(myArray) To UBound(myArray)
.Cells(1, myCount).Value = myArray(myCount)
Next myCount
End With

[Bob Phillips]

UBound and LBound are the upper and lower boundaries of the array, the last and first element index.

[NewDaddy]

xld

Thank you, I though it would be as simple as that and now that you mention it, it makes sence.

When you say 'cater for all possibilities' and you provide amended code
For myCount = LBound(myArray) To UBound(myArray)
What do you mean? as I still needed to set the base as 1.

Your explaination of UBound & LBound.
Sorry but I still dont quite understand.
UBound is the lowest value while UBound is the highest, how does this work in this example? does it count the number of values in the array and as there are 4 UBound becomes 4.

Am I on the right line of thought? or do I not really worry about it as I have a working example?

Again thanks for your help.

Best wishes
J

[Bob Phillips]

No you don't have to set the base to anything, by looping from the lower to upper bounds, it will work correctly regardless of whether base 0 or base 1.

LBouund is the lower bound, UBound is the upper.

[SamT]

With no "Option Base" or "Option Base =0", LBound As Long =0. Then Cells(1, Mycount) means Cells(1,0) where the idexes are Varients, but 0 is not a valid cell index.


.Cells(1, myCount +1).Value = myArray(myCount)


Should be used.



With "Option Base =1, Lbound = 1. This will work in your example.

If you explicitly declare the array indexes,

Dim myArray(1 to4) As Variant
myCount = LBound(myArray) To UBound(myArray)

Will work.

SamT

[NewDaddy]

SamT & xld

Thank you both.
I am starting to understanding this better. I think I will now go away and look a bit more on the internet.

Cheers
Jay