Reports

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, Saying it cannot be done is, JIMVHO, incorrect

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.

79788078