Populate Defined Named Range with multi-element array of multi-element arrays

Question

I have defined 5 arrays.

One with undefined dimensions to store the other 4:

Dim outputArr() As Variant

and the rest as follows:

Dim Arr1(5, 0), Arr2(12, 0), Arr3(5, 0), Arr4(12, 0) As Variant

I assign the elements of the latter as follows:

Arr1(0, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]
Arr1(1, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]
Arr1(2, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]
Arr1(3, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]
Arr1(4, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]
Arr1(5, 0) = [{0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0}]

The above is applied to each array.

When I use

ReDim outputArray(3, 0)

outputArr = [{Arr1, Arr2, Arr3, Arr4}]

I get a 'Type Mismatch' error.

When I do not use Evaluate and assign without ReDim

outputArr = Array(Arr1, Arr2, Arr3, Arr4)

I can see the elements and their values in the Watch window, but when I try to populate Defined Named Ranges with the elements of outputArr I get an empty output

Range("nRange1name").Value = outputArr(0)
Range("nRange2name").Value = outputArr(1)
Range("nRange3name").Value = outputArr(2)
Range("nRange4name").Value = outputArr(3)

How can I work around this?

@RyanWildry, I am afraid this is it. The only things that I have left out are the assignments of the other 3 arrays Arr2, Arr3, Arr4 and a pretty lengthy Select Case that adds values to the array elements, before I assign them to the parent array outputArr. — I. Я. Newb
– I. Я. Newb, Commented Nov 25, 2019 at 22:04

freeflow · Accepted Answer · 2019-11-25 23:11:08Z

1

The use of variants in the OP code introduces unecessary dimensions. I don't understand why two transpose functions are needed but the following code pastes 2d arrays satisfactorily.

Option Explicit

Sub TestArrays()

Dim outputArr               As Variant
Dim Arr1                    As Variant
Dim Arr2                    As Variant
Dim Arr3                    As Variant
Dim Arr4                    As Variant

Arr1 = Array(Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Array(1, 2, 3, 4, 5, 6, 7, 8, 9))
Arr2 = Array(Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Array(1, 2, 3, 4, 5, 6, 7, 8, 9))
Arr3 = Array(Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Array(1, 2, 3, 4, 5, 6, 7, 8, 9))
Arr4 = Array(Array(1, 2, 3, 4, 5, 6, 7, 8, 9), Array(1, 2, 3, 4, 5, 6, 7, 8, 9))

outputArr = Array(Arr1, Arr2, Arr3, Arr4)
' For Horizontal ranges
Range("A1:H2") = Application.WorksheetFunction.Transpose(Application.WorksheetFunction.Transpose(outputArr(2)))


'For Vertical ranges
Range("A4:B11") = Application.WorksheetFunction.Transpose(outputArr(3))

End Sub

answered Nov 25, 2019 at 23:11

freeflow

4,3753 gold badges13 silver badges19 bronze badges

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

1 Comment

I. Я. Newb Over a year ago

I will test out this approach in the morning.

Tim Williams · Accepted Answer · 2019-11-25 23:05:06Z

1

You need to construct an actual 2D array to do something like that.

Dim arr(1 to 6, 1 to 12)
dim r as long, c as long

for r = lbound(arr, 1) to ubound(arr, 1)
    for c = lbound(arr, 2) to ubound(arr, 2)
        arr(r, c) = 0
    next c
next r

Range("A1").Resize(ubound(arr, 1), ubound(arr, 2)).value = arr

answered Nov 25, 2019 at 23:05

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

5 Comments

I. Я. Newb Over a year ago

Thank you very much. I will try your approach. However, I am doing my best not to go to loops for that, because the database where the data for the elements comes from is pretty big. I used a Select Case for the purpose, as I do not have rights to use Dictionaries not to mention FSO on my corporate Excel account.

Tim Williams Over a year ago

I never worry about performance unless it proves to be poor. The obvious way is typically the best way, and only occasionally will you ever need to come back and re-work something. EDIT: clearly "obvious" is subjective and I realize different people will take different approaches...

I. Я. Newb Over a year ago

I will try this in the morning.

I. Я. Newb Over a year ago

thank you for the time and effort spent on my question. However, Freeflow provided a simpler, yet efficient solution. Assigning elements to a 2D array with loop is like looping trough all the cells that I need to be populated. This is what I was avoiding the whole time, as performance was the reason this project was returned to our team in the first place.

Tim Williams Over a year ago

Your choice, but want to point out that looping over an array is not comparable in performance to looping over a range cell by cell. If you need confirmation of that I’d urge you to test it for yourself.

Collectives™ on Stack Overflow

Populate Defined Named Range with multi-element array of multi-element arrays

2 Answers 2

1 Comment

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related