Comment Evolution of Excel Work (Score 1) 318
When I first started working with spreadsheets back in 1988 on SuperCalc, I only has basic knowledge of computers then. In 1989, that knowledge was then transferred to Lotus 123 V2.3, which I had a summer employment while still in high school that involved using the spreadsheet program. Namely, the reason why I got the employment is simple. My level of skills to pick up programming terminology was very quick, which I also worked with BASIC in 1984, which my teacher had me type in the program into BASIC character for character and run the program for spelling lessons back in 7th grade.
In my college years, I also had employment, which do you know how many things people did by hand, which I thought was rather tedious and time consuming to do by hand, thus I turned to the computer to do the work for me? Started out with me working in Lotus 123, v3.1 for payroll stuff. I then went to work at the university and did athletic stuff in Excel 5.0. However, as the funds got tight cause I was only working part time while going to school full time, I ended up having to find other employment, which was a very rough part of my life. Eventually cause of my Accounting education, I started working through Accountemps, which I helped very places get more things done on the computer. I then landed a permanent job, which I still work for the company despite the division has been sold 2 different types since my employment with the company 6 years ago.
I started out doing a lot of things in Excel 97, which when I was working in Excel 97, I hated it. Even to this day, I still think Lotus 123 v 2.3 is better than Excel 97 cause Excel 97 is so unstable and unreliable. Anyhow, after I got a lot of smaller tasks done so fast, my boss handed me a job that he thought was going to take me 3 days to get done, but I got it done in 3 hours just by using my comptuer skills with Excel 97.
Well after that, I was then moved from Accounting and out to Production. At that point of time, they had 2 people doing their production reports, one person gathered the information and typed it in, and another person, who initially had set it up, worked the information through the system. Tell you the truth, what they had was a basic framework of the production reporting system, but lacked so much of the automations. It was like building a house without running water, so you would have to go out to the well and fetch your water by hand.
I told my boss, give me 2 weeks to get it more automated, as I'm for one not withstanding to do things inefficiently. I got the automations fully in place within 2 weeks time, for what we had at that time. We still had to do paper logs and input the data by hand, but at least, we didn't have to do any of the calculations or copying and pasting by hand anymore.
However, the system took 35 to 40 minutes just for all of the calculations to take place within Excel 97. This was a vast improvement cause it now meant that it only took 1 person up to a total of 5 hours to do the work, but I still wasn't pleased with the results cause for what I had in place, I knew it shouldn't had taken that long to calculate, even on a P2, 266MHz system. That led me to think of Excel doing redundant calculations, which then at that time, I knew about macros, but never had worked with macros up to that point of time. I also knew, in order to speed it up by knocking out the redundant calculations, I would have to take off the automatic calculations, but I also didn't want to do it by hand either, thus macros was the only thing that would fit the job.
Well I started messing around with the macro stuff and once I got to learn the VBA environment, I had created a relatively simple macro that opened and closed each workbook in their respective order, and ran the calculations via code using the Worksheet.Calculate Method. Once, I setup the code, and ran it, it only took the program 3 to 5 minutes to run all of the calculations and charts.
Well while I got the later part done, the IT department was working on getting a manufacturing DB program in place. Well they got SynQuest, which for the most part work decent, but there were some major issues with it. I pointed out one of the major issues to one of the programmers of SynQuest. That person looked at me and basically said, "That would take too much work." The very first thing that came to my mind was, "He must think I'm ignorant about programming." That's cause within the program, they already had all of the data codes that they needed to rectify the issue, just needed to modify the script somewhat.
I had therefore created a work around to the issue, though be it a backdoor way, which was rather inefficient and still posed some possible issues, but it did at least make the data a lot more accurate. Over the following 1.5 years, one by one, each plant stopped using the program due to the various issues with it, which the plant I work at was the last plant to drop it, but it was due to the cost factor. For just one plant to use it, it wasn't worth the huge cost.
I had gotten this warning of the program being dropped just 3 weeks prior to the date of it being dropped, and at that point, I was like, "Do you think you going to stick me to do all of that data entry by hand again?" That's cause after the experience that I had working at the IRS doing nothing but data entry, CTS (Carpal Tunnel Syndrome) came into factor and the manager at the IRS said, "Oh, that's only muscle soreness.", insulting my cross country running experience.
While at Chesapeake, soon to be sold to CorrFlex, I had 3 weeks to create a workable solution. I would have rather do it in Access, but from a programming stand point of view, my knowledge of Access was rather limited as opposed to Excel, which I knew inside out along with the various issues. Given the limited time, I had no real choice, but to start working on a solution in Excel, which I told my boss at that time, it will only be meant to be an intermediate solution, but it's better than the alternative cause even our main DB program is so text based, issues with security, with it text based, not so user friendly not to mention that it still takes manaual tasks before putting information into the system, and requires so many screens to go into.
I therefore, created an Excel version of the production reporting system, got it setup to report into our main DB program, though be it, having to use the SendKeys Statement, which btw, teh SendKeys Method DOES NOT work properly in Excel 97, 2000, and XP(2002). I can't speak for Excel 2003 in this regards. Even to this day, we are still using my intermediate program as we are now owned by Sonoco.
Anyhow, basically what transpired as to why Excel was used as the base language, despite it's various issues and limitations, as follows:
Level of knowledge
Easy to develop on a quick and dirty type of scheme (this is relatively speaking)
So many systems already have Excel on it, thus no real additional cost other than regular maintenance, but the cost saved in this case is 6 digit figures per year as opposed to having the manufacturing DB program.
It's the platform used for reports as report files gets sent via email to various people.
What's already in place when having to make adjustments due to changes in the workforce.
However, given the various issues, it comes with a few cautions too.
Can't open the same file multiple times, as only one copy of the file will save to the network, which is also why I had to setup one system to act as the server side to collect all of the information from each individual machine center file and report the data to the main DB program.
Just as the case is with real time reporting, operators do have to report on a timely basis, which doesn't always happen cause their first and primary job is to take care of their machine and the job at the machine. For this and along with the fact that we are all humans, thus we tend to make mistakes from time to time, there needs to be one person to monitor the data, which is what I do, as I'm held responsible for the accuracy of the reports to the extent reasonably possible.
Can only stored detailed data for so long without slowing down the system, but since the main DB program has the same information though in slightly different format (Some might say more than slightly cause of it being in tables for the DB file as opposed to worksheets, thus data format in that regards are different, but from the perspective of visibility for datatype, it's about the same.), if we need to retrieve data that dates further back, we can go to the main DB program.
Excel 2002 still has a tendency of crashing, but not as bad as previous versions, though Excel 2000 is the first real workable version in my opinion as it resolved a lot of the technical issues that I had including but not limited to the fact that the legend in Excel 97 would move to the top center position of the chart regardless what you had set for the legend, and couldn't use a range greater than 25 cells to divide the sum of one range by the some of another range as it would return the result of "1/0", if you did within Excel 97. Have to keep within an 80MB RAM usage limitation for Excel 97/2000 and 160MB RAM usage limitation for Excel 2002 else Excel would more than likely crash on you regardless how much RAM you have on your system, even if your system has 3.0GB of RAM.
I have looked into Access and I still think we can use Access as a more long term solution, but due to the various issues that I have ran into Access, which makes it very mouse user UNFRIENDLY to use bound forms and controls, I have had to create my own set of error checking codes and unbound every single form and control, which then presented some other issues, some of which I even had to write my own code to rectify such as the listbox extension for multiple selections, which by the way, I detected a bug within Access 2002 dealing specifically with the list box and it's selection process using the keyboard method and wasn't able to rectify it within my own scripted code, so I had to document it and also make a note of what needs to be done by the user when it happens. It's only been of recent that I have finally been able to switch gears from setting up base DB codes for a workable and functional DB program that would work not only for keyboard users, but also for mouse users (majority of my users are mouse users), to actually working on setting up how the data will be entered, processed, and stored within Access.
So the basic point here is that while Excel may not be the preferred choice for the long-term and large scale projects, it at least can serve as a workable tool in the short-term as you can develop things on a quick and dirty type of environment to see if a more long-term solution would be workable or not. Excel in this case would be more or less like a preview of what the long-term solution would be like. However, Excel is also used for report formats as you can manipulate numbers and create charts so easy as opposed to something like Word, Access, or Power Point. There are a few chart issues within the programming environment of Excel, but I will leave it to what I had said in this message.
I even have been doing my own personal financial stuff within Excel, but I am also in the process of creating an Access version. However, given Access isn't nearly as intuitive nor does it allow for quick and easy setup given it's various issues, it's taking time to set up all of the stuff. For the time period, I will continue to do stuff in Excel until such a time proves that I have a workable DB file, which I do want to get to cause the DB file is more preferred given the data management capacities and can use SQL for what you need, which I do know how to code in SQL too. While Excel may not be the greatest program for long-term solutions, without this program, I would not have been able to increase the family's long-term networth by 5 digits not even counting the contributions to retirement, which for 2004, total contributions was effectively 24% of gross income when you convert $2,000 ROTH IRA contribution for my wife from the after tax to before tax basis using the marginal tax rate of 20.2% (15% for federal and 5.2% for state), and AGI was still low enough for me to use the QRS Credit. Family size is 6 members. The IRS effectively funded the IRA account via the QRS credit ($200 of this was a direct impact cause refund would have been reduced by $200 otherwise), and the remaining amount from the ACTC. So you see, there are reasons to use Excel, at least on a short term solution, even if it's not the preferred choice for the long-term.
In my college years, I also had employment, which do you know how many things people did by hand, which I thought was rather tedious and time consuming to do by hand, thus I turned to the computer to do the work for me? Started out with me working in Lotus 123, v3.1 for payroll stuff. I then went to work at the university and did athletic stuff in Excel 5.0. However, as the funds got tight cause I was only working part time while going to school full time, I ended up having to find other employment, which was a very rough part of my life. Eventually cause of my Accounting education, I started working through Accountemps, which I helped very places get more things done on the computer. I then landed a permanent job, which I still work for the company despite the division has been sold 2 different types since my employment with the company 6 years ago.
I started out doing a lot of things in Excel 97, which when I was working in Excel 97, I hated it. Even to this day, I still think Lotus 123 v 2.3 is better than Excel 97 cause Excel 97 is so unstable and unreliable. Anyhow, after I got a lot of smaller tasks done so fast, my boss handed me a job that he thought was going to take me 3 days to get done, but I got it done in 3 hours just by using my comptuer skills with Excel 97.
Well after that, I was then moved from Accounting and out to Production. At that point of time, they had 2 people doing their production reports, one person gathered the information and typed it in, and another person, who initially had set it up, worked the information through the system. Tell you the truth, what they had was a basic framework of the production reporting system, but lacked so much of the automations. It was like building a house without running water, so you would have to go out to the well and fetch your water by hand.
I told my boss, give me 2 weeks to get it more automated, as I'm for one not withstanding to do things inefficiently. I got the automations fully in place within 2 weeks time, for what we had at that time. We still had to do paper logs and input the data by hand, but at least, we didn't have to do any of the calculations or copying and pasting by hand anymore.
However, the system took 35 to 40 minutes just for all of the calculations to take place within Excel 97. This was a vast improvement cause it now meant that it only took 1 person up to a total of 5 hours to do the work, but I still wasn't pleased with the results cause for what I had in place, I knew it shouldn't had taken that long to calculate, even on a P2, 266MHz system. That led me to think of Excel doing redundant calculations, which then at that time, I knew about macros, but never had worked with macros up to that point of time. I also knew, in order to speed it up by knocking out the redundant calculations, I would have to take off the automatic calculations, but I also didn't want to do it by hand either, thus macros was the only thing that would fit the job.
Well I started messing around with the macro stuff and once I got to learn the VBA environment, I had created a relatively simple macro that opened and closed each workbook in their respective order, and ran the calculations via code using the Worksheet.Calculate Method. Once, I setup the code, and ran it, it only took the program 3 to 5 minutes to run all of the calculations and charts.
Well while I got the later part done, the IT department was working on getting a manufacturing DB program in place. Well they got SynQuest, which for the most part work decent, but there were some major issues with it. I pointed out one of the major issues to one of the programmers of SynQuest. That person looked at me and basically said, "That would take too much work." The very first thing that came to my mind was, "He must think I'm ignorant about programming." That's cause within the program, they already had all of the data codes that they needed to rectify the issue, just needed to modify the script somewhat.
I had therefore created a work around to the issue, though be it a backdoor way, which was rather inefficient and still posed some possible issues, but it did at least make the data a lot more accurate. Over the following 1.5 years, one by one, each plant stopped using the program due to the various issues with it, which the plant I work at was the last plant to drop it, but it was due to the cost factor. For just one plant to use it, it wasn't worth the huge cost.
I had gotten this warning of the program being dropped just 3 weeks prior to the date of it being dropped, and at that point, I was like, "Do you think you going to stick me to do all of that data entry by hand again?" That's cause after the experience that I had working at the IRS doing nothing but data entry, CTS (Carpal Tunnel Syndrome) came into factor and the manager at the IRS said, "Oh, that's only muscle soreness.", insulting my cross country running experience.
While at Chesapeake, soon to be sold to CorrFlex, I had 3 weeks to create a workable solution. I would have rather do it in Access, but from a programming stand point of view, my knowledge of Access was rather limited as opposed to Excel, which I knew inside out along with the various issues. Given the limited time, I had no real choice, but to start working on a solution in Excel, which I told my boss at that time, it will only be meant to be an intermediate solution, but it's better than the alternative cause even our main DB program is so text based, issues with security, with it text based, not so user friendly not to mention that it still takes manaual tasks before putting information into the system, and requires so many screens to go into.
I therefore, created an Excel version of the production reporting system, got it setup to report into our main DB program, though be it, having to use the SendKeys Statement, which btw, teh SendKeys Method DOES NOT work properly in Excel 97, 2000, and XP(2002). I can't speak for Excel 2003 in this regards. Even to this day, we are still using my intermediate program as we are now owned by Sonoco.
Anyhow, basically what transpired as to why Excel was used as the base language, despite it's various issues and limitations, as follows:
Level of knowledge
Easy to develop on a quick and dirty type of scheme (this is relatively speaking)
So many systems already have Excel on it, thus no real additional cost other than regular maintenance, but the cost saved in this case is 6 digit figures per year as opposed to having the manufacturing DB program.
It's the platform used for reports as report files gets sent via email to various people.
What's already in place when having to make adjustments due to changes in the workforce.
However, given the various issues, it comes with a few cautions too.
Can't open the same file multiple times, as only one copy of the file will save to the network, which is also why I had to setup one system to act as the server side to collect all of the information from each individual machine center file and report the data to the main DB program.
Just as the case is with real time reporting, operators do have to report on a timely basis, which doesn't always happen cause their first and primary job is to take care of their machine and the job at the machine. For this and along with the fact that we are all humans, thus we tend to make mistakes from time to time, there needs to be one person to monitor the data, which is what I do, as I'm held responsible for the accuracy of the reports to the extent reasonably possible.
Can only stored detailed data for so long without slowing down the system, but since the main DB program has the same information though in slightly different format (Some might say more than slightly cause of it being in tables for the DB file as opposed to worksheets, thus data format in that regards are different, but from the perspective of visibility for datatype, it's about the same.), if we need to retrieve data that dates further back, we can go to the main DB program.
Excel 2002 still has a tendency of crashing, but not as bad as previous versions, though Excel 2000 is the first real workable version in my opinion as it resolved a lot of the technical issues that I had including but not limited to the fact that the legend in Excel 97 would move to the top center position of the chart regardless what you had set for the legend, and couldn't use a range greater than 25 cells to divide the sum of one range by the some of another range as it would return the result of "1/0", if you did within Excel 97. Have to keep within an 80MB RAM usage limitation for Excel 97/2000 and 160MB RAM usage limitation for Excel 2002 else Excel would more than likely crash on you regardless how much RAM you have on your system, even if your system has 3.0GB of RAM.
I have looked into Access and I still think we can use Access as a more long term solution, but due to the various issues that I have ran into Access, which makes it very mouse user UNFRIENDLY to use bound forms and controls, I have had to create my own set of error checking codes and unbound every single form and control, which then presented some other issues, some of which I even had to write my own code to rectify such as the listbox extension for multiple selections, which by the way, I detected a bug within Access 2002 dealing specifically with the list box and it's selection process using the keyboard method and wasn't able to rectify it within my own scripted code, so I had to document it and also make a note of what needs to be done by the user when it happens. It's only been of recent that I have finally been able to switch gears from setting up base DB codes for a workable and functional DB program that would work not only for keyboard users, but also for mouse users (majority of my users are mouse users), to actually working on setting up how the data will be entered, processed, and stored within Access.
So the basic point here is that while Excel may not be the preferred choice for the long-term and large scale projects, it at least can serve as a workable tool in the short-term as you can develop things on a quick and dirty type of environment to see if a more long-term solution would be workable or not. Excel in this case would be more or less like a preview of what the long-term solution would be like. However, Excel is also used for report formats as you can manipulate numbers and create charts so easy as opposed to something like Word, Access, or Power Point. There are a few chart issues within the programming environment of Excel, but I will leave it to what I had said in this message.
I even have been doing my own personal financial stuff within Excel, but I am also in the process of creating an Access version. However, given Access isn't nearly as intuitive nor does it allow for quick and easy setup given it's various issues, it's taking time to set up all of the stuff. For the time period, I will continue to do stuff in Excel until such a time proves that I have a workable DB file, which I do want to get to cause the DB file is more preferred given the data management capacities and can use SQL for what you need, which I do know how to code in SQL too. While Excel may not be the greatest program for long-term solutions, without this program, I would not have been able to increase the family's long-term networth by 5 digits not even counting the contributions to retirement, which for 2004, total contributions was effectively 24% of gross income when you convert $2,000 ROTH IRA contribution for my wife from the after tax to before tax basis using the marginal tax rate of 20.2% (15% for federal and 5.2% for state), and AGI was still low enough for me to use the QRS Credit. Family size is 6 members. The IRS effectively funded the IRA account via the QRS credit ($200 of this was a direct impact cause refund would have been reduced by $200 otherwise), and the remaining amount from the ACTC. So you see, there are reasons to use Excel, at least on a short term solution, even if it's not the preferred choice for the long-term.
