Excel VBA Programming

1. Getting Started

1. Course introduction
2. Excel VBA overview
3. The Developer Tab
4. Macro security
5. The Visual Basic Editor and Project Explorer
6. Excel objects and event code
7. Writing a macro in the VBE
8. Using the Immediate window
9. Saving a macro-enabled workbook
10. The correct way to use the macro recorder
11. Analysing a recorded macro
12. Improving a recorded macro
13. Assigning a macro to a button
14. Testing a macro on another worksheet

2. Key Components of VBA Code

1. The Excel object model
2. VBA classes and functions
3. Properties and methods
4. Data variables
5. Object variables
6. Option explicit
7. Declaring And Using Constants
8. Using MsgBox for Output
9. Using MsgBox for Input
10. Using VBA InputBox
11. Using Application.InputBox

3. VBA Control Statements

1. If Statements
2. If … ElseIf Statements
3. Select Case
4. For … Next Loops
5. For Each … Next Loops
6. Do … While Loops
7. Do … Until Loops
8. Creating Arrays
9. Dynamic Arrays
10. Utilising Arrays

4. Procedures and Functions

1. Sub Procedures and Module Level Variables
2. Static Variables
3. Passing Parameters
4. Creating and Calling Functions
5. Creating User-defined Functions

5. The Application Object

1. Changing and Restoring User Settings
2. Application.GetOpenFilename
3. Application.GetSaveAsFilename
4. Controlling Screen Updating
5. Suppressing Warning Messages

6. Code Recognition Clinic

1. Example 1
2. Example 2
3. Example 3
4. Example 4
5. Example 5
6. Example 6
7. Hyper-disambiguation Practice

7. Files and Workbooks

1. Targeting Workbooks Efficiently
2. Checking Whether A Workbook Is Open
3. Checking Whether Several Workbooks Are Open
4. Checking Whether A File Exists
5. Checking Whether A Folder Exists
6. Deleting Files And Folders
7. Processing All Files In Folder

8. Worksheets and Charts

1. Targeting Worksheets Efficiently
2. Copying Worksheets
3. Moving Worksheets Between Workbooks
4. Saving Worksheets As Seperate Workbooks
5. Working With The Sheets Collection
6. Creating A Standalone Chart
7. Creating An Embedded Chart

9. The Range Object

1. Copying A Range Of Cells
2. Using The Offset Property
3. The End Property
4. The Resize Property
5. Entering Formulas With VBA
6. Deleting Ranges
7. Inserting Ranges
8. Outlining Ranges