VBA – Update Other Cells via User-Defined Function

by

Here is a way you can circumvent the restraint, you must do it indirectly. Method copied from Excel – How to fill cells from User Defined Function?:

In a standard module:

Public triggger As Boolean
Public carryover As Variant
Function reallysimple(r As Range) As Variant
    triggger = True
    reallysimple = r.Value
    carryover = r.Value / 99
End Function

In worksheet code:

Private Sub Worksheet_Calculate()
    If Not triggger Then Exit Sub
    triggger = False
    Range("C1").Value = carryover
End Sub

This could be expanded for your purposes. Essentially, the UDF updates public variables which are then read from the Worksheet_Calculate event to do… anything you like.

Another more complicated approach would be to write a vbscript file from your function that will attempt to automate Excel and run it via Shell. However, the method I listed above is much more reliable.

More Related Contents:

  1. Wait until Application.Calculate has finished
  2. How to add events to Controls created at runtime in Excel with VBA
  3. How can an Excel Add-In respond to events in any worksheet?
  4. Handling Events for OLEObject CommandButtons created at Runtime
  5. Excel Number Separation [closed]
  6. excel vba compare & copy worksheets from 2 excel files
  7. VBA Stuck after certain loop
  8. How to return a result from a VBA function
  9. Cycle through sub-folders and files in a user-specified root directory [duplicate]
  10. Scraping data from website using vba
  11. Convert Text to Date?
  12. Properly Handling Errors in VBA (Excel)
  13. Is there a way to slow down a Web Scraper so it will pick up the code?
  14. How to “flatten” or “collapse” a 2D Excel table into 1D?
  15. Renaming Files with Excel VBA
  16. Is there a way to import data from .csv to active excel sheet?
  17. VBA – How the colon `:` works in VBA code with condition
  18. Click button or execute JavaScript function with VBA
  19. Comparison of Dictionary, Collections and Arrays
  20. Break out of a While…Wend loop
  21. Turn Excel range into VBA string
  22. Handle “No cells were found” error when filtered range is empty
  23. Excel: the Incredible Shrinking and Expanding Controls [closed]
  24. Generate Word Documents (in Excel VBA) from a series of Document Templates
  25. Creating form programmatically in the module using vba
  26. Using Excel VBA Macro To Capture + Save Screenshot of Specific Area In Same File
  27. Find and FindNext for Excel VBA
  28. VBA:IE-How to assign pathname to file input tag without popup file upload form?
  29. VBA Excel large data manipulation taking forever
  30. Function Overloading and UDF in Excel VBA

Leave a Comment