Manually assign a 2D array with 1 entry

Question

I am trying to assign a 2D array by just setting the array equal to an excel range (n Rows X 1 Column).

When (n = 1) this just gives me a value, so I want to manually assign the array in this case. I tried the below but it populates with a Error 2015

Function Get_Params(ws As Worksheet, LR As Long, Target As Range) As Variant

Dim Temp As Worksheet: Set Temp = ThisWorkbook.Sheets("Temp")
Dim LR2 As Long

ws.Range("D1:D" & LR).SpecialCells(xlCellTypeVisible).Copy
Temp.Range("U1").PasteSpecial xlPasteValues

Temp.Range("U1").RemoveDuplicates 1, xlYes
    LR2 = Temp.Range("U" & Temp.Rows.Count).End(xlUp).Row

    If LR2 = 2 Then
        Get_Params = [{Temp.Range("U2"), 1}] '<-- ISSUE HERE
    Else
        Get_Params = Temp.Range("U2:U" & LR2).Value
    End If

    Temp.Range("U1").EntireColumn.ClearContents

End Function

The goal is to do something like

For i = LBound(Get_Params, 1) to UBound(Get_Params, 1)
    'Do something with Get_Params(i, 1)
Next i

I tried to manually assign the array via this solution utilizing the Evaluate function, but this yields my error. I have also tried to use Get_Params = [{Temp.Range("U2").Value, 1}] and receive same error

How can I manually assign this array to allow me to go through the above loop without error?

What sort of object is `Get_Params = [{Temp.Range("U2"), 1}]` supposed to construct? A 1-based 2-dimensional array whose sole value is `Ger_Params(1,1) = Temp.Range("U1")`? — John Coleman, Mar 21 '19 at 18:19
@JohnColeman The same type of object that would be generated with `Get_Params = Temp.Range("A1:A10").Value`. The first element would have 10 values, and the second would have 1 value. The only difference between the array set is that one will strictly have 1 value in each element and the other will be a function of `LR2` — urdearboy, Mar 21 '19 at 18:20
My goal is to tackle that issue here in the function rather have to recreate the exact same code on my main sub that does the same thing for the array vs the value I.E. `If Array Then Do This, Else Do The Exact Same Thing for Value`. If I can force it to match the 2D array, my main macro needs no modification. — urdearboy, Mar 21 '19 at 18:21
`Get_Params = Temp.Range("A1:A10").Value` Has 10 values, each of which is accessed with 2 indices. — John Coleman, Mar 21 '19 at 18:22
Yes, @JohnColeman. So how do I get `Get_Params` to have 1 value with 2 indices where the 2nd index is 1 and the 1st index is the value in `U2`is the question — urdearboy, Mar 21 '19 at 18:23

score 2 · Accepted Answer · answered Mar 21 '19 at 18:27

2

You could replace

Get_Params = [{Temp.Range("U2"), 1}]

By

Dim returnValue As Variant

'... then in the if statement:

ReDim returnValue(1 to 1, 1 to 1)
returnValue(1,1) = Temp.Range("U2").Value
Get_Params = returnValue

answered Mar 21 '19 at 18:27

John Coleman

51,337
7
54
119

Manually assign a 2D array with 1 entry

1 Answers1