Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Many people around the world enjoy and benefit from our tutorials. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You must enable JavaScript to be able to view our online training courses (the tabbed menu below doesn't work otherwise).

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.

4.1 - Basic Data Variables
4.1.1 - Declaring Basic Variables
4.1.2 - Debugging With Variables
4.1.3 - Non-Declared Variables
4.1.4 - Module-Level Variables
4.1.5 - Public Variables

4.2 - Object Variables
4.2.1 - Basic Object Variables
4.2.2 - Object Variable Scope

4.3 - Parameters
4.3.1 - Basic Parameters
4.3.2 - Multiple Parameters
4.3.3 - Object Parameters

4.4 - Functions
4.4.1 - Basic Functions
4.4.2 - Function Parameters
4.4.3 - Returning Object References
4.4.4 - Returning Boolean Values

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

Try doing one or more of the following exercises for this module:

Exercise 4.01 Exercise 4.02 Exercise 4.03

There is currently no test for this module.

This page has 0 threads

Add a new post

MODULES

LESSONS

Basic Data Variables

Object Variables

Parameters

Functions

VBA Data Types

Variable Scope

Head office

London

Manchester