Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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:

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

To practise using basic variables:

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

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

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

Click the button on the worksheet to test that the code works:

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

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS