Module 1 - Getting Started in VBA

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:

Saving file

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:

Adding button

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:

Assign macro

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 post