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?

Excessive

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:

Using Long

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:

Enter value

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:

Rounded

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:

Overreaching

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

 

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

Overflow

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:

test for validity

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:

  1. Using the same module, create a subroutine called TakeAByte.
  2. 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

  1. 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

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

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

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

You should see a message like this.

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

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

 
  1. 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

  1. 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

  1. 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

  1. Save and close the workbook.
This page has 0 threads Add post