Module 4 - Variables, Parameters and Functions
Lesson 4.1 - Basic Data Variables
Topic 4.1.1 - Declaring Basic Variables

Before using a variable in a procedure, it's good practice to declare it.  This part of the lesson explains the basics of declaring and using variables in a procedure.

Files Needed

You can click here to download the file for this page.

Completed Code

You can click here to download a file containing the sample code.

The Sample Workbook

Extract and open the file linked to in the Files Needed section above.  You'll find a list of characters along with details of their height and weight:

Sample data

We'd like to calculate new values for each character using the height and weight details.

 

Open the VBE and find the subroutine called ProcessBMIList:

Sample code

We've already created a basic loop structure which will continue until it reaches the bottom of the list of characters.

 

Our aim to begin with is to add the code which will calculate the Body Mass Index (BMI) for each character.

Identifying Values to Store in Variables

Before writing any code, it's worth considering which values we want our program to remember.  To calculate the BMI and return an answer, there are three separate values we need to consider:

Value Description
Weight The weight in kilograms.  This will be a number with decimal places.
Height The height in metres.  This will be a number with decimal places.
BMI The BMI is the result of the formula Weight / (Height * Height).  This will be a number with decimal places.

The table above indicates that we should have three variables in the procedure, each of which must be capable of storing a number with decimal places.

We could solve this problem without using variables, but using variables will make the code easier to read, write and maintain.

Declaring Variables

It's good practice, although not required in VBA, to declare a variable.  Declaring a variable informs your program that you'd like to reserve a space in memory to hold a value of a particular type.  This allows the program to allocate the correct amount of memory to hold it.  To declare a variable in VBA you use the Dim keyword, followed by the name you'd like to use to reference the value later:

Declare

In VBA, it's conventional to declare variables at the beginning of a procedure.

 

The Dim keyword in VBA is derived from the DIMENSION keyword in the FORTRAN programming language.  This isn't a particularly helpful thing to remember!  You may prefer to use the mnemonic "Declare In Memory" to remind you what Dim does.

After writing the name of the variable you can write the keyword As, followed by the data type of the value you'd like to store.  You can see a table of VBA data types in the reference page for this module.  In order to store a number with decimal places, we'll use a VBA data type called Double:

Data type

You'll find that VBA data types appear in the IntelliSense list as you write your code.

 

You can declare the two other variables we need in the same way:

Three variables

After declaring three variables, the start of your procedure should look like this.

 

Assigning Values to Variables

Once you've declared a variable, you can assign a value to it.  We'll start by assigning the value of the cell containing a character's weight to the appropriate variable:

Weight

The weight of a character will be in the cell which is one column to the right of the active cell when the code is running.

 

To assign a value to a variable, start by writing its name:

Write variable

You can use the IntelliSense list to help you enter the variable. This can help to avoid typing mistakes.

 

After the variable name, write an = symbol, followed by the value you want to assign to the variable:

Assign value

Here we're assigning the value of the cell that is one column to the right of the active cell.

 

You can repeat this process to assign the appropriate value to the HeightM variable:

Height

The height is two columns to the right of the active cell.

 

Reading the Contents of a Variable

In order to calculate the BMI and assign the answer to the BMI variable, we need to read the contents of both the WeightKg and HeightM variables.  Reading the contents of a variable is as simple as writing its name in your code:

Read from variable

Assign a value to the BMI variable by reading the values from the WeightKg and HeightM variables into the formula shown here.

 

After calculating the result and storing it in the BMI variable, we can read the contents of the BMI variable into the relevant cell:

Write into cell

The value of the BMI variable needs to be placed in the cell which is three columns to the right of the active cell.

 

You can now run the code by clicking the button on the worksheet:

Run code

Click the Calculate BMI button to see BMI column populated with the results of the calculation.

To practise using basic variables:

  1. Using the same subroutine you have been working on, declare a new variable called BMIBand to hold the BMI category for a character.  This variable must be capable of storing text - the VBA data type for text is called String:

Sub ProcessBMIList()

 

Dim WeightKg As Double

Dim HeightM As Double

Dim BMI As Double

Dim BMIBand As String

  1. Add code within the Do Until loop which assigns a value to the BMIBand variable using the result of a Switch function:

Do Until ActiveCell.Value = ""

 

'Add code to calculate BMI here

WeightKg = ActiveCell.Offset(0, 1).Value

HeightM = ActiveCell.Offset(0, 2).Value

 

BMI = WeightKg / (HeightM * HeightM)

 

BMIBand = Switch( _

BMI < 18.5, "Underweight", _

BMI < 25, "Healthy weight", _

BMI < 30, "Overweight", _

BMI >= 30, "Obese")

 

ActiveCell.Offset(0, 3).Value = BMI

 

ActiveCell.Offset(1, 0).Select

Loop

  1. Add code within the Do Until loop which reads the contents of the BMIBand variable into the cell which is four columns to the right of the active cell:

Do Until ActiveCell.Value = ""

 

'Add code to calculate BMI here

WeightKg = ActiveCell.Offset(0, 1).Value

HeightM = ActiveCell.Offset(0, 2).Value

 

BMI = WeightKg / (HeightM * HeightM)

 

BMIBand = Switch( _

BMI < 18.5, "Underweight", _

BMI < 25, "Healthy weight", _

BMI < 30, "Overweight", _

BMI >= 30, "Obese")

 

ActiveCell.Offset(0, 3).Value = BMI

ActiveCell.Offset(0, 4).Value = BMIBand

 

ActiveCell.Offset(1, 0).Select

Loop

  1. Click the button on the worksheet to test that the code works:
Result

Check that the correct BMI category appears in column E of the worksheet.

  1. Save and close the workbook.
This page has 0 threads Add post