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:

Multi variables

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:

Variant date

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:

Subtype 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:

Convert varaible

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:

Converted 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:

  1. Using any workbook, create a new module and add a new subroutine called BeMyBMI.
  2. 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

  1. 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

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

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

 
  1. 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

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

You can see this information in the Locals window.

 
  1. 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

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

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

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