Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Problems when multiple workbooks are open
I have written a number of custom functions for the manipulation and sorting
of a large amount of data. These work exactly as I intended and I can not see any problem with the way I have coded them, however I am not very experienced with VBA or any other programming language for that matter. These functions use a range of data from the sheet, and are used on 116 rows of the sheet. I am not sure if I have placed the code for them in the correct place (in Module1) them or defined them correctly (Function [function name](parameter)), as the cause some strange results when other workbooks are open at the same time. When certain other workbooks are open (and active) and a recalculation is performed, when I return to my original workbook/sheet the custom functions have thrown up a €śValue!€ť error (or it might be a REF! error I cant remember which). This is not so much of a problem as a simple recalc (F9) corrects this. The big problem occurs when I have two versions of my workbook open. (The workbook is used to hold manufacturing data and a new one is created every month from a mast file for that months production). When 2 copies of the workbook are open any change of data in a single cell results in a recalculation that can take up to 210 minutes to perform. It is as if the custom functions can be triggered by any recalculation in any sheet. So when another workbook is open the custom calculation is triggered and looks at the active sheet for data. It does not find it so hence the error. When tow of the same sheets are open the custom function is triggered in the non active sheet as well as the active one, updates for the active one causing the rest of the sheet to recalculate, and a loop of recalculation results until a point 10 minutes later or so when it completes. (this is only supposition on my part as I can not monitor or prove that this is what is happening) Needles to say a recalculation that takes less than a couple of seconds with only one copy of the workbook open takes minutes when two copies are open, which makes me think that somehow the two open workbooks are interacting somehow. The problem is that I need to create a similar workbook (Which will have to be open at the same time as the current one) to cover another product we manufacture, and will have to use both similar functions and additional slightly more complex ones to achieve the desired result, however if it takes as long to recalculate for every cell of data entered the new workbook will be unusable. Thanks in anticipation for any help or direction anyone can give on this. Regards Richard |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Custom Function Problems when multiple workbooks are open
PLEASE IGNORE THIS POST IT IS A DUPLICATE OF "Problems with Custom Functions
and Multiple open workbooks" It semems that it is not only custom functions that I am having problems with! Sorry "Richard Wood" wrote: I have written a number of custom functions for the manipulation and sorting of a large amount of data. These work exactly as I intended and I can not see any problem with the way I have coded them, however I am not very experienced with VBA or any other programming language for that matter. These functions use a range of data from the sheet, and are used on 116 rows of the sheet. I am not sure if I have placed the code for them in the correct place (in Module1) them or defined them correctly (Function [function name](parameter)), as the cause some strange results when other workbooks are open at the same time. When certain other workbooks are open (and active) and a recalculation is performed, when I return to my original workbook/sheet the custom functions have thrown up a €śValue!€ť error (or it might be a REF! error I cant remember which). This is not so much of a problem as a simple recalc (F9) corrects this. The big problem occurs when I have two versions of my workbook open. (The workbook is used to hold manufacturing data and a new one is created every month from a mast file for that months production). When 2 copies of the workbook are open any change of data in a single cell results in a recalculation that can take up to 210 minutes to perform. It is as if the custom functions can be triggered by any recalculation in any sheet. So when another workbook is open the custom calculation is triggered and looks at the active sheet for data. It does not find it so hence the error. When tow of the same sheets are open the custom function is triggered in the non active sheet as well as the active one, updates for the active one causing the rest of the sheet to recalculate, and a loop of recalculation results until a point 10 minutes later or so when it completes. (this is only supposition on my part as I can not monitor or prove that this is what is happening) Needles to say a recalculation that takes less than a couple of seconds with only one copy of the workbook open takes minutes when two copies are open, which makes me think that somehow the two open workbooks are interacting somehow. The problem is that I need to create a similar workbook (Which will have to be open at the same time as the current one) to cover another product we manufacture, and will have to use both similar functions and additional slightly more complex ones to achieve the desired result, however if it takes as long to recalculate for every cell of data entered the new workbook will be unusable. Thanks in anticipation for any help or direction anyone can give on this. Regards Richard |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Problems with Custom Functions and Muiltiple open workbooks | Excel Programming | |||
workbooks.open function fails to open an existing excel file when used in ASP, but works in VB. | Excel Programming | |||
custom menu cross contamination between open workbooks. | Excel Programming | |||
Sharing a custom function bewteen workbooks | Excel Programming | |||
Custom function problems | Excel Programming |