Excel VBA 24-Hour Trainer. Tom Urtis

various software publishers were making their own enhancements to BASIC for their products' programming languages, resulting in a wide and confusing range of functionality and commands among software applications that were using BASIC. Microsoft recognized the need for developing a standardized programming language for its software products, and created Visual Basic for Applications.

      VBA was first released by Microsoft with Excel 5 in the Office 1995 suite. Since then, VBA has become the programming language for Microsoft's other popular Office applications, as well as for external software customers of Microsoft to whom VBA has been licensed for use.

      THERE'S A BIG DIFFERENCE BETWEEN VB AND VBA!

      With all the acronyms bandied about in the world of computing, it's easy to get some terms confused. VB stands for Visual Basic, and it is not the same as VBA. Though both VB and VBA are programming languages derived from BASIC and created by Microsoft, they are otherwise very different.

      VB is a language that enables you to create standalone executable applications that do not even require its users to have Office or Excel loaded onto their computers. VBA cannot create standalone applications, and it exists within a host application such as Excel and the workbook containing the VBA code. For a VBA macro to run, its host application workbook must be open. This book is about VBA and how it controls Excel.

      What VBA Can Do for You

      Everyone reading this book uses Excel for their own needs, such as financial budgeting, forecasting, analyzing scientific data, creating invoices, or charting the progress of their favorite football team. One thing all readers have in common is the need to automate some kind of frequently encountered task that is either too time-consuming or too cumbersome to continue doing manually. That's where VBA comes in.

      The good news is that utilizing VBA does not mandate that you first become a world-class professional programmer. Many VBA commands are at your disposal, and are relatively easy to implement and customize for your everyday purposes.

      Anything you can do manually you can do with VBA, but VBA enables you to do it faster and with a minimized risk of human error. Many things that Excel does not allow you to do manually, you can do with VBA. The following sections describe a handful of examples of what VBA can do for you.

      Automating a Recurring Task

      If you find yourself needing to produce weekly or monthly sales and expense reports, a macro can create them in no time flat, in a style and format you (and more importantly, your boss) will be thrilled with. And if the source data changes later that day and you need to produce the updated report again, no problem – just run the macro again!

      Automating a Repetitive Task

When faced with needing to perform the same task on every worksheet in your workbook, or in every workbook in a particular file folder, you can create a macro to “loop” through each object and do the deed. You find out how to repeat actions with various looping methods in Lesson 10. Figure 1.1 shows an example of worksheets that were sorted in alphabetical order by a macro that looped through each tab name, repositioning each sheet in the process.

Figure 1.1

      Running a Macro Automatically if Another Action Takes Place

      In some situations, you want a macro to run automatically so you don't have to worry about remembering to run it yourself. For example, to automatically refresh a pivot table the moment its source data changes, you can monitor those changes with VBA, ensuring that your pivot table always displays real-time results. This is called “event” programming, which is cool stuff, and is discussed in Lessons 13 and 14.

An event can also be triggered and programmed anytime a cell or range of cells is selected. A common request I've received from Excel users is to highlight the active cell, or the row and column belonging to the active cell, automatically when a cell is selected. Figure 1.2 shows three options to easily locate your active cell as you traverse your worksheet.

Figure 1.2

      Creating Your Own Worksheet Functions

      You can create your own worksheet functions, known as user-defined functions, to handle custom calculations that Excel's built-in functions do not provide, or would be too complicated to use even if such native functions were available. For example, later in the book you see how to add up numbers in cells that are formatted a certain color. UDFs, as these custom functions are called, are covered in Lesson 19, “User-Defined Functions.”

      Simplifying the Workbook's Look and Feel for Other Users

When you create a workbook for others to use, there will inevitably be users who know little to nothing about Excel, but who will still need to work in that file. You can build a customized interface with user-friendly menus and informational pop-up boxes to guide your novice users throughout their activities in the workbook. You might be surprised at how un-Excel-looking an Excel workbook can be, with VBA providing a visually comfortable and interactive experience for users unfamiliar with Excel, enabling them to get their work done. Figure 1.3 shows an example of accomplishing this with UserForms, which are discussed in Lessons 21, 22, and 23.

Figure 1.3

      Controlling Other Office Applications from Excel

      If you create narrative reports in Word that require an embedded list of data from Excel, or if you need to import a table from Access into an Excel worksheet, VBA can automate the process. VBA is the programming language for Microsoft's other Office applications, enabling you to write macros in Excel to perform tasks in those other applications, with the users being none the wiser that they ever left Excel while the macro was running.

      As you might imagine, the list of advantages to using VBA could fill the capacity of your average flash drive. The point is, you are sure to have tasks in your everyday dealings with Excel that can be accomplished more quickly and efficiently with VBA, and this book shows you how.

      Liabilities of VBA

      Although VBA is a tremendously useful and versatile tool, it is not a 100 percent perfect programming language – but then, no programming language anywhere can truthfully claim infallibility. The pros of VBA far outweigh its cons, but learning and using VBA does come with a few objective caveats that you should be aware of:

      • With each version release of Excel, Microsoft may add new VBA commands or stop supporting existing VBA commands, sometimes without advance warning. Surprises do happen, as was especially the case when Office 2007 was released with all its added features. Such is life in the world of Excel VBA. You will probably learn of coding errors from people who have upgraded to a newer version and are using the workbook you created in an earlier version.

      • VBA does not run uniformly in all computer operating environments. Sometimes, no matter how extensively you test your code and how flawlessly the macros run on your computer as you develop a project, there will be users of your workbook who will eventually report an error in your code. It won't be your fault or VBA's fault, it's just the idiosyncrasies of how programming languages such as VBA mix with various operating systems, Office versions, and network configurations. Debugging your code is the subject of Lesson 20.

      • Programming