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

by

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

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Excel VBA, code to count the # of filled cells, then find highest and 2nd highest and subtract the corresponding column #’s [closed]
  5. Levenshtein Distance in VBA [closed]
  6. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  7. How do I base64 encode a string efficiently using Excel VBA?
  8. Assign event handlers to controls on user form created dynamically in VBA
  9. Programmatic Access To Visual Basic Project Is Not Trusted
  10. Can I make this macro more efficient or faster?
  11. Conditional Formatting using Excel VBA code
  12. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  13. How to get the background color from a Conditional Formatting in Excel using VBA
  14. VBA Excel Populate ListBox with multiple columns
  15. VBA Integer Overflow at 70,000
  16. How to count up text of a different font colour in excel
  17. How to “flatten” or “collapse” a 2D Excel table into 1D?
  18. VBA Macro to compare all cells of two Excel files
  19. What does ReDim Preserve do?
  20. VBA – How the colon `:` works in VBA code with condition
  21. How to create a separate CSV file from VBA?
  22. Convert RTF (Rich Text Format) code into plain text in Excel
  23. lastrow and excel table.
  24. Open a workbook from VBA and disable Workbook_Open() code?
  25. Creating form programmatically in the module using vba
  26. How to filter listbox values based on a Textbox value
  27. Find and FindNext for Excel VBA
  28. How do I get a list of unique values from a range in Excel VBA?
  29. How to get selected value in multicolumn listbox
  30. Query my excel worksheet with VBA

Leave a Comment