|
User Defined Function being called by changes to an independent workbook
Excel 97
Hi, we have a Workbook originally developed by my company's Actuarial department to validate results for our bespoke application. This workbook, contains numerous worksheets containing lookup tables, and calculations, some of which are User Defined functions. The User Defined Functions are written in the the code module of the relevant worksheet and are called from a cell within the corresponding worksheet. eg. B50 : =Actuarial_Function(input1,input2,input3) Each year new rates are added to our application and are verified by a corresponding updated version of the workbook. This year the Workbook has manifested a strange behaviour of calling one the User Defined functions when changes are made to an independent workbook opened in the same session of Excel. eg, open testing workbook, enter data everything is fine calcualations work, open a new workbook or existing workbook and when modifications are made to that it is apparent that the User Defined Functions have been called in the Testing Workbook and when I switch back to the Testing Workbook I get a #VALUE! in the cell which has called the UDF. I can see the reason the error generated in the way the User Defined Function has been written, the range object has not been specifically allocated to the Testing Workbook, so when a 'foreign' workbook calls this function it failsover as the range does not exist and falls over with an object not found error. I could modify the UDFs to fix this but I want to understand why this is happening in the first place. I have tried removing the "Update remote references" and "Save External link values" from the Calculation tab in the Options menu to no avail. This workbook has been in operation for 5 years and whilst not perfect I have never seen this behaviour. I would be much obliged if anyone can shed any light on why this has suddenly started happening. Simon |
User Defined Function being called by changes to an independent workbook
Just off the top of my head...
Where any of these problem formulas copy/pasted into the new workbook from the test workbook? This would establish a link, I would thing, causing the new workbook to call the UDF in the test workbook. Were any of the sheets containing problem formulas copied from the test workbook into the new workbook? Is the new workbook being created from a template, OR is it the old workbook "SavedAs"? If anything that references a range object in the test workbook is copied into another workbook, the definition copies as well. This suggests to me the range objects in the test workbook are globally defined. If, as you state, the UDFs are in the code module of each sheet then any range references should be defined as local to that sheet. Global range defs travel with copy/paste, replacing any global defs with the same name. (Dependant on how the Q asked during paste was answered) Local range defs are unique to their respective sheet, and travel with it wherever it goes. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
User Defined Function being called by changes to an independent workbook
On Jan 12, 7:10*pm, GS wrote:
Just off the top of my head... Where any of these problem formulas copy/pasted into the new workbook from the test workbook? This would establish a link, I would thing, causing the new workbook to call the UDF in the test workbook. Were any of the sheets containing problem formulas copied from the test workbook into the new workbook? Is the new workbook being created from a template, OR is it the old workbook "SavedAs"? If anything that references a range object in the test workbook is copied into another workbook, the definition copies as well. This suggests to me the range objects in the test workbook are globally defined. If, as you state, the UDFs are in the code module of each sheet then any range references should be defined as local to that sheet. * Global range defs travel with copy/paste, * replacing any global defs with the same name. * (Dependant on how the Q asked during paste was answered) * Local range defs are unique to their respective sheet, * and travel with it wherever it goes. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thanks Garry, To answer your questions no in all cases. I could understand there being some form of link in place if the sheets were any related. However, if I just do a file new workbook and start typing, it upsets the "Test Workbook". I double checked the UDF's and my initial statement that they were linked to the code sheet was wrong, they are placed in generic code modules within the workbook. I have tried limiting their scope to PRIVATE but the same behaviour still manifests. Regards Simon |
User Defined Function being called by changes to an independent workbook
It happens that armsiee formulated :
On Jan 12, 7:10*pm, GS wrote: Just off the top of my head... Where any of these problem formulas copy/pasted into the new workbook from the test workbook? This would establish a link, I would thing, causing the new workbook to call the UDF in the test workbook. Were any of the sheets containing problem formulas copied from the test workbook into the new workbook? Is the new workbook being created from a template, OR is it the old workbook "SavedAs"? If anything that references a range object in the test workbook is copied into another workbook, the definition copies as well. This suggests to me the range objects in the test workbook are globally defined. If, as you state, the UDFs are in the code module of each sheet then any range references should be defined as local to that sheet. * Global range defs travel with copy/paste, * replacing any global defs with the same name. * (Dependant on how the Q asked during paste was answered) * Local range defs are unique to their respective sheet, * and travel with it wherever it goes. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thanks Garry, To answer your questions no in all cases. I could understand there being some form of link in place if the sheets were any related. However, if I just do a file new workbook and start typing, it upsets the "Test Workbook". I double checked the UDF's and my initial statement that they were linked to the code sheet was wrong, they are placed in generic code modules within the workbook. I have tried limiting their scope to PRIVATE but the same behaviour still manifests. Regards Simon Does the UDF in question ref ActiveWorkbook or ThisWorkbook? -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
User Defined Function being called by changes to an independent workbook
"GS" wrote in message
... It happens that armsiee formulated : On Jan 12, 7:10 pm, GS wrote: Just off the top of my head... Where any of these problem formulas copy/pasted into the new workbook from the test workbook? This would establish a link, I would thing, causing the new workbook to call the UDF in the test workbook. Were any of the sheets containing problem formulas copied from the test workbook into the new workbook? Is the new workbook being created from a template, OR is it the old workbook "SavedAs"? If anything that references a range object in the test workbook is copied into another workbook, the definition copies as well. This suggests to me the range objects in the test workbook are globally defined. If, as you state, the UDFs are in the code module of each sheet then any range references should be defined as local to that sheet. Global range defs travel with copy/paste, replacing any global defs with the same name. (Dependant on how the Q asked during paste was answered) Local range defs are unique to their respective sheet, and travel with it wherever it goes. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thanks Garry, To answer your questions no in all cases. I could understand there being some form of link in place if the sheets were any related. However, if I just do a file new workbook and start typing, it upsets the "Test Workbook". I double checked the UDF's and my initial statement that they were linked to the code sheet was wrong, they are placed in generic code modules within the workbook. I have tried limiting their scope to PRIVATE but the same behaviour still manifests. Regards Simon Does the UDF in question ref ActiveWorkbook or ThisWorkbook? If you "start typing, and it upsets the test workbook" suggests to me that you are doing something with Application.OnKey. I have a workbook that I created under xl2003 that uses Application.OnKey to modify the tab / enter keystroke behavior. For reasons that I never did get to the bottom of, that workbook does not always reset Application.OnKey when it loses focus ... and the modified Application.OnKey processing invokes the code module that it is pointing to, even if that workbook happens to be closed at the time. In fact, that was the clue that helped me to identify the trouble - seeing a completely unrelated workbook suddenly open itself "for no reason!" -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
User Defined Function being called by changes to an independent workbook
Clif McIrvin used his keyboard to write :
"GS" wrote in message ... It happens that armsiee formulated : On Jan 12, 7:10 pm, GS wrote: Just off the top of my head... Where any of these problem formulas copy/pasted into the new workbook from the test workbook? This would establish a link, I would thing, causing the new workbook to call the UDF in the test workbook. Were any of the sheets containing problem formulas copied from the test workbook into the new workbook? Is the new workbook being created from a template, OR is it the old workbook "SavedAs"? If anything that references a range object in the test workbook is copied into another workbook, the definition copies as well. This suggests to me the range objects in the test workbook are globally defined. If, as you state, the UDFs are in the code module of each sheet then any range references should be defined as local to that sheet. Global range defs travel with copy/paste, replacing any global defs with the same name. (Dependant on how the Q asked during paste was answered) Local range defs are unique to their respective sheet, and travel with it wherever it goes. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thanks Garry, To answer your questions no in all cases. I could understand there being some form of link in place if the sheets were any related. However, if I just do a file new workbook and start typing, it upsets the "Test Workbook". I double checked the UDF's and my initial statement that they were linked to the code sheet was wrong, they are placed in generic code modules within the workbook. I have tried limiting their scope to PRIVATE but the same behaviour still manifests. Regards Simon Does the UDF in question ref ActiveWorkbook or ThisWorkbook? If you "start typing, and it upsets the test workbook" suggests to me that you are doing something with Application.OnKey. I have a workbook that I created under xl2003 that uses Application.OnKey to modify the tab / enter keystroke behavior. For reasons that I never did get to the bottom of, that workbook does not always reset Application.OnKey when it loses focus ... and the modified Application.OnKey processing invokes the code module that it is pointing to, even if that workbook happens to be closed at the time. In fact, that was the clue that helped me to identify the trouble - seeing a completely unrelated workbook suddenly open itself "for no reason!" What would really be helpful is the OP posting the code for the errant UDF! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
User Defined Function being called by changes to an independent workbook
"GS" wrote in message
... What would really be helpful is the OP posting the code for the errant UDF! -- Garry No kidding. This guessing game isn't all that easy <g. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
User Defined Function being called by changes to an independent workbook
On Jan 17, 7:30*pm, "Clif McIrvin" wrote:
"GS" wrote in message ... What would really be helpful is the OP posting the code for the errant UDF! -- Garry No kidding. This guessing game isn't all that easy <g. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) I hadn't posted the UDF as my original post was getting so long in the first place and as stated I know what the problem is with the UDF in the context of this issue. What I don't understand is why this UDF is being called by an event on a completely seperate workbook but as requested .... Function MVA_Factor_Function(MVA_Expected_Return_Fac, MVA_Actual_Return_Fac, _ MVA_Minimum, MVA_Maximum, MVA_Max_Multiplier) 'Set variables to be used in the calculation Dim MVA_Ratio As Double 'Variables picked up from the input sheet MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor") MVA_Actual_Return = Range("MVA_Actual_Return_Factor") MVA_Maximum = Range("MVA_Max") MVA_Max_Multiplier = Range("MVA_Max_Mult") MVA_Minimum = Range("MVA_Min") 'Calculate MVA as a ratio of the Actual return factor and the expected return factor MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 + MVA_Expected_Return_Fac) 'MVA is applied under the following conditions If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio If MVA_Ratio MVA_Maximum Then MVA_Factor_Function = MVA_Ratio * MVA_Max_Multiplier If MVA_Ratio MVA_Minimum And MVA_Ratio < MVA_Maximum Then MVA_Factor_Function = 1 |
User Defined Function being called by changes to an independent workbook
"armsiee" wrote in message
... On Jan 17, 7:30 pm, "Clif McIrvin" wrote: "GS" wrote in message ... What would really be helpful is the OP posting the code for the errant UDF! -- Garry No kidding. This guessing game isn't all that easy <g. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) I hadn't posted the UDF as my original post was getting so long in the first place and as stated I know what the problem is with the UDF in the context of this issue. What I don't understand is why this UDF is being called by an event on a completely seperate workbook but as requested .... Function MVA_Factor_Function(MVA_Expected_Return_Fac, MVA_Actual_Return_Fac, _ MVA_Minimum, MVA_Maximum, MVA_Max_Multiplier) 'Set variables to be used in the calculation Dim MVA_Ratio As Double 'Variables picked up from the input sheet MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor") MVA_Actual_Return = Range("MVA_Actual_Return_Factor") MVA_Maximum = Range("MVA_Max") MVA_Max_Multiplier = Range("MVA_Max_Mult") MVA_Minimum = Range("MVA_Min") 'Calculate MVA as a ratio of the Actual return factor and the expected return factor MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 + MVA_Expected_Return_Fac) 'MVA is applied under the following conditions If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio If MVA_Ratio MVA_Maximum Then MVA_Factor_Function = MVA_Ratio * MVA_Max_Multiplier If MVA_Ratio MVA_Minimum And MVA_Ratio < MVA_Maximum Then MVA_Factor_Function = 1 --------- Is this the entire UDF? Your post ended rather abruptly. Does this behavior manifest on any workstation, or only some? What I don't understand is why this UDF is being called by an event on a completely seperate workbook but as requested .... Have you attempted to pare the complexity down to the simplest demonstration of the problem that you can achieve? That is, can you create a brand new workbook, copy / paste the UDF into it, and reproduce the behavior? I'm still puzzled as to precisely what you mean by "event" .... from your earlier description of the problem, I understand that you can open Workbook "A" (containing the UDFs, tables, etc) and all works as expected, then you can File | New (Create empty Workbook "B"), and when you begin typing into Workbook "B" the UDF is called. Is this correct? From the code you posted, I see nothing at all to explain what you are seeing. In my (admittedly limited) experience, the only mechanisms I know to invoke a UDF from a worksheet are a) by an explicit call from a formula in a cell, b) through a "command button" - whether a control or object on a form, in the worksheet, or from a toolbar, c) using "Application.OnKey" to modify keyboard processing or d) using a UDF in a conditional formatting formula. I'm wondering if something "in the innards" of that workbook has become "confused" and the solution will be to rebuild the workbook "from scratch." I don't have enough experience to know of any method for doing that other than "brute force" ... If I had to try, I'd try to export/import as .csv, and copy/paste the code manually. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
User Defined Function being called by changes to an independent workbook
Clif McIrvin laid this down on his screen :
I'm wondering if something "in the innards" of that workbook has become "confused" and the solution will be to rebuild the workbook "from scratch." I don't have enough experience to know of any method for doing that other than "brute force" ... If I had to try, I'd try to export/import as .csv, and copy/paste the code manually. You could try Rob Bovey's VBA Code Cleaner addin... http://www.appspro.com/Utilities/CodeCleaner.htm -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
User Defined Function being called by changes to an independent workbook
"GS" wrote in message
... Clif McIrvin laid this down on his screen : I'm wondering if something "in the innards" of that workbook has become "confused" and the solution will be to rebuild the workbook "from scratch." I don't have enough experience to know of any method for doing that other than "brute force" ... If I had to try, I'd try to export/import as .csv, and copy/paste the code manually. You could try Rob Bovey's VBA Code Cleaner addin... http://www.appspro.com/Utilities/CodeCleaner.htm -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Thanks! I bookmarked that. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
User Defined Function being called by changes to an independent workbook
On Jan 19, 8:16*pm, "Clif McIrvin" wrote:
"armsiee" wrote in message ... On Jan 17, 7:30 pm, "Clif McIrvin" wrote: "GS" wrote in message ... What would really be helpful is the OP posting the code for the errant UDF! -- Garry No kidding. This guessing game isn't all that easy <g. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) I hadn't posted the UDF as my original post was getting so long in the first place and as stated I know what the problem is with the UDF in the context of this issue. *What I don't understand is why this UDF is being called by an event on a completely seperate workbook but as requested .... Function MVA_Factor_Function(MVA_Expected_Return_Fac, MVA_Actual_Return_Fac, _ * * * * * * * * * * * * * * * * MVA_Minimum, MVA_Maximum, MVA_Max_Multiplier) 'Set variables to be used in the calculation Dim MVA_Ratio As Double 'Variables picked up from the input sheet MVA_Expected_Return_Fac = Range("MVA_Expected_Return_Factor") MVA_Actual_Return = Range("MVA_Actual_Return_Factor") MVA_Maximum = Range("MVA_Max") MVA_Max_Multiplier = Range("MVA_Max_Mult") MVA_Minimum = Range("MVA_Min") 'Calculate MVA as a ratio of the Actual return factor and the expected return factor MVA_Ratio = (1 + MVA_Actual_Return_Fac) / (1 + MVA_Expected_Return_Fac) 'MVA is applied under the following conditions If MVA_Ratio < MVA_Minimum Then MVA_Factor_Function = MVA_Ratio If MVA_Ratio MVA_Maximum Then MVA_Factor_Function = MVA_Ratio * MVA_Max_Multiplier If MVA_Ratio MVA_Minimum And MVA_Ratio < MVA_Maximum Then MVA_Factor_Function = 1 --------- Is this the entire UDF? *Your post ended rather abruptly. Does this behavior manifest on any workstation, or only some? What I don't understand is why this UDF is being called by an event on a completely seperate workbook but as requested .... Have you attempted to pare the complexity down to the simplest demonstration of the problem that you can achieve? *That is, can you create a brand new workbook, copy / paste the UDF into it, and reproduce the behavior? I'm still puzzled as to precisely what you mean by "event" .... from your earlier description of the problem, I understand that you can open Workbook "A" (containing the UDFs, tables, etc) and all works as expected, then you can File | New (Create empty Workbook "B"), and when you begin typing into Workbook "B" the UDF is called. *Is this correct? From the code you posted, I see nothing at all to explain what you are seeing. *In my (admittedly limited) experience, the only mechanisms I know to invoke a UDF from a worksheet are a) by an explicit call from a formula in a cell, b) through a "command button" - whether a control or object on a form, in the worksheet, or from a toolbar, c) using "Application.OnKey" to modify keyboard processing or d) using a UDF in a conditional formatting formula. I'm wondering if something "in the innards" of that workbook has become "confused" and the solution will be to rebuild the workbook "from scratch." *I don't have enough experience to know of any method for doing that other than "brute force" ... If I had to try, I'd try to export/import as .csv, and copy/paste the code manually. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) I'm sorry if I haven't made myself clear but have kept trying to say that I don't believe the UDF is the issue (which is why i did not post it originally) it is a sympton of the problem. It appears that the "Test Sheet" recalculates itself whenever anything else happens within that instance of Excel, eg, open another spreadhseet, type into a blank spreadhseet and thus calls that UDF which produces errors (as it is not specific with its Range declarations) sorry just missed the End Function on the code snippet. And yes the same behaviour occurs on a colleagues workstation. Good idea, regarding code cleaner i will give it a whirl. |
User Defined Function being called by changes to an independent workbook
armsiee explained :
I'm sorry if I haven't made myself clear but have kept trying to say that I don't believe the UDF is the issue (which is why i did not post it originally) it is a sympton of the problem. It appears that the "Test Sheet" recalculates itself whenever anything else happens within that instance of Excel, eg, open another spreadhseet, type into a blank spreadhseet and thus calls that UDF which produces errors (as it is not specific with its Range declarations) sorry just missed the End Function on the code snippet. And yes the same behaviour occurs on a colleagues workstation. What I don't see in your UDF is how it knows NOT to run on any workbook since there's no explicit ref to the workbook that uses it. You ref the Range object in general and so Excel takes that to be a range on the active sheet. IOW, your UDF (as written) should work with any open workbook. I think <IMO you need to fully qualify the workbook that it's to work with so Excel knows when/where it should be used. For example, wrap your code in a With...End With construct something like... Function MVA_Factor_Function(MVA_Expected_Return_Fac, _ MVA_Actual_Return_Fac, _ MVA_Minimum, MVA_Maximum, _ MVA_Max_Multiplier) 'Set variables to be used in the calculation Dim MVA_Ratio As Double With ThisWorkbook.ActiveSheet 'Variables picked up from the input sheet MVA_Expected_Return_Fac = .Range("MVA_Expected_Return_Factor") MVA_Actual_Return = .Range("MVA_Actual_Return_Factor") MVA_Maximum = .Range("MVA_Max") MVA_Max_Multiplier = .Range("MVA_Max_Mult") MVA_Minimum = .Range("MVA_Min") '**Note the dot before Range. End With 'Calculate MVA as a ratio of the Actual return factor... 'other code here... End Function Also, if it happens whenever another workbook is activated then there must be some code in an event that's causing it to execute, OR the test workbook uses a similar named function also not restricted to that workbook. Surely this would cause conflict that would cause unexpected results. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
User Defined Function being called by changes to an independent workbook
On Jan 21, 4:57*pm, GS wrote:
armsiee explained : I'm sorry if I haven't made myself clear but have kept trying to say that I don't believe the UDF is the issue (which is why i did not post it originally) it is a sympton of the problem. *It appears that the "Test Sheet" recalculates itself whenever anything else happens within that instance of Excel, eg, open another spreadhseet, type into a blank spreadhseet and thus calls that UDF which produces errors (as it is not specific with its Range declarations) sorry just missed the End Function on the code snippet. *And yes the same behaviour occurs on a colleagues workstation. What I don't see in your UDF is how it knows NOT to run on any workbook since there's no explicit ref to the workbook that uses it. You ref the Range object in general and so Excel takes that to be a range on the active sheet. IOW, your UDF (as written) should work with any open workbook. I think <IMO you need to fully qualify the workbook that it's to work with so Excel knows when/where it should be used. For example, wrap your code in a With...End With construct something like... * Function MVA_Factor_Function(MVA_Expected_Return_Fac, _ * * * * * * * * * * * * * * * *MVA_Actual_Return_Fac, _ * * * * * * * * * * * * * * * *MVA_Minimum, MVA_Maximum, _ * * * * * * * * * * * * * * * *MVA_Max_Multiplier) * * 'Set variables to be used in the calculation * * Dim MVA_Ratio As Double * * With ThisWorkbook.ActiveSheet * * * 'Variables picked up from the input sheet * * * MVA_Expected_Return_Fac = .Range("MVA_Expected_Return_Factor") * * * MVA_Actual_Return = .Range("MVA_Actual_Return_Factor") * * * MVA_Maximum = .Range("MVA_Max") * * * MVA_Max_Multiplier = .Range("MVA_Max_Mult") * * * MVA_Minimum = .Range("MVA_Min") * * * '**Note the dot before Range. * * End With * * 'Calculate MVA as a ratio of the Actual return factor... * * 'other code here... * End Function Also, if it happens whenever another workbook is activated then there must be some code in an event that's causing it to execute, OR the test workbook uses a similar named function also not restricted to that workbook. Surely this would cause conflict that would cause unexpected results. -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc Yes thanks Garry, I have had similar thoughts, however this workbook has been written like this and functioning for nearly 5 years with the UDF code exactly the same (i've gone back and double checked through the version history) I have searched through the workbook to see if the UDF is called anywhere else and the only place is the explicit reference within the sheet, which leads me to believe that something else has happened to the Workbook. I can work around it, thanks for your efforts. |
| All times are GMT +1. The time now is 04:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com