VBA - Update Other Cells via User-Defined Function

4
votes

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?

2
eventsvbaexcelhandlers
What do you mean by modify cell range? What exactly are you trying to do? - Daniel
And what do you mean by functions cannot do this? - Olle Sjögren
Functions cannot modify cells, correct ? They can only return values but when we want function execution to actually modify cells, we play a trick by getting help from Application events such as App_SheetChange that can write to cells. Now instead using Application Events, I want to use my own Events( say PrintGrid) that I created using Event keyword but my testing shows that the custom event handler throws error when attempting to modify excel cells. SO my question - Is it possible to modify excel cells from custom event handlers that are triggered manually from functions using RaiseEvent - user1684104
I would say a VBA function can modify cells in any way you want. Again, what do you mean by modify cell range on Excel? What is it that you want to do with the cell range? Please describe what you want to do, and we'll get to the events if you really need them (it sounds overly complex). - Olle Sjögren
@DanielCook A UDF called from a sheet can modify the sheet - but it is complex. stackoverflow.com/a/8711582/641067 - brettdj

2 Answers

9
votes

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.

0
votes

If call other function with Application.Evaluate method in your UDF function you can change everything on sheet (Values,Steel,Etc.) because VBA does not know which function is called.

Example: 

Sub UDFfunction()
  Evaluate "otherfunc(""abc"")"
End Sub

Public Function otherfunc(ByVal str As String)
  ActiveSheet.Cells(1, 1).Value = str
End Function