Advanced Excel – Module 5

Module 5 – 3 hours

1. Create a Macro: Swap Values, Run Code from a Module, Macro Recorder, Use Relative
   References,
2. Create a Macro: FormulaR1C1, Add a Macro to the Toolbar, Macro Security, Protect Macro
3. MsgBox: MsgBox Function, InputBox Function
4. Workbook and Worksheet Object: Path and FullName, Close and Open, Loop through Books and
   Sheets,
5. Workbook and Worksheet Object: Sales Calculator, Files in a Directory, Import Sheets,
   Programming Charts
6. Range Object: Current Region, Dynamic Range, Resize, Entire Rows and Columns, Offset,
7. Range Object: From Active Cell to Last Entry, Union and Intersect, Test a Selection,
8. Range Object: Possible Football Matches, Font, Background Colors, Areas Collection, Compare
   Ranges
9. Variables: Option Explicit, Variable Scope, Life of Variables
10. If Then Statement: Logical Operators, Select Case, Tax Rates, Mod Operator,
11. If Then Statement: Prime Number Checker, Find Second Highest Value, Sum by Color, Delete
    Blank Cells
12. Loop: Loop through Defined Range, Loop through Entire Column, Do Until Loop, Step Keyword,
13. Loop: Create a Pattern, Sort Numbers, Randomly Sort Data, Remove Duplicates, Complex
    Calculations, Knapsack Problem
14. Macro Errors: Debugging, Error Handling, Err Object, Interrupt a Macro, Macro Comments