Sometimes Excel is not an appropriate solution. Well, at least it's not appropriate as the only component of your solution. Many times, I see Excel used as a database. There is a fine line when it comes to using Excel as a database. Using the term database to describe the use of Excel in this manner is, in all fairness, an injustice to real database products. A better way to think of Excel's capabilities in this regard is that it functions as a list manager.
If your list is small, maybe a couple thousand rows at most, Excel may be adequate for your needs. However, I would urge you to consider using a database to handle the storage of your data. You can still use Excel to analyze and display your data; you'll learn techniques to do this later in the book.
If your list is larger, you really should use a database. Microsoft Access is a good choice because it is probably already installed on your computer if you have Excel, and it contains many features that help beginners learn the basics of using a database.
Making the call as to when to use a database comes with experience. Generally however, when you find yourself writing a lot of code to manage or otherwise work with a list in Excel, a big red light should come on in your head. You should stop immediately and do the following three things. First, import all of your data into a table in Access. Second, investigate the capabilities of Microsoft Query for bringing necessary data from your database into Excel for analytical or reporting purposes. Third, check out Chapter 16 to learn how to programmatically retrieve data from a database and use the data in your spreadsheet.
By combining Access (or another database product) and Excel, you will significantly expand your capabilities to develop capable and sophisticated systems that can handle large amounts of data.
With time, the following statement will become clear, but anytime you are writing a lot of code to deal with one particular facet of an application, red flags should appear in your mind. You'll know it when this happens to you—your code will seem awkward and complex. This is not right. Stop, take a breather, and reevaluate. Evaluate three things: your design, the technology you used to develop a solution, and the application of the technology you choose. One or more of these is wrong. You may find some advice regarding your problem in Chapter 13.
Was this article helpful?