VBA Excel Course Singapore - VBA Excel 2010/2013

Course details

• The 3-day VBA Excel course in Singapore guides participants in using some of Excel's commonly used formulas. Participant alsolearns how to simplify your work in the Excel environment by automating many of the repetitive tasks that are part ofdatabase development.

Prerequisites:

• Prior knowledge for Excel 2007/2010/2013 and especially suitable for students who had attended Excel 2007/2010/2013 Intermediate and Advance course.

Certificate Of Attendance:

• Certificate Of Attendance will be awarded to trainees completing the course achieving minimum 75% attendance.

Course Outline:

1. Introduction to VBA in Microsoft Excel 2010/2013

• Introduction to Visual Basic for Application (VBA)
• Installing Visual Basic Editor for Excel 20010/2013
• Setting up the Visual Basic Editor in Excel (VBE)
• The VBE Menu and ToolBar
• The VBE Object Browser
• VBE Options
• The Three Windows in the Visual Basic Editor
• The Project Window
• The Properties Window

2. The Macro Recorder in Excel 2010/2013

• Recording Your First New Macro
• Running your first recorded macro
• Looking at your first recorded macro
• Modifying a Macro in Excel

3. Starting, Triggering a Macro in Excel 2010/2013

• Preparing the Exercise on Events
• Macros Triggered from the Developer Ribbon
• Macros Triggered by a Keystroke
• Macros Triggered by Clicking on a Text Box on the Worksheet

4. :VBA Variables

• Datatypes & VBA Variables
• Static, Constants & Arrays
• Enumeration Datatype
• Scope of VBA Variables
• Strings and String Manipulations

5. Working with Procedures and Functions

• Understanding Proceures and Modules
• Creating a Standard Module
• Creating Procedures and Functions
• Understanding the Local and Global Scope
• Passing Arguments to Procedures and Functions
• Using Instrinsic Functions.

6. Conditions, Branches and Looping Control

• Using the If..Then..Else Statement
• Nested If Statements
• Case Statement and Choose Statement.
• For Loop and For Each Loop
• The Do While, Do Until, Do…While & Do… Until Loops

7. Debugging in VBA Excel

• Testing & Debugging Step-by-Step
• Break Points
• Immediate Window
• Watch Window and Add Watch
• Local Windows

8. Error Handling

• Introduction to Error types
• Handling Errors
• The Error Object
• Debugging and Debug Print

9. Excel Object Model

• VBA for Excel for Workbooks
• VBA for Excel for Worksheets
• Cells, Ranges, Columns and Rows in VBA for Excel
• Message and Input Boxes (MsgBox, InputBox) in Excel

10. Userforms and Controls

• Creating a Userform in Excel
• Text Boxes in VBA for Excel
• Command Buttons in VBA for Excel
• Combo Boxes in VBA for Excel
• List Boxes in VBA for Excel
• Option Buttons, Check Boxes and Frames
• Excel Spin Buttons
• Excel Image Controls

11. VBA Macros Security and Protection in Excel

• Password Protecting the code
• Hiding and very hiding Worksheets
• Protecting the Sheet
• Protecting the Workbook
• Password Protecting a single VBA Procedure