Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

Module 1 - Getting Started in VBA

This module will show you how to write your first programs in VBA.

1.1 - Your First Program
1.1.1 - Preparing to Write Code
1.1.2 - Writing a Basic Procedure
1.1.3 - Saving Your Code
1.1.4 - Running a Procedure
1.1.5 - Dealing with Problems

1.2 - Basic Debugging
1.2.1 - Stepping Through Code
1.2.2 - Using Breakpoints

1.3 - Basic User Interfaces
1.3.1 - Worksheet Buttons
1.3.2 - Keyboard Shortcuts
1.3.3 - Shapes as Buttons

1.4 - How VBA Works
1.4.1 - Making Things Happen
1.4.2 - Parameters and Arguments
1.4.3 - Using Parentheses
1.4.4 - Calling Procedures
1.4.5 - Getting Help
1.4.6 - The With Statement
1.4.7 - Recording Macros

Choose what you want to learn from the list of lessons above.

This page provides a brief summary of what you've learned (or will learn) in this module. You can click here to download the example code shown below.

Your First Program

Preparing to Write Code

You can open the Visual Basic Editor (VBE) from within an Excel workbook.

Ribbon	Keyboard
Developer \| Visual Basic	Alt + F11

You can create a container for your code by inserting a module into the VBA project.

Menu	Keyboard	Right-click
Insert \| Module	Alt then I then M	Insert \| Module

Writing a Basic Procedure

You can begin a new procedure by writing sub followed by a name and pressing Enter.

Sub MyFirstSubroutine()

End Sub

You can add comments to your code by typing ' (an apostrophe) followed by any text.

'this is a comment

Saving Your Code

You should save a workbook which contains VBA code using the .xlsm or .xlsb file type:

Choose the file type when you save the workbook.

Running a Procedure

You can use several options in the VBE to run a subroutine from start to finish.

Menu	Keyboard	Toolbar
Run \| Run Sub/UserForm	F5

Basic Debugging

Stepping Through Code

You can use several options in the VBE to step through a subroutine.

Menu	Keyboard	Toolbar
Debug \| Step Into	F8

Using Breakpoints

You can toggle a breakpoint on a line of code.

Menu	Keyboard	Toolbar
Debug \| Toggle Breakpoint	F9

Basic User Interfaces

Worksheet Buttons

You can draw a button on a worksheet using a tool on the Developer tab of the Excel ribbon:

Choose Developer | Insert | Button (Form Control) to add a button to a worksheet.

Shapes as Buttons

You can assign a macro to a shape or other drawing object on a worksheet:

Right-click on the shape and choose Assign Macro...

How VBA Works

Making Things Happen

The VBA language has four main building blocks: Objects, Collections, Methods and Properties.

You can manipulate an object by applying a method to it.

'Object.Method

Range("A1").Select

You can manipulate an object by altering one of its properties.

'Object.Property = SomeValue

ActiveCell.Value = "Wise Owl"

Parameters and Arguments

Some methods and properties have parameters to which you can pass arguments.

ActiveCell.Copy Range("B2")

You can write the name of the parameter before the value you pass to it.

ActiveCell.Copy Destination:=Range("B2")

If you pass multiple arguments to a method or property, use commas to separate each one.

ThisWorkbook.PrintOut From:=1, To:=2, Copies:=5

Using Parentheses

You must put arguments in parentheses if you want to apply another method or property to the result.

Cells.Find("Wise Owl").Select

You must put arguments in parentheses if you're using the result of a method or property to set the value of something.

ActiveCell.Font.Color = RGB(12, 34, 56)

Don't put arguments in parentheses when you're not making use of the result of a method or property.

ThisWorkbook.PrintOut 1, 2, 5

Calling Procedures

You can call one procedure from another by writing the name of the procedure you want to call.

Sub MainRoutine()

'do something useful

ProcedureToCall

'do something else

End Sub

Sub ProcedureToCall()

'do something useful

End Sub

You can optionally add the Call keyword before the name of the procedure you are calling.

Sub MainRoutine()

'do something useful

Call ProcedureToCall

'do something else

End Sub

You can state that a procedure is Public so that it can be called from any module in the project.

Public Sub CallMeFromAnyModule()

End Sub

You can state that a procedure is Private so that it can be called only within its own module.

Private Sub CallMeFromThisModuleOnly()

End Sub

Getting Help

You can get help using the Object Browser.

Menu	Keyboard	Toolbar
View \| Object Browser	F2

You can view help online.

Menu	Keyboard	Toolbar
Help \| Microsoft Visual Basic for Applications Help	F1

The With Statement

You can use a With statement to apply multiple methods and properties to the same object.

With ActiveCell.Font

.Color = rgbRed

.Size = 12

.Name = "Arial"

End With

Recording Macros

You can record a macro in Excel to make the VBE write the VBA instructions for the actions that you perform.

Ribbon	Keyboard
Developer \| Record Macro	Alt then L then R

This page contains reference material for the code used in this module.

Rules for Naming Things in VBA

There are several rules you must follow when assigning a name to something in VBA:

Rule
The first character must be a letter.
Spaces and many of the punctuation and symbol characters are not allowed.
The maximum length is 255 characters for most things but only 31 characters for modules.
You can't have duplicate names in the same scope, e.g. every module in a single project must have a unique name and every procedure in a single module must have a unique name.
Many VBA keywords can't be used to name things.

Naming Conventions

There are several conventions you could choose to use when naming things in VBA:

Convention	Description	Example
Pascal Case	Capitalise the first letter of every word in the name.	ThisIsPascalCase
Camel Case	Capitalise the first letter of every word except the first one.	thisIsCamelCase
Underscores	Separate words using the underscore character.	This_Uses_Underscores

This module doesn't have any exercises.

There is currently no test for this module.

This page has 0 threads

Add a new post

MODULES

LESSONS

Your First Program

Preparing to Write Code

Writing a Basic Procedure

Saving Your Code

Running a Procedure

Basic Debugging

Stepping Through Code

Using Breakpoints

Basic User Interfaces

Worksheet Buttons

Shapes as Buttons

How VBA Works

Making Things Happen

Parameters and Arguments

Using Parentheses

Calling Procedures

Getting Help

The With Statement

Recording Macros

Rules for Naming Things in VBA

Naming Conventions

Head office

London

Manchester