Microsoft Excel  - VBA for ExcelDownload Course Outline

This is the outline of our Microsoft Excel VBA course - Duration: 2 days

Visual Basic for Applications (VBA) is an event driven programming language with an associated integrated development environment (IDE) which is built into most Microsoft Office applications including Microsoft Excel. This course introduces you to the basic concepts that underlie this programming language and how it can be used to control aspects of Excel.  You will start on the journey of writing your own procedures to manipulate user interface features, such as menus and toolbars, and working with custom user forms or dialog boxes.

To get the most out of this course you don’t need any previous experience of VBA or writing computer programs but you should be a competent Microsoft Excel user.

We specialise on on-site training and can come to you to run our course either on your PC's or provide our own high specification equipment. 

Microsoft Excel  - VBA for Excel course content

Download a PDF course outline using the link on the right.

An Introduction to Macros

Record an Excel Macro.

Run Macros from the Macro Window, Custom Toolbars and Menus.

Using the Integrated Development Environment (IDE)

  • Correctly use the Project Explorer to locate Class or Standard Modules.
  • Use the Properties Pane to change object properties.
  • Use the Code Window to write your procedures.

Discovering Excel Objects

  • Understand how to reference the components of the Excel application; workbooks, worksheets, ranges.
  • Browse Excel Objects in the Excel Object Library.

Working with Methods and Properties

  • Change aspects of objects by correctly assigning Properties.
  • Manipulate the behaviour of objects by correctly assigning Methods

Creating User Forms

  • Create a User Form to show information to users.
  • Create a User Form to automatically insert user information into in a spreadsheet.

 

 

Constructing Code

  • Use “If Then”, “If Then Else” and “If Then ElseIf” decision structures in your procedures.
  • Use Variables to hold values to be used by the procedure.
  • Use Select Case statements for complex decision structures.
  • Use For…Next…Loops to execute code  repeatedly until conditionally stopped.
  • Use On Error statements to control errors in your code.
  • Step through your code to find bugs.

Using the MsgBox Function

  • Display user messages that respond to user actions.
  • Display user messages to prompt the user for an action e.g. “Yes/No” Message Boxes.

Using the InputBox Function

  • Collect user information to which the procedure can respond.

Responding to Events

  • Write procedures to respond to workbook and worksheet events

Using Standard Modules

  • Use Standard Modules to store standard procedures.
  • Call procedures from Standard Modules.
  • Create your own Function Procedures