RE: [ExcelDna] UDF function - Turn off default trigger by VBA [Application/Worksheet/Range].Calculate

[Govert van Drimmelen]

A non-volatile UDF like this would normally not be calculated when calling .Calculate.

If you (the code running the explicit .Calculate) know where the formula is used, I think you can set Range.Dirty for those cells, and the next .Calculate should calculate it.

I’s suggest you start with that approach and see if it gives you the control you need.

 

Then you might want to add some bookkeeping in the function itself, that registers (in some global object) the caller information for the function.

And then add some macro that you can call to make the callers Dirty before the explicit .Calculate.

One issue here will be tracking which cells have your formula in.

That’s not so easy if the sheet is edited, but might not be a problem for a sheet that is static and just used for calculation.

You might add another method that clears the registered areas, or something like that.

 

That’s about the best plan I can think of, but not something I’ve tried and there will be some details to work out along the way.

 

-Govert

 

 

From: exce...@googlegroups.com <exce...@googlegroups.com> On Behalf Of TokyoDerm
Sent: Wednesday, April 3, 2024 1:15 AM
To: Excel-DNA <exce...@googlegroups.com>
Subject: [ExcelDna] UDF function - Turn off default trigger by VBA [Application/Worksheet/Range].Calculate

 

Hi All, I have a non-volatile UDF function which is called from the Excel sheet as an array formula and returns a large block of data (5000 rows x 100 cols). For performance reasons I want to control when the function is triggered by .Calculate. I am wondering if this is possible using a flag set on the C# side.

For context, it's a large workbook with calculation set to manual. Excel takes about 100ms to calculate the range in question, even if the underlying data has not changed and I only want it to caclulate it when I explicitly call <range>.Calculate. However, the contains intentional circular references so I need to use Application.Caclulate to calculate the rest of it. If possible I want to exclude my UDF range from the Application.Caclulate.

Thanks!

 

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/3fa2b2cb-615e-4ca9-8806-36a818f0b20cn%40googlegroups.com.

[TokyoDerm]

Hi Govert, Thanks for getting back. I will look at Range.Dirty. Actually, since I posted, I discovered some more interesting details on the behavior. Using the simple function below (I create it as an array formula on the Excel sheet) :-

[ExcelFunction(Description = "Test calculate behavior from Excel")]
public static object[,] GetDataTest()
{
Debug.WriteLine("GetDataTest() has been called...");
object[,] sheetData = { {1,2,3}, {4,5,6} };
return sheetData;
}

Then, in VBA...

1. If I execute Range("$A$1:$C$2").Calculate, then it calls the function every time.

2. If I execute Application.Calculate AFTER calling Range("$A$1:$C$2").Calculate then it will also call the function BUT subsequent calls to Application.Calculate will not call the function.

So basically Application.Calculate will work once but only after Range("$A$1:$C$2").Calculate has been called. Isn't this strange? Is there something peculiar with my setup? My ideal scenario is that Range("$A$1:$C$2").Calculate always works but Application.Calculate never works.

Thanks again!

Dermot

[Govert van Drimmelen]

Hi Dermot,

 

This page and the related ones by Charles Williams of FastExcel might be instructive

How the Excel Smart Recalculation Engine works - Decision Models

 

It’s the best information about this topic that I know of.

 

You should expect some quirks with the Excel calculation too. For example I think Range.Calculate might work or fail, or behave differently, depending on whether it is on the active sheet.

To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/d50bddb2-b006-4d69-9b89-377a1b58e328n%40googlegroups.com.

[TokyoDerm]

Hi Govert, Great info on that page - thanks a lot! In the end I used Worksheet.EnableCalculation=False (so Application.Calculate has no effect on the worksheet) and Range.Calculate to keep full control of the calculations. Also, in my experience Range.Calculate works reliably regardless of the sheet being active or not, so that part is all good.

Dermot