Calculate Event and Changes in Range

Calculate Event and Changes in Range

• Alsajoso

  Hi,
  
  I'm still struggling with this problem for nearly 1 week ... I searched one and only one related thread from google in this topic, and I'd like to know does VSTO and the latest event modelling fix this bug of Excel That is, we're not able to change the Application.Calculation when the VSTO program is executing, so as to stop the user defined function to be triggered.
  
  Thanks,
  
  

• Babylon

  Hi Peter,

  A couple of things. First, even if calculation is set to manual, if you change a formula, you are going to get a recalc of that formula (though nothing else will recalc) so the calculation event is going to fire.

  In general, setting Application.EnableEvents to false should keep the calculation and the sheet change events from firing under any circumstances. The only time I can think of when this doesn't work is if EnableEvents are being called from an automation client--in which case Excel treats each call as a single macro in isolation (see http://support.microsoft.com/default.aspx scid=kb;en-us;211626). The workaround in that case is to use Application.Run to run a macro that sets the property there. It might be worth trying that workaround in this case. I'm wondering if perhaps the fact that you are using the UDF callback mechanism is somehow triggering this behavior.

  Beyond that, I'm not sure what to suggest. The fact that your code is running in the VSTO runtime shouldn't make a difference. If the workaround suggested above doesn't solve the problem, you may want to post this in the Excel forum where the (depth of Excel knowledge should be a bit deeper) http://msdn.microsoft.com/newsgroups/default.aspx dg=microsoft.public.excel.programming&lang=en&cr=US. Alternatively, this might be a good candidate for opening a support incident with Product Support.

  Sincerely,

  Geoff Darst

  Microsoft VSTO Team

  
  

• volaru

  Hi,

  Setting Application.EnableEvents to false prior to changing the formula in the SheetChange handler should be sufficient to avoid the stack overflow. Recalculation shouldn't cause the SheetChange event to fire.

  This code works fine for me:

  private void Sheet1_Change(Microsoft.Office.Interop.Excel.Range Target)

  {

  Application.EnableEvents = false;

  Range["a2","a2"].Formula = "=a1";

  Application.EnableEvents = true;

  }

  Is there something about your scenario I'm missing

  Sincerely,

  Geoff Darst

  Microsoft VSTO Team

  
  

• Sandrina

  Hi Geoff,
  
  Thanks for your information. I'm not so understand your message in fact, although I read the KB throughfully. Maybe I'm not using the Office Automation approach so this workaround may not suitable for me.
  
  I'm still not so sure why Application.EnableEvents doesn't work, but I'll try to contact product support if my boss have this budget.
  
  Thanks,
  
  

• mcslemon

  Hi Geoff,
  
  Thanks for your answer, and now, I tried to change the formula of a Cell in the OnSheetChange event handler, but this would trigger the VBA user define function to fire automatically, even I set the Application.EnableEvents to false AND change Application.Calculation to Manual. This would become an endless loop if I can't freeze this happen.
  
  Do you have any idea
  
  Thanks,
  
  

• Sanko

  What's the method to freeze any events or calculation to happen in VSTO runtime in the SheetChange or SheetCalculate event handler Application.Calculation and Application.EnableEvents are both failed!
  
  

• KillerPR

  Hi Geoff,
  
  My situation maybe a bit more complicate (well, that's the reality)
  
  I have a UDF written by my VSTO project, and I perform some data retrieve in it, and then I also capture the SheetChange event, so that I can perform other tasks 'afterward'. These 2 events execute in order properly (up to this moment at least). However, I change the UDF "cell" formula in the SheetChange event, which trigger the UDF to fire again, and then perform another round-trip to database, modify the caller and fire the SheetChange event again - *LOOPING*.
  The loop will be stopped by itself after looping 4-5 times, but I have no clue why it know stopping itself and break from the loop. But anyway, my dev concern at this moment is not the internal looping/rescue mechanism, but why the Application.EnableEvent=false won't freeze other function/rountine being executed.
  
  Thanks for your attention.
  
  Peter

  
  

• mdulsy

  Hi,

  If calculation is set to automatic, this will occur if your insert or delete affects formulas that already exists on the worksheet (i.e. =SUM(A1:A10) and you delete row 3). It will also occur if you have a volatile function anywhere on the sheet. Volatile functions include CELL, OFFSET, TODAY, INDIRECT, NOW, INFO, and RAND. These functions are always recalculated whenever calculation occurs, so their presence would always cause the event to fire in the insert/delete case.

  If this is a problem, you can work around it by setting the Calculation property of the Application object to xlCalculationManual while you are doing your insert/deletes.

  Sincerely,

  Geoff Darst

  Microsoft VSTO Team

  
  
• Calculate Event and Changes in Range