ExcelMacroMastery Courses

Learn how to build a simple framework for any VBA application. This application reads data from a closed workbook and writes it to the current worksheet. It may sound simple but it covers techniques you will use in 90% of your applications.

Major Topics Include:
Building a simple project framework, selecting a range of data, comparing text in different cells, opening workbooks, copying data between worksheets.

Building on what you did in the first application, this one takes things much further. We run through the data keeping a running total for each team. Using data like this is a very common requirement in many VBA applications. Learn how to use the Dictionary to easily maintain running totals for multiple items.

Major Topics Include:
Building a report, updating totals as the application runs, sorting a worksheet range, formatting cells, writing code to handle errors.

Use Excel to send an Email with a worksheet attached. This application shows you how you can easily interact with Microsoft Outlook. If you are planning to have an email component in your application then you can pretty much copy and paste the code from here. How emails are sent does not vary much between applications.

Major Topics Include:
Sending an email, adding attachments to an email, Dealing with a collection of items, adding/deleting Worksheets, creating a new workbook.

Worksheet tables are a very useful and important part of Excel. Learn how to convert your worksheet data into a beautifully formatted table. Automatically sort or filter the table based on cell entries.

Major Topics Include:
Creating a worksheet table, adding/removing table columns, using worksheet events to automatically update items, using PasteSpecial to paste formats or formulas.

Charts can add a lot to your Excel projects. Learn how to maniplate them using VBA. Create a Chart of daily prices for a user selected currency. In this application you will see how to use the Windows File Dialog to allow the user to select a file and how to create your own UserForm.

Major Topics Include:
Creating a custom UserForm, adding/deleting a Chart, Inserting a chart trendline, using the Windows File Dialog to allow the user to select a file.

If you are using data in Excel then a database is never too far away. Being able to connect to a database can greatly enhance the potential of your application. In this project we will read data from an Access database and write it to a worksheet. Next, we will write data from a worksheet back to the database. Even though we use an Access database, this code will work for any major database including SQL Server and Oracle. The only difference is the connection string.

Major Topics Include:
Connecting to a database, reading and writing between a Database table and a worksheet, deleting all records in a database table, using optional parameters in a sub or function.

If you use VBA for any length of time you will at some stage be asked to create a formatted report. Learn the two ways to do this - using code or using a template. Here we do both so you can use either in your own code.

Major Topics Include:
Reading from multiple workbooks/worksheets, formatting a report, using a template to format a report, adding sparklines to cells, measuring how long a VBA application takes to run.

Pivot Tables are one of the most powerful parts of Excel. Discover how to easily build a pivot table using VBA. Turn raw book royalties data into easily readable data.

Major Topics Include:
Creating/deleting a Pivot table, adding rows/columns/filters and values to a pivot table, groupings dates by week.

Reading from a text file to a worksheet and vice versa is a common task in VBA. This application reads and writes using files of both fixed and delimited types.

Major Topics Include:
Opening text files,read and writing between text files and worksheets, parsing strings, padding strings. copying files.