Excel VBA 'Overflow' Error Returning Range to UDF

Question

I have a UDF which gets a range of data and trims off x number of rows from the top (row headers).

I call this to load the data range into a variant variable as an array.

I use this function in this way on all worksheets and it works fine every time, however on 1 particular worksheet it throws an 'overflow' error and I don't know why.

I call the function like this:

Dim vTableArray As Variant
vTableArray = Get_Data_Range(Invoices", "A11", 2)

The Function is:

Function Get_Data_Range(sWorksheetName As String, sDataRange As String, iResizeOffset As Long) As Range

    Set Get_Data_Range = Sheets(sWorksheetName).Range(sDataRange).CurrentRegion
    Set Get_Data_Range = Get_Data_Range.Offset(iResizeOffset, 0).Resize(Get_Data_Range.Rows.Count - iResizeOffset, Get_Data_Range.Columns.Count)

End Function

When I debug the code, the error is thrown on the 'End Function' line of the UDF.

The range of data has 4925 rows and 59 columns. There are no errors in the data range.

Any help fixing this error appreciated.

Note that your function is not a UDF. A UDF (=User defined function) is a function that you can use in an Excel formula. UDFs have some limitations (eg they might not modify a sheet). In your case, Get_Data_Range is a normal function. — FunThomas
– FunThomas, Commented Nov 27, 2023 at 13:59

James · Accepted Answer · 2023-11-27 13:18:16Z

1

I believe I've found the answer to my own question by reading this post:

Why am I having issues assigning a Range to an Array of Variants

If I explicitly tell the variant array to get .value2 from the range, the code works.

'   Load Data Into Array
    vTableArray = Get_Data_Range("Invoices", "A11", 2).Value2

edited Nov 27, 2023 at 13:18

answered Nov 27, 2023 at 13:17

James

213 bronze badges

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

Collectives™ on Stack Overflow

Excel VBA 'Overflow' Error Returning Range to UDF

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related