CODEWITHSUNDEEP
vbaexcel
Vantomex
Vantomex

Reputation: 2295

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

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.

Upvotes: 4

Views: 15379

Answers (4)

Przemyslaw Remin
Przemyslaw Remin

Reputation: 6960

YES, of course, it is possible.

enter image description here

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

Upvotes: 8

Charles Williams
Charles Williams

Reputation: 23550

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

Upvotes: 5

iDevlop
iDevlop

Reputation: 25272

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

Upvotes: 0

Charles Williams
Charles Williams

Reputation: 23550

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.

Upvotes: 3

Related Questions