Why and for who?
This is for advanced user who:
- has been using Excel for a substantial length of time and wants to automate spreadsheets, writes own functions and procedures
- wants to explore the VBA language
- wants to write programs to ensure that future editing, amending or updating can be carried out with the minimum of effort
- wants to learn steps to create custom solutions with VBA in Excel
What will you learn?
The following topics will be covered during the workshop, and the Payroll system will be developed through the workshop.
Making a Macro Do Simple Task
- Recording a Macro
- Running a Macro
- Editing a Macro
- Adding a Macro to a Toolbar
|
Making a Macro Do Complex Task
- Create a Simple Payroll System
- Testing the Completed Macro
|
Making a Macro Do Repetitive Task
- Repeating an Action with a Macro
- Interacting with Users
- Making Decision
|
Introduction to VBA
- What is Visual Basic Editor
- What is Object Browser
- What is Project Explorer
- The Code Window
- The Properties Window
- Debugging Tools
|
UserForm and ActiveX Controls
- Working with Userforms
- Working with ActiveX Controls
- Exploring ActiveX Controls
- Creating Employee Leave Application Userform
- Creating Employee Payroll System Userform
|
Exploring Excel Controls
- Developing Employee Leave Application System
Adding VB Code to the Userform Testing Employee Leave Application
|
Creating a Package Applicatio
- Packaging the Payroll Application
|
Collection Object and Object
- Object & Collections
- Object Hierarchy
- Referencing an Object
- Properties of an Object
- Methods of an Object
- Exploring Workbooks Collection Object
- Exploring Workbooks Object
- Exploring Worksheets Objects
|
Exploring Range Object
- Referencing a Range Object
- Range Properties and Methods
|
Exploring Name Object
- Names Collection Object
- Name Object
|
Visual Basic Modules and Procedures
- Structure of a Module
- Procedures
- Creating Procedures
- Calling Procedures
- Procedures with Arguments
- Constants and Variables
- Specifying Scope
- Writing Visual Basic Statement
- With..End With
- Handling Run-time Errors
|
Visual Basic Control Structure
- Making Decisions
- Looping
- Nesting Control Structures
- Exiting Loop and Procedure
|
Workshop Projects
During the training, you will be building the following application:
- Leave Application -
~ System allows employee to apply leave via Form, and calculates remaining leaves available.
~ Data will automaitcally be updated to the Excel worksheets
- Payroll Application -
~ System able to calculate salary with EPF, SOCSO, and prints out the payslips for all employees at once or only a selected employee.
What will you receive?
For every entolment, the participant will get
- Our Microsoft Excel VBA handouts and exercises
- CD with the sample codes
~ Payroll System created during the Workshop
~ Converting Number to Text
~ Calculating the Number of Days, Months & Years between two Dates
