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.

Leave a Comment

tech