Outcome
At the end of this course you will be able to:
- plan and record dynamic Macro
- read and understand various VBA blocks
- add simple branching & repetition logic into recorded Macro
- using simple way to understand and troubleshoot macro
- manage recorded Macro
Prerequisites
To ensure your success, we recommend the delegates equip with the following or equivalent knowledge:
- An advanced working knowledge of Microsoft Excel
- A working knowledge of Microsoft Windows
- A working knowledge of computer, keyboard and mouse
- Introduction to Macro/ VBA
- What is Macro Recorder
- Limitations of Macro Recorder
- What is VBA
- Macro Recording vs. VBA
- Showing Developer Ribbon
- Understanding Various Consideration of Macro Security
- Save File as Macro-Enabled Workbook
- Opening a Macro-Enabled Workbook
- Introduction to Macro Recording
- Record Static Macro
- Understand Different Ways of Recording Dynamic Macro
- Using Simple Way in Viewing Macro Recording
- Understanding Plan Your Own Macro (PYOM™) Sheet
- Using Different Ways to Run a Macro via Macro Menu, Shortcut Key, Quick Access Toolbar, Shape/Image and Button
- Macro Recording, The Pro Techniques
- Aware of Typical Mistakes in Macro Recording
- Essential Techniques of Dynamic Macro Recording
- Making a Recorded Static Macro Dynamic
- Gain Better Clarity by Running Recorded Macro in Slow Motion
- Introduction to Visual Basic for Application (VBA)
- Understanding Various Screen Elements in Visual Basic Editor (VBE)
- Aware of General Structure (Syntax) of VBA
- Adding Comment to Ease Future Reference
- Getting User Feedback
- Using Various Types of Message Box
- Capturing User Responses from Message Box
- Using Different Types of Input Box
- Taking User Inputs from Input Box
- Troubleshooting the Macro (Simple Debugging)
- Using Stepping Through to Understand the Code
- Using Immediate Window to run Quick Check
- Using Breakpoint to Pause Code Execution
- Aware of Different Types of Error
- Introduction to Control Structures
- Aware of Various Decision Structures (IF, SELECT CASE)
- Aware of Different Loop Structures (FOR…NEXT, LOOP)
- Aware of Different Ways to End a Macro Prematurely
- Managing Macros
- Easiest Way to Copy Macro Module Between Different Workbooks
- Export Macro Module as an External File
- Import Macro Module from an External File
- Make a Macro Available to All by Using Personal Macro Workbook
- Speeding Tips
- Turning Off Screen Updating
- Preventing Alert Messages
- Pause Automatic Calculation
- Macro Projects, Learn by Doing
- Project #1: Create New Formula Columns and Copy the Formulas
Until Last Record
- Project #2: Combining Multiple Worksheets into a Specific Worksheet
- Project #3: Prompting for User Data Entry, and Append the Data
Entered to a Specified Worksheet
- Project #4: Change Selected Text to Uppercase/ Lowercase
- Project #5: Duplicate Current Worksheet to a Brand-New File with
Values Only
Note: The outline coverage may be adjusted to meet the actual training pace.
