Vba code examples from the course

Here’s a collection of handy code examples like those used in the macros glossary and on our Excel VBA macros course. We’re pretty sure some of them will remain useful to you – that’s why we’ve collected them up here.

Copy and paste

This macro copies and pastes a cell:
Sub CopyMacro()
'This macro copies the value in cell A1 and then pastes it into cell B1
'The critical first line here is the one containing the code: ActiveCell.Copy
'Before you try running the macro enter something like "X" into A1 so you can test that the macro works
'Select cell A1
'This is the critical line: copy the active cell
'Select cell B1
'Paste the copied cells as values only
ActiveCell.PasteSpecial (xlPasteValues)
'Turn off paste special
Application.CutCopyMode = False
End Sub

Here is a version of the code simplified:
This macro copies and pastes a cell:
Sub CopyMacro()
ActiveCell.PasteSpecial (xlPasteValues)
Application.CutCopyMode = False
End Sub

Here is another version of the code simplified still further:
Sub CopyMacro()
Range("A1").Copy Range("B1")
End Sub

The above is an abbreviated version of code available from Excel VBA help (“Alt” and “F11” to open the VBA editor, and then “F1” to open VBA help, followed by searching for “copy method”):

If you look under “Select” and find out how to select a range of cells you should be able to work out how to adapt the above code to copy and paste a larger range of cells:
Sub CopyMacro()
Range("A1:A5").Copy Range("B1")
End Sub

Here’s how you could copy and paste a column of data of unknown length (in just one line of code):
Sub CopyMacro()
Range("A1", Range("A1").End(xlDown)).Copy Range("B1")
End Sub

Under “Select a cell…” below we explain a bit more about what’s going on in the above code and how we created it. In summary, the code is applying a regular keyboard shortcut (“Ctrl” “Shift” and then the down arrow) to select a column of cells in VBA.

Declare a variable

You declare variables like this at the top of your macro:
Dim YourVariableName as Double

Frequently used variable types are double (= a number with a decimal point), string (= a text string) and variant (gives Excel the job of working out what variable type to use but uses a bit more memory).

Here we show you how to define a variable as being equal to the value of the active cell. Also, how to output a variable to the active cell. It takes a different route to solving the same copy and paste problem above:
Sub VariablesExample()
'This macro shows you how to assign the value of the active cell to a variable
'Also, it shows you how to output the a variable to the active cell
'You need a value in cell A1 (e.g. you could try placing the letter X in A1) to see it working
'Establish the variable MyVariable
Dim MyVariable As Variant
'Select cell A1
'Define MyVariable as being equal to the value of the active cell
MyVariable = ActiveCell
'Now you could manipulate MyVariable if you want e.g. add 1 - see the next comment below for an example
'MyVariable = MyVariable + 1
'Select cell A2
ActiveCell = MyVariable
End Sub

Here is the above example simplified further:
Sub VariablesExample()
Dim MyVariable As Variant
MyVariable = Range("A1")
Range("A2") = MyVariable
End Sub

If you want your variables ‘remembered’ by Excel and available to any other macros in your spreadsheet, you need to declare them absolutely right at the very top above anything else above the code Sub YourMacroName() and you need to declare them as public variables:
Public YourVariableName as Double

GoTo a location

GoTo a location in your macro:
'Creates a location early on in your code - note the colon
'Go to that location from a later point in your code
GoTo YourLocationName

If then else statements

Does the same job as Excel’s regular if function, but it’s the VBA version we’re looking at here. Here is an example:
Sub IfThenElseStatement()
'This macro demonstrates an If Then Else statement
'Establish the variable Result
Dim Result As String
'Display input box to collect up Result
Result = InputBox("See whether you can enter 'x' (lower case) below:")
'Start If statement
If Result = "x" Then
'This is what happens if the condition is met
MsgBox ("Well done, you got it right!")
'This is what happens if the condition is not met
MsgBox ("You got it wrong")
'End the if statement
End If
End Sub

You don’t always need the “Else” part depending on what you are trying to achieve (it’s not compulsory).

Input boxes

Collect up inputs for use in your macro. For the code you need see the previous “If then else” example.

Loop to the bottom of a column of data

The do until loop:
'Start loop which keeps working until it reaches an empty cell
Do Until IsEmpty(ActiveCell)
'Comment: insert or separately record code that manipulates data and place it here
'Step down one row in column one
ActiveCell.Offset(1, 0).Select
'End loop

The do while loop:
'Start loop which works while it can find a non-blank cell
Do While ActiveCell.value <> ""
'Insert your code that manipulates data the way you want and place it here
'Step down one cell
ActiveCell.Offset(1, 0).Select
'End loop

The for each loop:
'Tells Excel which cells to look at
For Each MyCell In Range("A:A")
'Sets the condition - repeats for all cells that meet the condition - in this case it works on each non-blank cell
If MyCell <> 0 Then
'Insert your code here
End If

Message box

Here is an example of a simple message box:
Sub MessageBox()
MsgBox ("Macros are cool")
End Sub

Note for this very simple message box you could leave out the brackets and it would still work:
Sub EvenSimplerMessageBox()
MsgBox "Macros are cool"
End Sub

Here is an example that establishes variables and combines an input box with a message box to display a result:
Sub InputAndMessageBoxCombined()
'Establish variable
Dim YourName As String
'Use input box to collect the variable YourName
YourName = InputBox("What's your name?")
'Use message box to display the variable YourName
'Note the brackets in the simple message box below are optional - it will work without them
MsgBox ("Your name is " & YourName)
End Sub

Here is a more complex example that adds yes and no buttons:
Sub YesNoMessageBox()
'Establish variable
Dim Answer As Double
'Create message box with yes/ no answer
Answer = MsgBox("Do you think Macros are cool? ", vbYesNo)
'Start If statement
If Answer = vbYes Then
'If condition is met, display message box
MsgBox ("We agree: they're great!")
'If condition is not met, display this message box
MsgBox ("Honestly, they're really great")
'End if statement
End If
End Sub

When things get more complex than the above you’ll want to start creating your own custom user forms.

Select a cell or a range of cells

Select the active cell:
You could create this code by setting the macro recorder to run, selecting relative references, and clicking on any cell in the spreadsheet.

Select cell A1:
You can create this code with the macro recorder by not depressing the relative references button and clicking on cell A1.

Select a range of cells e.g. A1:A5:
Create this code by using the macro recorder, not hitting the relative references button and selecting cells A1 through A5 in your Excel spreadsheet.

Select to the bottom of a column of data in Excel:
Range(Selection, Selection.End(xlDown)).Select

To create this use the macro recorder with relative references off, select cell A1 and make use of the Excel keyboard shortcut for selecting to the bottom of a column of cells in a spreadsheet (“Ctrl” “Shift” followed by the down arrow on your keyboard).

This simplified code would do the same job for you:
Range("A1", Range("A1").End(xlDown)).Select

Set a property for the active cell

This example changes the font of the active cell to bold:
ActiveCell.Font.Bold = True

Use a worksheet function to sum a range of cells

Sub AddCells()
'This macro adds the contents of column A and places the result in cell B1
'To see the macro working, place some dummy values say in cells A1 through A5
'First we declare a variable
Dim CellsToAdd As Variant
'Assign the cells in column A to the variable CellsToAdd
CellsToAdd = Range("A1", Range("A1").End(xlDown))
'Sum the CellsToAdd and output the result in cell B1
Range("B1") = WorksheetFunction.Sum(CellsToAdd)
End Sub

The variables used above perhaps make it clearer what’s going on but this piece of abbreviated code would achieve the very same job for you. It adds all the data in a column no matter how long the list is.
Sub AddCells()
Range("B1") = WorksheetFunction.Sum(Range("A1", Range("A1").End(xlDown)))
End Sub

As an alternative, this code creates variables and uses a loop to solve the same kind of problem:
Sub AddCellsMacroWithLoop()
'This macro loops through a list keeping and outputting a running total
'Enter values into column A to see it at work
'Establish variables
Dim SubTotal As Double
Dim CellContents As Double
'Select cell A1
'Start loop, which later stops when an empty cell is reached.
Do Until IsEmpty(ActiveCell)
'Define CellContents as being equal to the value of the active cell
CellContents = ActiveCell.Value
'Take the value of the active cell (CellContents) and add it to the subtotal
SubTotal = SubTotal + CellContents
'Step down 1 row in column 1
ActiveCell.Offset(1, 0).Select
'End loop
'Output the result into cell B1
Range("B1") = SubTotal
'Message box - just for fun - it can be deleted
MsgBox ("The total is " & SubTotal)
'Select cell A1, taking you back to the top left of the spreadsheet
End Sub

User forms

Get a user form working and popping up:

Make something happen if, for example, an option button on your user form is selected:
If UserForm1.OptionButton1.Value = True Then
MsgBox ("Display a messagebox with this text in it")
End If

With statements

With statements just allow you to reduce the amount of code you need to type. The example below changes four font settings for the active cell. Everything between the lines starting “With” and “End With” is applied to the active cell:
Sub WithEndWith()
'This macro changes the font settings of the active cell
With ActiveCell.Font
.Bold = True
.Name = "Arial"
.Size = 10
.Italic = True
End With
End Sub

About this online Excel course material

This material isn’t really designed to be read! It’s supposed to remain as a compact resource for you once you’ve worked through our free online course covering Excel VBA macros.

3 comments so far:

...join the conversation...

Your email address will not be published. Required fields are marked *

  1. Until I have got myself an Excel VBA reference book, I shall keep these notes at my elbow.

  2. Very good. Thanks a lot.

    • Ian we would recommend one of John Walkenbach’s VBA books as the next step beyond what you’ve got here.

      That, combined with some of your own practising and a bit of internet surfing for other code snippets, will help you from here.