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.

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

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

Herllo,

I have been struggling for a few hours to understand some of the answers here, in particular the ones from Cem Firat and Greg. I am thinking perhaps there may be some typos or procedures have got a bit mixed up.  Or I have simply missed the point that was trying to be made in those answers. That is always possible. I did not know what to do with the example procedures, or if I did, it was not clear to me what they were telling me. I apologise if I am wrong, but I suspect the people answering had a good answer, but writing it went astray a bit, maybe not enough for them themselves  to be thrown off, but enough to make it very hard or impossible to see what was trying to be explained.

In the Thread in general it is not always clear if we are talking about Functions and/ or User Defined Functions (UDFs) and so on

However, I have gleaned some info and  I think I can make a useful contribution by giving my take on both

_ what the question is

and

_  what a simply direct answer could be..

First the question:

I have a UDF(User-Defined Function) in VBA that needs to modify cell range on Excel. (Since a UDF cannot do this……) …. how do I update other cells by calling a UDF?

Before my take on an answer, there may have been a bit of confusion as to what the requirement was, that is to say what we a talking about in terms of functions.

I suggest we are talking about

_ a UDF(User-Defined Function) "making a call" from a worksheet

, and not

_a function in general, or a UDF, which is called from VBA. As TimWilliams said in a comment, …. As long as you're not calling it as a UDF then yes a function can modify the sheet in the ……

In other words a UDF called from VBA or any function called from VBA are in principal the same thing.

But when one refers to a UDF, I think, it generally implies that the user wants to call it from a worksheet. Perhaps this definition is not set in stone, hence the confusion and uncertainty in what we are talking about, in terms of the requirement. The original question did ask …. how do I update other cells by calling a UDF? … This is implying, I think, that we are calling the function from a cell.

Now my answer.

The first part of my answer is to say, JIMVHO, is that it's usually a bad idea to say that something can't be done. I am very open to discusion of why maybe it should not be done, but just saying it cannot be done, is, JIMVHO, incorrect here

My answer does something similar to the worded suggestion from Cem Firat  …..If call other function with ..........Evaluate method in your UDF function you can change everything on sheet (Values,Steel,Etc.) because VBA does not know which function is called….

I was not able to see an actual answer from his (Cem Firat's), examples.

That is what I am doing here in my answer: In my UDF I am calling another procedure  with the Evaluate method, and that procedure will  modify a cell range on Excel.

Let me make it clear again what I am doing, as it is easy to get things in a muddle: I am going to write a function which I will name UDFfunction( ). It will take two arguments, a range,  and a text. The range is where you want the text to go. The function is to be put in a normal code module. This means that if I write a  = in a cell, followed by the function name ( and arguments if it requires them, as it does in this case ), then the function will run. I might refer to this as calling the function from Excel, or calling the function from a cell, or calling a UDF(User-Defined Function) in VBA from an Excel cell range.

So…

All of this coding should be put into a normal module. (Change the worksheet name to suit the worksheet you want to use the function from, in other words, change the worksheet name to suit the worksheet in which you will write the formula/ funtion)

    ' This is the UDF.  A user would use it by typing in any cell in a spreadsheet, something like   = UDFfunction(A1:B2)
    Public Function UDFfunction(Rng As Range, Txt As String) As String
    Worksheets("CemFiratGreg").Evaluate "OtherProc(" & Rng.Address & ", """ & Txt & """)"                  ' Unconventional use of a UDF
     Let UDFfunction = "You did just put the text of """ & Txt & """ in range " & Rng.Address(RowAbsolute:=False, ColumnAbsolute:=False) ' A conventional use of a UDF
    End Function
    
    ' This can be a  Sub  or a  Function
    Sub OtherProc(ByVal Rng As Range, ByVal Txt As String)
     Let Rng = ""
     Let Rng = Txt
    End Sub

(For some versions of Excel it may be necessary at this point to save and close and reopen the file)

Now type something like this in any cell

=UDFfunction(A1:B2,"Texties")

As a result of this, two things should happen.

_(i)  In the cell you typed the UDF  into, you will get the text

You did just put the text of "Texties" in cell A1:B2.

That is what one might commonly expect a UDF to do, in other words give some text or numbers in the cell that it is written in. Commonly one hears that A UDF is a function that returns a value in the cell where it is called.

_(ii)  The word Texties will appear in the range A1:B2. That is doing something often regarded as impossible. One often hears it said that A UDF cannot change the contents of any cell, other than the one it is in.

I apologise if I have repeated something in the answers so far, but I could not get this far for love nor money from reading any of them. But it gave me some thoughts in the direction.

Alan

Share ‘StackOverflowUDFChangeOtherCells.xls’ https://app.box.com/s/knpm51iolgr1pu3ek2j96rju8aifu4ow