Excel Automation with VBA |
|---|
| Course Outline |
Outline
|
| Introduction |
|
| Reading Material |
|
| DAY | Topics | Sharing |
|---|
| 1 |
- What is automation?
- The benefits
- Case study #1: Comment Pictures
- Case Study #2: Calander
- Case Study #3: Exam Mock Engine
- Record and play-back macros
- 3 basic techniques to trigger techniques from Excel main window
- Explore the recorded macro and identify its weaknesses
- Hand code your own macro
- Compare with the recorded macro
- 5 techniques trigger macros from IDE
- Use macro recorder to learn VBA coding
- What is event programming?
- Event and Event Handlers
- Event parameters
- Worksheet events
- Worksheet_SelectionChange event
- Worksheet_Change event
- Beware of Re-Entrant Issue
- Worksheet_Activate event
- Worksheet_Deactivate event
- Worksheet_BeforeDoubleClick event
- Intercept and Hijack Before_* events
- Worksheet_BeforeRightClick event
|
|
| 2 |
- The use of workbook level events
- Workbook_Open event
- Workbook_Open event
- Workbook_NewSheet event
- Downcast Object with Worksheet reference
- Workbook_Sheet* events
- Form controls vs ActiveX controls
- Renaming controls
- Program ActiveX controls
- ActiveX Button
- ActiveX Combo box
- ActiveX Check box
- ActiveX Scrollbar
- The purpose of user forms
- Add user form
- Add controls to user form
- Dealing with Form events
- Dealing with Control events
|
|
| Recommanded Books |
|---|
 | Title | Master the Art of Automation: A Comprehensive VBA Guide for Finance & Accounting |
|---|
| ISBN | B0CRT56LHZ |
|---|
| Author | Sampson, Josh; Strauss, Johann; Van Der Post, Hayden |
|---|
| Year | 2024 |
|---|
| Publisher | Reactive Publishing |
|---|
 | Title | 40 Ready to Use Excel VBA and Macros for beginners |
|---|
| ISBN | B0C1XGGYHG |
|---|
| Author | Mac Guru |
|---|
| Year | 2023 |
|---|
| Publisher | |
|---|
 | Title | Excel Macros For Dummies |
|---|
| ISBN | 978-1-119-84443-3 |
|---|
| Author | Dick Kusleika |
|---|
| Year | 2022 |
|---|
| Publisher | Wiley |
|---|
.jpg) | Title | Microsoft Excel VBA and Macros (Office 2021 and Microsoft 365) |
|---|
| ISBN | 978-0-137-52152-4 |
|---|
| Author | Bill Jelen; Tracy Syrstad |
|---|
| Year | 2022 |
|---|
| Publisher | Microsoft Press |
|---|
 | Title | VBA Automation for Excel 2019 Cookbook-Solutions to automate routine tasks and increase productivity with Excel |
|---|
| ISBN | 978-1-789-61003-1 |
|---|
| Author | Van Niekerk, Mike |
|---|
| Year | 2020 |
|---|
| Publisher | Published by Packt Publishing Ltd. |
|---|
 | Title | Microsoft Excel 2019 VBA and Macros |
|---|
| ISBN | 978-1-509-30611-4 |
|---|
| Author | Bill Jelen, Tracy Syrstad |
|---|
| Year | 2019 |
|---|
| Publisher | Microsoft Press |
|---|