Why and for whom?
This is for beginner users who wish to:
- learn and understand the basic concept of Excel macro / VBA programming language
- learn to automate repetitive and routine tasks
- write simple VBA codes and build modest applications
After completing this course, you will be able to:
- create simple macros using the macro recorder and run the macros via various methods, i.e. a command on the ribbon, a button on Excel worksheet or shortcut key
- explore the Excel Object Model and learn to manipulate data referencing the cells, ranges, worksheets and workbooks
- use Visual Basic Editor (VBE) to create simple macro / VBA codes and understand the debugging techniques available
- understand the usage of VBA, by explore and edit existing sample macro / VBA codes
What will you learn?
The following topics will be covered during the workshop.
Introducing VBA
- Automating a Recurring Task
- Automating a Repetitive Task
- Running a Macro Automatically if Another Action Takes
Place
- Creating your own Worksheet Functions
- Simplifying the Workbook's Look and Feel for Other Users
- Controlling Other Office Applications from Excel
|
Getting Started with Macros
- Composing Your First Macro
- Running a Macro
- Saving Your Workbook that Contain Macros
|
Introducing The Visual Basic Editor
- Getting Into VBE
- Discovering the VBE Parts
- Understanding Modules
- Knowing What Goes Into a VBA Module
- Working With Project Window
- Editing a Macro with Comments and Improvement to The Code
- Locking and Protecting the VBE
- Debugging Tools
|
Introducing Excel Object Model
- Introducing the Concept of Objects
- Finding Out About the Excel Object Hierarchy
- Understanding Object and Collections
- Referring to Specific Objects in Your VBA Code
- Object Oriented Programming
- Accessing or Changing an Object's Properties
- Performing Actions with an Object's Methods
- An Object's Events
|
Exploring Range Object
- Working with Contiguous Populated Ranges
- Working with Non-contiguous Populated Ranges
|
Making Decision With VBA
- Choosing Between This or That with IF Statements
- Getting Users To Make Decisions
|
Handling Repetition Actions With VBA
- What is a Loop
- Types of Loops
- Repeating an Action for a specified Number of Times
- Repeating an Action Upon an Object in a Collection
|
Appendix
|
|
Workshop Projects
During the training, you will be working on the following exercises
What will you receive?
For every enrollment, the participant will get:
- Our Microsoft Excel VBA handouts and exercises
- The sample codes :
| ~ Using Text to Speech |
~ Creating Welcom Message |
| ~ Creating A Backup of Current Workbook |
~ Protecting Worksheet |
| ~ Unprotecting Worksheet |
~ Protecting All Worksheets Instantly |
| ~ Converting All Formulas into Values |
~ Converting Text to Upper Case |
| ~ Highlighting Negative Numbers |
~ Removing Text Wrap |
| ~ Unmerging Cells |
~ Inserting Timesstamp |
| ~ Replacing Blank Cells with Zeros |
~ Adding Serial Number |
| ~ Adding A-Z Alphabet in A Range |
~ Removing Spaces from Selected Cells |
| ~ Highlighting Top 10 Values |
~ Highlighting Greater Than Values |
| ~ Highlighting Named Ranges |
~ Highlighting Cells with a Specific Text in Worksheet |
| ~ Unhiding all Rows and Columns |
~ Inserting Multiple Worksheets |
| ~ Sorting Worksheets |
~ Saving Each Worksheet as a Single PDF |
| ~ Adding Workbook to A Mail Attachment |
~ ASCII Characters |
