Is there a "compact" way of assigning values to arrays, perhap
something like the FORTRAN DATA statement? Assume a string array
"strA", dimensioned at (5) and the need to assign values to strA(1)
strA(2), ... perhaps:

Dim strA(10) as String

strA(1) = "alpha"
strA(2) = "bravo"

Another way

Dim strA

strA = Array("alpha","bravo")

etc.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"MWE " wrote in message
...
Is there a "compact" way of assigning values to arrays, perhaps
something like the FORTRAN DATA statement? Assume a string array,
"strA", dimensioned at (5) and the need to assign values to strA(1),
strA(2), ... perhaps:

Dim strA(10) as String

strA(1) = "alpha"
strA(2) = "bravo"

It's not clear what you mean by "dimensioned at 5" followed by
Dim StrA(10). But in any event, be aware that the default condition is
that VBA arrays are 0-based. So after Dim strA(10) As String, the first
element of the array is accessed with strA(0). This can be overcome
either by including the statement Option Base 1 at the beginning of your
code module, or by explicit declaration, a la

Dim strA(1 to 10) As String

Alan Beban

MWE < wrote:
Is there a "compact" way of assigning values to arrays, perhaps
something like the FORTRAN DATA statement? Assume a string array,
"strA", dimensioned at (5) and the need to assign values to strA(1),
strA(2), ... perhaps:

Dim strA(10) as String

strA(1) = "alpha"
strA(2) = "bravo"

Alan: the Dim statement should have been for an array of length 5 (my
mistake) and I do know that VBA arrays assume an initial index of zero.
But thanks for replying.

MWE


---
Message posted from http://www.ExcelForum.com/

Bob: thanks for the reply about using the Array function. I have trie
to use it in the past and it never worked. I now understand that yo
can not define the dimenionality of the variable nor can you define it
type. So:

Sub TestArrayFill()
'
' procedure to test the use of the Array function
'
Dim strA(1 to 5) as String

strA = Array("a", "b", "c", "d", "e")
MsgBox strA(1) + " " + strA(5)

End Sub

generates compiler errors as does any other code that defines the scop
of strA or its type.

I can see where the use of Array would be useful, but it bothers m
that it is so vague and it encourages bad code. It also forces the us
of zero as the first index which I find irksome. Logically there is n
such thing as a zeroth index. I understand why it is there -- make
things more compact -- but it bugs me none the less.

Is there nothing that will allow me to "fill and array" that has bee
previously scoped and types. Seems like a very reasonable thing t
want.

Thanks

MW

--
Message posted from http://www.ExcelForum.com

only a variant can hold an array through an assignment - thus

dim strA as Variant

strA = Array("a", "b", "c", "d", "e")

is what works with the limitations you cite.

You could do

dim Dum as Variant
Dim strA(1 to 5)

Dum = Array("a", "b", "c", "d", "e")

i = 1
for j = lbound(dum) to ubound(dum)
strA(i) = j
i = i + 1
Next
Dum = Empty

or you could put your data in a file, read it in, and assign it to elements
of your array.

--
Regards,
Tom Ogilvy



MWE wrote in message
...
Bob: thanks for the reply about using the Array function. I have tried
to use it in the past and it never worked. I now understand that you
can not define the dimenionality of the variable nor can you define its
type. So:

Sub TestArrayFill()
'
' procedure to test the use of the Array function
'
Dim strA(1 to 5) as String

strA = Array("a", "b", "c", "d", "e")
MsgBox strA(1) + " " + strA(5)

End Sub

generates compiler errors as does any other code that defines the scope
of strA or its type.

I can see where the use of Array would be useful, but it bothers me
that it is so vague and it encourages bad code. It also forces the use
of zero as the first index which I find irksome. Logically there is no
such thing as a zeroth index. I understand why it is there -- makes
things more compact -- but it bugs me none the less.

Is there nothing that will allow me to "fill and array" that has been
previously scoped and types. Seems like a very reasonable thing to
want.

Thanks

MWE


---
Message posted from http://www.ExcelForum.com/

Not exactly. That's true for arrays of type other than Variant(). But
you *can* "assign" the dimensionality of a Variant() array, first by
declaring it as a dynamic array, then fixing the dimensionality, a la

Option Base 1
_____________________
Sub test1()
Dim strA() As Variant
ReDim strA(1 to 5)
strA = Array("a", "b", "c", "d", "e")
MsgBox strA(1) & " " & strA(5) '<---displays a e
End Sub

As I mentioned in a previous post in this thread, you can avoid the
0-base by using the Option Base 1 statement at the beginning of your
code module.

By the way, I don't know what logic you're intending to impose, but I
don't find anything illogical about a zeroth index, nor, for that
matter, negative indexes. They're just indexes, after all.

Dim strA(-2 to 3) declares a 1-dimensional array with lower bound -2,
upper bound 3, and, once loaded, elements accessible by

strA(-2)
strA(-1)
strA(0)
strA(1)
strA(2)
strA(3)

Alan Beban

MWE < wrote:
Bob: thanks for the reply about using the Array function. I have tried
to use it in the past and it never worked. I now understand that you
can not define the dimenionality of the variable nor can you define its
type. So:

Sub TestArrayFill()
'
' procedure to test the use of the Array function
'
Dim strA(1 to 5) as String

strA = Array("a", "b", "c", "d", "e")
MsgBox strA(1) + " " + strA(5)

End Sub

generates compiler errors as does any other code that defines the scope
of strA or its type.

I can see where the use of Array would be useful, but it bothers me
that it is so vague and it encourages bad code. It also forces the use
of zero as the first index which I find irksome. Logically there is no
such thing as a zeroth index. I understand why it is there -- makes
things more compact -- but it bugs me none the less.

Is there nothing that will allow me to "fill and array" that has been
previously scoped and types. Seems like a very reasonable thing to
want.

Thanks

MWE


---
Message posted from http://www.ExcelForum.com/