VBA - Updating Other Cells Using a Custom Function

Question

I have a UDF (custom function) in VBA that needs to change the range of cells in Excel.

Since UDF cannot do this, I tried using event calls.

When I raise a custom event and try to write to cells, I get a #Value error. On the other hand, application events such as Private Sub App_SheetChange(ByVal Sh As Object, ByVal Target As Range) can be recorded in cells.

My questions are: how to update other cells by calling UDF?

Answer 1

Here you can get around the limitation; you must do it indirectly. Method copied from Excel - How to populate cells from a custom function? :

In the 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 the text of the worksheet:

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

It can be expanded for your purposes. Essentially, UDF updates public variables, which are then read from the Worksheet_Calculate event to do ... whatever you like.

Another tricky approach would be to write a vbscript file from your function, which will try to automate Excel and run it through Shell. However, the method described above is much more reliable.

Answer 2

if you call another function using the Application.Evaluate method in your UDF function. You can change everything on the sheet (values, steel, etc.) since VBA does not know which function is being called.