Introduction
In this Introduction, you will:
Find out what is in this book.
Have a peek at the future of VBA and Windows versions of Excel.
Learn about special elements and typographical conventions in this book.
Find out where to find code files for this book.
As corporate IT departments have found themselves with long backlogs of requests, Excel users have discovered that they can produce the reports needed to run their businesses themselves using the macro language Visual Basic for Applications (VBA). VBA enables you to achieve tremendous efficiencies in your day-to-day use of Excel. VBA helps you figure out how to import data and produce reports in Excel so that you don’t have to wait for the IT department to help you.
Is TypeScript a threat to VBA?
Your first questions are likely: “Should I invest time in learning VBA? How long will Microsoft support VBA? Will the new TypeScript language released for Excel Online replace VBA?”
Your investments in VBA will serve you well until at least 2049.
The last macro language change—from XLM to VBA—happened in 1993. XLM is still supported in Excel to this day. That was a case where VBA was better than XLM, but XLM is still supported 28 years later. Microsoft introduced TypeScript for Excel Online in February 2020. I expect that they will continue to support VBA in the Windows and Mac versions of Excel for the next 28 years.
In the Excel universe today, there are versions of Excel running in Windows, in MacOS, on mobile phones powered by Android and iOS, and in modern browsers using Excel Online. In my world, I use Excel 99% of the time on a Windows computer. There is perhaps 1% of the time where I will open an Excel workbook on an iPad. But, if you are in a mobile environment where you are using Excel in a browser, then the TypeScript UDFs might be appropriate for you.
For an introduction to TypeScript UDFs in Excel, read Suat M. Ozgur’s Excel Custom Functions Straight to the Point (ISBN 978-1-61547-259-8).
However, TypeScript performance is still horrible. If you don’t need your macros to run in Excel Online, the VBA version of your macro will run eight times more quickly than the TypeScript version. For people who plan to run Excel only on the Mac or Windows platforms, VBA will be your go-to macro language for another decade.
The threat to Excel VBA is the new Excel Power Query tools found in the Get & Transform tab of the Data tab in Excel for Windows. If you are writing macros to clean imported data, you should consider cleaning the data once with Power Query and then refreshing the query each day. I have a lot of Power Query workflows set up that would have previously required VBA. For a primer on Power Query, check out Master Your Data with Excel and Power BI: Leveraging Power Query to Get & Transform Your Task Flow by Ken Puls and Miguel Escobar (ISBN 978-1-61547-058-7).
What is in this book?
You have taken the right step by purchasing this book. We can help you reduce the learning curve so that you can write your own VBA macros and put an end to the burden of generating reports manually.
Reducing the learning curve
This Introduction provides a case study about the power of macros. Chapter 1, “Unleashing the power of Excel with VBA,” introduces the tools and confirms what you probably already know: The macro recorder does not work reliably. Chapter 2, “This sounds like BASIC, so why doesn’t it look familiar?,” helps you understand the crazy syntax of VBA. Chapter 3, “Referring to ranges,” cracks the code on how to work efficiently with ranges and cells.
Chapter 4, “Looping and flow control,” covers the power of looping using VBA. The case study in this chapter demonstrates creating a program to produce a department report and then wrapping that report routine in a loop to produce 46 reports.
Chapter 5, “R1C1-style formulas,” covers, obviously, R1C1-style formulas. Chapter 6, “Creating and manipulating names in VBA,” covers names. Chapter 7, “Event programming,” includes some great tricks that use event programming. Chapter 8, “Arrays,” covers arrays. Chapter 9, “Creating classes and collections,” covers classes and collections. Chapter 10, “Userforms: An introduction,” introduces custom dialog boxes that you can use to collect information from a human using Excel.
Excel VBA power
Chapters 11, “Data mining with Advanced Filter,” and 12, “Using VBA to create pivot tables,” provide an in-depth look at Filter, Advanced Filter, and pivot tables. Report automation tools rely heavily on these concepts. Chapters 13, “Excel power,” and 14, “Sample user-defined functions,” include dozens of code samples designed to exhibit the power of Excel VBA and custom functions.
Chapters 15, “Creating charts,” through 20, “Automating Word,” handle charting, data visualizations, web queries, sparklines, and automating Word.
Techie stuff needed to produce applications
Chapter 21, “Using Access as a back end to enhance multiuser access to data,” handles reading and writing to Access databases and SQL Server. The techniques for using Access databases enable you to build an application with the multiuser features of Access while keeping the friendly front end of Excel.
Chapter 22, “Advanced userform techniques,” shows you how to go further with userforms. Chapter 23, “The Windows Application Programming Interface (API),” teaches some tricky ways to achieve tasks using the Windows API. Chapters 24, “Handling errors,” through 26, “Creating Excel add-ins,” deal with error handling, custom menus, and add-ins. Chapter 27, “An introduction to creating Office add-ins,” provides a brief introduction to building your own TypeScript application within Excel. Chapter 28, “What’s new in Excel 365 and what’s changed,” summarizes the changes in Excel 365.
Does this book teach Excel?
Microsoft believes that the ordinary Office customer touches only 10% of the features in Office. We realize that everyone reading this book is above average, and the visitors to MrExcel.com are a pretty smart audience. Even so, a poll of 8,000 MrExcel.com readers showed that only 42% of smarter-than-average users are using any 1 of the top 10 power features in Excel.
Bill regularly presents a Power Excel seminar for accountants. These are hard-core Excelers who use Excel 30 to 40 hours every week. Even so, two things come out in every seminar. First, half of the audience gasps when they see how quickly you can do tasks with a particular feature, such as automatic subtotals or pivot tables. Second, someone in the audience routinely trumps me. For example, someone asks a question, I answer, and someone in the second row raises a hand to give a better answer.
The point? Both the authors and the audience of this book know a lot about Excel. However, we assume that in any given chapter, maybe 58% of the people have not used pivot tables before and maybe even fewer have used the Top 10 Filter feature of pivot tables. With this in mind, before we show how to automate something in VBA, we briefly cover how to do the same task in the Excel interface. This book does not teach you how to make pivot tables, but it does alert you when you might need to explore a topic and learn more about it elsewhere.
Versions of Excel
This seventh edition of VBA and Macros is designed to work with Microsoft 365 features released up through August 2021. The previous editions of this book covered code for Excel 97 through Excel 2019. In 80% of the chapters, the code today is identical to the code in previous versions.
Differences for Mac users
Although Excel for Windows and Excel for the Mac are similar in terms of user interface, there are a number of differences when you compare the VBA environment. Certainly, nothing in Chapter 23 that uses the Windows API will work on the Mac. That said, the overall concepts discussed in this book apply to the Mac. You can find a general list of differences as they apply to the Mac at http://www.mrexcel.com/macvba.html. The VBA Editor for the Mac does not let you design UserForms (Chapter 10). It also has a bug that makes it difficult to create event handler macros (Chapter 7). Excel throws an error when you try to select from the drop-downs at the top of the Code window. You have to first copy and paste an empty event procedure; then the drop-downs will work.
Special elements and typographical conventions
The following typographical conventions are used in this book:
Italic—Indicates new terms when they are defined, special emphasis, non-English words or phrases, and letters or words used as words.
Monospace—Indicates parts of VBA code, such as object or method names.Bold monospace—Indicates user input.
In addition to these typographical conventions, there are several special elements. Each chapter has at least one case study that presents a real-world solution to common problems. The case study also demonstrates practical applications of topics discussed in the chapter.
In addition to the case studies, you will see Notes, Tips, and Cautions.
Note
Notes provide additional information outside the main thread of the chapter discussion that might be useful for you to know.
Tip
Tips provide quick workarounds and time-saving techniques to help you work more efficiently.
Caution
Cautions warn about potential pitfalls you might encounter. Pay attention to the Cautions; they alert you to problems that might otherwise cause you hours of frustration.
About the companion content
As a thank-you for buying this book, we have put together a set of 50 Excel workbooks that demonstrate the concepts included in this book. This set of files includes all the code from the book, sample data, and additional notes from the authors.
To download the code files, visit this book’s webpage at MicrosoftPressStore.com/ExcelVBAMacros365/downloads.
Errata, updates, and book support
We’ve made every effort to ensure the accuracy of this book and its companion content. Any errors that have been reported since this book was published are listed at
MicrosoftPressStore.com/ExcelVBAMacros365/errata.
If you find an error that is not already listed, you can report it to us through the same page.
For additional book support and information, please visit MicrosoftPressStore.com/Support.
Please note that product support for Microsoft software and hardware is not offered through the previous addresses. For help with Microsoft software or hardware, go to http://support.microsoft.com.
Stay in touch
Let’s keep the conversation going! We’re on Twitter: