2 days – Visual Basic Application for Excel 2016 (15 hrs)
Course Code: TGS-2018500205 | Broad-based Funding
*Classroom-based Learning*
This course equips participants with the necessary skills to use Visual Basic functions like Macros to convert tedious manual tasks into one click of a button to increase spreadsheet productivity, and to use Userforms for user interactive interfaces.
- Use Visual Basic functions like Macros
Dashboard fundamentals
- What is a dashboard
- Key characteristics of a dashboard
Planning considerations
- Define information needs
- Acquire and arrange data
Dashboard design consideration
- Dashboard “dos” and “don’ts”
- Selecting appropriate tools
- Determine chart elements and colour selection
- Histogram
Reporting with PivotTable and PivotChart
- Insert and populate a PivotTable and PivotChart
- Update PivotTable data
- Using Slicer
Advanced Excel tools
- Conditional formatting
- Using Range Names
- Create drop-down list with data validation
Obtain data with Excel functions
- Use Lookup and Reference functions
- Use INDEX and MATCH functions
- Database functions
Create Excel chart
- Create standard charts
- Combination chart
- Sparkline
Macros for spreadsheet automation
Understanding macros
- Uses of macro
- Planning the recording and recording a macro Relative and absolute recording options
- Various methods of executing a macro
- Storing a macro
Modifying existing macros
- Examining the codes in a macro
- Adding comments
- Cleaning up the macro codes
- Testing modified macros
VBA basics
Visual basic editor environment
- The VBE Window
- Using the Project Explorer, Code & Immediate Window
- Creating a new module
- Entering VBA codes
- Customising VBE environment
Learning about objects and property
- Understanding the Excel Object Model
- Using the Object Browser
- Importing and export objects
- Objects referral
- Using the Application, Workbooks, Worksheets & Window objects
- Using the Range, Cells, Offset properties
- Selecting cells, rows and columns
- Using the Value and Formula properties
- Properties for applying cell formatting
VBA programming novice
Variables, data type and constants
- Commenting for programming
- Declaring and Scoping variables
- Defining data type
- Working with Date & String type
- Assignment expressions
- Using logical operators
- Arrays
Procedures, functions and constructs
- Creating Procedures and Scoping a procedure
- Various methods of executing a procedure
- Passing arguments to procedures
- Using built-in, MsgBox InputBox functions
- Constructs for manipulating objects
- With … End, For Each … Next
- Constructs for controlling program execution
- Understanding decision making in VBA
- GoTo, If … Then, Select Case, For … Next loops, Do … While loops, Do … Until loops
VBA programming senior
Error handling
- Error handling techniques
- Using Break Mode, Setting Breakpoints and Trapping Error
Using custom functions
- Creating custom functions for use in worksheet and procedures
- Passing arguments to procedures
- Adding custom functions to Paste Function dialog box
VBA programming graduate
Introducing USERFORMS
- Incorporating UserForm
- Deploying and setting UserForm controls
- Display and close UserForm using VBA
- Deploying UserForm events
- Applications of UserForm
Excel events
- Understanding event sequences
- Using event handlers for workbook, worksheet and charts
Lectures, demonstration and hands-on activities designed to provide practical experiences with skills being taught.
This course is suitable for those in Finance, Sales department and individuals who need to automate task to increase productivity.
Prerequisites
Participants are assumed to:
- Have a good knowledge of Excel
Duration : 2 days (15 hrs)
Time : 9:00am to 5:30pm
With effect from 1 Jan 2024
Type | Individuals | |||
Employer-sponsored Singapore Citizens and Permanent Residents | ||||
Type of Funding | ||||
SkillsFuture Funding (Broad-Based Funding for Non-certifiable courses) | ||||
Course Fee | $680.00 | |||
Less: SkillsFuture Funding | $30.00 | |||
Total Nett Fee | $650.00 | |||
Add: GST @ 9% of Course Fee | $61.20 | |||
Total Fee Payable | $711.20 |
Funding valid till 31 Dec 2024
* Please click HERE for detailed information on general terms and conditions.
* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.
(A course in partnership with James Cook Institute Pte. Ltd. [formerly known as Eagle Infotech] UEN198802365N)
- Use Visual Basic functions like Macros
Dashboard fundamentals
- What is a dashboard
- Key characteristics of a dashboard
Planning considerations
- Define information needs
- Acquire and arrange data
Dashboard design consideration
- Dashboard “dos” and “don’ts”
- Selecting appropriate tools
- Determine chart elements and colour selection
- Histogram
Reporting with PivotTable and PivotChart
- Insert and populate a PivotTable and PivotChart
- Update PivotTable data
- Using Slicer
Advanced Excel tools
- Conditional formatting
- Using Range Names
- Create drop-down list with data validation
Obtain data with Excel functions
- Use Lookup and Reference functions
- Use INDEX and MATCH functions
- Database functions
Create Excel chart
- Create standard charts
- Combination chart
- Sparkline
Macros for spreadsheet automation
Understanding macros
- Uses of macro
- Planning the recording and recording a macro Relative and absolute recording options
- Various methods of executing a macro
- Storing a macro
Modifying existing macros
- Examining the codes in a macro
- Adding comments
- Cleaning up the macro codes
- Testing modified macros
VBA basics
Visual basic editor environment
- The VBE Window
- Using the Project Explorer, Code & Immediate Window
- Creating a new module
- Entering VBA codes
- Customising VBE environment
Learning about objects and property
- Understanding the Excel Object Model
- Using the Object Browser
- Importing and export objects
- Objects referral
- Using the Application, Workbooks, Worksheets & Window objects
- Using the Range, Cells, Offset properties
- Selecting cells, rows and columns
- Using the Value and Formula properties
- Properties for applying cell formatting
VBA programming novice
Variables, data type and constants
- Commenting for programming
- Declaring and Scoping variables
- Defining data type
- Working with Date & String type
- Assignment expressions
- Using logical operators
- Arrays
Procedures, functions and constructs
- Creating Procedures and Scoping a procedure
- Various methods of executing a procedure
- Passing arguments to procedures
- Using built-in, MsgBox InputBox functions
- Constructs for manipulating objects
- With … End, For Each … Next
- Constructs for controlling program execution
- Understanding decision making in VBA
- GoTo, If … Then, Select Case, For … Next loops, Do … While loops, Do … Until loops
VBA programming senior
Error handling
- Error handling techniques
- Using Break Mode, Setting Breakpoints and Trapping Error
Using custom functions
- Creating custom functions for use in worksheet and procedures
- Passing arguments to procedures
- Adding custom functions to Paste Function dialog box
VBA programming graduate
Introducing USERFORMS
- Incorporating UserForm
- Deploying and setting UserForm controls
- Display and close UserForm using VBA
- Deploying UserForm events
- Applications of UserForm
Excel events
- Understanding event sequences
- Using event handlers for workbook, worksheet and charts
Lectures, demonstration and hands-on activities designed to provide practical experiences with skills being taught.
This course is suitable for those in Finance, Sales department and individuals who need to automate task to increase productivity.
Prerequisites
Participants are assumed to:
- Have a good knowledge of Excel
Duration : 2 days (15 hrs)
Time : 9:00am to 5:30pm
With effect from 1 Jan 2024
Type | Individuals | |||
Employer-sponsored Singapore Citizens and Permanent Residents | ||||
Type of Funding | ||||
SkillsFuture Funding (Broad-Based Funding for Non-certifiable courses) | ||||
Course Fee | $680.00 | |||
Less: SkillsFuture Funding | $30.00 | |||
Total Nett Fee | $650.00 | |||
Add: GST @ 9% of Course Fee | $61.20 | |||
Total Fee Payable | $711.20 |
Funding valid till 31 Dec 2024
* Please click HERE for detailed information on general terms and conditions.
* Please click HERE for detailed information on course fee funding schemes, SkillsFuture credit, and complete listing of funded courses. This course is eligible for use of SkillsFuture credit.
(A course in partnership with James Cook Institute Pte. Ltd. [formerly known as Eagle Infotech] UEN198802365N)
Course Application
Details | Registration |
---|---|
January 24, 2024 - January 25, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Closed |
February 22, 2024 - February 23, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Closed |
March 18, 2024 - March 19, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Closed |
April 25, 2024 - April 26, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Closed |
May 20, 2024 - May 21, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Register Now |
June 24, 2024 - June 25, 2024 (9:00 am - 6:00 pm)
[Classroom]
|
Register Now |