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?
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:
You could then call the function in any of the following ways: