This is how you write a macro that performs actions repeatedly. The lesson covers the various types of loops and it also has a section explaining what collections are and how they relate to looping. This lesson covers looping. The screenshots are of excel 2013 but the code should work for versions of Excel 2007 and forward.
Combining the topic of mastering Excel Macros: IF Statements and this lesson gives you a solid foundation you can use to build more complex macros. Now you can add logic to a macro covered in book 4 and repeat that logic as often as needed this lessonAs usual, the lesson comes with several follow along workbooks that you can use to work through the exercises at your own pace.
You can download the follow along workbooks by signing up at my website and you will receive an email with the files: http://markmoorebooks. Com/macros-looping/Note these lessons are for Excel for Windows.
Mastering Excel Macros - IF Statements Book 4
Along with that topic, we review how to use the OFFSET function programmatically and the ActiveCell. As with all the other lessons, there is a sample workbook you can get from my website to follow along with the exercises. In this lesson we cover how to write IF statements in VBA. The if statement is the basic unit of logic in Excel programming.
It gives your program the ability to 'think' and perform actions based on one or several conditions.
Mastering Excel Macros: Beginning to Code Book 3
Future lessons will continue to present a new programming topic and then incorporate them into this macro. As with all lessons, this one also comes with follow along workbooks. The follow along workbook contains an Invoice. In this lesson #3 of the macro series you will begin to code a macro. You begin by recording a simple macro then adding custom commands to improve upon what you recorded.
The first one is the initial starting invoice and I have also included the end result for your reference. The goal is to build an database of all invoices so you can analyze and report performance over time.
Mastering Excel Macros: Debugging Book 2 of Mastering Excel Macros
This is the first 'real' book in the Mastering Excel Macros Series. It does have a follow along text file. The text file contains the macro that we will debug. It is much faster to import the macro and not have you type in macro code line by line. This lesson goes in depth on the macro editing environment and how to use the built in Excel features to find and fix macro errors.
This lesson doesn't have a full follow along workbook. The introduction book covered macros, what they are, why they are important and the macro coding environment.
Mastering Excel Macros: Object Variables Book 6
This lesson is a big step. You will learn about object variables. These are variables that are not numbers, not text but entire objects worksheets, workbooks, etc. That you can create and then manipulate as needed. In the follow along workbook in this lesson, you will have a list of customers and write a macro that will create a new invoice, in a new workbook, for each customer.
As with all the other lessons, there is a sample workbook you can get from my website to follow along with the exercises.
Mastering Excel Macros: PowerPoint Automation Book 7
The lesson exercise includes a sample certificate built in PowerPoint. You will programmatically take information from Excel and put it in PowerPoint. This exercise can be altered to work with any other existing PowerPoint presentation. This lesson comes with several follow along workbooks that you can use to work through the exercises as you read the lesson.
Note: to work through this lesson you must have PowerPoint installed on your PC. In this lesson you will create an object variable that represents not a cell, not a workbook but PowerPoint. This functionality is called Automation. This lesson continues with the topic of object variables. You will then learn to write a macro that lets you control PowerPoint from Excel.
.
Function Procedures Book 11 - Mastering Excel Macros
Custom functions are a great way to package routine logic in a function that less experienced users can use. This lesson covers how you can create custom functions in Excel. For example, you can create a function called MyBestCalc and use it in a cell in a worksheet or in a macro. The example exercises in this lesson cover how to return a letter grade from a numerical score and optionally apply a curve, how to extract a part number from a text string and how to process a range in a function, how to handle multiple parameters and how to add custom descriptions to your functions.
Although this is lesson #11, you do not need to have all previous 10 lessons in the series to use this material. Any macro knowledge that you know can be incorporated into custom functions. As with all my lessons, this one comes with a follow along workbook that you can use to practice what you read.
Mastering Excel Macros: Arrays
Each one of these items help you use macros to manage, calculate and process large amounts of information. This lesson covers the VBA topics: Arrays, Collections and Dictionaries. Why are these needed? Because performing calculations in macro is exponential faster than performing calculations in a worksheet.
This lesson has a sample workbook where you can test and see the performance improvements. If you are new to my lessons by lesson 10 you shouldn't be. But who knows!, you will see a link in the lesson where you can download follow along workbooks so you can practice what you read. I also don't abandon you after you buy the lesson, if you have any questions, you can email me and I will help you out.
Seriously faster.
Mastering Excel Macros: FileSystemObject Book 8
You will learn how to use the fsO to list all the subfolders in a folder, the files in a folder, create text files, etc. There are many additional actions you can do with FSO, at the end of the lesson there is a list of some popular FSO methods. This lesson comes with a follow along workbook that has all the completed exercises.
. Here you are going to learn how to use the FileSystemObject FSO object. The fso object is the object that Windows uses to create, delete, copy files and folders. This macro lesson continues with the topic of automation.
Mastering Excel Macros: VBA Forms and Events - Book 9
These forms are very different than the regular Form Objects you place directly on a worksheet. Vba forms are completely customizable and contain their own macros inside them. One that you use to work through the lesson and a second one with all the answers. You will learn when it is appropriate to use regular Form Objects vs.
This lesson continues the path towards macro mastery, one topic at a time. You will learn all about VBA forms. Vba forms, using the data form, leveraging Excel's built-in dialogs and coding your own VBA Form with several different control types. This lesson comes with two follow along workbooks.
Mastering Excel Macros: Charts Book 12
You will learn how to:* update a charts data series* set up a worksheet where users can change the chart type* Build a worksheet where charts update automatically based on the cell a user selects* and more!There is a link in the lesson where you can download the follow along workbooks to practice the exercises in the book to get hands-on experience.
. This macro lesson focuses on manipulating charts in Excel.
