Introduction

What is VBA?
VBA is a programming language created by Microsoft to automate operations in Excel. In addition to Excel, VBA can also manipulate other Microsoft Office applications such as Access, Word, PowerPoint, and Outlook. It is an extremely powerful tool that allows you to control many methods in Excel that you cannot or do not want to do manually. VBA is the tool you use to develop macros and manipulate objects to control Excel and other Office applications from within Excel. You do not need to purchase anything other than the Office suite to also have VBA. If you have Excel on your computer, then you have VBA on your computer.

What is a “macro”?
VBA is therefore a programming language, and it is also a macro language. Terminology confusion arises when referring to VBA code, which is a series of commands written and executed in Excel. So, what is a macro? A macro can be described as a sequence of instructions written in the VBA language that are stored in a module. When we call a macro, by pressing a button or using a key combination, the instructions stored in it are triggered.
With macros, we can not only streamline tasks that we perform frequently, but we can also extend Excel’s functionality by creating new functions to solve calculations that cannot be performed with the program’s standard functions.
There are two ways to create a macro. One uses the Record Macro tool, and the other involves writing the instructions directly in the Visual Basic Editor built into Excel. Since Excel 2007, Microsoft has distinguished between saving a workbook with macros (file extension .xlsm) and a workbook without macros (file extension .xlsx).

There is a big difference between VB and VBA!
With all the acronyms circulating in the world of computing, it’s easy to confuse certain terms. VB means Visual Basic, and it is not the same as VBA.
Although both VB and VBA are programming languages derived from BASIC and created by Microsoft, they are otherwise very different.
VB is a language that allows you to create executable, standalone applications that do not even require users to have Office or Excel installed on their computers.
VBA, on the other hand, cannot create standalone applications. It can only exist within a host application such as Excel and the workbook that contains the VBA code. For a VBA macro to run, its host application’s workbook must be open.
This book is based on VBA and how it controls Excel.

What can you do with VBA?
Everyone reading this book uses Excel for their own purposes, such as financial budgeting, forecasting, scientific data analysis, creating invoices, or tracking the progress of their favorite soccer team. One thing all readers have in common is the need to automate some frequently encountered task that takes too much time or is too cumbersome manually. This is where VBA comes into play.
Many VBA commands are at your disposal and are relatively easy to implement and customize for your daily needs.
Anything you can do manually, you can do with VBA — but VBA allows you to do it faster and with a reduced risk of human error.
Many things that Excel doesn’t allow you to do manually can be done using VBA. Here are a few examples of what VBA can do for you:

• Automate a recurring task: If you need to produce weekly or monthly sales and expense reports, a macro can create them in no time. And if the source data changes later in the day and you need to generate the updated report again — no problem, just rerun the macro!
• Automate a repetitive task: When you need to perform the same task on every worksheet in your workbook or in every workbook in a particular folder, you can create a macro to loop through each object and perform the action.
• Automatically run a macro when another action occurs: In some cases, you want a macro to run automatically so you don’t have to remember to run it yourself. For example, to automatically refresh a PivotTable when its source data changes, you can monitor those changes with VBA, ensuring your PivotTable always displays real-time results. This is called event-driven programming.
• Create your own worksheet functions: You can create your own worksheet functions, called User Defined Functions (UDFs), to handle custom calculations that Excel’s built-in functions do not support.
• Create full-scale applications driven by macros:
  If you’re willing to invest the time, you can use VBA to create large-scale applications with a custom Ribbon tab, dialog boxes, screen tips, and many other features.
• Create custom Excel add-ins:
  You’re probably familiar with some of the add-ins that come with Excel — for example, the Analysis ToolPak is a popular one. You can use VBA to develop your own add-ins for specific purposes.