#VALUE error when dealing with long string in UDF in VBA(excel)

Question

I've encountered #VALUE error when using an UDF returning an array with long strings (>256 symbols).

Sample Code:

Function longString() As Variant
        Dim res(1 To 1, 1 To 2)
        res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
        res(1, 2) = "world"
        longString = res
End Function

When calling longString() as an array formula in a cell, the cell got #Value error, but through debugging, longString() returns without error.

how can i resolve this issue?

Your code doesn't throw any error on my machine (Excel 2010). What version are you on? — silentsurfer, Dec 30 '14 at 09:49
@silentsurfer Did you enter the UDF as a formula in a worksheet cell? It returns a `#VALUE!` error in that cell in Excel 2007. — Ron Rosenfeld, Dec 30 '14 at 11:23

score 2 · Accepted Answer · answered Dec 30 '14 at 11:21

I believe you have run into one of the obscure limitations in the interactions between VBA and Excel.

One workaround would be to change the formula to return only a single element, and have the particular element as an argument in the UDF.

For example:

Option Explicit
Function longString(Optional R As Long = 1, Optional C As Long = 1)
        Dim res(1 To 1, 1 To 2)
        res(1, 1) = "hellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhh\nhellohhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhhellohh\n"
        res(1, 2) = "world"
        longString = res(R, C)
End Function

You could then call the function in any of the following ways:

=longString()      <-- returns the first element
=longString(1,1)   <-- returns the first element
=longString(1,2)   <-- returns the second element
=longString(ROWS($1:1), COLUMNS($A:A))  <--could be dragged down and right to return an array of the elements

Thanks. The workaround works. Glad to know that it is one of the obscure limitations that only affect arrays. — Terence Hang, Dec 31 '14 at 03:37
Just got an answer from MSDN forum, String array needs to decleared explicitly. ie. `Dim res(1 To 1, 1 To 2) as String`. Then it'll accept long strings. — Terence Hang, Dec 31 '14 at 03:54
@TerenceHang That's true. For some reason, when I noticed you had explicitly declared the return type of the function to be Variant, and implicitly declared res as Variant, I gave you a workaround that would preserve those data types. One possibly undesirable side affect of declaring res as string would be that numeric data will be returned as a string; hence ignored by certain functions. — Ron Rosenfeld, Dec 31 '14 at 04:10

#VALUE error when dealing with long string in UDF in VBA(excel)

1 Answers1

Linked