CODEWITHSUNDEEP
eventsvbaexcelhandlers
user1684104
user1684104

Reputation: 41

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?

Upvotes: 4

Views: 22329

Answers (3)

Greg
Greg

Reputation: 3326

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.

  1. Define the Private variables param_1, param_2, ..., param_n at a modular scope; and
  2. 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

  1. set the modular variables to the values of the intended arguments: param_1 = arg_1, param_2 = arg_2, ..., param_n = arg_n; then
  2. 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

Upvotes: 0

Cem Firat
Cem Firat

Reputation: 11

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

Upvotes: 0

Daniel
Daniel

Reputation: 13142

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.

Upvotes: 9

Related Questions