Initialising array directly from range name

[ElvisSteel]

I have some VBA that requires an array to be initialised. It is a simple 8-element string array, which is working fine when populated in the code as follows:

ArrayIn = Array("A", "B", "C", "D", "E", "F", "G", "H")

I would like to have this populated from a named range, thereby allowing the user to modify the array values without changing the code.
I am sure this must be possible, and I realise I could do it by looping through the range data, but is there a neater way of achieving this.
I tried:

ArrayIn = Array(Range("ArrayValues"))

but this is not working

Any ideas?

Thanks

 

[MickG]

Hi, Do you mean like this:-
If you named Cell has your Letters Seperated by Commas, then this will dispay the array in row 10.

[COLOR="Navy"]Sub[/COLOR] MG27Jul47
[COLOR="Navy"]Dim[/COLOR] oLets [COLOR="Navy"]As[/COLOR] Variant, L [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Integer[/COLOR]
oLets = Split(Range("MyRng"), ",")
    
    [COLOR="Navy"]For[/COLOR] L = 0 To UBound(oLets)
        Cells(10, L + 1) = oLets(L)
    [COLOR="Navy"]Next[/COLOR] L
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

 

[ElvisSteel]

Mick,

Thanks for the reply.
I already have the array in separate cells, what I am trying to do is to allow the array in VBA to be populated directly from the range in the spreadsheet.

Using your example, we end up with the individual array elements in cells A10:J10. I want the VBA code to declare a 10-element array, and then fill the array elements with the contents of cells A10:J10 (which I would give a name to)

Steve

 

[MickG]

Hi, Perhaps like this:-

Dim MyArray
MyArray = Range("MyRange") ' "MyRange" = Range("A1:J10")

'Check array by Placing data back On sheet
Range("A12").Resize(, UBound(MyArray, 2)) = MyArray
'or
Range("A14").Resize(UBound(MyArray, 2)) = Application.Transpose(MyArray)

Regards Mick

 

[ElvisSteel]

Mick,

Thanks for your help, much appreciated