Creating an array from named range

Question

I'm trying to create few arrays from named range in my workbook instead of defining that arrays manually.

Example: This is the array I want to create

Array("bardzo lekka", "lekka", "średnia", "ciężka") The same data is definied in named range $A$5:$A$8. I want to load that range into array like above.

I've tried to do this like that:

kategoria_a = ActiveWorkbook.Names("kategoria_agronomiczna_gleby").RefersToRange(1, 1)

It creates only one object array.

kategoria_a = ActiveWorkbook.Names("kategoria_agronomiczna_gleby")

That makes

kategoria_a = ='Dane wyjściowe'!$A$5:$A$8

Thanks in advance.

Is your question, how to load all a workbook's named ranges into an array? — BruceWayne
– BruceWayne, Commented Apr 3, 2018 at 15:06
Your manually-created array contains strings, not named ranges. Your question is unclear. — Mathieu Guindon
– Mathieu Guindon, Commented Apr 3, 2018 at 15:44
I want to load array kategoria_a from named value. It's definied in range $A$5:$A$8. Sorry if I made unclear question. — Filip Frątczak
– Filip Frątczak, Commented Apr 3, 2018 at 18:42

DisplayName · Accepted Answer · 2018-04-03 15:10:29Z

1

if your values are listed in along a column you could use:

kategoria_a = Application.Transpose(ActiveWorkbook.Names("kategoria_agronomiczna_gleby").RefersToRange.Value)

while if they are listed along a row then you could use

kategoria_a = Application.Transpose(Application.Transpose(ActiveWorkbook.Names("kategoria_agronomiczna_gleby").RefersToRange.Value))

to obtain a 1D array

answered Apr 3, 2018 at 15:10

DisplayName

13.4k2 gold badges13 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Filip Frątczak Over a year ago

This is what I intended to do. Than you.

DisplayName Over a year ago

Prosze bardzo :-)

MGP · Accepted Answer · 2018-04-03 15:17:42Z

1

Try this:

Dim a As Variant
Dim i As Long
Dim rng As Range, r As Range

a = Array("bardzo lekka", "lekka", "średnia", "ciężka")

For i = LBound(a) to UBound(a)
    If i = LBound(a) Then
        Set rng = Range(a(i))
    Else
        Set rng = Union(rng, a(i))
    End If
Next i

a contains all named ranges. To access the values you could use this:

For Each r In rng
    debug.print r.value
Next r

answered Apr 3, 2018 at 15:17

MGP

2,6732 gold badges22 silver badges35 bronze badges

1 Comment

Mathieu Guindon Over a year ago

IMO this is most likely what OP is asking about - although, there's no way to be sure until they clarify their question.

BruceWayne · Accepted Answer · 2018-04-03 15:11:16Z

0

This will take all the named ranges, and put them in an array (myArr):

Sub get_named_ranges()
Dim rng As Range
Dim nm
Dim myArr() As Variant

ReDim myArr(1 To ThisWorkbook.Names.Count)

Dim i As Long
i = 1
For Each nm In ThisWorkbook.Names
    myArr(i) = nm.Name
    i = i + 1
Next nm

For i = LBound(myArr) To UBound(myArr)
    Debug.Print (myArr(i))
Next i

End Sub

answered Apr 3, 2018 at 15:11

BruceWayne

23.5k16 gold badges75 silver badges126 bronze badges

Comments

mooseman · Accepted Answer · 2018-04-03 15:11:51Z

0

If you mean you want to set an array equal to the values in a named range,instead of entering them manually (like your example), then just set the array equal to the range. As long as "kategoria_agronomiczna_gleby" is your named range.

kategoria_a = ActiveWorkbook.Range("kategoria_agronomiczna_gleby")

You can use this loop to check the values in your array

 For i = LBound(kategoria_a, 1) To UBound(kategoria_a, 1)
    Debug.Print kategoria_a(i, 1)
  Next

answered Apr 3, 2018 at 15:11

mooseman

2,0052 gold badges17 silver badges31 bronze badges

Collectives™ on Stack Overflow

Creating an array from named range

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related