Excel Visual Basic for Applications online training

This page has 0 threads | Add post

7,359 reviews

Self-paced training

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.4 - Capturing Numbers/Dates

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Capturing Different Data Types

Why This Works

Head office

London

Manchester