Please be informed that in view of the current COVID-19 situation, we will be changing the format of this course into a Live Webinar. A detailed set of instructions on the Live Webinar will also be sent to you closer to date. Please sign up for a Zoom account (https://zoom.us/) if you currently do not have an account.
Notice
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 one-day training programme is an intensive macro programming course, designed as a follow-up to course IT036: An Introduction to Excel Macros for Accounting and Finance Professionals. Participants must have basic to intermediate level of Excel experience as a prerequisite. In addition, they must have either attended course IT036 OR have some experience in writing macros to fully benefit from this course. Course participants will learn the crucial aspects of writing macros for dynamic spreadsheet data processing.
Programme Outline
- 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.
Pre-requisites
Please take note of the following admission requirements:
- Trainings will be conducted on Zoom platform, thus video camera and microphone are compulsory
- Mandatory for video camera to be turned on throughout the course
- Display your official name (as per NRIC) in Zoom, to facilitate attendance taking
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 and are current users of macros or have created macros through recording or simple editing.
Competency Mapping
Category 5 = 7.00 Hours
Schedule & Fees
Date & Time
04 Sep 2023 (9:00 AM - 5:00 PM)
Fee (inclusive of GST)
For Members:
$ 301.32
For Non-Members:
$ 361.80
Programme Facilitator(s)
Zaid Ismail
Venue
Online Classroom in or outside of
Singapore
Testimonial
Funding
1] NTUC Union Training Assistance Programme (UTAP)
UTAP (Union Training Assistance Programme) is an individual skills upgrading account for NTUC members.
To find out more on the UTAP funding and support validity period please click here.
Should you have queries on the funding scheme, you can email to UTAP@e2i.com.sg or call NTUC Membership Hotline at 6213-8008
Programme Facilitator(s)
Zaid Ismail
Zaid Ismail has been in the IT and training industry for 20 years. His experience as an IT practitioner encompasses software development, database administration and end-user support. He is also a consultant and developer of IT solutions for SMEs utilising Excel macros and Access databases.
A Microsoft Certified Trainer since 1997, Zaid has been conducting Microsoft Office courses and Microsoft Official Curriculum (MOC) systems training in areas of programming, SQL Server database administration and development, and web technologies such as HTML5, CSS3 and JavaScript.
Please be informed that in view of the current COVID-19 situation, we will be changing the format of this course into a Live Webinar. A detailed set of instructions on the Live Webinar will also be sent to you closer to date. Please sign up for a Zoom account (https://zoom.us/) if you currently do not have an account.
Notice
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 one-day training programme is an intensive macro programming course, designed as a follow-up to course IT036: An Introduction to Excel Macros for Accounting and Finance Professionals. Participants must have basic to intermediate level of Excel experience as a prerequisite. In addition, they must have either attended course IT036 OR have some experience in writing macros to fully benefit from this course. Course participants will learn the crucial aspects of writing macros for dynamic spreadsheet data processing.
Programme Outline
- 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.
Pre-requisites
Please take note of the following admission requirements:
- Trainings will be conducted on Zoom platform, thus video camera and microphone are compulsory
- Mandatory for video camera to be turned on throughout the course
- Display your official name (as per NRIC) in Zoom, to facilitate attendance taking
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 and are current users of macros or have created macros through recording or simple editing.
Competency Mapping
Category 5 = 7.00 Hours
Programme Facilitator(s)
Zaid Ismail
Zaid Ismail has been in the IT and training industry for 20 years. His experience as an IT practitioner encompasses software development, database administration and end-user support. He is also a consultant and developer of IT solutions for SMEs utilising Excel macros and Access databases.
A Microsoft Certified Trainer since 1997, Zaid has been conducting Microsoft Office courses and Microsoft Official Curriculum (MOC) systems training in areas of programming, SQL Server database administration and development, and web technologies such as HTML5, CSS3 and JavaScript.