Excel VBA 24-Hour Trainer. Tom Urtis

Чтение книги онлайн.

      So VBA is a programming language, and it is also a macro language. Confusion of terminology arises when referring to VBA code that is a series of commands written and executed in Excel. Is it a macro, a procedure, or a program? Microsoft commonly refers to its VBA procedures as macros, so that's good enough for me to call them macros also. Outside of a few exceptions that I explain when the time comes, I refer to VBA procedures as macros.

      A Brief History of VBA

      VBA is a present-day dialect of the BASIC (Beginner's All-purpose Symbolic Instruction Code) programming language that was developed in the 1960s. BASIC became widely used in many software applications throughout the next two decades because it was easy to learn and understand.

      Over the years, BASIC has evolved and improved in response to advancing technology and increased demands by its users for greater programming flexibility. In 1985, Microsoft released a much richer version of BASIC, named QuickBASIC, which boasted the most up-to-date features found in programming languages of the day. In 1992, Microsoft released Visual Basic for Windows, designed to work within the burgeoning Windows environment.

      Meanwhile, 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.

      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.

СКАЧАТЬ