Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Issue
Firstly I am not even sure if this is a programming issue or not.
I have a spreadsheet that contains both standard Excel functions and functions in an external spreadsheet (which is ALWAYS referenced and thus open) Sometimes I can see in the worksheet that the results are incorrect and calculation the workbook does not make any difference. HOWEVER if I make a copy worksheet, delete that copy, and THEN calculate. All the results are correct. Now, I do not think that the answer is to copy/delete a worksheet but something must be happening during this process to force Excel to fully complete and calculations and what question is exactly what is happening. The problem for my users is that because of the nature of the data in the workbook, it is impossible for them to tell is it has fully calculated or not. I have considered making the function VOLATILE, however there are just too many functions and I am worried about slowing down the whole calculation process. Any ideas Sean *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Issue
You must be using some functions written in VB Script. And you are passing
some values to it and taking other values by reference into this function. Now any function or Macro will not Re-Run unless the values passed to it are changed. That is the reason why your function is not refreshing and the calculations are done partially with new values and partially with old. Functions / Macros need some kind of event to execute. Now opening an excel file is such event and it refreshes these functions / Macros. Frankly I too faced this problem sometime back and the remedy I used then was to hyperlink my calculation sheet which used Macros to the input sheet. Thus the calculation sheet was opened each time refreshing my macros. Don't worry there is an easier solution I learnt later. Here is what you can do. First find such functions/Macros. Then put them in an event like a button click or something. If you don't know programming at all get someone who knows it. If none of this is possible you can try my old method. Create an input sheet and pass the values to your calculation sheet and hyperlink calculation sheet from input sheet. This way clacultion sheet will open each time the input values change. Vikrant "Sean Howard" wrote: Firstly I am not even sure if this is a programming issue or not. I have a spreadsheet that contains both standard Excel functions and functions in an external spreadsheet (which is ALWAYS referenced and thus open) Sometimes I can see in the worksheet that the results are incorrect and calculation the workbook does not make any difference. HOWEVER if I make a copy worksheet, delete that copy, and THEN calculate. All the results are correct. Now, I do not think that the answer is to copy/delete a worksheet but something must be happening during this process to force Excel to fully complete and calculations and what question is exactly what is happening. The problem for my users is that because of the nature of the data in the workbook, it is impossible for them to tell is it has fully calculated or not. I have considered making the function VOLATILE, however there are just too many functions and I am worried about slowing down the whole calculation process. Any ideas Sean *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Issue
erm. thanks but I think you've jumped way of base here.
VBScript. No Hyperlinks. No I understand your point about using HyperLinks, but even though I can be sure my "macro/function" workbook will always be open (and thus can be referenced) I cannot be sure where it is actually stored. Also if the "macro/function" workbook has to be re-opened chances are the users macro security setting will kill it (presently the "macro/function" workbooks are loaded on Excel startup, that's how I know they are always there) Thanks Vikrant but that solution will not work for me Sean *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel Calculation Issue
Do any of your functions use information not passed via range
arguments? For example do they look at values in other sheets directly? That can lead to behaviour where recalculation becomes a problem: they will only be refreshed when one of the input arguments changes. Tim "Sean Howard" wrote in message ... erm. thanks but I think you've jumped way of base here. VBScript. No Hyperlinks. No I understand your point about using HyperLinks, but even though I can be sure my "macro/function" workbook will always be open (and thus can be referenced) I cannot be sure where it is actually stored. Also if the "macro/function" workbook has to be re-opened chances are the users macro security setting will kill it (presently the "macro/function" workbooks are loaded on Excel startup, that's how I know they are always there) Thanks Vikrant but that solution will not work for me Sean *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table % Calculation Issue | Excel Discussion (Misc queries) | |||
Strange Calculation issue | Excel Discussion (Misc queries) | |||
calculation issue? | Excel Discussion (Misc queries) | |||
Calculation speed issue | Excel Discussion (Misc queries) | |||
Vlookup Calculation Issue | Excel Worksheet Functions |