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.
