| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Defined Function - actioned in unrelated WB
I have a user defined function contained within a Module Function
procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User Defined Function - actioned in unrelated WB
Richard,
Are you sure it not just a case of the UDF code being fired during the normal course of the WS/WB re-calculation ? NickHK "Richard" egroups.com... I have a user defined function contained within a Module Function procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User Defined Function - actioned in unrelated WB
Hi Nick,
Must admit I hadn't thought of that. I had naively assumed that if values in a WS/WB were not changed, then the UDF would not recalculate. If this is indeed the case, is there any way of stopping the recalc in a non active WB/WS? Many thanks, On Wed, 4 Oct 2006 20:15:26 +0800, "NickHK" wrote: Richard, Are you sure it not just a case of the UDF code being fired during the normal course of the WS/WB re-calculation ? NickHK "Richard" legroups.com... I have a user defined function contained within a Module Function procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User Defined Function - actioned in unrelated WB
Richard,
If this is a calculation issue, you can control when/how Excel calculates. You can set Application.Calculation = xlCalculationManual (back to xlCalculationAutomatic when ready). Then you call .Calculate on the appropriate object ; Application, WorkSheet, Range. But why is a problem if the UDF is called anyway ? NickHK "Richard Buttrey" ... Hi Nick, Must admit I hadn't thought of that. I had naively assumed that if values in a WS/WB were not changed, then the UDF would not recalculate. If this is indeed the case, is there any way of stopping the recalc in a non active WB/WS? Many thanks, On Wed, 4 Oct 2006 20:15:26 +0800, "NickHK" wrote: Richard, Are you sure it not just a case of the UDF code being fired during the normal course of the WS/WB re-calculation ? NickHK "Richard" glegroups.com... I have a user defined function contained within a Module Function procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User Defined Function - actioned in unrelated WB
Thanks Nick
When I noticed this I had the VBE open. This apparently slows UDF calls down quite considerably. WIth the VBE closed it's much less of a problem, but it still seemed to be taking about 20 seconds or so. I suppose my question is really why does it have to call the UDF anyway if the UDF is not related to the second WB? I'll take up your suggestion of switching to xlCalculationManual and experiment with calculating just the relevant range. Kind regards, On Thu, 5 Oct 2006 20:28:01 +0800, "NickHK" wrote: Richard, If this is a calculation issue, you can control when/how Excel calculates. You can set Application.Calculation = xlCalculationManual (back to xlCalculationAutomatic when ready). Then you call .Calculate on the appropriate object ; Application, WorkSheet, Range. But why is a problem if the UDF is called anyway ? NickHK "Richard Buttrey" m... Hi Nick, Must admit I hadn't thought of that. I had naively assumed that if values in a WS/WB were not changed, then the UDF would not recalculate. If this is indeed the case, is there any way of stopping the recalc in a non active WB/WS? Many thanks, On Wed, 4 Oct 2006 20:15:26 +0800, "NickHK" wrote: Richard, Are you sure it not just a case of the UDF code being fired during the normal course of the WS/WB re-calculation ? NickHK "Richard" oglegroups.com... I have a user defined function contained within a Module Function procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
User Defined Function - actioned in unrelated WB
Richard,
This page explain Excel's calculation engine: http://decisionmodels.com/calcsecrets.htm As to the why, it would depend what you UDF is/does ? NickHK "Richard Buttrey" ... Thanks Nick When I noticed this I had the VBE open. This apparently slows UDF calls down quite considerably. WIth the VBE closed it's much less of a problem, but it still seemed to be taking about 20 seconds or so. I suppose my question is really why does it have to call the UDF anyway if the UDF is not related to the second WB? I'll take up your suggestion of switching to xlCalculationManual and experiment with calculating just the relevant range. Kind regards, On Thu, 5 Oct 2006 20:28:01 +0800, "NickHK" wrote: Richard, If this is a calculation issue, you can control when/how Excel calculates. You can set Application.Calculation = xlCalculationManual (back to xlCalculationAutomatic when ready). Then you call .Calculate on the appropriate object ; Application, WorkSheet, Range. But why is a problem if the UDF is called anyway ? NickHK "Richard Buttrey" om... Hi Nick, Must admit I hadn't thought of that. I had naively assumed that if values in a WS/WB were not changed, then the UDF would not recalculate. If this is indeed the case, is there any way of stopping the recalc in a non active WB/WS? Many thanks, On Wed, 4 Oct 2006 20:15:26 +0800, "NickHK" wrote: Richard, Are you sure it not just a case of the UDF code being fired during the normal course of the WS/WB re-calculation ? NickHK "Richard" ooglegroups.com... I have a user defined function contained within a Module Function procedure in Workbook 1. When I have a second workbook open, which has it's own macros and VBA code - totally unrelated to Workbook 1, i.e. no links or similarly named procedures, the second workbook seems to want to run through the UDF contained in Wb 1. In case it matters the same UDF is also held in my Personal.xls workbook although it is the UDF in Wb1 that is being used. How can I prevent the second workbook seeming to want to use the UDF in the 1st Wb? Usual TIA __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ __ Richard Buttrey Grappenhall, Cheshire, UK __________________________ |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| User defined function: All | Excel Programming | |||
| Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
| Need to open the Function Arguments window from VBA for a user defined function. | Excel Programming | |||
| User-defined data type; Error: Only User-defined types... | Excel Programming | |||
| User-Defined Function pre-empting Built-in Function? How to undo???? | Excel Programming | |||
Linear Mode
