MS Excel VBA

Duration: One Day

Interested in this course?

Complete the form and let us know your preferred training date. Our team will contact you to discuss your requirements and arrange a suitable course schedule.

Whether you're booking for yourself or a team, we'll work with you to find a convenient training date.

Prefer to get in touch directly? You can also email us at forrest@forresttraining.com.au, and we'll be happy to assist you.

    Target Audience & Purpose

    Excel VBA training is for highly skilled developers who wish to add high-end automation.

    Objectives

    By the end of this course participants should be able to:

    • Work with the VBA editor
    • Understand VBA concepts and features
    • Create procedures and sub routines
    • Work with variables
    • Work with Excel objects
    • Use standard programming techniques and concepts
    • Create custom forms
    • Program custom forms
    • Make macros start automatically

    Course Inclusions

    • Comprehensive learning materials incl. exercise files
    • Post course telephone support
    • ‘Certificate of Attendance’ on completion of course
    • Morning & afternoon tea

    Course Prerequisites

    This course assumes a sound knowledge of the standard features of Excel covered in the Introduction and Advanced courses.

    MS Excel VBA Course Content

    Download Exercise Files

    Getting Started
    What is VBA, Open the VBA Editor, Create a Module, Create A New Subroutine, Add Commands, Run the Subroutine, Save the File

    Security
    Open a File Containing Code, Create a Digital Signature, Digitally Self-Sign a File, Add the Publisher to the Trusted List, Install Certificates, Remove Trusted Publishers

    Using the VBA Editor
    Run Code in the VBA Window, Step Through Code, Step Over Code, Set a Break Point, Comment Code, IntelliSense and AutoComplete, Getting Help, The Project Explorer and Properties Window

    Variables
    What is a Variable, Naming Variables, Store a Value in a Variable, Why Should I Declare Variables, Procedure-Level Declarations, Module-Level Declarations, Global-Level Declarations, Static Declarations, Option Explicit, Declaring Data Types, Recognise Errors from Declaring Variables, Discover the Current Value of a Variable

    Interactions With The User
    Interacting with the User, MsgBox as a Method, MsgBox as a Function, Message Box using Numerical Constants, The InputBox Function, Long Messages

    Making Decisions
    A Note On Indenting, Line If, Block If, ElseIf and Else, Select Case

    Looping
    For/Next Iterations, For/Next Stepped Iterations, Do/Loop With Initial Test, Do/Loop with Final Test

    Error Handling
    Run-Time Errors, Add a Simple Stop, The Err Object, Exit Sub, Error Handling with Another Sub, Resuming

    The Personal Macro Workbook
    Course Prerequisite, Record in the Personal Macro Workbook, Saving the Personal Macro Workbook, Use a Macro from the Personal Macro Workbook, Edit a Personal Macro Workbook Macro, Locate the Personal Macro Workbook

    Excel Objects
    The Object Hierarchy, Locate an Object, Properties and Methods, The Application Object, The Workbooks Object, ActiveWorkbook and ThisWorkbook, Sheets, Worksheets and Charts, The Worksheets Object, Simple Range Objects, More Complex Range Objects, Adding Higher Hierarchical Levels

    Common VBA Techniques
    Copy and Paste, Paste Special, Offset, Insert Calculations in a Cell, Autofill, Add Rows and Columns, Delete Rows and Columns, Convert Data Types

    User Functions
    Functions, Single Input Functions, Call the Function from Another Procedure, Use the Function on the Spreadsheet, Multiple Input Functions, Use the Multiple Input Function, The WorksheetFunction Objects

    User Forms
    Insert a UserForm, The Project Explorer and Properties Window, The Final Product, Create TextBoxes and ComboBoxes, Align and Resize the Controls, Create Labels and use ControlTipText, Create Command Buttons, Code the Close Buttons, Populate the Combo Box List, Create a Macro to open the UserForm, Create a Button on the Spreadsheet, Automatically Load a Form

    Connect to External Databases
    ODBC and VBA, Some General Considerations, How Does ADO Work, Setting ADO References, Connecting to the Database, Populate ADO Recordsets, Locate a Record using ADO, BOF and EOF, Filter and Count the Records, Add a Record, Modify a Record, Delete a Record, Close the Connection and Recordset, Attaching an SQL Database, Using VBA with a Local SQL Database