To some extent, I agree with Ken, such as VBA isn't ran automatically unless it's tied to some event like the change event. But here's why I stated as I did:
If the results are formula based and the Application Calculation mode is set to Automatic:
Each and every time the user makes any sort of a change to any open workbook within the instance of Excel that has the Auto-Calc mode, calculation will take place after each and every change. Normally, you may think, 2 or 3 seconds isn't very much, but if you are doing data entry directly in the worksheet, let's say 100 total cell entry, that 2 seconds per entry has just become a 200 second calculation time, which is 3 minutes and 20 seconds.
Of course, at that point, you could either change the Calculation mode to Manual or use VBA resolution, but either way, still similar type results.
Now when you do that same data entry, it may take only 1 minute and 30 seconds, which then you either calculate the formula based, or run the VBA code.
Issues that I have with formula based.
F9 function key doesn't always calculate everything in all open workbooks within that instance of Excel like it is suppose to, thus has proven to be unreliable. Only way to get around this is to use the Shift-F9 on a worksheet by worksheet basis. Would you like to do that by hand, or would you rather let code handle that?
Most of your Excel users are your standard non-power users. If they are going to be entering the information into the workbook, they may call back and ask why isn't it working or why are their other workbooks not calculating like they are expecting them to, so from a practical stand point of view, this only adds to the reason why I don't use complex formula based results when expecting other more common Excel users to be working with the workbook.
I do have s lot of things going on in Excel, of which currently, I'm in the process of seeing about getting that information switched over from Excel to Access given the various complexities involved, and Excel is not meant to handle the type of processes and data storage that I been having it do. Initially, we had a manufacturing DB system, but given it's various issues and it's high cost to renew, we just didn't renew, and I had to create at a bare minimal, a temp program that would capture all of the information.
Yes, even my production reports are still in Excel. Main thing about it though, I have it all setup via code to be ran on the click of a command toolbar button. Granted, this is pushing the limitations of Excel with regards to how it gathers data (through user form and stores on the local workbooks which are saved on the network file server), saves and processes the information (a separate file which is ran on my system at the bare minimal, once a week). Each individual machine center workbook retains it's records for a period of 45 days, and my summary files archives the most detail level of each type of mode and reason code by day and shift (each row contains the date, shift, and how much total time for that shift on that date in setup, setup problem, setup idle, run, run problem, run idle, and how much total time for each of the different reasons why for the problem/idle times.
Something like this is really more so meant in a DB program, but at the time, I only had 3 weeks to come up with a temp program, got it into full test mode within 2 weeks, and I knew I didn't have time to learn all of the objects in VBA coding for Access given the time critical issue. Tell you the truth, I did that cause I was not about to get stuck doing nothing but validating data that they provide on paper and data entry all day long. After my previous experience with the extensive data entry that I had done working at the IRS, I don't want to go back to those days and possibly have to deal with CTS (Carpal Tunnel Syndrome). After all, not only do I have to use common sense, but I also have to listen to my body. The other reason why I did it, it's been proven over and over that live time reporting is a very good majority of the time much more accurate than keeping paper logs for 2 reasons, one it knocks out a lot of the manual tasks that would have to be done manually when doing paper logs (I.e. calculations or recording of start and end times), and 2, it also reduces the human error factor rather significantly.
Ronald R. Dodge, Jr.
Production Statistician
Master MOUS 2000
