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 5 - Messages and User Inputs

Lesson 5.3 - The VBA Input Box

Topic 5.3.6 - Using Variant Variables

If you want to avoid type mismatch errors when capturing numbers or dates from an input box but you don't want the extra work of declaring two variables for each value, you can use Variant variables instead:

Creating two variables for each value feels a little too much like hard work.

Files Needed

You don't need any files for this section.

Completed Code

You can click here to download the sample code.

Create a new workbook and insert a new module in the VBE.

Capturing a Value in a Variant Variable

If you'd rather not declare multiple variables for each value you want to store, you could use a single variable with the Variant data type. Create a new subroutine called UsingVariant and add code to capture the result of an input box in a Variant variable:

Declaring a variable with the Variant type means that you can store any type of value in it.

After capturing the value entered into the input box, the variable will have a subtype of String:

You can see the subtype of Variant variables in the Locals window.

Converting the Subtype of the Variable

You can then check if the value can be treated as a date and, if so, convert the subtype of the variable using the CDate function:

You set the value of the DoB variable to be equal to the converted version of itself!

After converting the variable in this way, it will have a subtype of Date:

Check the Locals window to see the new subtype of the variable.

Although this technique uses fewer variables it does have a couple of drawbacks: it's less easy to tell, at a glance, what data type the variable will have at any given point, and; you might inadvertently alter the subtype of the variant in another part of your code. Ultimately, of course, the technique you choose is up to you!

To practise using Variant variables:

Using any workbook, create a new module and add a new subroutine called BeMyBMI.
Declare two Variant variables and use input boxes to populate them with a user's height in metres and weight in kilograms:

Sub BeMyBMI()

Dim HeightM As Variant

Dim WeightKg As Variant

HeightM = InputBox( _

"Enter height in metres")

WeightKg = InputBox( _

"Enter weight in kg")

End Sub

After each input box, add an If statement to check that the user entered a number and exit the procedure if not:

Sub BeMyBMI()

Dim HeightM As Variant

Dim WeightKg As Variant

HeightM = InputBox( _

"Enter height in metres")

If Not IsNumeric(HeightM) Then

MsgBox "Your height must be a number!"

Exit Sub

End If

WeightKg = InputBox( _

"Enter weight in kg")

If Not IsNumeric(WeightKg) Then

MsgBox "Your weight must be a number!"

Exit Sub

End If

End Sub

Step through the procedure (press F8 to do this) and use the Locals window to confirm that your variables have a subtype of String:

Choose View | Locals Window from the menu if you can't see it.

Add code to convert the subtype of the variables to Double:

Sub BeMyBMI()

Dim HeightM As Variant

Dim WeightKg As Variant

HeightM = InputBox( _

"Enter height in metres")

If Not IsNumeric(HeightM) Then

MsgBox "Your height must be a number!"

Exit Sub

End If

WeightKg = InputBox( _

"Enter weight in kg")

If Not IsNumeric(WeightKg) Then

MsgBox "Your weight must be a number!"

Exit Sub

End If

HeightM = CDbl(HeightM)

WeightKg = CDbl(WeightKg)

End Sub

Use the F8 key to step through the procedure and make sure that the subtype of the variables changes to Double:

You can see this information in the Locals window.

Declare a new Double variable and use it to calculate the user's body mass index. Present this information in a message box at the end of the procedure:

Sub BeMyBMI()

Dim HeightM As Variant

Dim WeightKg As Variant

Dim BMI As Double

HeightM = InputBox( _

"Enter height in metres")

If Not IsNumeric(HeightM) Then

MsgBox "Your height must be a number!"

Exit Sub

End If

WeightKg = InputBox( _

"Enter weight in kg")

If Not IsNumeric(WeightKg) Then

MsgBox "Your weight must be a number!"

Exit Sub

End If

HeightM = CDbl(HeightM)

WeightKg = CDbl(WeightKg)

BMI = WeightKg / (HeightM * HeightM)

MsgBox _

"Height: " & HeightM & "m" & vbNewLine & _

"Weight: " & WeightKg & "kg" & vbNewLine & _

"BMI: " & Format(BMI, "0.00"), _

vbInformation

End Sub

Run the subroutine and check that you see an appropriate response whether you type in numbers or not:

If you type numbers into each of the input boxes you'll see a message resembling this one.

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Capturing a Value in a Variant Variable

Converting the Subtype of the Variable

Head office

London

Manchester