MODULES
LESSONS
TOPICS
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.