Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
546 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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.
Click here to download the file needed for this section.
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.
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.
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.
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:
Sub TakeAByte()
Dim b As Byte
b = Application.InputBox( _
Prompt:="Enter a number between 0 - 255", _
Type:=1)
End Sub
Sub TakeAByte()
Dim b As Byte
b = Application.InputBox( _
Prompt:="Enter a number between 0 - 255", _
Type:=1)
MsgBox "You entered " & b
End Sub
Actually, I entered 123.45 but let's not split hairs.
You should see a message like this.
I entered the value 256, which is outside the range of valid numbers for the Byte data type.
Sub TakeAByte()
Dim b As Variant
b = Application.InputBox( _
Prompt:="Enter a number between 0 - 255", _
Type:=1)
MsgBox "You entered " & b
End Sub
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
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
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.