Programme Objective
 
This two-day training programme is an intensive macro programming course, covering introduction to advanced Excel Macro. Participants must have basic to intermediate level of Excel experience as a prerequisite. Course participants will learn the crucial aspects of writing macros for dynamic spreadsheet data processing.

Programme Outline

Day 1:
 
This one-day training programme is designed as an introduction to understanding the role of macros in automating tasks within Microsoft Excel.
 

• Introduction to Excel Macros
  • The History of Visual Basic for Applications (VBA)
  • An Orientation of the Visual Basic Integrated Development Environment
  • Saving Workbooks Containing Macros
• Recording Macros
  • Understanding the purpose of recording Macros
  • Preparation before Recording a Macro
• Basic Object-oriented Concepts in Macro programming
  • Properties and Methods
  • The Core of Macros
  • Excel Object Model and Collections
  • Range Exercises:
    A Demonstration on Using Objects for Data Manipulation
• Using Functions in Macros
  • Functions in Excel 
  • VBA Intrinsic Functions 
  • Built-in Excel Functions
  • Creating User-defined Function with VBA 
  • Characteristics of Functions
• Hands-on Demonstration and Exercise
  • Application in real life scenarios
  • Applying Macros to common scenarios
  • Adding code to partially-completed Macros

 
Day 2:
 

• 3 Components of Macros
  • This introductory chapter elaborates the role played by the 3 components of macro: knowledge of Excel features, Objects and VBA.
• Data Manipulation with Range Object and VBA
  • This chapter explores selected methods and properties of the Range object that can be used for spreadsheet data manipulation without having to code entirely from scratch.
• Developing Dynamic Macros with VBA
  • VBA transforms a macro into a dynamic piece of code through the use of variables, functions, control-of-flow statements. This chapter provides scenarios on the use of VBA constructs for data manipulation.
• Managing Data with Worksheet and Workbook Objects
  • Macros are more often used to extract data from or copy to different worksheets and workbooks. To make it more complicated, data may come from various sources and format like CSV, XML or even databases. This chapter provides several scenarios of writing macros to deal with diverse sources of data.
• Code Debugging
  • Code debugging is an essential part of any programming task. This chapter explores and demonstrates the use of several debugging tools available within the Visual Basic integrated development environment to fix errors.

To view our full list of Infocomm courses, please click here.
 

Programme	Date	Course Fees (With GST)
IT036C: Excel Macros 1 & 2: Excel Macros Workshop   Cost Saving $17.11(members) $86.02 (non members)	2 & 30 Jul 2020           21 Sep & 12 Oct 2020            28 & 29 Dec 2020	Members: $679.45 Non-Members: $815.34
IT036: Excel Macros 1: An Introduction to Excel Macros for Accounting and Finance Professionals	20 Jan 2020    26 Mar 2020    02 Jul 2020     21 Sep 2020	Members: $348.28 Non-Members: $450.68
IT036B: Excel Macros 2: Mastering Excel Objects with VBA for Effective Spreadsheet Automation	30 Jul 2020              12 Oct 2020	Members: $348.28 Non-Members: $450.68

         

Training Methodology
 
Lecture style, with hands-on exercises
 
Closing Date for Registration

1 week before programme or until full enrolment
 

Intended For

Users with Microsoft Excel experience of at least Intermediate Level’

Programme Facilitator(s)