Link to home
Start Free TrialLog in
Avatar of Robert Berke
Robert BerkeFlag for United States of America

asked on

How to modify cells from inside a UDF?

I very smart expert said there was a way involving Ontime.

I wonder what it might be
SOLUTION
Avatar of byundt
byundt
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Robert Berke

ASKER

Wow amazing stuff.

I'll dig in to it tomorrow.
I am suddenly getting pressure from my boss to get a project done.  

So, I think I will close this problem and add it to my personal todo list.

But, I do have one observation.

I'm probably missing something, but I wonder if an event handler might do the same thing in a more straightforward way?


sub sheetCalculate
   call AfterUDFRoutine2
end sub

Public Function AddTwoNumbers( _
      ByVal Value1 As Double, _
      ByVal Value2 As Double _
   ) As Double

' This is a UDF that returns the sum of two numbers and starts a windows timer
' It relies on SheetCalculate to perform activities not
' allowed in a UDF. Do not make this UDF volatile, pass any volatile functions
' to it, or pass any cells containing volatile formulas/functions or
' uncontrolled looping will start.

   AddTwoNumbers = Value1 + Value2
   
   ' Cache the caller's reference so it can be dealt with in a non-UDF routine
   If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection
   On Error Resume Next
   mCalculatedCells.Add Application.Caller, Application.Caller.Address
   On Error GoTo 0


End Function






Bob,

You are correct but there are a couple of cases where the Calculate even will not be invoked. If the calculation mode is manual and the cell is entered, or if the cell or any range containing that cell is calculated using the Calculate method then the event will not fire.

I tend to avoid UDFs anyway for other reasons having to do with stability and predictability so the above was really more of an excercise to see if it could even be done. I have not actually used the technique in any production tool. I do build many tools with calculation mode set to manual and I do a lot of controlled calculations so the restriction might apply to me in the future.

Kevin
Bob,
What was the original objective for your UDF? Why did you want to be able to modify the user interface?

I ask because I have probably written ten times more functions in my day job than subs. In fact, the ratio is as low as 10:1 only because I started participating in EE--I had very little use for subs before then.

Writing all those functions showed me that it is very important to pass all the inputs as parameters to the function. Any time I deviated from that practice, it caused problems for debugging and code maintenance.

I also learned that it was desirable to return more than one output if the UDF was calculation intensive. The need for speed introduced me to the world of array formulas back in 1995--turning a five minute recalc into a few seconds. And if you think about it, an array formula gives the UDF a chance to change a cell other than the one in the top left corner of the range.
Brad

Zorvek:  Aha!  I had not thought of cases with xlCalculationManual.  That explains the need for the OnTime trick.  And it is also a surprise to learn that range.calculate does not trigger the event.  I suppose that is why they call it SHEETcalculate (as opposed to SheetOrCellCalculate).

Brad:

Your thoroughness is always appreciated, but in this case I wish I had warned that I just suffer from idle curiousity.  You might have been able to give me a quicker answer and saved yourself some time.

It is only about once a year that I try to modify cells inside a UDF. Maybe only 4 times in my whole life.   2 of those 4 were accidental, and the other 2 had such an easy workaround, that I don't remember any details.

But, I always like having tricks in my tool kit just in case I might someday need it.  So, when I zorvek mentioned (in an unrelated thread) that he knew how to do this "impossible" task, I just felt a desire to know how it was done.

By the way: you taught me the array formula function trick about a year ago and I have used it occasionaly since then. It is very helpful on those rare occasions when it is needed.

And, by the way #2: I usually agree that one should "pass all the inputs as parameters to the function".  One "exception" is application.caller which is invaluable when resolving named ranges.

For instance =myfunc(Credits,Debits) passes the entire range. Implicit intersection is skipped unless you remember to say myfunc(+credits,+debits)

I have a few functions that "fix" this problem by  saying
    if param.areas.count + param.cells.count = 2 then
        set realparam= param
   else
        set realParam = intersect(Param,union(application.caller.entirerow, application.caller.entirecolumn))
        if realParm is nothing then set realParam = Param
   end if
It is a new technique and still experimental for me, but it seems to work very well.



Thank you both for the interesting discussion.