Overview
Have you felt bored in performing routine tasks?
Have you ever imaged that Excel can perform routine tasks for you?
Do you fear the complexity of VBA programming?
If so, you’ll learn how to record a macro, make changes in VBA, so that you can automate your routine tasks with a single mouse click. This training course is designed to equip you with core skill in reading the VBA code. Subsequently, you will be exposed to essential Macro recording techniques. With these Macro recording techniques and capability to read VBA, you can have your very own workable Macro right after this course.
Who Must Attend
- Regular Excel users that want to automate their tasks and with minimal VBA (Visual Basic for Applications) programming.
- Learned Excel users wanted to create their tools while automate their tasks.
OUTCOME
At the end of this course you will be able to:
- read and understand VBA
- plan and record Macro
- perform adjustments on recorded Macro
- add simple branching & repetition logic into recorded 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
Please Note: If you attend a course and do not meet the prerequisites you may be asked to switch to appropriate course or to excuse yourself.
DURATION
2 Days (9 am - 5 pm)
METHODOLOGY
Face-to-face learning with demonstrations and practical hands-on.
Training Outline
Day 1
1. Introduction to Macro/ VBA
- How to Create Excel Macro/ VBA
- What is Macro Recorder
- Limitations of Macro Recorder
- What is VBA
- Macro Recording vs. VBA
2. Setting the Stage
- Showing Developer Ribbon
- Macro Security
- Save File as Macro-Enabled Workbook
- Saving Macro-Enabled Workbook
3. Visual Basic for Application (VBA)
- Working in Basic Editor (VBE)
- General Structure (Syntax) of VBA
- Adding Comment
- Creating Sub Procedure
4. Getting User Feedback
- Message Boxes
- Input Boxes
5. Running the Macro
- via Macro Menu
- via Shortcut Key
- via Quick Access Toolbar
- via Shape/ Image
- via Button
6. Running the Macro in Slow Motion (Debugging)
- Stepping Through
- Immediate Window
- Breakpoint
- Type of Errors
7. Macro Recording
- Record Macro
- Viewing Macro
- Use Relative References
- Plan Your Own Macro (PYOM™) Sheet
8. Macro Recording Techniques
- Benefits of Macro Recording
- Typical Mistakes in Macro Recording
- Essential Techniques of Dynamic Macro Recording
Day 2
9. Introduction to Control Structures
- Decision Structures
- Loop Structures
10. Macro Recording in Action
- Project #1: Display Standard Message on Selected Cells
- Project #2: Prompt User and Store the Data Entered in Specified Cell
- Project #3: Prompting for User Data Entry, and Insert the Data Entered at the End of Specified Worksheet
- Project #4: Continuously Prompting for User Input Until a Secret Phrase is Entered or the User Give Up
- Project #5: Create a new formula on specified location, and fill the new formula downwards until the last record
- Project #6: Convert Formula To Actual Values
- Project #7: Change text to uppercase/ lowercase
- Project #8: Combining Multiple Worksheets into Single Worksheet
- Project #9: Split Data into Designated Worksheets
- Project #10: Import/ Export Recorded Macro (from between files, or between computers)
11.Speeding Tips
- Turning Off Screen Updating
- Preventing Alert Messages
- Pause Automatic Calculation
Hardware & Software
- A laptop is required for this course (PCs not provided)
- Excel 2007 or later is installed