recalculate only active sheet

[Frank Halbach]

Hi,

I use CalculateFull to refresh my UDF's via the Ribbon. This updates all open workbooks and sheets (not ideal if you have many workbooks open).

Is it possible to limit the refresh to just the active worksheet and/or workbook.

Regards,

Frank

[Govert van Drimmelen]

Hi Frank,

While there's a Worksheet.Calculate() method, there's no Workbook.Calculate(), so you'd need to loop though all the Worksheets in the Workbook.Worksheets collection, and Calculate() each.

You don't have the control of Calculate vs. CalculateFull vs. CalculateFullRebuild on the worksheet level.

The best information I know of about the Excel calculation is on Charles Williams's site: http://www.decisionmodels.com/calcsecretsg.htm

-Govert

[Frank Halbach]

Govert,

Thanks for your reply.

My functions aren't volatile.

They only recalc when i do CalculatedFull or CalculatedFullRebuild which runs for all open workbooks and worksheets.

Background.

I used your batch example and run SQL queries in the background once a user hits the refresh button. 

However, it's inconvenient if the refresh runs for all open workbooks and worksheets.

[Govert van Drimmelen]

Hi Frank,

 

Maybe you can try a call to Range.Dirty() on range you’re interested in before calling Calculate.

 

-Govert

--
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 post to this group, send email to exce...@googlegroups.com.
Visit this group at http://groups.google.com/group/exceldna.
For more options, visit https://groups.google.com/d/optout.

[Frank Halbach]

Thanks Govert!,

I didn't think about this...!

I tried with "UsedRange", seems to be working very well. 

xlApp.activeSheet.UsedRange.Dirty();

- Frank

[Frank Halbach]

Hi,

Need some advise again.

I have a strange behaviour, not sure I'm doing something wrong.I try to loop thru all workbooks and set the UsedRange to dirty. 

dynamic xlApp = ExcelDnaUtil.Application;

dynamic workbook = xlApp.ActiveWorkbook;

foreach (var sheet in workbook.Worksheets)

{

sheet.UsedRange.Dirty();     

}

This will only calculate the activeSheet in my workbook.

Then I tried this to verfify.

xlApp.Worksheets[1].UsedRange.Dirty();

xlApp.Worksheets[2].UsedRange.Dirty();

If I selected Sheet3 e.g and run the this code it will refresh only sheet3 but not 1 and 2.

- Frank

[Govert van Drimmelen]

Hi Frank,

This is a long-standing Excel bug.

According to Charles Williams (http://www.decisionmodels.com/calcsecretsh.htm):

"There is a bug in Range.Dirty. It always acts on the currently active worksheet rather than the worksheet the Range object actually refers to."

So in your loop you should first activate the sheet before you call Dirty().

Also see this discussion: https://social.msdn.microsoft.com/Forums/office/en-US/9e8362f3-e9f4-4642-bda6-a8fa3d893b04/rangedirty-doesnt-work-on-an-inactive-worksheet-bug?forum=exceldev

-Govert

[Frank Halbach]

Thanks Govert

--
You received this message because you are subscribed to a topic in the Google Groups "Excel-DNA" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/exceldna/knhC7wUGohE/unsubscribe.
To unsubscribe from this group and all its topics, send an email to exceldna+u...@googlegroups.com.