Training

Excel & Visual Basic Training – Brisbane

There’s loads of Excel and Visual Basic training available out there – most of which will have you sitting for days going through generic information that is not applicable to your workplace.

There is an alternative! You can have Advanced Excel Training and Visual Basic Training specifically designed and tailored for you and your business – to match your needs and your budget.

Learn the tips and techniques the Excel experts use to reduce the time spent in preparing Excel reports, while also increasing data quality and accuracy. And all of our training is delivered by a qualified VBA consultant and trainer, with over a decade of experience in the industry.

We Deliver Excel & VBA Training that is:

  • Customised to your skill level
  • Within your budget and time constraints
  • Delivered on-site at your workplace which reduces lost travel time

And we offer a range of advanced Excel training solutions including one-on-one training, structured classroom training and individual mentoring.

Choose from a range of topics including:

  • Excel Training Modules
  • Visual Basic Training Modules

 

Excel Training Modules

Foundation Skills in Excel

  • Fundamentals of spread sheet layout
  • Fundamentals of workbook structure
  • Importance of data integrity
  • Tips and tricks to ensure data integrity
  • Simplifying data management
  • Using formulas
  • Using named ranges

 

Excel Formula Construction

  • Review of core functions and formulas available
  • Determining when to use which formula
  • Audit of existing spread sheets
  • Improving current formulas in the workplace

 

Analytical Techniques

  • Use the VLOOKUP feature to combine data from different sources
  • Use conditional formatting to highlight data automatically and have it stand out
  • Manipulating imported data for improved presentation
  • Working with data sources outside of Excel

 

Using Pivot Tables

  • Introduction to Pivot Tables
  • How to create a Pivot Table
  • Fundamentals of Pivot Table design
  • Using Pivot Tables to create dynamic views of data
  • Profiling data using Pivot Table techniques
  • Manipulating a Pivot Table using VBA code
  • Use Pivot tables to view and profile your data

 

Improving Data Input and Validation

  • Fundamentals of minimising data entry and human error
  • Introduction to validation controls
  • Creating custom warning and data entry messages
  • Using drop down lists to avoid manual keying in of data
  • Setting up checks and balances to prevent data error

 

Visual Basic Training Modules

 

Introduction to Visual Basic Applications (VBA)

  • Recording a macro
  • Editing a macro in Visual Basic Editor
  • Orientation inside the editor
  • Setting up a sub routine to call another routine

 

Running/triggering VBA Macros

  • Linking your macro to a button within your workbook
  • Creating a custom shortcut key to call your macro
  • Placing a custom icon on the ribbon to call your macro
  • Setting up Visual Basic to watch your workbook for certain events
  • Sharing your macro with others
  • Macro security
  • Visual Basic reference libraries
  • Common debugging issues

 

Constructing Your Own User Defined Function

  • Introduction to user defined functions
  • When and why use a user defined function
  • How to write a custom function
  • How to share a custom function
  • How to use a custom function
  • Tips, tricks and traps of user defined functions

 

VBA Code Writing

  • Basic programming concepts
  • Defining a variable in code
  • Structure of sub routines and functions
  • Calling code within code
  • How to introduce inputs into code
  • Returning results from code
  • How to use logic statements like the IF function, Do loops and For loops
  • Using the Select Case function

 

VBA Variables

  • An introduction to variable types, context and limitations
  • Selecting the appropriate type for the data to be stored
  • Using Option Explicit to ensure variables are explicitly declared
  • Using references to interact with variables
  • Using values to interact with variables
  • Understanding arrays

 

Using Code To Interact With Other Sources Of Data

  • Reading text files within code for output in Excel
  • Creating a link to a web data source
  • Getting data from another application
  • Using SQL code within VBA to interact with any SQL enabled data sources
  • Using Excel to interact with MS Access
  • Creating a link with another Excel workbook

 

Using VBA To Enhance Excel

  • Using named ranges within a macro to refer to a set of cells
  • Create a custom format to be applied to various worksheets
  • Breaking down strings of alphanumeric data within VBA
  • Manipulate Excel based filters in VBA
  • Creating advanced sorting and filtering criteria in VBA
  • Enhance Pivot Tables using VBA

 

Using Custom Forms Within VBA

  • Introduction to custom forms
  • The when and why of using custom forms
  • How to design a custom form
  • Adding various input and control fields to a form
  • Setting up field properties to ensure form runs as expected
  • Writing VBA code to populate options within the combo box
  • Attaching code to a command button on a form
  • Setting up spinner controls in VBA
  • How to trigger a custom form
  • Using code to handle events triggered by forms
  • Hiding and/or properly closing a form

 

Debugging And Error Handling Within VBA

  • Introduction to common error types
  • Setting up an error handler using the On Error statement
  • Using an error handler to terminate code
  • Using an error handler to recover code
  • Getting more information about the error using the Err object
  • Returning an error message to user
  • Returning an error message to programmer only

 

VBA Run Time Communication Techniques

  • Using MsgBox functions to inform user of progress through code
  • Receiving input from user during code execution
  • Responding to input from a MsgBox or InputBox
  • Validating InputBox entries
  • Handling a cancel event mid code