MODULES
LESSONS
TOPICS
Although the VBA input box always returns a string, you can use it to capture values of other data types too:
You can enter dates and numbers into an input box and capture them as a non-string data type.
Files Needed
You can click here to download the file needed for this part of the lesson.
Completed Code
You can click here to download a file containing the sample code.
The VBA InputBox function always returns the value you enter as a string. You can, however, capture the return value as a different data type simply by storing it in a variable with the appropriate type.
Capturing Different Data Types
Extract and open the workbook linked to in the Files Needed section above. In Module1 find the subroutine called CaptureDifferentDataTypes. The sdubroutine asks a user to enter a string, a date and a number into three separate input boxes:
You can ask users to enter any data type, although the result will always be returned as a string.
When you run the subroutine, you can enter a value of the appropriate type into each input box:
You can enter a date in a variety of different formats.
You must enter a value of the appropriate type into the input box, otherwise you'll encounter a type mismatch run-time error. This will also happen if you choose to cancel the input box.
If you step through the procedure using the F8 key, you can see the values stored with the appropriate data type using the Locals window (choose View | Locals Window from the menu if you can't see it):
Providing you type a valid value into each input box it will be captured with the appropriate data type.
Why This Works
This process works because of a feature of the VBA language referred to as implicit data type conversion. In simple terms, this means that if a value can be converted from one data type to another, VBA will do this automatically. The animation below shows this process with three different data types. You can follow along using the subroutine called ImplicitDataTypeConversion in Module1:
The first value is entered into a String variable.
To practise capturing non-string values:
- Using the same module, create a subroutine called WhatsMyBMI.
- Declare two Double variables to hold a person's height in metres and weight in kilograms:
Sub WhatsMyBMI()
Dim HeightM As Double
Dim WeightKg As Double
End Sub
- Use input boxes to ask the user to enter their height and weight and store the results in the variables you have declared:
Sub WhatsMyBMI()
Dim HeightM As Double
Dim WeightKg As Double
HeightM = InputBox("Enter height in metres")
WeightKg = InputBox("Enter weight in kilograms")
End Sub
- Run the subroutine and check that the values you enter are stored correctly:
You can step through the procedure by pressing the F8 key and use the Locals window to check the values have been stored (choose View | Locals Window from the menu if you can't see it).
Remember that if you don't enter a number into each input box you'll receive a run-time error message!
- Add a new Double variable and use it to calculate the body mass index of the user (the formula is shown in the code below):
Sub WhatsMyBMI()
Dim HeightM As Double
Dim WeightKg As Double
Dim BMI As Double
HeightM = InputBox("Enter height in metres")
WeightKg = InputBox("Enter weight in kilograms")
BMI = WeightKg / (HeightM * HeightM)
End Sub
- Display a message box to tell the user what their BMI is:
Sub WhatsMyBMI()
Dim HeightM As Double
Dim WeightKg As Double
Dim BMI As Double
HeightM = InputBox("Enter height in metres")
WeightKg = InputBox("Enter weight in kilograms")
BMI = WeightKg / (HeightM * HeightM)
MsgBox _
"Height: " & HeightM & "m" & vbNewLine & _
"Weight: " & WeightKg & "kg" & vbNewLine & _
"BMI: " & Format(BMI, "0.00"), _
vbInformation, _
"What's my BMI?"
End Sub
- Run the subroutine to check that it produces a result:
If you were curious what the number means you could check it against the categories listed here.
- Save and close the file.