CODEWITHSUNDEEP
arraysexcelvbamultidimensional-array
I. Я. Newb
I. Я. Newb

Reputation: 329

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

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?

Upvotes: 0

Views: 95

Answers (2)

freeflow
freeflow

Reputation: 4355

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

Upvotes: 1

Tim Williams
Tim Williams

Reputation: 166511

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

Upvotes: 1

Related Questions