CODEWITHSUNDEEP
excelvbaintegerlong-integer
Pawel
Pawel

Reputation: 555

Test if value is Long or Integer (no decimal places)

I need to test if the value in textbox1 and textbox2 is integer or long (with no decimal places).

I tried something like this:

    Dim x As Integer
    Dim y As Integer

    x = TextBox1.Value
    y = TextBox2.Value

    If x = Int(x) Then
        'my code
    Else
        MsgBox("value is not an integer")

If I put x=2.3 it does not display MsgBox but it rounds the value to 2.

Upvotes: 0

Views: 5681

Answers (5)

MrEribus
MrEribus

Reputation: 21

it looks like you are getting your values from a textbox, so my proposed solution is around the textbox change event.

Adding a reference to 'microsoft vbscript regular expressions' to your project is really handy.

I add a private/public reference to this object at the public declarations area at the top of your class/form, so you can refer to it anywhere in your class/form and a Temporary String for holding the previous textbox value.

Private myRegex as RegExp
private mTempText as string

For this particular solution I have added a text box to the form and this code to the change event

Private Sub txtCount_Change()

  If Not MyRegex.Test(txtCount.value) Then
    txtCount.value = mTempText
  Else
    mTempText = txtCount.value
  End If

End Sub

I set up a new instance of the the RegExp and set the pattern in the initialization code.

Private Sub UserForm_Initialize()

  Set MyRegex = New RegExp

  With MyRegex
'   .Global = True '' not needed for numbers
    .Pattern = "^[0-9]{0,4}$" ' and whole number between 0 and 9 up to 4 
'    digits, this can be changed to more or less digits as required, 
'    .IgnoreCase = False '' not needed for numbers
  End With

End Sub

The text change event code allows the user to add only whole numbers one at a time up to 4, else it resets back to the last correct value.

Upvotes: 0

Ιωάννης Καραδημητρόπουλος
Ιωάννης Καραδημητρόπουλος

Reputation: 1

Here is a piece of code that solved the issue in my case. It is noted that additional test could be done for checking if string is a proper number by using the function isNumeric(checkString)

Function isInteger(num As String) As Boolean
Dim iNum As Integer
On Error Resume Next

if Not isNumeric(num) then exit function
iNum = CInt(num)
If Err.Number = 0 Then isInteger = True
Err.clear

End Function

Sub test_isInteger()
Dim bool As Boolean
Dim str As String

str = "1234567890"
bool = isInteger(str)  ' --> FALSE

str = "12345"
bool = isInteger(str)  ' --> TRUE

End Sub

Upvotes: 0

ChrisB
ChrisB

Reputation: 3205

You can't Dim var as Decimal in VBA but you can Dim var as Variant and use var = CDec(...) to store a decimal data type in a variant. Even then, I've found that decimals with 14 or 15 zeroes after the decimal followed by a number (or the equivalent decimal subtracted from a whole number) are rounded to a whole number. I haven't found a solution yet that differentiates tiny decimals from whole numbers.

Upvotes: 1

FDavidov
FDavidov

Reputation: 3675

Here is another approach (pseudo-code, so check syntax!)

Dim l_Temp_D1 as Decimal
Dim l_Temp_D2 as Decimal
Dim l_Temp_I  as Integer

l_Temp_I  = TextBox1.Value
l_Temp_D1 = TextBox1.Value
l_Temp_D2 = l_Temp_I

if (not IsNumber(TextBox1.Value)) then
    ' Error!!!
End If

if (l_Temp_D1 = l_Temp_D2) then
  ' Integer
  ...
else
  ' Float
  ...
End If

Upvotes: 0

Bathsheba
Bathsheba

Reputation: 234665

Since you've specified the type of x to be an Integer, the conversion from the text box string to the Integer has already taken place. Hence Int(x) is a no-op.

One fix would be to use something like

If IsNumber(TextBox1.Value) Then
    If Fix(TextBox1.Value) = TextBox1.Value Then
        'I am an integeral type.
    End If
End If

Here, Fix truncates a numeric type, and I'm relying on VBA to make the correct comparison. Note that VBA doesn't implement a short-circutted And so you need to use a nested If or similar.

Upvotes: 4

Related Questions