Course Detail()

14.00 CPE Hours (Category 1, Category 2, Category 3, Category 4, Others)
Classroom

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. 


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.
 

ProgrammeDateCourse 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’

Schedule & Fees

Testimonial

Funding

No funding Available!

Programme Facilitator(s)

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. 


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.
 

ProgrammeDateCourse 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)


No course instances or course instance sessions available.