VBA in Excel Basics – Quick Tutorial

If you use Excel (everyone uses Excel) and you don’t yet understand Macros and VBA for Excel STOP! Don’t click away, this is really simple and quick so read on…

VBA is a scripting language baked into Microsoft Office applications which enables you to program them. VBA stands for Visual Basic for Applications.

VBA enables you to record Macros (repetitive tasks). It also gives you a way to program individual scripts for use within Office. Here I will concentrate on the main app – EXCEL.

SAVE AS .XLSM

Open a new workbook in Excel and save as .xlsm, which enables macros to be saved into Excel files.

Developer Tab

Make sure the developer tab is present on the ribbon. If not, click the office button, top left and click ‘Excel Options’ button – popular – and select ‘Show developer tab in the ribbon.

Trust Center Settings

Click Office button – Excel Options – Trust Centre – Trust Center Settings – Macro Settings – Disable all Macros with Notification.

PRESS ALT + F11 TO ACCESS THE CODE EDITING SECTION OF EXCEL

Create your first Macro

  • Make sure the ‘Project’ window is open (Click ‘View’ and ‘Project Explorer’ if not).
  • In the worksheet press ‘Record Macro’ (There is also a button on the bottom left that does this)
  • Type a name for the Macro and click ‘OK’ (Module appears in the Project Explorer Window)
  • Clicking ‘Module 1’ brings up the code:
Sub Macro1()
'
' Macro1 Macro
'

'
End Sub

This is where the Macro code will appear once you start typing into the worksheet.

  • Click in Cell A1 shows this code:
Range("A1").Select
  • and typing ‘hello’ in Cell A1 shows:
ActiveCell.FormulaR1C1 = "hello"

Note: R1C1, as opposed to the familiar ‘A1’ style is a different way of referencing cells. This explains it clearly.

  • As you can see it’s just recording your actions and turning it into code. So carry on and record some actions like typing words and numbers into cells, formatting to bold, cope and paste into another cell range.
  • When done press ‘Stop Recording’.
  • You can then clear the screen, press ‘View Macros’ button in the ribbon, select the Macro that you just made and it will recreate every action that you recorded. How powerful is that?

Run VBA code direct from VBA editor

  • Put cursor anywhere inside the body of the Macro
  • Press the ‘Play’ button and the Macro gets played.

‘STEP INTO’ – RunNING VBA code line by line

  • Select – Developer Tab – Macros – Select Macro name – instead of selecting ‘Run’, Select ‘Step Into’.
  • Now once in the editor, press F8 to Step Into. The line that is highlighted is the next line that is going to run. Keep pressing ‘F8’ to see the Macro running line by line.

The DEBUG menu item shows other tasks, Step Over, Step Out, Run to Cursor

‘run to cursor’

  • Once ‘Step Into’ has been selected, Place the cursor in the code and select ‘Run to Cursor’. This runs the code up to where the cursor is.
  • You can press ‘Stop’ to reset the running of the Macro, or ‘Run Sub’ to resume running the code to the end.

Comments

Note, you can comment within the editor using an apostrophe “‘”:

' this is a single line comment

VARIABLES & NUMBERS – VBA – EXCEL

Open up the Editor and click ‘Insert’ – Module’. Type this:

Option Explicit (forces you to declare variable types)
sub Mysub (creates a new sub (short for sub-routine) and gives it a name)

Click enter and the editor fills in to make:

Option Explicit
sub mySub()
End sub

You will write the body of the Macro inside the sub. You’ll be able to select and play the Macro within Excel.

Here, we declare a variable ‘myInt’ (Dim maybe short for Dimension), told Excel that it is of type integer and given it a value:

Sub mySub()
 Dim myInt As Integer
 myInt = 1
End Sub

Press ‘Run Sub’ or even better press F5 to PLAY. The code will run (you just wont see anyting yet).

type – Integer

Integers are a type that is limited to numbers ranging -32768 to +32767. If you type a number lower or higher than this it will show an overflow error. The number will be too low or high to be stored as this type. Integers can store 2 bytes. You cannot save decimals. 0.5 will be truncated to 0.

type – long

Uses more memory by taking 4 bytes and can store a range of -2147483648 to 2147483647.

type – double

Uses even more memory but can store numbers of -10 308  to +10 308 but you may lose precision working close to the max. DOUBLE can also store decimal numbers.

Here are the examples so far:

Sub MySub()
 Dim myInt As Integer
 myInt = 2
 
 Dim myLong As Long
 myLong = 2147483647
 
 Dim myDouble As Double
 myDouble = 0.5
End Sub

Immediate window

Click ‘View’ ‘Immediate Window’ to view the immediate window. It is a section that runs along the bottom of the editor. We can type code that will ‘print’ information to the window:

Sub MySubInt()
 Dim myInt As Integer
 myInt = 2
 Debug.Print myInt
End Sub

The Immediate Window will show ‘2’.

Type a new Sub with the code here:

Sub mySubInt()
 Dim x As Integer, y As Integer
 x = 5
 y = 10
 Debug.Print x + y
End Sub

The above code shows the declaring of two variables and there values, as well as some maths being printed to the Immediate Window, with the result as ’15’.

We can also type code in the immediate window by starting it with a question mark:

?UCASE("hello")
HELLO

 


type – string

Declare a variable of type string and within double quotation marks, holds not numbers but characters:

Sub mySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print myString
End Sub

' prints hello world

string concatenation

Join strings together using the ampersand symbol &:

Sub mySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print myString & " concatenation"
End Sub
' prints hello world concatenation

ucase() function – convert string to uppercase

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print UCase(myString)
End Sub
' prints HELLO WORLD

This calls the function ‘UCase’ and passes the string that you want to change case as an argument.

lcase() function – convert string to lower case

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print LCase(myString)
End Sub
' prints hello world

replace() function – replaces one string with another

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print Replace(myString, "World", "Replace")
End Sub
' prints Hello Replace

As you can see the REPLACE function takes three arguments. This first is the original string, the second is the word within the string that you want to remove and the third argument is the word that you want to replace.

left() function – returns the first set of characters from the left

Sub MySubString()
Dim myString As String
 myString = "Hello World"
 Debug.Print Left(myString, 3)
End Sub
' prints Hel

right() function – returns the last set of characters from the right

Sub MySubString()
Dim myString As String
 myString = "Hello World"
 Debug.Print Right(myString, 3)
End Sub
' prints rld

mid() FUNCTION – returns a set of characters in the middle of a string

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print Mid(myString, 3, 3)
End Sub
' prints llo

As you can see the MID function takes three arguments. This first is the original string, the second is the starting character numbered from the left, the third argument is the number of characters you want to return.

len() function – count the number (LENgth) of characters in a string

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print Len(myString)
End Sub
' prints  11

instr() function – finds the first position of a character in a string (in string)

 Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print InStr(myString, " ")
 End Sub
' prints 6

Here we see that the blank character ” ” is found as the sixth character in the string.

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print InStr(myString, " World")
 Debug.Print InStr(myString, "*")
End Sub
' prints 6
' prints 0

Here we see that the argument can be a string of characters and InStr() will return the position of the first character.

If the argument contains a character not found, InStr() will return zero.

INSTRRev() FUNCTION – FINDS THE last POSITION OF A CHARACTER IN A STRING (IN STRING Reverse)

 

Sub MySubString()
 Dim myString As String
 myString = "Hello World"
 Debug.Print InStr(myString, "o")
 Debug.Print InStrRev(myString, "o")
End Sub
' prints 5 and 8

As you can see, InStrRev() finds the position of the last occurence of “o” which is 8.


Booleans – True or False

A Boolean can only have two possible values – True or False.

Sub mySubBoolean()
 Dim myBoolean As Boolean
 myBoolean = True
 Debug.Print myBoolean
End Sub
' prints True

The default value is False.

Not operator

Reverses the value thus:

Sub mySubBoolean()
 Dim myBoolean As Boolean
 myBoolean = True
 Debug.Print Not myBoolean
End Sub
' prints False

AND operator- returns true if both booleans are true (false if one is not true):

Sub mySubBoolean()
 Dim myBoolean As Boolean
 myBoolean = True
 Debug.Print True And True
End Sub
'prints True

OR Operator – returns true if either first or second boolean is true:

Sub mySubBoolean()
 Debug.Print True Or True   'prints True
 Debug.Print False Or True  'prints True
 Debug.Print True Or False  'prints True
 Debug.Print False Or False 'prints False
End Sub

Booleans are useful as you can ask a question and store the answer to a boolean:

Sub mySubBoolean()
 Dim myBoolean As Boolean
 myBoolean = 10 > 5
 Debug.Print myBoolean
End Sub
'prints True

As you can see a question is asked and the answer becomes a boolean.

Comparison Operators

<  less than ie 5 < 10 returns true
<= less than or equal to ie 5 <= 5 returns true
> more than ie 5 > 10 returns false
>= more than or equal to ie 5 >= 10 returns false
= equality operator ie 10 = 10 returns true

Use brackets if it makes things lest complicated to read:

Sub mySubBoolean()
 Dim myBoolean As Boolean
 myBoolean = (10 = 10)
 Debug.Print myBoolean
End Sub
'prints True

You can use the Immediate Window to evaluate comparisons by typing an question mark before the comparison:

? True and False
False

You can compare strings in the Immediate Window:

? "Hello World" = "Hello World"
True

Other Comparison Operators can be used:

? "a" < "b"
True

As you can see it works out the place in the alphabet as a number.



 

IF Statements

If statements allow you to either execute or skip over a certain block of code depending on a condition. The condition is a boolean.

Sub MySubIf()
 Dim password As String
 password = InputBox("Enter your password: ", "Enter password")
End Sub

Above, we declare a string variable called password.

To get input from a user we can use a built in function called InputBox

InputBox() – function – Accepts input from a user

The first argument is the prompt message, the second is an optional title:

Sub MySubIf()
 Dim password As String
 password = InputBox("Enter your password: ", "Enter password")
End Sub

(Remember to run the code using F5.)

This is the very basic syntax of an IF statement:

IF (this condition returns true) THEN
   Code to execute if true
END IF

You can use an IF statement to determine if a string is long enough:

Sub MySubIf()
 Dim password As String
 password = InputBox("Enter your password: ", "Enter password")
 If Len(password) < 8 Then
 MsgBox "The password is too short."
 End If
End Sub

If the LENgth of password is less than or equal to 8 then show a message box with the following string.

We can also check if the string contains a number:

 If InStr(password, "0" > 0 Then
 ...
 End If
End Sub

This expression will return true if zero appears in password because, as we know InSTR() returns the number of the index of a certain character if it is found in a string. Therefore, if there is a zero in the password the index will be greater than zero thus the statement returns true and the IF statement will finish.

We will not only check if the password contain a certain number of characters, but also if it contains at least one number. This can be done using the ELSEIF statement:

ELSEIF Function

If the first statement returns false, you use the ELSEIF statement to evaluate another condition. Used multiple times until the statment returns true:

Sub MySubIf()
 Dim password As String
 password = InputBox("Enter your password: ", "Enter password")
 
 If Len(password) < 8 Then
 MsgBox "The password is too short."
 End If
 
 Dim containsNumber As Boolean
 If InStr(password, "0") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "1") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "2") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "3") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "4") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "5") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "6") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "7") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "8") > 0 Then
 containsNumber = True
 ElseIf InStr(password, "9") > 0 Then
 containsNumber = True
 End If
 
 If Not containsNumber Then
 MsgBox "Your password must contain at least one digit"
 End If
End Sub

So what the above code is doing is:

  • Check if password has eight or more characters. (If not, show message).
  • If so, check if it has a zero. If it does, skip to ENDIF.
  • If it does not contain a zero, check if it has a one.
  • If it does, skip to ENDIF. If not, check it has a two.
  • If it does, skip to ENDIF. If not, move to next statement and continue this through all the digits.
  • If the password does not contain a digit – show the message box. (ie if ‘containsNumber is FALSE, then ‘NOT containsNumber’ will return TRUE and the message box will run.

Got it? Well try it out. grab the code, paste into a new module within Excel code editor and pres F8 to step into each line.


 

Loops

Looping is a way of repeatedly executing a block of code any number of times until a condition is met. There are various different types of loops depending on the requirements.

Do While – Loop until a condition is false – in VBA

Sub MySubLoop()
 Dim i As Integer
 i = 0
 Do While i < 10
    Debug.Print (i)
    i = i + 1
 Loop
End Sub
' prints 0 1 2 3 4 5 6 7 8 9
  • Create an integer variable with a value of zero
  • While zero is less than ten, Do this:
  • Print the value of i to the immediate window and…
  • Increment the value of zero to one
  • Then go Loop back to the start of the Do While block and start again:
  • If 1 is less than 10, print and change value to 2, go back to the start etc.
  • This happens until the value is 9. Then when the condition returns false the Do While statement stops and moves to the next code.

Do Until – Looping  until a condition is true – in VBA

This is very similar to the Do While loop but this stops when the condition becomes true:

Sub MySubLoop2()
 Dim i As Integer
 i = 0
 Do Until i = 10
 Debug.Print (i)
 i = i + 1
 Loop
End Sub
'prints 0 1 2 3 4 5 6 7 8 9

 

FOR  – Looping

Sub MySubLoop3()
 Dim i As Integer
 For i = 1 To 10
 Debug.Print i
 Next i
End Sub
' prints 1 2 3 4 5 6 7 8 9 10

In the For loop we are giving it the values of 1 through to ten. We are using the ‘i’ variable in the ‘Debug.Print’ section in order to show the output in the immediate window.

You can ad ‘Step’ to the For loop if you want to return increments other than 1:

Sub MySubLoop3()
 Dim i As Integer
 For i = 1 To 10 Step 2
 Debug.Print i
 Next i
End Sub
' prints 1 3 5 7 9

You can use a negative Step value if you want to count backwards:

Sub MySubLoop3()
 Dim i As Integer
 For i = 10 To 1 Step -1
 Debug.Print i
 Next i
End Sub
'prints 10 9 8 7 6 5 4 3 2 1

 

If you remember the Elsif() function which searched for each individual number, the For loop can do this but with much less code:

Sub MySubForLoop()
 Dim password As String
 password = InputBox("Enter your password: ", "Enter password")
 
 If Len(password) < 8 Then
 MsgBox "The password is too short."
 End If
 
 Dim containsNumber As Boolean
 Dim i As Integer
 
 For i = 0 To 9
 If InStr(password, i) > 0 Then
 containsNumber = True
 Exit For
 End If
 Next i
 
 If Not containsNumber Then
 MsgBox "Your password must contain at least one digit"
 End If
End Sub

Here we can see the password checking code. The For loop looks for numbers in the password variable and loops through each number from 0 to 9. Once it encounters a number, the ‘Exit If‘ then stops the For loop from carrying on and completing the loop.



 

Worksheets and Ranges in Excel VBA

In this exercise we look at how to work with the cells in the work sheet in Excel. Along side standard types in Excel VBA such as Boolean and String are the Worksheet type and the Range type.

ActiveSheet – VBA

Getting a reference to Sheet1

Sub MyRangeSub()
 Dim wSheet As Worksheet
 Set wSheet = ActiveSheet
 Debug.Print wSheet.Name
End Sub
' prints Sheet1

Here we can see a variable being assigned the type of ‘Worksheet’ and set to the ‘ActiveSheet’ which mean whichever sheet is being worked on at the time. We can print the value of this to the immediate window.

Set keyword

The worksheet is an ‘object’. As it is an object we have to use ‘Set’ if we want to assign the worksheet to a variable. We can then use dot notation to access properties.

ThisWorkbook.Sheets(” “)

You can also reference a sheet by its name:

Sub MyRangeSub()
 Dim wSheet As Worksheet
 Set wSheet = ThisWorkbook.Sheets("Sheet2")
 Debug.Print wSheet.Name
End Sub
'prints Sheet2

You can also select the sheet from the sheet object (found in the Project Explorer window):

Sub MyRangeSub()
 Dim wSheet As Worksheet
 Set wSheet = Sheet3
 Debug.Print wSheet.Name
End Sub
'prints Sheet3

Editing worksheet names from the properties window

If the Properties Window is not showing, click View – Properties Window  or F4.  Click to the right cell of the ‘Name’ section and rename the sheet ‘NewNameSheet’. The ane of the sheet automatically changes in the Project Explorer window. You’ll need to rename any references from the old name to the new one, otherwise the code won’t work.

⋮
Set wSheet = NewNameSheet
⋮

Range( ) – Selecting a range of cells – VBA

.Value( ) – RETURNING the CONTENTS OF CELLS – VBA

 

Using the Range and Value properties of the worksheet variable, we can return the value of a selected cell:

Sub MyRangeSub2()
Dim wSheet As Worksheet
 Set wSheet = Sheet1
 Debug.Print wSheet.Range("A1").Value
End Sub
'prints the contents of cell A1 on Sheet1

Cells property – VBA

We can also do it this way:

⋮
Debug.Print wSheet.Cells(1, 1).Value
⋮

Here, we use the Cells property and inside the parenthesis we specify the row and column.

.Address – VBA

We can also assign a Range to a variable. Type the below code and then move to the worksheet and select a range of cells such as A1 to E19:

Sub MyRangeSub3()
 Dim myRange As Range
 Set myRange = Selection
 Debug.Print myRange.Address
End Sub
'prints $A$1:$E$19

Run the code using F5 and the range will be printed to the immediate window. The dollar sign represents absolute references as you may know.

R1C1 and A1 Excel reference styles – VBA

Just a note that there are two main reference styles to use when referring to cells in Excel. A1 is the normal Excel reference as it uses letters for columns and numbers for rows. R1C1 on the other hand uses numbers for columns and rows. The main difference is that A1 is slightly easier to use for a human but R1C1 maybe slightly better when in VBA.

Using .Address you can also record the range using both A1 reference style and .Cells:

Sub MyRangeSub3()
 Dim myRange As Range
 Dim wSheet As Worksheet
 Set wSheet = Sheet1
 
 Set myRange = Selection
 Debug.Print myRange.Address
 
 Set myRange = wSheet.Range("A1")
 Debug.Print myRange.Address
 
 Set myRange = wSheet.Cells(1, 1)
 Debug.Print myRange.Address
End Sub
'prints $A$1
'       $A$1
'       $A$1

Record Macros to learn to code VBA

Recording Macros is a great way to learn VBA code. For example you could record a Macro and set the selected cell to italic. Then stop the Macro and open the editor to look at the code:

Sub Macro1()
'
' Macro1 Macro
'

'
 Selection.Font.Italic = True
End Sub

 

Subs and Functions in VBA Excel

Subs and Functions are concepts used in Excel VBA in order to break down your code into small reusable chunks.

Arguments – VBA Excel

We already know about VBA’s built in functions:

'Eg:
FUNCTION("argument")
' Pass an argument to the function and it returns the result.

The function “UCASE() takes an argument (the data that you pass to the function). The argument is within the curly brackets. The function returns the output that it produces.

The difference between a sub and a function (for the purposes of this discussion) is that a sub doesn’t return a value.

Lets look at a sub in more detail.

Putting a concurrent number in each cell of a selected range, using subs

  1. Option Explicit
  2. Sub Main()
  3.  Call Number_in_Cells(Selection)
  4. End Sub

  5. Sub Number_in_Cells(rng As Range)
  6.  Dim i As Long
  7.  Dim c As Range
 
  8.  For Each c In rng.Cells
  9.    c.Value = i
 10.    i = i + 1
 11.    Next c
 12. End Sub

Here, we:

  • Define a sub (5.)
  • Give the sub a name “Number_in_Cells” (5.)
  • Give an argument – “rng” (The argument of the function happens to be a variable)
  • The variable gets a type “Range” so that it knows what kind if variable it is.
  • Create a string and an integer variable for use in the loop (Dim i and c) (6. and 7.)
  • Create a For Each loop. If you have a range object (rng) you can iterate through each individual cell (.Cells) using the loop. (8. 9. 10. 11.)
  • Write into the first cell using the Value property, it is zero so add 1 (9.). The next cell is 1 + 1 = 2, etc
  • Define the top sub, called “Main”. This is where you call the sub below (5.) and use the selected cells (Selection) as an argument.(1. 2. 3. 4.).

Since the Sub “Number_in_Cells” takes an argument, it doesn’t show up in the list of Macros. Only the Sub “Main” does because it does not take any arguments.

We can add more Subs and call them from the main sub.

Lets create a sub to make the values of the cells in the selection bold and italic:

Sub Format_Cells(rng As Range)
 rng.Font.Bold = True
 rng.Font.Italic = True
End Sub

and we’ll call this from the main sub. Here we access the properties of the range object by using dot syntax and set the Boolean to True (meaning that you’ve turned them on).

The full code is below:

Sub Main()
 Call Number_in_Cells(Selection)
 Call Format_Cells(Selection)
End Sub

Sub Number_in_Cells(rng As Range)
 Dim i As Long
 Dim c As Range
 
 For Each c In rng.Cells
 c.Value = i
 i = i + 1
 Next c
End Sub

Sub Format_Cells(rng As Range)
 rng.Font.Bold = True
 rng.Font.Italic = True
End Sub

Subs can be called from the user interface – as long as they don’t take any argument and as long as they are not set to ‘Private’.

Private

The code below will not show up in the list of Macros:

Private Sub Main()
 Call Number_in_Cells(Selection)
 Call Format_Cells(Selection)
End Sub


Functions – VBA Excel

Functions return a value

Lets make a start:

Function Sum_of_Cells(rng As Range) As Long

End Function

Here, we:

  • Define a new function called ‘Sum_of_Cells’
  • It will take a range as an argument to the function
  • It will also return a value (As Long). This is what Subs can’t do.

Next, we will form the body of the function which will add up the values

Function Sum_of_Cells(rng As Range) As Long
 Dim c As Range
 For Each c In rng
 Sum_of_Cells = Sum_of_Cells + c.Value
 Next c
End Function

Here, we:

  • Define a variable to use within a loop.
  • Define the For Each loop that will loop through each cell in the range and add the value of the next cell to the total. This will loop through until all the values have been added. The function will return this total value from the Function variable name.

Lastly, we will call the function from the main Sub and put the result into a Message Box thus:

Option Explicit
Sub Main()
  MsgBox Sum_of_Cells(Selection)
End Sub

and here is the full code:

Option Explicit
Sub Main()
 MsgBox Sum_of_Cells(Selection)
End Sub

Function Sum_of_Cells(rng As Range) As Long
 Dim c As Range
 For Each c In rng
 Sum_of_Cells = Sum_of_Cells + c.Value
 Next c
End Function

All you have to do, is to create a selection where there are values in the cells, place your cursor inside the Sub Main() and press F5.

This is all of the code in one place. Make a selection on the worksheet and you can run all of the code in one go. It will place concurrent numbers inside all of the cells in the range, it will make the values bold and italic, and it will add all if the values up and place the result in a message box:

Option Explicit
Private Sub Main()
 Call Number_in_Cells(Selection)
 Call Format_Cells(Selection)
 MsgBox Sum_of_Cells(Selection)
End Sub

Sub Number_in_Cells(rng As Range)
 Dim i As Long
 Dim c As Range
 
 For Each c In rng.Cells
 c.Value = i
 i = i + 1
 Next c
End Sub

Sub Format_Cells(rng As Range)
 rng.Font.Bold = True
 rng.Font.Italic = True
End Sub

Function Sum_of_Cells(rng As Range) As Long
 Dim c As Range
 For Each c In rng
 Sum_of_Cells = Sum_of_Cells + c.Value
 Next c
End Function

 

User Defined Function – VBA Excel

Although function will not show up in the list of Macros, you can still use them within the worksheet. In this instance they are referred to as ‘User Defined Functions’.

  • In a clear cell in your worksheet, type =SUM
  • A list of functions will show up that start with SUM – one of which will be our ‘Sum_of_Cells() function.
  • Select this, finish the function syntax by selection a range of cells that has values in them. (=Sum_of_Cells( put range here)
  • The result will be shown in the cell.
=Sum_of_Cells(B4:H22)

 

Summary of Subs and Functions

Subs:

  • Will show up in your list of Macros
  • as long as you don’t give them any arguments (ie sub Main( ))
  • You can use a Sub to Call another Sub

Functions:

  • You can define your own Functions using the Function keyword (Function)
  • You have to define it’s own name (Function Name() )
  • and the arguments that it takes( Function Name(variable As Range) )
  • As well as it’s return value ( Function Name(variable As Range) As Long )
  • Then you can use the Function name (Name) within the body of the Function in order to return a value.

Exercise – How to format phone numbers to a consistent format

Here, we are going to create a macro to format phone numbers in Column A, into a consistent format and write the result to the next cell to the right in Column B.

Column one with unformatted numbers looks like this:

1235554841
5125489654
632-968-8524
(451)458-8563
541.365.9854
968-8745 ext 856
(874)965 9854
(895)9656985 x777

Step one – create sub and test to make sure the selection of numbers is in only one column. If not, have a message display:

Option Explicit
Sub Format_Phone_Numbers()
 ' Formats phone numbers in the current selection
 ' into this format (123) 456-7890 ext. 123
 ' Writes output into the cells to the right of
 ' the current selection
 
'Checks only Col A selected
 If Selection.Columns.Count > 1 Then
 MsgBox "You must select a single column of Data.", vbCritical
 Exit Sub
 End If
 
End Sub
  • Here we use an If statement. By using dot syntax we can count the number of columns of the selection via the properties of the selection object.
  • If the number of columns in the selection is more than one, show a message box containing the following text.
  • You can select different types of message box. Here we use “vbCritical” which means that a red cross will show as an icon in the box.
  • If the statement returns true (ie, if there is more than one column in the selection then stop the macro using ‘Exit Sub’.

Now that we have ensured that one column selected we can loop through the selection.

Step two- create a loop to loop through each row

Dim c As Range
 For Each c In Selection     'Select the first cell
     'Do something with each selection
 Next c     'Loop back to the beginning
  • Here we create a variable ‘c’ which is the selected range.
  • The loop: For each instance (cell) within the selected range,
  • We will add the formatting operation later.
  • Next mean that it will loop back to the beginning of the For each and select the next cell in the range

Step three- create a loop to loop through each row

Dim c As Range
 For Each c In Selection
 Dim formattedNumber As String   'Declare new variable
 formattedNumber = FormatPhoneNumber(c.Value)
 c.Offset(0, 1).Value = formattedNumber     'Place value to Col 2
 Next c
  •  We declare a second variable ‘formattedNumber’ (in camelCase) which is a string.
  • It will take the output of a new function called ‘FormatPhoneNumber. This will take the value that is inside ‘c’ and return the format that we want.
  • We will then place the formatted number to the right column. (Offset zero rows and one column).

Step four- create the function ‘FormatPhoneNumber’ and test it

Function FormatPhoneNumber(rawString As String) As String
 FormatPhoneNumber = "test"
End Function
  • We will just test that the loop and function works ok. Here – the output will place the word test to the right of each selection:
1235554841 test
5125489654 test
632-968-8524 test
(451)458-8563 test
541.365.9854 test
968-8745 ext 856 test
(874)965 9854 test
(895)9656985 x777 test

Step five- strip hyphens, dots and brackets etc and just return the digits

The function will run in two phases, the first is to strip extraneous characters and just return digits.

  • Declare a new variable (Dim digits As String. It will take the value of another function called ‘ExtractDigits’, thus:
Function FormatPhoneNumber(rawString As String) As String
 Dim digits As String
 digits = ExtractDigits(rawString)
End Function
  • The ExtractDigits function for the moment will just return a set string of number in order for us to continue with the logic of the remaining steps:
Function ExtractDigits(rawString As String) As String
 ExtractDigits = "1234567890"
End Function

 

Step six- format digits

  • Then we format the digits like this: (123) 456 7890 :
 Dim digits As String
 digits = ExtractDigits(rawString)
 
 FormatPhoneNumber = "(" & Left(digits, 3) & ") " & Mid(digits, 4, 3)_
 & "-" & Right(digits, 4)
End Function

Note: You can use the underscore character “_” to continue a line of code onto the next line.

  • So put a left brackets “(” character as a string (in quotation marks)
  • Using the ‘Left’ function to grab the first 3 digits
  • and then a right bracket, including a space “) “
  • Now select the next three numbers using the Mid function
  • Put a hyphen next “-“
  • Then lastly select the right-most 4 numbers using the Right function.

This will now work with the pre-defined string that we selected (1234567890) and will format it to the right of each row as (123) 456 – 7890.

1235554841 (123) 456-7890
5125489654 (123) 456-7890
632-968-8524 (123) 456-7890
(451)458-8563 (123) 456-7890
541.365.9854 (123) 456-7890
968-8745 ext 856 (123) 456-7890
(874)965 9854 (123) 456-7890
(895)9656985 x777 (123) 456-7890

It work, however there is a slight issue in that the last four digits may include an extension number. So we will need to refactor the code a bit to accommodate this:

FormatPhoneNumber = "(" & Left(digits, 3) & ") " & Mid(digits, 4, 3)_
 & "-" & Mid(digits, 7, 4)
 
 If Len(digits) > 10 Then
 FormatPhoneNumber = FormatPhoneNumber & " Ext. " & _
Right(digits, Len(digits) - 10)
  • For formatting the third set of digits, change Right function to Mid function Mid(digits, 7, 4) (Start at digit 7 and read for 4 chars).
  • As the extension maybe more than three digits we will find the length (Len). If it the length of the phone number is more than ten (ie that it has an extension) then add “Ext. “, then using the ‘Right’ function – and however many characters are in digits, minus 10. So a 13 digit phone number becomes 3, 14 digit becomes 4 etc.
  • Add an extra 3 characters to the ExtractDigits function string that we created for the test: “1234567890888”, then we can test the extension function, thus:
1235554841 (123) 456-7890 Ext. 888
5125489654 (123) 456-7890 Ext. 888
632-968-8524 (123) 456-7890 Ext. 888
(451)458-8563 (123) 456-7890 Ext. 888
541.365.9854 (123) 456-7890 Ext. 888
968-8745 ext 856 (123) 456-7890 Ext. 888
(874)965 9854 (123) 456-7890 Ext. 888
(895)9656985 x777 (123) 456-7890 Ext. 888

Now that we have the other code set up, we can complete the ExtractDigits function with actual data instead of sample data. Type the following:

Function ExtractDigits(rawString As String) As String
 Dim char As String
 Dim position As Integer
 
 For position = 1 To Len(rawString)
 char = Mid(rawString, position, 1)
 If InStr("0123456789", char) > 0 Then
 ExtractDigits = ExtractDigits & char
 End If
 Next position
End Function

We can test this using the Immediate Window:

>> ?ExtractDigits("hello123")
>> 123

If we pass a string with text and numbers, we can see that the function strips out the text and just leaves the numbers. Our function works!

So here is the full code, try it yourself!

Option Explicit
Sub Format_Phone_Numbers()
 ' Formats phone numbers in the current selection
 ' into this format (123) 456-7890 ext. 123
 ' Writes output into the cells to the right of
 ' the current selection
 
 If Selection.Columns.Count > 1 Then
 MsgBox "You must select a single column of Data.", vbCritical
 Exit Sub
 End If
 
 Dim c As Range
 For Each c In Selection
 Dim formattedNumber As String
 formattedNumber = FormatPhoneNumber(c.Value)
 c.Offset(0, 1).Value = formattedNumber
 Next c
 
End Sub

Function FormatPhoneNumber(rawString As String) As String
 Dim digits As String
 digits = ExtractDigits(rawString)
 
 FormatPhoneNumber = "(" & Left(digits, 3) & ") " & Mid(digits, 4, 3) & "-" _
 & Mid(digits, 7, 4)
 
 If Len(digits) > 10 Then
 FormatPhoneNumber = FormatPhoneNumber & " Ext. " & Right(digits, Len(digits) - 10)
 End If
End Function

Function ExtractDigits(rawString As String) As String
 Dim char As String
 Dim position As Integer
 
 For position = 1 To Len(rawString)
 char = Mid(rawString, position, 1)
 If InStr("0123456789", char) > 0 Then
 ExtractDigits = ExtractDigits & char
 End If
 Next position
End Function