As this class places strong emphasis on the hands-on experience, you will be working with data for the majority of the time in the class. To optimize the learning experience, it is mandatory for all participants to be equipped with a laptop, pre-installed with either Microsoft Excel 2010, 2013, 2016 (full licence) or any latest version. If you have a Microsoft Office 365 subscription, you will need to have a locally installed Excel 365 application in your PC or laptop. Macros cannot be executed when the workbook containing them is opened in Excel online. Also, kindly note that Mac Excel version has limited Excel functionality.
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.
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’
Schedule & Fees
Testimonial
Funding
No funding Available!
Programme Facilitator(s)
As this class places strong emphasis on the hands-on experience, you will be working with data for the majority of the time in the class. To optimize the learning experience, it is mandatory for all participants to be equipped with a laptop, pre-installed with either Microsoft Excel 2010, 2013, 2016 (full licence) or any latest version. If you have a Microsoft Office 365 subscription, you will need to have a locally installed Excel 365 application in your PC or laptop. Macros cannot be executed when the workbook containing them is opened in Excel online. Also, kindly note that Mac Excel version has limited Excel functionality.
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.
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)
