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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,647
Messages
5,597,361
Members
414,139
Latest member
okela0

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