Assigning values into array using the "Array" vba function.......not working

[welchs101]

Hi,

I have an array such as this

Please Login or Register  to view this content.

I tried using this assignment and it does not work........

Please Login or Register  to view this content.

The following will work but i dont want to do this if i dont have to.

Please Login or Register  to view this content.

any ideas on what i am doing wrong.

[GeneralDisarray]

i've always hit the same wall (and i go through the same solution)

You can use a Variant type to behave like an array to make this work - step through this to see it in action.

Please Login or Register  to view this content.

[Leith Ross]

Hello welchs101,

The problem lies with the how the two arrays are dimensioned. Your array is dimensioned 1 to 6 making the first element 1. The first element of a VBA array is 0 (zero).

[GeneralDisarray]

Yep, just found this explanation. "Array" returns a variant containing an array...

Excel 2010 Developer Reference > Visual Basic for Applications Language Reference > Visual Basic Language Reference > Functions
Array Function

Returns a Variant containing an array.

Syntax

Array(arglist)

The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant. If no arguments are specified, an array of zero length is created.

Remarks

The notation used to refer to an element of an array consists of the variable name followed by parentheses containing an index number indicating the desired element. In the following example, the first statement creates a variable named

Please Login or Register  to view this content.

as a Variant. The second statement assigns an array to variable

Please Login or Register  to view this content.

. The last statement assigns the value contained in the second array element to another variable.

Please Login or Register  to view this content.

The lower bound of an array created using the Array function is determined by the lower bound specified with the Option Base statement, unless Array is qualified with the name of the type library (for example VBA.Array). If qualified with the type-library name, Array is unaffected by Option Base.


Note
A Variant that is not declared as an array can still contain an array. A Variant variable can contain an array of any type, except fixed-length strings and user-defined types. Although a Variant containing an array is conceptually different from an array whose elements are of type Variant, the array elements are accessed in the same way.


Example
This example uses the Array function to return a Variant containing an array.

Please Login or Register  to view this content.

[snb]

Equivalents:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

these are variant variables, containing a 1-dimensional arrays

not equivalent

Please Login or Register  to view this content.

Please Login or Register  to view this content.

you will see junk_array3(2) is as string "12-03-2011"
junk_array3a(2) is a Date

If you dimension an array you'll have to fill it using:

Please Login or Register  to view this content.

[welchs101]

i think i get it. I am going to have to read this a few times but what i take from it is this..........i defined an array of type string and if i want to use the "ARRAY" assignment then i have to use it on an array of type variant.

i will read it again, but thanks a lot for responding.

little things like this i spend hours on ..........thanks!

i wish there was a really good array tutorial for excel.......i mean i understand arrays but using them in excel is "different".....like assigning a range to an array etc..........wish there was a summary some where.