VBA - Update Other Cells via User-Defined Function

Question

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?

What do you mean by modify cell range? What exactly are you trying to do? — Daniel, Sep 19 '12 at 19:47
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, Sep 19 '12 at 20:03
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, Sep 19 '12 at 20:55
The function should actually fill data in a range of cells. The formula is =TSS_GETEMPLOYEEDATA which may return data as big as 1.5G. If I try to return grid from the function, I am getting Out Of Memory error, so I am thinking to display the grid in batches using EventHandlers and when all the grid is populated, return True from function. — user1684104, Sep 19 '12 at 21:02
@Olle - a function *called from a worksheet* cannot modify the sheet, only return a value (or values if used as an array formula). As long as you're not calling it as a UDF then yes a function can modify the sheet in the same way as a Sub. — Tim Williams, Sep 19 '12 at 23:16
@TimWilliams Thanks for the explanation, I didn't know about that limitation which is part of why I didn't understand this question. The more you know... — Daniel, Sep 20 '12 at 00:01
@DanielCook A UDF called from a sheet can modify the sheet - but it is complex. http://stackoverflow.com/a/8711582/641067 — brettdj, Sep 20 '12 at 00:05

Daniel · Answer 1 · 2018-12-06T15:09:47.927

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.

Thanks Daniel and everyone. I used a similar approach but instead of using Workseet_calculate, I used App_SheetChange which will look for function completion event(cell A4 gets True) and then reads the global variable and displays on Excel grid. This works very nice but I would like to have my own event, say PrintGrid( by using Event keyword and raising by RaiseEvent) but the event handler gives error while writing to the range of cells..so my questin - SO my question - Is it possible to modify excel cells from custom event handlers that are triggered manually from UDF using RaiseEvent ? — user1684104, Sep 20 '12 at 15:23

score 0 · Answer 2 · edited Mar 16 '20 at 20:49

0

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

edited Mar 16 '20 at 20:49

stenci

8,290
14
64
104

answered May 20 '16 at 10:29

Cem Firat

11

Can you explain a bit more? – Dieter Meemken May 20 '16 at 10:54
Example: Sub UDFfunction() Dim s As string s="Text" Evaluate "otherfunc(""" & s & """)" End Sub Function c() End Function ---------------- Public Function otherfunc(ByVal str As String) ActiveSheet.Cells(1, 1).Value = str End Function --------------- active sheet Cell "A1" Value will be "Text" – Cem Firat May 20 '16 at 11:23
This is the best answer, because it does the job without relying on delayed actions triggered by other events. – stenci Mar 16 '20 at 20:50

Greg · Answer 3 · 2022-09-21T18:16:25.733

Background

I too have been seeking a way to modify the worksheet in ways typically forbidden to UDFs. I was intrigued by @Cem Firat's answer here, which used Application.Evaluate()

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

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

to evaluate forbidden code as a String. This successfully disassociates UDFfunction() from restrictions that prevent it from modifying other cells: Cells(1, 1).Value = ....

However, hacking strings is unstable for calls with complex arguments, and UDFfunction() should only use it ("otherfunc(""abc"")") as a last resort.

Theory

After some experimentation, I seem to have discovered an alternative, which I call "pseudoinvocation". It simulates a call Fun(arg_1, arg_2, ..., arg_n) from within a UDF, where Fun() performs actions that are typically forbidden to UDFs.

Define the Private variables param_1, param_2, ..., param_n at a modular scope; and
define the Private function Call_Fun() with no arguments, which in turn makes a call to Fun(param_1, param_2, ..., param_n).

When you want some UDF My_UDF() to call Fun(arg_1, arg_2, ..., arg_n), then simply write My_UDF() to

set the modular variables to the values of the intended arguments: param_1 = arg_1, param_2 = arg_2, ..., param_n = arg_n; then
perform pseudoinvocation via Application.Evaluate("Call_Fun()").

Application

As an improvement upon Cem's answer, pseudoinvocation could be applied in a standard module like so:

' The role of param_1 as a (pseudo)parameter to Fun().
Private pseudo_str As String

' The role of My_UDF().
Public Function UDFfunction()
    ' Pass the (pseudo)argument ("abc") in the role of arg_1, to the
    ' (pseudo)parameter (pseudo_str) in the role of param_1.
    pseudo_str = "abc"
    
    ' Perform pseudoinvocation of otherfunc() with that argument.
    UDFfunction = Application.Evaluate("Call_otherfunc()")
End Sub

' The role of Fun().
Public Function otherfunc(ByVal str As String)
    ActiveSheet.Cells(1, 1).Value = str
End Function

' The role of Call_Fun().
Private Function Call_otherfunc()
    otherfunc(psuedo_str)
End Function

This could be further simplified to:

' A (psuedo)parameter to otherfunc().
Private pseudo_str As String

' Only this UDFfunction() is exposed for public use.
Public Function UDFfunction()
    pseudo_str = "abc"
    UDFfunction = Application.Evaluate("otherfunc()")
End Sub

' The helper function takes the (psuedo)parameter.
Private Function otherfunc()
    ActiveSheet.Cells(1, 1).Value = psuedo_str
End Function

VBA - Update Other Cells via User-Defined Function

3 Answers3

Background

Theory

Application

Linked