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 JavaScript 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 JavaScript language announced in May 2018 replace VBA?˝
Your investments in VBA will serve you well until at least 2046.
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 26 years later. If Microsoft ever switches from VBA to JavaScript, I expect that they will continue to support VBA in the Windows and Mac versions of Excel for the next 26 years.
In May 2018, Microsoft announced a new JavaScript user-defined function (UDF) that would allow macro code to run on both the client version of Excel and in Excel Online. The cross-platform ability is interesting.
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 JavaScript UDFs might be appropriate for you.
For an introduction to JavaScript UDFs in Excel, read Suat M. Ozgur′s Excel JavaScript UDFs Straight to the Point (ISBN 978-1-61547-247-5).
However, JavaScript 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 quicker than the JavaScript 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. Chapters 8, ″Arrays,˝ and 9, ″Creating classes and collections,˝ cover arrays, 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 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 JavaScript application within Excel. Chapter 28, ″What′s new in Excel 2019 and what′s changed,˝ summarizes the changes in Excel 2019.
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.
Case study: Monthly accounting reports
This is a true story. Valerie is a business analyst in the accounting department of a medium-size corporation. Her company recently installed an overbudget $16 million enterprise resource planning (ERP) system. As the project ground to a close, there were no resources left in the IT budget to produce the monthly report that this corporation used to summarize each department.
However, Valerie had been close enough to the implementation to think of a way to produce the report herself. She understood that she could export general ledger data from the ERP system to a text file with comma-separated values. Using Excel, Valerie was able to import the general ledger data from the ERP system into Excel.
Creating the report was not easy. As in many other companies, there were exceptions in the data. Valerie knew that certain accounts in one particular cost center needed to be reclassed as expenses. She knew that other accounts needed to be excluded from the report entirely. Working carefully in Excel, Valerie made these adjustments. She created one pivot table to produce the first summary section of the report. She cut the pivot table results and pasted them into a blank worksheet. Then she created a new pivot table report for the second section of the summary. After about three hours, she had imported the data, produced five pivot tables, arranged them in a summary, and neatly formatted the report in color.
Becoming the hero
Valerie handed the report to her manager. The manager had just heard from the IT department that it would be months before they could get around to producing ″that convoluted report.˝ When Valerie created the Excel report, she became the instant hero of the day. In three hours, Valerie had managed to do the impossible. Valerie was on cloud nine after a well-deserved ″atta-girl.˝
More cheers
The next day, Valerie′s manager attended the monthly department meeting. When the department managers started complaining that they could not get the report from the ERP system, this manager pulled out his department′s report and placed it on the table. The other managers were amazed. How was he able to produce this report? Everyone was relieved to hear that someone had cracked the code. The company president asked Valerie′s manager if he could have the report produced for each department.
Cheers turn to dread
You can probably see what′s coming. This particular company had 46 departments. That means 46 one-page summaries had to be produced once a month. Each report required importing data from the ERP system, backing out certain accounts, producing five pivot tables, and then formatting the reports in color. It had taken Valerie three hours to produce the first report, but after she got into the swing of things, she could produce the 46 reports in 40 hours. Even after she reduced her time per report, though, this is horrible. Valerie had a job to do before she became responsible for spending 40 hours a month producing these reports in Excel.
VBA to the rescue
Valerie found Bill′s company, MrExcel Consulting, and explained her situation. In the course of about a week, Bill was able to produce a series of macros in Visual Basic that did all the mundane tasks. For example, the macros imported the data, backed out certain accounts, made five pivot tables, and applied the color formatting. From start to finish, the entire 40-hour manual process was reduced to two button clicks and about 4 minutes.
Right now, either you or someone in your company is probably stuck doing manual tasks in Excel that can be automated with VBA. We are confident that we can walk into any company that has 20 or more Excel users and find a case just as amazing as Valerie′s.
Versions of Excel
This sixth edition of VBA and Macros is designed to work with Excel 2019 and Office 365 features released up through June 2018. The previous editions of this book covered code for Excel 97 through Excel 2016. In 80% of the chapters, the code for Excel 2019 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 Development in VBA for Mac Excel 2019 is far more difficult than in Windows, with only rudimentary VBA editing tools. Microsoft actually recommends that you write all of your VBA in Excel 2019 for Windows and then use that VBA on the Mac.
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 web page at microsoftpressstore.com/Excel2019VBAMacros/downloads.
Support and feedback
The following sections provide information on errata, book support, feedback, and contact information.
Stay in touch
Let′s keep the conversation going! We′re on Twitter:
http://twitter.com/MicrosoftPress
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/Excel2019VBAMacros/errata.
If you find an error that is not already listed, you can report it to us through the same page.
If you need additional support, email Microsoft Press Book Support at [email protected].
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.