Module 4 - Variables, Parameters and Functions

In this module you'll learn how to create variables, and how to use them to make your programs easier to understand and write.

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

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

Basic Data Variables

You can declare a variable to hold a single value of a particular data type.

'Dim VariableName as DataType

 

Dim MyName As String

Dim DateOfBirth As Date

Dim HeightCm As Integer

You can assign a value to a variable by making the variable equal to a piece of data of the appropriate type.

MyName = "Wise Owl"

DateOfBirth = #1/1/1992#

HeightCm = 30

You can read a value from a variable by referring to its name.

Range("A1").Value = MyName

Range("A2").Value = DateOfBirth

Range("A3").Value = HeightCm

You can create and use a variable without declaring it.

Sub Non_Declared_Variables()

 

MyName = "Wise Owl"

 

Range("A1").Value = MyName

 

End Sub

Using non-declared variables can cause problems that are difficult to find when you accidentally misspell a variable name.

Sub Misspelling_Variables()

 

MyName = "Wise Owl"

 

Range("A1").Value = MyNmae

 

End Sub

You can force all variables in a module to require a declaration by adding Option Explicit to the top of the module.

Option Explicit

 

Sub Require_Variable_Declarations()

 

Dim MyName As String

 

MyName = "Wise Owl"

 

Range("A1").Value = MyName

 

End Sub

Declaring a variable in a procedure limits its scope to that procedure.

Sub Assign_Name()

 

Dim MyName As String

 

MyName = "Wise Owl"

 

Write_Name

 

End Sub

 

Sub Write_Name()

 

'Cannot reference MyName

Range("A1").Value = MyName

 

End Sub

You can declare a variable outside a procedure at the top of a module to make it available to every procedure in the module.

Dim MyName As String

 

Sub Assign_Name()

 

MyName = "Wise Owl"

 

Write_Name

 

End Sub

 

Sub Write_Name()

 

Range("A1").Value = MyName

 

End Sub

You can declare a variable outside a procedure using Dim or Private to make it available to every procedure in the module.

Private MyName As String

 

Sub Assign_Name()

 

MyName = "Wise Owl"

 

Write_Name

 

End Sub

 

Sub Write_Name()

 

Range("A1").Value = MyName

 

End Sub

You can declare a variable outside a procedure using Public to make it available to every procedure in every module of the project.

Public MyName As String

Object Variables

You can declare a variable to hold a reference to a class of object.

Dim MyCell As Range

Dim MySheet As Worksheet

Dim MyFile As Workbook

You use the Set keyword to assign a reference to an object variable.

Set MyCell = Range("A1")

Set MySheet = Worksheets("Sheet1")

Set MyFile = ThisWorkbook

You can use the variable to access properties and methods of the object it references.

MyCell.Value = "Wise Owl"

MySheet.Calculate

MyFile.Save

You can use other methods and properties to return an object reference to a variable.

Set MySheet = Worksheets.Add

Set MyFile = Workbooks.Open("C:\Book1.xlsm")

Set MyCell = ActiveCell.SpecialCells(xlCellTypeLastCell)

Parameters

You can declare a parameter for a procedure in the parentheses after the procedure's name.

Sub Basic_Parameter(MyName As String)

You can read a parameter's value by referring to its name.

Sub Basic_Parameter(MyName As String)

 

ActiveCell.Value = MyName

 

End Sub

You can pass a value into a parameter by calling the procedure.

Sub Call_Procedure()

 

Basic_Parameter "Wise Owl"

 

End Sub

You can optionally name the parameter before passing a value to it.

Sub Name_Parameter()

 

Basic_Parameter MyName:="Wise Owl"

 

End Sub

You can optionally use the Call keyword and enclose the argument list in parentheses when you call a procedure.

Sub Using_Call()

 

Call Basic_Parameter(MyName:="Wise Owl")

 

End Sub

You can define multiple parameters separated with commas.

Sub Multi_Parameters(MyName As String, DateOfBirth As Date)

Functions

You can define a function to return a value of a particular type.

Function Current_Month_Name() As String

 

End Function

You can return a value from a function by assigning a value to the function's name.

Function Current_Month_Name() As String

 

Current_Month_Name = Format(Date, "mmmm")

 

End Function

You can define parameters for a function.

Function Month_Name(DateToUse As Date) As String

 

Month_Name = Format(DateToUse, "mmmm")

 

End Function

You can call a function in another procedure to return its value.

Sub Call_Functions()

 

Range("A1").Value = Current_Month_Name

 

Range("A2").Value = Month_Name(DateToUse:=#1/1/1992#)

 

End Sub

You can return a reference to an object from a function.

Function Next_Blank_Cell() As Range

 

Set Next_Blank_Cell = _

Range("A1048576").End(xlUp).Offset(1, 0)

 

End Function

You can use the function name to return a reference to an object and make use of the object's methods and properties.

Sub Use_Object_Reference()

 

Next_Blank_Cell.Select

 

End Sub

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

VBA Data Types

You can see a summary of the basic VBA data types in the table below:

Data Type Description Size in Bytes
Byte A whole number between 0 and 255 1
Integer A whole number between -32,768 and 32,767 2
Long A whole number between -2,147,483,648 and 2,147,483,647 4
LongLong A whole number from -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807. You can only use this data type in 64-bit editions of Microsoft Office 8
LongPtr Automatically transforms to a Long in 32-bit editions of Office and a LongLong in 64-bit editions 4 or 8
Single A number with an absolute value up to 3.402823 x (10^38) and with a maximum of seven digits 4
Double A number with an absolute value up to 1.79769313486232 x (10^308) and with a maximum of fifteen digits 8
Currency A number with four decimal places and a range of -922,337,203,685,477.5808 to 922,337,203,685,477.5807 8
Boolean The value True or False 2
Date A date and/or time from 01/01/100 to 31/12/9999 8
String Any string of text up to approximately 2 billion characters 10+
Variant Any value of any data type listed above. A Variant has a subtype indicating what type of value it actually holds 16+
Object A reference to any class of object defined in VBA 4

Variable Scope

Variables in VBA can have one of three levels of scope, as described in the table below:

Scope Location of declaration Keyword used to declare Available to
Procedure Within a procedure Dim Only the procedure in which the variable is declared
Module In a module, outside any procedure Dim or Private Every procedure in the same module as the declaration
Project In a module, outside any procedure Public Every procedure in every module of the project

 

There is currently no test for this module.

This page has 0 threads Add post