Assigning a named range to array

Question

I've found something that I don't quite understand. Please help explain.

so I used this code to assign values within named ranges to an array. (see below).

Dim accountStructure As Variant
ReDim accountStructure(2)
accountStructure(0) = Range("namedrange1")
accountStructure(1) = Range("namedrange2")
accountStructure(2) = Range("namedrange3")

The code works. However, in order to call the first item in accountstructure(0) I need to type accountStructure(0)(1,1), second item accountStructure(0)(2,1) third item (0)(3,1) and so on. I understand why there is (0) there but I don't understand why there is always a 1 after the item number. Please help me understand this.

You can (and should for performance reasons) manipulate ranges assigning them to arrays. So, if we have any range Dim rng as Range Set rng=Range("A1:A10") We can assign Dim arr As varient arr=rng But we have to treat it as two dimensional array. Because Excel VBA does'nt "know" it is one dimensional. So we can now For i=1 To 10 arr(i,1) = i Next — Siyon DP
– Siyon DP, Commented Jan 31, 2018 at 12:41
thanks TSion, so it will always be 2 dimensional, just how VBA works? Unfortunately the namedranges are dynamic (in a table). so this way is super easy. :) — Tanpo
– Tanpo, Commented Jan 31, 2018 at 12:44

hornetbzz · Accepted Answer · 2023-01-19 17:08:04Z

1

If we have any range

Dim rng as Range 
Set rng=Range("A1:A10")

We can assign

Dim arr As Variant 
arr = rng

But we have to treat it as two dimensional array. Because Excel VBA does'nt "know" it is one dimensional.
So we can now

For i=1 To 10 
    arr(i,1) = i 
Next

edited Jan 19, 2023 at 17:08

hornetbzz

9,4056 gold badges41 silver badges58 bronze badges

answered Jan 31, 2018 at 13:30

Siyon DP

5146 silver badges21 bronze badges

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

Collectives™ on Stack Overflow

Assigning a named range to array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related