Ошибка # ЗНАЧЕНИЕ при работе с длинной строкой в ​​UDF в VBA(Excel)

Question

Я столкнулся с ошибкой #VALUE при использовании UDF, возвращающей массив с длинными строками (>256 символов).

Образец кода:

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

При вызове longString() в качестве формулы массива в ячейке ячейка получает ошибку #Value, но при отладке longString() возвращается без ошибок.

как я могу решить эту проблему?

Answer 1

Я считаю, что вы столкнулись с одним из неясных ограничений во взаимодействиях между VBA и Excel.

Один из обходных путей - изменить формулу так, чтобы она возвращала только один элемент, и указывать конкретный элемент в качестве аргумента в UDF.

Например:

---

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

---

Затем вы можете вызвать функцию любым из следующих способов:

=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