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

Excel VBA will not allow a user-defined function to alter the value of another cell. The only thing a UDF is allowed to do (with a few minor exceptions) is to return values to the cells it is called from.

Answer 2

Why not just typing you formula in E6 then ? That's the Excel logic: put your formula where you want the result to appear.

Answer 3

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