Posted by Sean MacKenzie on Mon, Feb 15, 2010
Spreadsheets are one of the most useful tools that businesses have today. Some people have become so proficient at using them that, as a result they use them all the time, for all kinds of things. This often leads into people using spreadsheets for purposes they weren't designed for. You would not use a spreadsheet when:
It needs to be shared concurrently.
When many others need to have access to view and update it, you need another tool. Excel allows one user at a time. This often leads to many people taking a copy of their own, which leads to a real mess when it all has to come back together.
You are continuously collecting data.
An engineer who has 50,000 rows and growing in an Excel sheet will soon hit the worksheet row limit of 65,536 rows. At that point she would have to figure out a way to continue.
The data needs to be secure.
Excel does not have data security in the same way an enterprise application does, and so can only protect data using some simple mechanisms.
The data needs to validated on entry.
Though it is possible to set up some data entry validation in excel, in practice, most people just pull up a sheet and start punching data into it. This can lead to errors in data-entry, making the data less accurate, and harder to run statistics on.
There is obviously another tool that would do it better.
There are examples of users creating letter templates, trying to maintain multi-user mission critical manufacturing schedules, trying to create graphics files with it, and more.
You're building a "database".
Many IT support staff find this to be one of the biggest misuses of Excel. Usually users spend a lot of time designing and entering data before they find their solution does not work. At this point the sheet is mission critical, so they ask their IT support staff to help them. It takes more time to analyze it, so time is wasted on both sides.
Ask for Help
If you find yourself in a position where you're considering using Excel for a project, consider some of the points above. If you fall into any of these categories, you may want to have a chat with your IT support staff or an IT specialist before continuing. You might find that a small amount of time invested with them up front will save you a large amount of grievance in the long run.
For example, they might be able to set up a simple SQL Server/MS Access database that everyone can use, has unlimited data entry, is more secure, and validates data on entry. As an added bonus, an MS Access front-end could spit out reports in Excel for analysis, a purpose Excel is more suited for.
Many thanks to people who contributed to a discussion on this topic at http://www.linkedin.com/answers/technology/information-technology/computers-software/TCH_ITS_CMP/625070-2600475
Posted by Sean MacKenzie on Tue, Feb 02, 2010
Microsoft discontinued support and enhancement of its Visual Basic 6 platform years ago, instead moving to its new .Net platform for enterprise applications. However, what many people don't know is that a flavour of VB called Visual Basic for Applications, or VBA as it is known, continued on in the Microsoft Office suite as the primary language for things like Excel and Word macros, and automation of Access database applications. It appears that, in spite of the relentless move toward .Net at Microsoft for many years, this technology will continue on and will be supported in Office 2010 (Office 14), and for SME business owners this is a very good thing.
1. Most businesses over 20 employees have invested in VBA-automated things for people "in the trenches", from simple mail merge applications in Word, to data extractions for analysis in Excel, to multi-user MS Access databases. Continuing support for these means that these companies will be able to extend the life of their investment.
2. Support for VBA also means that related technologies like the Jet database engine and Data Access Objects, or DAO can be supported by providers for years to come, on newer, more advanced platforms like Office 2010. For the large number of businesses who wrote entire systems around the MS Access/DAO/ODBC/SQL Server platform, this means they can stay current and be sure to be supported for years to come.
3. VBA with MS Access is regarded by many as the quickest way to prototype a working application for SME businesses due to a data access technology (DAO) tightly knit with the VBA language, a powerful design environment, and the best integration with other technologies in the office environment, like Word and Excel.
Ongoing VBA support means SME businesses can still get the best bang for their buck for years to come.