VBA: How to change the value of another cell via a function?

Question

I'm an Excel VBA newbie.

How to change the value of the specified cell via a user-defined function? What's wrong with this code:

Function Test(ByVal ACell As Range) As String
  ACell.Value = "This text is set by a function"
  Test := "Result"
End Function

My wish is ... when I type =Test(E6) in cell E1, Excel will display the specified text in E6.

Answer 1

YES, of course, it is possible.

Put this code in Module1 of VBA editor:

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

Private Sub FireYourMacro(ResultCell As Range, A As Integer, B As Integer)
    ResultCell = A * B
End Sub

The result of this example UDF is returned in another, adjacent cell. The user defined function UDF_RectangleArea calculates the rectangle area based on its two parameters A and B and returns result in a cell to the right. You can easily modify this example function.

The limitation Microsoft imposed on function is bypassed by the use of VBA Evaluate function. Evaluate simply fires VBA macro from within UDF. The reference to the cell is passed by Application.Caller. Have fun!

UDF limitation documentation: https://support.microsoft.com/en-us/help/170787/description-of-limitations-of-custom-functions-in-excel

Answer 2

A VBA UDF can be used as an array function to return results to multiple adjacent cells. Enter the formula into E1 and E2 and press Ctrl-Shift-Enter to create a multi-cell array formula. Your UDF would look something like this:

Public Function TestArray(rng As Range)
    Dim Ansa(1 To 2, 1 To 1) As Variant
    Ansa(1, 1) = "First answer"
    Ansa(2, 1) = "Second answer"
    TestArray = Ansa
End Function