VBA - Update Other Cells via User-Defined Function
I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel.
Since a UDF cannot do this, I tried using Event calls.
When I raise a Custom Event and try to write to cells, I get #Value error. On the other hand, Application events such as
Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range)
can write to cells.
My questions is how do I update other cells by calling a UDF?
Solution 1:
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.