New user? Register as member

Increasing Productivity Using VBA Excel Macros

Even though Excel is a fantastic software, we often find ourselves repeating mundane tasks which can be very time consuming. In this course participant will learn the basics of macro programming which will allow them to automate such tasks with a click of a button.

This course is not for those who run Excel on Mac.

Objective

Participants will learn how to record and modify macros. They will also learn some basic programming techniques like the loops and decision making constructs which will enable them to write their macros from scratch.

Outline

Lesson 1: Basics of Macros

Introducing the Developer Tab
Choosing the Appropriate Macro Security
Executing a Recorded Macro
Working in the VB Editor
Understanding the Recorded Code
Editing the Recorded Code
Stepping through a Procedure

Lesson 2: Working With Common Excel Objects

Introduction to objects
Using the Object Browser
The Application Object
The Workbook and Workbooks Object
The Worksheet and Worksheets Object
The Range and Cells Object
Accessing Rows and Columns in a Range

Lesson 3: Working With Variables and Constants

Variables and their Scope Levels
Variables and their Data Types
Working with Object data types

Lesson 4: Prompting User for Input

Input Box Function
Msgbox Function

Lesson 5: Controlling Code Execution (Looping and Decision making Code)

For...Next Loop
For Each...Next Loop
Do...While Loop
Do...Until Loop
If...Then...Endif
If..Then...Else..End If
If...Then...Elseif...End If
Select...Case

Lesson 6: Working With Functions

Creating User Defined Functions (UDF)
Executing UDF from Worksheets
Running UDF from Macros

Lesson 7: Understanding Events

Writing Event Handler Procedures for workbook and worksheets
Writing Event Handler Procedures for custom button

Who should attend

This course is for participants with a good working knowledge of Excel. Participants must at least know how to perform if and vlookups in Excel. No prior background in programming is needed.

Testimonials

Trainer provided practical analogies made understanding very easily! very experience and very helpful!
Health Promotion Board

A very course for VBA. Trainer has good knowledge and answers participants questions. Duration was sufficient for course objectives to be met.
PUB

Workshop length & pace is very good Stella is very engaging & provides the optional mix of theory plus practice to facilitate the learning. Very useful course.
National Institute of Education

- I learnt Macros within these 2 days, it can be applied at work.
- Informative and lots of hands-on
- Trainer acknowledgeable and approachable
( NHG Diagnostics )

Profile of Stella Anthony

Ms. Stella Anthony holds both a second class upper honours degree in Bachelor of Psychology from The Open University, United Kingdom and a Bachelor of Engineering (Electrical) from Nanyang Technological University. She currently holds industry certifications like Microsoft Certified Trainer and ACTA. She has over 22 years of training and customizing both Desktop Application courses and Microsoft Certified System courses. Besides training, she has also developed VBA, web and windows applications. As a trainer, clients can benefit from her vast experience through the practical approach she brings into her training.