Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
550 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
This module will show you how to write your first programs in VBA.
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 |
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.
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 |
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
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.
You can use several options in the VBE to run a subroutine from start to finish.
Menu | Keyboard | Toolbar |
---|---|---|
Run | Run Sub/UserForm | F5 |
You can use several options in the VBE to step through a subroutine.
Menu | Keyboard | Toolbar |
---|---|---|
Debug | Step Into | F8 |
You can toggle a breakpoint on a line of code.
Menu | Keyboard | Toolbar |
---|---|---|
Debug | Toggle Breakpoint | F9 |
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.
You can assign a macro to a shape or other drawing object on a worksheet:
Right-click on the shape and choose Assign Macro...
The VBA language has four main building blocks: Objects, Collections, Methods and Properties.
'Object.Method
Range("A1").Select
'Object.Property = SomeValue
ActiveCell.Value = "Wise Owl"
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
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
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
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 |
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
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.
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. |
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.
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.