CODEWITHSUNDEEP
excelvba
urdearboy
urdearboy

Reputation: 14580

Manually assign a 2D array with 1 entry

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?

Upvotes: 2

Views: 107

Answers (1)

John Coleman
John Coleman

Reputation: 51998

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

Upvotes: 2

Related Questions