VBA Array query

[VCDHunter]

I'm looking to set the values of an Array outside of a procedure in excel but I'm not sure it can be done. This is what I have so far -

Public Names(1 To 5) As String
Set Names = Array("name1", name2", "name3", "name4", "name5")

but the "Set Names" argument gives me an "Invalid Outside Of Procedure" error.

Can this be done ?

[pchan]

Try this ...
Names = Array("name1", name2", "name3", "name4", "name5")

Just remove "Set". But remember... the index starts from zero.
Names(0) = "name1"
Names(1) = "name2"

[pchan]

Sorry.. I did a test and it bomb.

This will work.... change String to Variant !
Dim Names As Variant
Names = Array("name1", "name2", "name3", "name4", "name5")

Funny thing... declare Names(1 to 5) as String still work but have to assign individually. In this case, the index starts from 1. Whereas for Variant, the index starts from zero.

[VCDHunter]

Sorry.. I did a test and it bomb.

This will work.... change String to Variant !
Dim Names As Variant
Names = Array("name1", "name2", "name3", "name4", "name5")

Funny thing... declare Names(1 to 5) as String still work but have to assign individually. In this case, the index starts from 1. Whereas for Variant, the index starts from zero.

Yes, I've come to those conclusions too ! It's a weird one - thanks for your help.

[tekkieman]

@VCDHunter - I know you're sorted, but for pchan's benefit...

You cannot assign to a static array, only a dynamic one, such as Dim Names() as String. Then if you need a specific size, you can Redim Preserve.

You can also create non-zero index arrays, but the zero index array is standard.

[pchan]

@tekkieman, Thanks. Is this you are refering static vs dynamic...

Sub test()

'I think you refer to this as STATIC ARRAY !

Dim Names(1 To 5) As String

Names(1) = "name1"
Names(2) = "name2"
Names(3) = "name3"
Names(4) = "name4"
Names(5) = "name5"

'Test dump...
Sheets("Sheet1").Range("A1").Offset(0, 0) = Names(1)
Sheets("Sheet1").Range("A1").Offset(1, 0) = Names(2)
Sheets("Sheet1").Range("A1").Offset(2, 0) = Names(3)
Sheets("Sheet1").Range("A1").Offset(3, 0) = Names(4)
Sheets("Sheet1").Range("A1").Offset(4, 0) = Names(5)

End Sub

Sub test2()

'I think you refer to this as DYNAMIC ARRAY !

Dim Names As Variant

Names = Array("name1", "name2", "name3", "name4", "name5")

'Test dump...
Sheets("Sheet1").Range("A1").Offset(0, 0) = Names(0)
Sheets("Sheet1").Range("A1").Offset(1, 0) = Names(1)
Sheets("Sheet1").Range("A1").Offset(2, 0) = Names(2)
Sheets("Sheet1").Range("A1").Offset(3, 0) = Names(3)
Sheets("Sheet1").Range("A1").Offset(4, 0) = Names(4)

End Sub