Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
My wife is an analyst with a very large company. She has recently changed
groups within the company and inherited responsibility for about 400 worksheets, each of which contains a Validate subroutine. She's a good programmer -- comp. sci. from Cornell; C doesn't scare her -- and she's been cleaning up and extending Validate for the past few weeks. Now she's doing testing for a new release. All 400 worksheets will be rolled out to the users at once tomorrow, with the new Validate code in place. Except...she ran one test too many. Here's the e-mail I got: I have an Excel "template" spreadsheet which has several macros associated with it. In particular, there's a subroutine StandAloneValidate, which is associated with a shortcut key, which has 3 lines of code: Dim RC as Boolean Call Validate(RC) End Sub The Validate subroutine has lots of logic, calls several functions, etc. I've added lots of functionality to it in the last few weeks. It's been working nicely. Today something odd happened: I did a bunch of testing with spreadsheets that had just been created/updated with the VBA in my template spreadsheet. They worked correctly. I edited the VBA code throughout to add comments -- nothing more. I brought this updated code into my "test" spreadsheet. I ran the StandAloneValidate macro. I got an error on the Call Validate(RC) line of code; the error occured on the return from the Validate subroutine. I didn't write the error down, but it was something about a problem calling a DLL function. The error occured both from running the code in the VBA environment, and running it from the spreadsheet using the shortcut key. Rebooting didn't help. I renamed the Validate subroutine to ValidateData, and the problem went away. I renamed it back to Validate just to see -- well, just to see. Ran just fine. No problem. Now I can't reproduce the error. Going back to an older version of the code and pasting in my edits doesn't cause the error. (I didn't save a copy of the spreadsheet when it was causing the error!) Any idea what caused my error? Any idea of if it will recur, or how I can prevent it from recurring? Any thoughts at all? The real problem here is that my wife's boss is from the mainframe world, doesn't understand that Windows programs just do this sort of thing sometimes. He reasons -- logically -- that if it happened during testing, it can happen during deployment. The fact that my wife has now tested her code on all 400 worksheets -- repeated all her regression testing -- doesn't impress him. The code failed once, after all. And no one knows why. So no one can say that it won't happen again. That's his position, and of course it's reasonable. But...this is a first major project for my wife, so she is undergoing some evaluation. Excel/VBA are undergoing ongoing evaluation, as is, I suppose, the Windows platform itself. Excel is too good a tool to fail this test...and yet, the boss's request for an explanation of the error is reasonable. (Yes, we'll be making screen shots of error messages from now on. Whoops.) -- Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
No error, no code.... I doubt anyone would be able to provide a "spot on"
explanation for this. Is the Validate routine exactly the same in all of the files? Tim "J.D. Hildebrand" wrote in message news:Wl__f.4274$ee6.3057@trndny01... My wife is an analyst with a very large company. She has recently changed groups within the company and inherited responsibility for about 400 worksheets, each of which contains a Validate subroutine. She's a good programmer -- comp. sci. from Cornell; C doesn't scare her -- and she's been cleaning up and extending Validate for the past few weeks. Now she's doing testing for a new release. All 400 worksheets will be rolled out to the users at once tomorrow, with the new Validate code in place. Except...she ran one test too many. Here's the e-mail I got: I have an Excel "template" spreadsheet which has several macros associated with it. In particular, there's a subroutine StandAloneValidate, which is associated with a shortcut key, which has 3 lines of code: Dim RC as Boolean Call Validate(RC) End Sub The Validate subroutine has lots of logic, calls several functions, etc. I've added lots of functionality to it in the last few weeks. It's been working nicely. Today something odd happened: I did a bunch of testing with spreadsheets that had just been created/updated with the VBA in my template spreadsheet. They worked correctly. I edited the VBA code throughout to add comments -- nothing more. I brought this updated code into my "test" spreadsheet. I ran the StandAloneValidate macro. I got an error on the Call Validate(RC) line of code; the error occured on the return from the Validate subroutine. I didn't write the error down, but it was something about a problem calling a DLL function. The error occured both from running the code in the VBA environment, and running it from the spreadsheet using the shortcut key. Rebooting didn't help. I renamed the Validate subroutine to ValidateData, and the problem went away. I renamed it back to Validate just to see -- well, just to see. Ran just fine. No problem. Now I can't reproduce the error. Going back to an older version of the code and pasting in my edits doesn't cause the error. (I didn't save a copy of the spreadsheet when it was causing the error!) Any idea what caused my error? Any idea of if it will recur, or how I can prevent it from recurring? Any thoughts at all? The real problem here is that my wife's boss is from the mainframe world, doesn't understand that Windows programs just do this sort of thing sometimes. He reasons -- logically -- that if it happened during testing, it can happen during deployment. The fact that my wife has now tested her code on all 400 worksheets -- repeated all her regression testing -- doesn't impress him. The code failed once, after all. And no one knows why. So no one can say that it won't happen again. That's his position, and of course it's reasonable. But...this is a first major project for my wife, so she is undergoing some evaluation. Excel/VBA are undergoing ongoing evaluation, as is, I suppose, the Windows platform itself. Excel is too good a tool to fail this test...and yet, the boss's request for an explanation of the error is reasonable. (Yes, we'll be making screen shots of error messages from now on. Whoops.) -- Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
Tim,
There was the 12:30 version of the Validate routine, which ran without error. There was the 1:30 version of the Validate routine, which had *only comments* added, but no executed code changed. This version gave an error. There was the 2:00 version of the Validate routine, which was identical to the 1:30 version except that the Validate routine was renamed to ValidateData. This ran without error. There was the 5:00 version of the Validate routine, which was identical to the 1:30 version: I renamed ValidateData back to Validate. This ran without error. All of these were within a single spreadsheet, abcde.xls, my "testing" spreadsheet. In addition, I tested 140 spreadsheets with the 2:00 version of the Validate routine -- yes, the identical version -- and all ran without error. The fact that the 1:30 version and the 5:00 version had the *identical* VBA leads me to believe that the problem is not in my code. I suspect there is a problem with Excel, or with my spreadsheet, that it is somehow sort of corrupted. I found a website, http://www.dotxls.com/excel-recovery/40/ , which says the following: “During the process of creating VBA programs, a lot of “junk” builds up in your files. If these files aren’t cleaned periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents out to text files, deleting the components, then importing the components back from the text files.” “You will often see a dramatic reduction in file size once you’ve done this. Even after recompiling the code….500k-600k projects routinely drop 100k if they haven’t been cleaned recently. This is all useless, error prone garbage getting thrown out of your file.” Reduce Excel File Size / VBUSERS Excel File Rebuilder “The rebuilder has been primarily designed for developers to help stabilise and compact Excel Workbooks. Rebuilding workbooks containing VBA code and references to COM objects results in the workbooks becoming more stabile and reducing in size by up to 70%. The rebuilder also overcomes many common problems including unexplained ‘Bad DLL Calling Convention’ errors, Excel locking up while saving workbooks and ‘Application undefined error’. The rebuilder will also automatically check all references and if necessary repoint any invalid references.” I don't know if they're just trying to sell a product or not. Does their explanation make sense to you, as a way of explaining what happened to me? Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... No error, no code.... I doubt anyone would be able to provide a "spot on" explanation for this. Is the Validate routine exactly the same in all of the files? Tim "J.D. Hildebrand" wrote in message news:Wl__f.4274$ee6.3057@trndny01... My wife is an analyst with a very large company. She has recently changed groups within the company and inherited responsibility for about 400 worksheets, each of which contains a Validate subroutine. She's a good programmer -- comp. sci. from Cornell; C doesn't scare her -- and she's been cleaning up and extending Validate for the past few weeks. Now she's doing testing for a new release. All 400 worksheets will be rolled out to the users at once tomorrow, with the new Validate code in place. Except...she ran one test too many. Here's the e-mail I got: I have an Excel "template" spreadsheet which has several macros associated with it. In particular, there's a subroutine StandAloneValidate, which is associated with a shortcut key, which has 3 lines of code: Dim RC as Boolean Call Validate(RC) End Sub The Validate subroutine has lots of logic, calls several functions, etc. I've added lots of functionality to it in the last few weeks. It's been working nicely. Today something odd happened: I did a bunch of testing with spreadsheets that had just been created/updated with the VBA in my template spreadsheet. They worked correctly. I edited the VBA code throughout to add comments -- nothing more. I brought this updated code into my "test" spreadsheet. I ran the StandAloneValidate macro. I got an error on the Call Validate(RC) line of code; the error occured on the return from the Validate subroutine. I didn't write the error down, but it was something about a problem calling a DLL function. The error occured both from running the code in the VBA environment, and running it from the spreadsheet using the shortcut key. Rebooting didn't help. I renamed the Validate subroutine to ValidateData, and the problem went away. I renamed it back to Validate just to see -- well, just to see. Ran just fine. No problem. Now I can't reproduce the error. Going back to an older version of the code and pasting in my edits doesn't cause the error. (I didn't save a copy of the spreadsheet when it was causing the error!) Any idea what caused my error? Any idea of if it will recur, or how I can prevent it from recurring? Any thoughts at all? The real problem here is that my wife's boss is from the mainframe world, doesn't understand that Windows programs just do this sort of thing sometimes. He reasons -- logically -- that if it happened during testing, it can happen during deployment. The fact that my wife has now tested her code on all 400 worksheets -- repeated all her regression testing -- doesn't impress him. The code failed once, after all. And no one knows why. So no one can say that it won't happen again. That's his position, and of course it's reasonable. But...this is a first major project for my wife, so she is undergoing some evaluation. Excel/VBA are undergoing ongoing evaluation, as is, I suppose, the Windows platform itself. Excel is too good a tool to fail this test...and yet, the boss's request for an explanation of the error is reasonable. (Yes, we'll be making screen shots of error messages from now on. Whoops.) -- Every normal man must be tempted, at times, to spit on his hands, hoist the black flag, and begin slitting throats. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
Naomi,
It is a common finding (in this newsgroup at least) that the process described by the site you visited does help. During the develoment process Excel files do build up "junk" which can only be removed by exporting and re-importing code modules. The utility they recommend is free, so there is no ulterior motive on their part, and many other posters here have confirmed it does work. We all manage at some point to introduce errors in our code which later - sometimes after some "unrelated" changes - cannot be reproduced. This in iteslf is not a failing of the platform, but a normal part any coding (no-one is perfect). As long as you have a set of tests which your code passes, and you feel the set of tests is enough to judge "production" quality, then that would seem to be sufficient. The original post mentioned something about a dll error - does the code call procedures in an external library (API calls?) Regards, Tim "J.D. Hildebrand" wrote in message news:uZ%_f.11632$_T5.3381@trndny08... Tim, There was the 12:30 version of the Validate routine, which ran without error. There was the 1:30 version of the Validate routine, which had *only comments* added, but no executed code changed. This version gave an error. There was the 2:00 version of the Validate routine, which was identical to the 1:30 version except that the Validate routine was renamed to ValidateData. This ran without error. There was the 5:00 version of the Validate routine, which was identical to the 1:30 version: I renamed ValidateData back to Validate. This ran without error. All of these were within a single spreadsheet, abcde.xls, my "testing" spreadsheet. In addition, I tested 140 spreadsheets with the 2:00 version of the Validate routine -- yes, the identical version -- and all ran without error. The fact that the 1:30 version and the 5:00 version had the *identical* VBA leads me to believe that the problem is not in my code. I suspect there is a problem with Excel, or with my spreadsheet, that it is somehow sort of corrupted. I found a website, http://www.dotxls.com/excel-recovery/40/ , which says the following: "During the process of creating VBA programs, a lot of "junk" builds up in your files. If these files aren't cleaned periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents out to text files, deleting the components, then importing the components back from the text files." "You will often see a dramatic reduction in file size once you've done this. Even after recompiling the code..500k-600k projects routinely drop 100k if they haven't been cleaned recently. This is all useless, error prone garbage getting thrown out of your file." Reduce Excel File Size / VBUSERS Excel File Rebuilder "The rebuilder has been primarily designed for developers to help stabilise and compact Excel Workbooks. Rebuilding workbooks containing VBA code and references to COM objects results in the workbooks becoming more stabile and reducing in size by up to 70%. The rebuilder also overcomes many common problems including unexplained 'Bad DLL Calling Convention' errors, Excel locking up while saving workbooks and 'Application undefined error'. The rebuilder will also automatically check all references and if necessary repoint any invalid references." I don't know if they're just trying to sell a product or not. Does their explanation make sense to you, as a way of explaining what happened to me? Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... No error, no code.... I doubt anyone would be able to provide a "spot on" explanation for this. Is the Validate routine exactly the same in all of the files? Tim |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
Tim,
Thanks. The reassurance about the utility -- and the problem it was designed to fix -- is helpful. I agree that complicated code can sometimes be flaky. If you can't exactly reproduce the input conditions, you can't reproduce the error. The input conditions might have depended on all kinds of things in the outside environment. Earlier in my career I encountered this in complex embedded programs. These are actual subtle coding errors, not errors in the platform. However, I am dealing here with pretty straightforward code. It's not event-driven, it doesn't do any file or database manipuation, it simply looks through the Excel worksheets and verifies that the data passes some simple tests. In an example like this, it's hard to imagine that identical code, run on identical data, would give different answers. To me, it's more reasonable to look at the platform in this case. And no, there are no DLL calls in the program. As I say, it's really very simple code. I completely agree that this code is production worthy. My problem right now is political -- convincing my manager (I'm new to this project, and haven't fully earned my new boss's respect) that the code has been tested enough, and the explanation I'm giving him (basically: Look, this is Windows programming, sh*t happens) is believable. In the programming world he comes from (mainframe & unix), if you run into a problem, you don't release your code until you've reproduced the error, and then fixed whatever caused it. I'm trying to tell him we just can't do that here. (And running a third party utility, where he has no access to its source code, would probably give him a heart attack!) I appreciate the time you're giving me. Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... Naomi, It is a common finding (in this newsgroup at least) that the process described by the site you visited does help. During the develoment process Excel files do build up "junk" which can only be removed by exporting and re-importing code modules. The utility they recommend is free, so there is no ulterior motive on their part, and many other posters here have confirmed it does work. We all manage at some point to introduce errors in our code which later - sometimes after some "unrelated" changes - cannot be reproduced. This in iteslf is not a failing of the platform, but a normal part any coding (no-one is perfect). As long as you have a set of tests which your code passes, and you feel the set of tests is enough to judge "production" quality, then that would seem to be sufficient. The original post mentioned something about a dll error - does the code call procedures in an external library (API calls?) Regards, Tim "J.D. Hildebrand" wrote in message news:uZ%_f.11632$_T5.3381@trndny08... Tim, There was the 12:30 version of the Validate routine, which ran without error. There was the 1:30 version of the Validate routine, which had *only comments* added, but no executed code changed. This version gave an error. There was the 2:00 version of the Validate routine, which was identical to the 1:30 version except that the Validate routine was renamed to ValidateData. This ran without error. There was the 5:00 version of the Validate routine, which was identical to the 1:30 version: I renamed ValidateData back to Validate. This ran without error. All of these were within a single spreadsheet, abcde.xls, my "testing" spreadsheet. In addition, I tested 140 spreadsheets with the 2:00 version of the Validate routine -- yes, the identical version -- and all ran without error. The fact that the 1:30 version and the 5:00 version had the *identical* VBA leads me to believe that the problem is not in my code. I suspect there is a problem with Excel, or with my spreadsheet, that it is somehow sort of corrupted. I found a website, http://www.dotxls.com/excel-recovery/40/ , which says the following: "During the process of creating VBA programs, a lot of "junk" builds up in your files. If these files aren't cleaned periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents out to text files, deleting the components, then importing the components back from the text files." "You will often see a dramatic reduction in file size once you've done this. Even after recompiling the code..500k-600k projects routinely drop 100k if they haven't been cleaned recently. This is all useless, error prone garbage getting thrown out of your file." Reduce Excel File Size / VBUSERS Excel File Rebuilder "The rebuilder has been primarily designed for developers to help stabilise and compact Excel Workbooks. Rebuilding workbooks containing VBA code and references to COM objects results in the workbooks becoming more stabile and reducing in size by up to 70%. The rebuilder also overcomes many common problems including unexplained 'Bad DLL Calling Convention' errors, Excel locking up while saving workbooks and 'Application undefined error'. The rebuilder will also automatically check all references and if necessary repoint any invalid references." I don't know if they're just trying to sell a product or not. Does their explanation make sense to you, as a way of explaining what happened to me? Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... No error, no code.... I doubt anyone would be able to provide a "spot on" explanation for this. Is the Validate routine exactly the same in all of the files? Tim |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
My suspicion is that VBA is confusing your Validate Sub with a .Validate
method for some object reference. This would explain why renaming it makes it work, and would also explain the error associated with the .dll file because it is looking for the library routine "Validate". Excel has a heirarchy for deciding which Sub to run when there are duplicate names, so it may be looking at some reference first to find that name, and the referenced library may no longer be there. And yes, this could be a holdover from something that was in there previously, but I would also check the references to see if there is perhaps some object model in there that has a .Validate method associated with it. If so, and if it is not needed, clean it out. But your code should be OK; the safe thing to do if possible is avoid the name Validate and call your Sub something different. -- - K Dales "Naomi Hildebrand" wrote: Tim, Thanks. The reassurance about the utility -- and the problem it was designed to fix -- is helpful. I agree that complicated code can sometimes be flaky. If you can't exactly reproduce the input conditions, you can't reproduce the error. The input conditions might have depended on all kinds of things in the outside environment. Earlier in my career I encountered this in complex embedded programs. These are actual subtle coding errors, not errors in the platform. However, I am dealing here with pretty straightforward code. It's not event-driven, it doesn't do any file or database manipuation, it simply looks through the Excel worksheets and verifies that the data passes some simple tests. In an example like this, it's hard to imagine that identical code, run on identical data, would give different answers. To me, it's more reasonable to look at the platform in this case. And no, there are no DLL calls in the program. As I say, it's really very simple code. I completely agree that this code is production worthy. My problem right now is political -- convincing my manager (I'm new to this project, and haven't fully earned my new boss's respect) that the code has been tested enough, and the explanation I'm giving him (basically: Look, this is Windows programming, sh*t happens) is believable. In the programming world he comes from (mainframe & unix), if you run into a problem, you don't release your code until you've reproduced the error, and then fixed whatever caused it. I'm trying to tell him we just can't do that here. (And running a third party utility, where he has no access to its source code, would probably give him a heart attack!) I appreciate the time you're giving me. Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... Naomi, It is a common finding (in this newsgroup at least) that the process described by the site you visited does help. During the develoment process Excel files do build up "junk" which can only be removed by exporting and re-importing code modules. The utility they recommend is free, so there is no ulterior motive on their part, and many other posters here have confirmed it does work. We all manage at some point to introduce errors in our code which later - sometimes after some "unrelated" changes - cannot be reproduced. This in iteslf is not a failing of the platform, but a normal part any coding (no-one is perfect). As long as you have a set of tests which your code passes, and you feel the set of tests is enough to judge "production" quality, then that would seem to be sufficient. The original post mentioned something about a dll error - does the code call procedures in an external library (API calls?) Regards, Tim "J.D. Hildebrand" wrote in message news:uZ%_f.11632$_T5.3381@trndny08... Tim, There was the 12:30 version of the Validate routine, which ran without error. There was the 1:30 version of the Validate routine, which had *only comments* added, but no executed code changed. This version gave an error. There was the 2:00 version of the Validate routine, which was identical to the 1:30 version except that the Validate routine was renamed to ValidateData. This ran without error. There was the 5:00 version of the Validate routine, which was identical to the 1:30 version: I renamed ValidateData back to Validate. This ran without error. All of these were within a single spreadsheet, abcde.xls, my "testing" spreadsheet. In addition, I tested 140 spreadsheets with the 2:00 version of the Validate routine -- yes, the identical version -- and all ran without error. The fact that the 1:30 version and the 5:00 version had the *identical* VBA leads me to believe that the problem is not in my code. I suspect there is a problem with Excel, or with my spreadsheet, that it is somehow sort of corrupted. I found a website, http://www.dotxls.com/excel-recovery/40/ , which says the following: "During the process of creating VBA programs, a lot of "junk" builds up in your files. If these files aren't cleaned periodically you will begin to experience strange problems caused by this extra baggage. Cleaning a project involves exporting the contents of all its VBComponents out to text files, deleting the components, then importing the components back from the text files." "You will often see a dramatic reduction in file size once you've done this. Even after recompiling the code..500k-600k projects routinely drop 100k if they haven't been cleaned recently. This is all useless, error prone garbage getting thrown out of your file." Reduce Excel File Size / VBUSERS Excel File Rebuilder "The rebuilder has been primarily designed for developers to help stabilise and compact Excel Workbooks. Rebuilding workbooks containing VBA code and references to COM objects results in the workbooks becoming more stabile and reducing in size by up to 70%. The rebuilder also overcomes many common problems including unexplained 'Bad DLL Calling Convention' errors, Excel locking up while saving workbooks and 'Application undefined error'. The rebuilder will also automatically check all references and if necessary repoint any invalid references." I don't know if they're just trying to sell a product or not. Does their explanation make sense to you, as a way of explaining what happened to me? Thanks. Naomi "Tim Williams" <timjwilliams at gmail dot com wrote in message ... No error, no code.... I doubt anyone would be able to provide a "spot on" explanation for this. Is the Validate routine exactly the same in all of the files? Tim |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
If your boss has qualms about running an external ultility then you can just
perform the required steps manually: export and re-import all of your code modules. I might agree with your boss if the codebase which produced the error had not been modified since the problem was observed: if you made changes which fixed the error then it seem odd to persist in trying to reproduce that particular error.... -- Tim Williams Palo Alto, CA "Naomi Hildebrand" wrote in message news:TC5%f.11633$_T5.798@trndny08... Tim, Thanks. The reassurance about the utility -- and the problem it was designed to fix -- is helpful. I agree that complicated code can sometimes be flaky. If you can't exactly reproduce the input conditions, you can't reproduce the error. The input conditions might have depended on all kinds of things in the outside environment. Earlier in my career I encountered this in complex embedded programs. These are actual subtle coding errors, not errors in the platform. However, I am dealing here with pretty straightforward code. It's not event-driven, it doesn't do any file or database manipuation, it simply looks through the Excel worksheets and verifies that the data passes some simple tests. In an example like this, it's hard to imagine that identical code, run on identical data, would give different answers. To me, it's more reasonable to look at the platform in this case. And no, there are no DLL calls in the program. As I say, it's really very simple code. I completely agree that this code is production worthy. My problem right now is political -- convincing my manager (I'm new to this project, and haven't fully earned my new boss's respect) that the code has been tested enough, and the explanation I'm giving him (basically: Look, this is Windows programming, sh*t happens) is believable. In the programming world he comes from (mainframe & unix), if you run into a problem, you don't release your code until you've reproduced the error, and then fixed whatever caused it. I'm trying to tell him we just can't do that here. (And running a third party utility, where he has no access to its source code, would probably give him a heart attack!) I appreciate the time you're giving me. Thanks. Naomi |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
I downloaded and ran the program on a few of my worksheets. It did reduce
the file size by a large ammount but it also removed all comments and indentation so I could no longer easily work with my code. I didn't fix the problem I had which happens to be unrelated to one the Naomi is having. Anyway, I think anyone in the future who reads this should at the very least know that. Im sure it has its uses but as far as I can tell you could probably only use it when you are totally done with your work. "Tim Williams" wrote: If your boss has qualms about running an external ultility then you can just perform the required steps manually: export and re-import all of your code modules. I might agree with your boss if the codebase which produced the error had not been modified since the problem was observed: if you made changes which fixed the error then it seem odd to persist in trying to reproduce that particular error.... -- Tim Williams Palo Alto, CA "Naomi Hildebrand" wrote in message news:TC5%f.11633$_T5.798@trndny08... Tim, Thanks. The reassurance about the utility -- and the problem it was designed to fix -- is helpful. I agree that complicated code can sometimes be flaky. If you can't exactly reproduce the input conditions, you can't reproduce the error. The input conditions might have depended on all kinds of things in the outside environment. Earlier in my career I encountered this in complex embedded programs. These are actual subtle coding errors, not errors in the platform. However, I am dealing here with pretty straightforward code. It's not event-driven, it doesn't do any file or database manipuation, it simply looks through the Excel worksheets and verifies that the data passes some simple tests. In an example like this, it's hard to imagine that identical code, run on identical data, would give different answers. To me, it's more reasonable to look at the platform in this case. And no, there are no DLL calls in the program. As I say, it's really very simple code. I completely agree that this code is production worthy. My problem right now is political -- convincing my manager (I'm new to this project, and haven't fully earned my new boss's respect) that the code has been tested enough, and the explanation I'm giving him (basically: Look, this is Windows programming, sh*t happens) is believable. In the programming world he comes from (mainframe & unix), if you run into a problem, you don't release your code until you've reproduced the error, and then fixed whatever caused it. I'm trying to tell him we just can't do that here. (And running a third party utility, where he has no access to its source code, would probably give him a heart attack!) I appreciate the time you're giving me. Thanks. Naomi |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Fortune 40 analyst seeks explanation for Excel/VBA quirk
In my version of the tool (4.4), stripping comments and blank spaces is an option and *unchecked* by default....
-- Tim Williams Palo Alto, CA "Abode" wrote in message ... I downloaded and ran the program on a few of my worksheets. It did reduce the file size by a large ammount but it also removed all comments and indentation so I could no longer easily work with my code. I didn't fix the problem I had which happens to be unrelated to one the Naomi is having. Anyway, I think anyone in the future who reads this should at the very least know that. Im sure it has its uses but as far as I can tell you could probably only use it when you are totally done with your work. "Tim Williams" wrote: If your boss has qualms about running an external ultility then you can just perform the required steps manually: export and re-import all of your code modules. I might agree with your boss if the codebase which produced the error had not been modified since the problem was observed: if you made changes which fixed the error then it seem odd to persist in trying to reproduce that particular error.... -- Tim Williams Palo Alto, CA "Naomi Hildebrand" wrote in message news:TC5%f.11633$_T5.798@trndny08... Tim, Thanks. The reassurance about the utility -- and the problem it was designed to fix -- is helpful. I agree that complicated code can sometimes be flaky. If you can't exactly reproduce the input conditions, you can't reproduce the error. The input conditions might have depended on all kinds of things in the outside environment. Earlier in my career I encountered this in complex embedded programs. These are actual subtle coding errors, not errors in the platform. However, I am dealing here with pretty straightforward code. It's not event-driven, it doesn't do any file or database manipuation, it simply looks through the Excel worksheets and verifies that the data passes some simple tests. In an example like this, it's hard to imagine that identical code, run on identical data, would give different answers. To me, it's more reasonable to look at the platform in this case. And no, there are no DLL calls in the program. As I say, it's really very simple code. I completely agree that this code is production worthy. My problem right now is political -- convincing my manager (I'm new to this project, and haven't fully earned my new boss's respect) that the code has been tested enough, and the explanation I'm giving him (basically: Look, this is Windows programming, sh*t happens) is believable. In the programming world he comes from (mainframe & unix), if you run into a problem, you don't release your code until you've reproduced the error, and then fixed whatever caused it. I'm trying to tell him we just can't do that here. (And running a third party utility, where he has no access to its source code, would probably give him a heart attack!) I appreciate the time you're giving me. Thanks. Naomi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
ANALYST TOOL PACK -- #VALUE! | Excel Worksheet Functions | |||
An Interesting vba Excel quirk | Excel Discussion (Misc queries) | |||
Crime Analyst needs help with co-ordinates | Excel Discussion (Misc queries) | |||
newbie seeks excel help - please!! | New Users to Excel | |||
Installed Analyst Toolpak but not working for files used at work? | Excel Worksheet Functions |