Initialising array directly from range name

ElvisSteel

Board Regular
Joined
Mar 23, 2009
Messages
122
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:
Code:
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:
Code:
ArrayIn = Array(Range("ArrayValues"))
but this is not working

Any ideas?

Thanks
 

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

MickG

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
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.

Code:
[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

Board Regular
Joined
Mar 23, 2009
Messages
122
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

MrExcel MVP
Joined
Jan 9, 2008
Messages
14,841
Hi, Perhaps like this:-
Code:
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,510
Messages
5,832,169
Members
430,114
Latest member
kefier

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top