Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

Module 5 - Messages and User Inputs

Lesson 5.4 - The Excel Input Box

Topic 5.4.4 - Controlling Number Types

You can force an Excel input box to return a number by setting its type to 1. This sets the return data type to Double, but what if you wanted a different numeric data type?

It seems excessive to use the Double data type when we want to capture such a small value.

Files Needed

Click here to download the file needed for this section.

Completed Code

Click here to download a file which contains the sample code used on this page.

Download and extract the workbook linked to in the Files Needed section above.

Using a Specific Numeric Data Type

Open the VBE and find the MortgageCalculator subroutine in Module1. The subroutine uses a specific numeric data type to declare a variable which will capture the value entered into an input box:

Here we're using the Long data type to ensure that we end up with a whole number.

You can run the subroutine and enter a value into the input box as normal:

Here we've entered a number with decimal places.

Provided that you enter a number within the range allowed by the data type of the variable, the value returned by the input box will be converted implicitly into that type:

The result of this example is a whole number stored as a Long value.

Entering Invalid Data

If you enter a value outside the range of the data type you've used, things go somewhat less smoothly:

Here we're ambitiously asking to borrow 3 billion pounds.

Clicking OK on the input box results in a run-time error:

The value we've entered is beyond the range of the Long data type.

Using the Variant Data Type

One solution to this problem is to capture the value in a Variant variable and test whether it can be stored in a Long variable. In the example below we've changed the data type of the LoanAmount variable to Variant. We've then added an If statement to check whether the number entered falls within the desired range. If so, we use the CLng function to convert the value into the Long data type:

It's a lot more effort to do this, but it prevents the user from experiencing a run-time error.

To practise using specific data types:

Using the same module, create a subroutine called TakeAByte.
Declare a Byte variable and populate it using an Excel input box whose Type is set to 1:

Sub TakeAByte()

Dim b As Byte

b = Application.InputBox( _

Prompt:="Enter a number between 0 - 255", _

Type:=1)

End Sub

Add a message box which shows the user the value they entered:

Sub TakeAByte()

Dim b As Byte

b = Application.InputBox( _

Prompt:="Enter a number between 0 - 255", _

Type:=1)

MsgBox "You entered " & b

End Sub

Run the subroutine and enter a number between 0 and 255 (try using a number with decimal places and check what happens):

Actually, I entered 123.45 but let's not split hairs.

Run the subroutine and enter a value that can't be evaluated as a number:

You should see a message like this.

Run the subroutine and enter a number with a value of less than 0 or greater then 255:

I entered the value 256, which is outside the range of valid numbers for the Byte data type.

Change the data type of the variable to Variant:

Sub TakeAByte()

Dim b As Variant

b = Application.InputBox( _

Prompt:="Enter a number between 0 - 255", _

Type:=1)

MsgBox "You entered " & b

End Sub

After the line which displays the input box, add an If statement to check whether the value is within the valid range of the Byte data type:

Sub TakeAByte()

Dim b As Variant

b = Application.InputBox( _

Prompt:="Enter a number between 0 - 255", _

Type:=1)

If b < 0 Or b > 255 Then

MsgBox "That's an invalid number", vbCritical

Exit Sub

End If

MsgBox "You entered " & b

End Sub

After the If statement, add code to convert the variable to a Byte. Alter the message box to indicate that this has worked:

Sub TakeAByte()

Dim b As Variant

b = Application.InputBox( _

Prompt:="Enter a number between 0 - 255", _

Type:=1)

If b < 0 Or b > 255 Then

MsgBox "That's an invalid number", vbCritical

Exit Sub

End If

b = CByte(b)

MsgBox b & " is a " & TypeName(b)

End Sub

Save and close the workbook.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Using a Specific Numeric Data Type

Entering Invalid Data

Using the Variant Data Type

Head office

London

Manchester