Assigning Array Values

[MWE]

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"

 

[Bob Phillips]

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)

 

[Alan Beban]

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]

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

 

[MWE]

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

 

[Tom Ogilvy]

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.

 

[Alan Beban]

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