Как вернуть array() в функцию vba, чтобы использовать его в формулах массива ячеек (matricial formula): для разделения текста в нескольких ячейках

Я пишу функцию в VBA для использования в Формуле excel, это нормально, если моя функция возвращает одно значение: 

=MYVALUE(A1)

Теперь я написал другую функцию, которая возвращает массив (1,2,3,4,...) и я заменяю свою формулу excel формулой массива: 

{=MYARRAY(A1)}
Но когда я растягиваю формулу, все ячейки отображаютпервое значение моего массива . почему?

Вот мой исходный код VBA (дополнение.xlam): 

Function MYVALUE(x as integer)
    MYVALUE = 123
End Eunction

Function MYARRAY(x as integer)
    MYARRAY = Array(10,20,30)
End Eunction
3 4
excel vba excel-vba arrays excel-formula

3 ответа:

Формулы массива нужно использовать так

Мой VBA для разбиения текста на несколько ячеек 

Function EXPLODE_V(texte As String, delimiter As String)
    EXPLODE_V = Application.WorksheetFunction.Transpose(Split(texte, delimiter))
End Function
Function EXPLODE_H(texte As String, delimiter As String)
    EXPLODE_H = Split(texte, delimiter)
End Function
  1. Выберите область C3:C7 это определяет направление вектора.
  2. нажмите F2 Для редактирования на месте и введите следующую формулу: =EXPLODE_V($B$3;" ")

  3. Нажмите CTRL+сдвиг+войдите ( вместо обычного введите ) - это определит формулу массива и приведет к {=EXPLODE_V($B$3;" ")} скобкам вокруг нее (но не вводите их вручную!).

    образец текста разделенных ячеек

6

Причина в том, что ваш вектор фактически отображается горизонтально, если вы создадите формулу массива горизонтально, вы получите свои числа. Для получения вертикального вектора используйте следующее: 

Function MYARRAY(x As Integer)

    MYARRAY = Application.WorksheetFunction.Transpose(Array(10, 20, 30))

End Function
4

Ячейка не будет отображать массив, вам придется перевести этот массив в диапазон / массив ячеек на листе. Кроме того, вы можете попробовать преобразовать массив в строку с разделителями, которая может работать до тех пор, пока вы не используете очень большие массивы.

Public Function MYARRAY(x As Integer)

Dim tmpArray() As Variant
Dim i As Long
Dim arrayString As String

tmpArray = Array(10, 20, 30)

For i = LBound(tmpArray) To UBound(tmpArray)
    If arrayString = vbNullString Then
        arrayString = tmpArray(i)
    Else:
        arrayString = arrayString & ", " & tmpArray(i)
    End If
Next

MYARRAY = "{" & arrayString & "}"

End Function
-2