Задайте значение ячейки из функции

Содержимое ячейки A1 =test(2) где test - это функция:

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

Можете ли вы объяснить, почему это дает # #VALUE! в ячейке A1 и ничего в ячейке A2? Я ожидал, что A2 будет содержать 2 а A1 будет содержать 4. Без строки Range("A2") = x функция работает как ожидалось (возводя в квадрат значение ячейки).

Что действительно сбивает с толку, так это если вы calltest test с подпрограммой calltest тогда это calltest

Sub calltest()
  t = test(2)
  Range("A1") = t
End Sub

Function test(ByRef x As Double) As Double
  Range("A2") = x
  test = x * x
End Function

Но это не

Function test(ByRef x As Double) As Double
  Range("A2") = x
End Function

Ответы

Ответ 1

Когда вы вызываете функцию из ячейки рабочего листа, вы эффективно используете функцию как пользовательскую функцию, которая имеет ограничения, как описано здесь:

http://support.microsoft.com/kb/170787

В тексте есть строка:

Любые изменения среды должны быть сделаны с помощью подпрограммы Visual Basic.

Интересно, как они используют это слово, а не должны. Интересно, знал ли автор КБ, что изменения в среде могут произойти из-за функции VBA.

Теперь, когда вы вызываете функцию из другой Sub/Function VBA, она обрабатывается по-другому. Из справочной документации (извините, я не смог найти ссылку на веб-страницу - в основном в VBE выделите слово Function и нажмите F1):

Как и процедура Sub, процедура Function - это отдельная процедура, которая может принимать аргументы, выполнять серию операторов и изменять значения своих аргументов. Однако, в отличие от процедуры Sub, вы можете использовать процедуру Function в правой части выражения так же, как и любую встроенную функцию, такую как Sqr, Cos или Chr, когда вы хотите использовать значение, возвращаемое функцией,

Похоже, что Subs и функции могут делать то же самое, когда используются только в VBA, за исключением того, что функции могут возвращать значения обратно вызывающей функции/подпрограмме.

На самом деле это довольно интересно, поскольку Excel может вызывать функцию с ограничением "только для чтения" для среды Excel.

Я думаю, что, в конце концов, Excel может вызывать функцию из ячейки рабочего листа не так, как VBA. Когда вы вызываете его из ячейки, он считается определяемой пользователем функцией, которая включает ограничения на изменение среды Excel. Если при вызове из VBA (где первоначально вызывающие из цепочки вызовов из VBA), он имеет всю власть Суб делает, плюс он может возвращать значение.

Ответ 2

Из-за Function оснований, которые утверждают, что вы не можете изменить или установить ячейки листа. Вам нужно удалить строку с помощью Range("A2") = x

EDIT Некоторая дополнительная ссылка (которая, я считаю, всегда полезна для тех, кто хочет анализировать тему UDF): Создание пользовательских функций Microsoft

Ответ 3

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

Вот простой пример. Функция UDF принимает два аргумента A и B и возвращает их произведение A * B. Но что интересно, он возвращает результат в соседней ячейке справа от ячейки, в которую мы ввели формулу.

Поместите этот код в стандартный модуль VBA:

Function UDF_RectangleArea(A As Integer, B As Integer)
    Dim MagicSpell As String
    MagicSpell = "Adjacent(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & A & "," & B & ")"
    Evaluate MagicSpell
    UDF_RectangleArea = "Hello world"
End Function

Private Sub Adjacent(CellToChange As Range, A As Integer, B As Integer)
    CellToChange = A * B
End Sub

Теперь введите B2 формулу: =UDF_RectangleArea(3,4)

enter image description here

Функция возвращает результаты в двух ячейках: "Hello world" в B2 (что неудивительно) и область прямоangularьника в C2 (это кролик из шляпы). Оба результата, а также место появления "кролика" могут быть легко настроены. Работа выполняется командой VBA EVALUALTE. В этом случае значение переменной MagicSpell становится равным Adjacent(C2,3,4), которое запускается из UDF, прежде чем возвращается результат UDF. Веселиться!

Ответ 4

Я адаптировал ваш код для работы со строками, функциями и всем остальным промежуточным, я надеюсь (все еще тестирую).

В результате тестирования возникает множество вопросов, но в первую очередь приходят 2:

Какие еще способы вы могли бы использовать для выполнения/обработки параметров и возврата желаемых результатов;

а. Как я сделал б. Не так, как я сделал (то есть по-другому).

Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants
 Dim WTVR1 As Variant '' ''20/05/2019'' '09/06/2019 Code by S Tzortzis/David Wooley
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 Dim A1 As Long
 Dim A2 As Long

 Dim c As String

 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)

bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)

c = Chr(34) & CELLR & Chr(34)
f = Chr(34) & callq & Chr(34)


'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)

''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS11h(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & [bb1] & ")"
Evaluate WTVR1


WTVR2 = "MOVEUS22h(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''

Evaluate WTVR2

A1 = cellq.Row
A2 = cellq.Column

CELLRR = Chr(34) & CELLR & Chr(34)
CELLRR1 = Chr(34) & [CELLRR] & Chr(34)
cellqq = Chr(34) & cellq & Chr(34)
cellqq1 = Chr(34) & [cellqq] & Chr(34)


''wtvr3 = "CopyFrom.Parent.Evaluate CopyOver234h(" & c & "," & f & ")" ''''20190531 1929
wtvr31 = "MOVEUS33h(" & Application.Caller.Offset(A1 - ActiveCell.Row + 1, A2 - ActiveCell.Column).Address(False, False) & "," & [CELLRR] & "," & [cellqq] & ")"

    Evaluate wtvr31

MOVEME27 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2 & "\\\\\/////" & wtvr31 & "\\\\\/////---" & ActiveCell.Row - A1 & "//////---" & ActiveCell.Column - A2

' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL         YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28

    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," &              CELLR.Address(False, False) & ")"  ''''2019050 1929



    End Function

    Private Sub MOVEUS11h(CELL1 As Range, G1 As Variant, G2 As Variant)

        '[ak333] = a


        CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2


    End Sub


    Private Sub MOVEUS22h(CELL2 As Range, G3 As Variant, G4 As Variant)

        CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4

    End Sub

    '' with chr(34) arond the p and a in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
    ''27/05/2019 :(

    '''''30/05/2019 .....'''''''


    '------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
    'private sub Movus3(cellfrom as range, cellto as range)
    'End Sub

    Private Sub moveus33h(cell3 As Range, CopyFrom As Variant, copyTo As Variant) ''''2019050 1929 ''' 03062019 change ema back to as Range here. :)
       '' copyTo.Value = CopyFrom.Value ''''2019050 1929

       ''CopyFrom.Value = ""

       cell3 =  CopyFrom  'Chr(34) & CopyFrom & Chr(34)

    End Sub ''''2019050 1929