Hi,

I have a couple of workbooks ( Excel ) that i need to run on the same CPU. Each of these workbooks have events ( example : workbook_calculate ). Each of the workbook_calculate when called will trigger calculation into manual mode. Of course turn it back to automatic when it is exiting the workbook_calculate.

It seems like one workbook_calculate triggered on workbook A will affect workbook B . Are there ways to get around this ? Meaning that each workbook runs their events independently from other workbooks.

Of course i would not want to run 10 different workbooks using ten different CPUs.

Thanks for the help !

Regards,
Yen

Yen,

As far as I'm aware events are specific to workbooks. The problem you seem to be suffering are that the Calculate event is percolating across all open workbooks when you're only interested in containing it to one workbook. The reason for this is potentially one of two causes that I can think of at the moment:

1) Excel the application has only one calculation state. Therefore when you turn calculation to Automatic you do it for all open workbooks and likewise when you switch calculation to Manual you do so to all open workbooks. Therefore switching the calculation to automatic will trigger lots of Calculate events in all open workbooks. The only way round this problem is to make sure you only have workbooks open that you want to work with at any one time.

2) When in the manual calculation state the most common way to trigger a recalculation is to use Application.Calculate however this affects the whole application and will therefore trigger a Calculation event in every open workbook. You can get round this problem by tailoring the calculation command to the desired worksheet or range of cells. (i.e. ThisWorkbook.Sheets("Sheet1").Calculate). You can't tailor the Calculate command to a Workbook, to my knowledge, but you can effectively do so by looping through every sheet. Note that in large workbooks with macros it often very desirable to tailor the Calculation on the smallest required range as it can dramatically improve macro running times.

Maccas

Hi Maccas,

Thank you so much for your reply. It has been insightful and yet worrying.

A little more info on what i am working on. Basically I am building some spreadsheets that need to snap live data feeds. This means that live data feeds (e.g live price of a stock that feeds into excel sheet) will be updating as and when they have changed. So whenever there is an update on the prices for example it will trigger a ' check ' procedure to check if the condition i want has been met. If met then i will go on to snap the string of data at that point in time.

So in such a process i would have done two things

1) turn the calculation to 'manual' --> to prevent any update when snapping data , and then switch it back to 'automatic' when the snapping has been done.

2) trigger a recalculate when i switch the calculation mode back to 'automatic"

This works fine when i am running only one workbook. But imagine i am setting this up for various workbook doing the same gist of thing. When one sheet triggers off it will affect another workbook.

I have tried it and it really did not run smoothly at all. The recalculate event on one workbook affected the other. Let alone having more workbooks running similiar processes.

I guess the complexity here is due to me dealing with real time snapping of data. Do you have any idea how i can get around with this as I cannot only open one workbook at any one time.

Thank you for helping. :)

Regards,
Yen

Yen,

From the sounds of things Excel, used in this way, is not your ideal development environment. Work-arounds which spring to mind and may or may not be feasible are as follows:

1) Set up Excel worksheet formulae to respond to and on-calculate your workings with live data feed formulae thereby never needing to switch calculation on and off (i.e. cut out VBA!). I guess this is unlikely to work since you want to know what's changed and if its a particular change type then do something, which is not readily done with worksheet formulae.

2) Have a workbook of live data feed formulae with calculation always on. Periodically, lets say every minute, the workbook could record a snapshot of its data state and by comparison with the previous snapshot could produce a change summary report. You could then work with this change summary to do your stuff.

3) Use a different type of data feed with less information and so less required calculation overhead.

4) Learn C++, VB.NET or other compiled language and write a more optimised program to deal with this.

Maccas

Hi Macca,

So sorry to have taken so long to reply. I think you are absolutely right with your suggestions and views on my issue. I will have to explore more into C++ and .Net to see how it can help.

Thank you for having look into this. Highly appreciated.

Regards,
Yen