Assign Values to array

[Jeff]

Hi,

I wanted to assign constants to an array at once.

Dim KK(1 to 10) as double

KK = ( 1, 2, 3, 4, 5, 6, 7,8,9,0)
Doesnt work...

How can I assign values to the array at once without assigning one by one.
KK(1) = 1
KK(2) = 2
.... etc

Thanks

 

[Dave Peterson]

One way:

dim kk() as variant
dim iCtr as long
kk = array(1,2,3,4,5,6,7,8,9,0)

for ictr = lbound(kk) to ubound(kk)
msgbox kk(ictr) & "--" & ictr
next ictr

 

[Jeff]

That works thanks for your help.

Is there a reason it has to be declared as a variant, I guess it really
doesnt matter for my case.

 

[Rick Rothstein \(MVP - VB\)]

The Array function requires it. That is because you can put almost anything
in the argument list to the Array function, as long as you remember what is
what, of course.<g> For example...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))
MsgBox V(0) & vbCrLf & V(1) & vbCrLf & V(2).Address

Notice that the 3rd element, V(2), is a Range object, so to MessageBox out
something from it, you need to reference one of its properties. True, I
could have let it use its default Value property, but I wanted to positively
demonstrate that it was an actual object being stored in the third element.

Rick

 

[Dave Peterson]

Dim V as variant
declares V as a variant that will end up holding an array.

This is different than this:
Dim V() As Variant
Which declares V as an array that will hold different types of elements.

 

[Dave Peterson]

Some versions of excel will allow you to do:

Dim V() As Variant 'or long or whatever
V = Array(1, 3, "a")

But not all versions support this syntax in the Dim statement. (I _think_ xl97
would not allow it--but I don't recall for sure.)

I find it easier to not have to remember and just use the syntax that works for
all versions.

 

[Rick Rothstein \(MVP - VB\)]

I am not sure I understand your point. Are you saying you think I should be
declaring V as a dynamic array V() instead? If so, why? While I am not sure
there is any practical difference between declaring V as a simple Variant or
as a Variant Array when it comes to the net result when assigning the result
of an Array function call to it, I would note that, according to the Help
files, the Array function returns "a Variant containing an array" and, as
such, does not need to have the variable it is being assigned to be a
variant array in order to work.

Rick

 

[Dave Peterson]

I guess my point is that there is a difference between a variant that can hold
an array (or anything else) and a array of variants.

But my real point to the OP was that some versions allow this syntax:

dim V() as variant
v = array(1,2,"A")

and some versions don't allow it.

 

[Rick Rothstein \(MVP - VB\)]

See inline comments...

I guess my point is that there is a difference between a variant that
can hold an array (or anything else) and a array of variants.

The only practical difference I can think of when assigning an array (of any
kind) to either of those declarations is that Variant variable declared as a
dynamic array must **always** be treated as an array (that is, when not
empty, it must always hold an array or it can only have an array assigned to
it) whereas a pure Variant variable can be assigned non-arrays as well as
arrays at any time... however, after assigning an array to either of them,
there is no practical difference in how you assign values to, or retrieve
values from, the array elements.

But my real point to the OP was that some versions allow this syntax:

dim V() as variant
v = array(1,2,"A")

and some versions don't allow it.

This is because with the syntax shown, you are assigning an array directly
to a dynamically declared array... the ability to do this was added in
Version 6 of VB/VBA.

Rick

 

[Dave Peterson]

The OP was asking (at least the way I read the question(!)) why I used:

Dim V as Variant
instead of
Dim V() as Long 'or variant or whatever.

This explanation:

This is because with the syntax shown, you are assigning an array directly
to a dynamically declared array... the ability to do this was added in
Version 6 of VB/VBA.

seem to disagree with this earlier statement:

The Array function requires it. That is because you can put almost anything
in the argument list to the Array function, as long as you remember what is
what, of course.<g> For example...

 

[Rick Rothstein \(MVP - VB\)]

The OP was asking (at least the way I read the question(!)) why I used:

Dim V as Variant
instead of
Dim V() as Long 'or variant or whatever.

Not exactly. The OP had posted this...

Dim KK(1 to 10) as double

You responded with this (note the parentheses on kk)...

dim kk() as variant

The OP then asked...

Is there a reason it has to be declared as a variant

To which I attempted to respond that the Variant (as opposed to any specific
data type such as Long) was required by the Array function and then I used
this example as a demonstration (note there are no parentheses on V) to show
that the Array function can return non-homogeneous data types requiring a
Variant to receive them...

Dim V As Variant
V = Array(123, "Text String", Range("A1"))

I think it is at this stage that I introduced some confusion into the
discussion. The interchangeability of these declarations...

Dim V As Variant
Dim V() As Variant

when what is being assigned to V is an array (the output from an Array
function being one such source) is clear in my own head and I never gave a
second thought that it might not be to others... I should have mentioned
something about it in my initial posting, but just didn't think to. You
eventually got me to do that, but it was like pulling teeth getting me
there<g>... sorry.

Rick

 

[Dave Peterson]

Ouch!!!!

I didn't mean to!!!!

(stupid fingers!)

In the words of Emily Litella: "Never Mind"

http://en.wikipedia.org/wiki/Emily_Litella

========
So we are in violent agreement! We both suffer from, well, something! <vbg>

 

[Rick Rothstein \(MVP - VB\)]

Ouch!!!!

I didn't mean to!!!!

(stupid fingers!)

In the words of Emily Litella: "Never Mind"

http://en.wikipedia.org/wiki/Emily_Litella

========
So we are in violent agreement! We both suffer from, well, something!
<vbg>

LOL... yes, I guess we are.<g> By the way, there was nothing to compare to
the original SNL cast... I used to look forward to Radner's Emily Litella...
great comedy back then.

Rick

 

[Dave Peterson]

After George Calin died, SNL repeated the very first show.

My memory of that show was much better than the actual show.

 

[Rick Rothstein \(MVP - VB\)]

I don't actually remember the *first* show (and I missed that repeat), but
my recollection is not of any single show (there were good and bad among the
lot), rather, it is of the overall impression of the first couple or so
years of the series. I think I understand what you mean though... between
then and now, times are different, tastes are different, socially accepted
(expected) humor is different, etc.

Rick