Excel VBA Course Essentials

VBA macros are powerful procedures that automate manual work in Excel.

Learning VBA in Excel is like learning a foreign language, you not only need to learn the vocabulary for most conversations, but also the grammar and how it is cleanly and efficiently structured. So if you want to learn VBA in Excel, you should already have a strong working knowledge of Excel and be prepared to write computer code.

So there are no intermediate or advanced level courses as once you have mastered the essentials, the rest of the learning will likely be determined by the needs of whatever project is being worked on. As VBA is a programming language it is safe to assume that you can get it to do anything in Excel. So I offer a bare bones essential course that covers around 80% of what most people will want, and without going into unnecessary depth – you can do that later if your project requires it.

So here are the main topics for the essential course that, dependant upon the delegates, would typically cover two or three days of hands-on development training…

Introduction

  • Adding the developer menu and toolbar
  • The VBA development environment – modules, windows
  • Creating a simple hand-written macro
  • Checking the macro settings in Options

Objects, Properties and Methods

  • What are these three things?
  • The dot notation with objects and properties
  • Typical and common Methods
  • The Range property,  Offset property & worksheets object

Recording and running a macro

  • Recording using the recorder and stripping out unwanted code
  • Saving the workbook as macro-enabled
  • Creating a simple run button or run object on the worksheet

Essential navigation & management

  • How and why not to use cell references and sheet names in code
  • Passwording the VBA code and even using external software to make it uncrackable
  • Hiding sheets in VBA in such a way that the user cannot unhide them
  • Considering the passwording of the workbook

Variables

  • The importance of defining variables using Dim and Public
  • Types of variable and the amount of memory used for each
  • Variable names and where to place the definitions
  • Doing mathematics with variables

Strings

  • Manipulating text strings with string functions

Using conditional logic

  • IF.. ELSEIF.. ELSE .. END IF
  • SELECT CASE.. CASE.. END SELECT
  • WITH.. END WITH

Looping

  • For…Next
  • Do…While
  • Do…Until

Basic user input and result display

  • INPUTBOX
  • MSGBOX

Programming array variables

  • Defining arrays and filling them
  • Multidimensional arrays

Subroutines

  • Calling a subroutine from another
  • Passing values between subroutines

Using Excel functions

  • Using the application functions in VBA
  • Some unique VBA functions

Error catching

  • ONERROR
  • GOTO
  • RESUME NEXT

Userforms (aka ‘dialog boxes’)

  • Displaying, hiding and closing a userform
  • Creating userform objects like;-
    • Text boxes, pick-lists, list boxes, buttons
  • Organising the objects
  • Naming each object appropriately
  • Data operations – reading, checking and writing data