Excel VBA Macros: Forms, Files, and Data Handling

Overview

To round out your data-analysis skills in VBA for Excel, you'll display a custom dialog box and program form events. You'll also use VBA to get file and directory structure, read from text files, and write to text files. Then, you'll clean and transform data by automating Power Query, transform data using VBA and workbook functions, using regular expressions, and managing errors in data. Lastly, you'll run other programs and commands and share your VBA projects. This is the third and final course in a multi-course Specialization. All of the courses in this Specialization require that you have a recent version of Microsoft Excel installed. The course setup instructions provided in the first course go into more detail about the hardware and software requirements.

Syllabus

Developing Custom Forms

Sometimes you need to support more complex interactions with a user than Microsoft® Excel's built-in dialog boxes can provide.

Using VBA to Work with Files

Not all data is going to be provided to you in a tidy Microsoft® Excel® workbook or relational database. It is common for business analysts to have to work with a wide array of different file types, including XML, JSON, CSV, HTML, and all sorts of proprietary file formats. While Excel includes an extensive toolset for importing such data, there may be circumstances in which you need to import a file that requires custom VBA code to extract, transform, and load the data.

Using VBA to Clean and Transform Data

You have developed procedures to import and aggregate data. In many cases, the data you import from other sources may not be ready for analysis until you perform a variety of data cleaning and transformation tasks.

Extending the Programming Environment Beyond the Workbook

You have extended Excel's capabilities by automating tasks within Microsoft® Excel®, adding new custom workbook functions, custom forms, and the ability to read and write external files and data. With VBA, you can reach beyond Excel's normal sphere of influence by automating external programs. And as you expand Excel's capabilities in this way, you can share what you develop so other users can take advantage of your efforts