The Excel® Analyst's Guide to Access®

Chapter 10. Leveraging VBA to Enhance Data Analysis

Many Access users are not programmers, and it would be fair to say that most do not aspire to be programmers. In fact, most of you are just trying to survive the projects you are juggling now; who has the time to learn VBA?

If you are tempted to take a polite look at this chapter and then move on, you should definitely fight that urge. Why? Because leveraging VBA (Visual Basic for Applications) in your analytical processes can make your life easier in the long run. VBA can help you do things faster and more efficiently. In fact, just a few lines of code can save you hours of work, freeing you up to do other things, and increasing your productivity. Consider some of the advantages that VBA offers:

• VBA can help you automate redundant analyses and recurring analytical processes, leaving you free to work on other tasks.

• VBA allows you to process data without the need to create and maintain queries and macros.

• With VBA, you can automate external programs such as Excel to expand the reporting capabilities.

• With VBA, you can perform complex, multi-layered procedures that involve looping, record-level testing, and If...Then...ElseIf statements.

• You can tailor your own error-handling procedures using VBA, allowing you to anticipate and plan for process flow changes in the event of an error.

This chapter covers some fundamental concepts and techniques that will lay the groundwork for your own ideas about how to enhance your analytical processes ...