Length of integer

[Fred]

Hi
I need to limit the number of characters used in a cell to 3 digits. I have
an input box for data entry.

The result I get for len(cpacode) is 2, irregardless of the number of digits
I use. Len returns the number of bytes for the variable because I declared
it to be an Integer.

If I declare the variable as a string, text characters can then be entered
into the variable. I need the variable to only contain numbers. How do I do
this?

Dim CPACode As Integer

CPACode = InputBox("Please enter in the new value for the CPA Code.")
'Get the new value for the CPA Code
Do Until Len(CPACode) = 3 'If the CPA Code is not 10
characters long, get the value again
CPACode = InputBox("The length of the CPA Code must be 3 characters.
Please make sure that you have the correct CPA Code.") 'Get the correct
value for the CPA Code
Loop 'End loop
If Len(CPACode) = 3 Then 'If the length of the CPA
Code is 3 characters long
Range("B5").Value = CPACode 'Put the new value for CPA
Code in cell B5
End If 'End the test


Thanks
Fred

 

[Barb Reinhardt]

You could try something like this to convert the string to an integer. You
can check the string length as part of your code.

Sub test()
Dim myString As String
Dim myVal As Integer

myString = "12"
myVal = CInt(myString)
Debug.Print myString, myVal

End Sub

 

[Dave Peterson]

Instead of checking the length, you could check to see if it's smaller than or
equal to 999 (and greater than 0???).

Option Explicit
Sub testme01()
Dim CPACode As Long


CPACode = -999 'invalid
Do
If CPACode < 1 _
Or CPACode > 999 Then
CPACode = CLng(Application.InputBox _
(prompt:="Please enter in the new value " _
& "for the CPA Code.", _
Type:=1))
Else
'it's ok, get out
Exit Do
End If
Loop

ActiveSheet.Range("B5").Value = CPACode
End Sub

Using the application.inputbox with type:=1 will avoid any runtime errors and
force the user to type a number.

 

[Fred]

Thanks Dave. Works great, and has error checking built in. Even better!

 

[Bernd P]

Hello,

Just another variant:

Sub testme02()
Dim CPACode As Long

Do
CPACode = CLng(Application.InputBox _
(prompt:="Please enter in the new value " _
& "for the CPA Code.", Type:=1))
Loop While CPACode > 999 Or CPACode < 1

ActiveSheet.Range("B5").Value = CPACode
End Sub

Regards,
Bernd