CODEWITHSUNDEEP
vbaexcel
olbinado11
olbinado11

Reputation: 161

Setting the Default TextBox.Value TypeName in Excel VBA

I have a vba function that checks if a user-input from a text box is positive integer or not. The code below: 

Public Function IsPosInteger(n As Variant) As Boolean  
    If IsNumeric(n) = True Then
        If (n = Int(n)) And n > 0 Then
            IsPosInteger = True
        Else
            IsPosInteger = False
        End If
    Else
        IsPosInteger = False
End If
End Function

The problem is that upon testing the function still returns false for a valid positive integer. Upon further investigation, I noticed that the variable type by default for texbox values is String. Probably the main reason why IsNumeric is returning false.

Function below is what I used to determine the type of the variable.

TypeName(n)

Upvotes: 1

Views: 491

Answers (2)

SendETHToThisAddress
SendETHToThisAddress

Reputation: 3704

Public Function IsPosInteger(s As String) As Boolean 'boolean data type is false by default.
    If (IsNumeric(s) = False) Then Exit Function
    If (s < 1) Then Exit Function
    If (InStr(s, ".") = False) Then IsPosInteger = True
End Function

Function tests that the input is numeric, not less than 1, and does not contain a decimal. Here is an example of how you could use it in your calling sub:

Sub TestInput()
    Dim sUserInput As String
    Dim boolPositiveInt As Boolean
    Dim i As Integer

    sUserInput = Range("A1").Value2
    boolPositiveInt = IsPosInteger(sUserInput)
    If (boolPositiveInt = False) Then
        MsgBox "Invalid input. Please enter a positive integer"
        Exit Sub
    End If

    i = CInt(sUserInput)
    'continue working with integer variable here
End Sub

Upvotes: 1

K.Dᴀᴠɪs
K.Dᴀᴠɪs

Reputation: 10139

This worked for me.

I used an inputbox that contained:

  • strings (False)
  • negative numbers (False)
  • positive numbers (true)
  • positive numbers and letters (false)

Public Function IsPosInteger(n As Variant) As Boolean
    If n > 0 And IsNumeric(n) Then IsPosInteger = True
End Function

Now, the other issue may arise that the value n is still technically type String due to the nature of the inputbox -- even if it passes the test of this function. If you wish to change this behavior, continue reading.

To do this, ensure that you are using ByRef (when this is intentional, I usually type out ByRef on the argument, even though it is automatically assumed by VBA that any argument passed to a function is ByRef if it doesn't explicitly state ByVal).

If this is the outcome you are wanting, then you can use this function:

Public Function IsPosInteger(ByRef n As Variant) As Boolean
    If n > 0 And IsNumeric(n) Then 
        IsPosInteger = True
        n = clng(n) '<-- This converts the variant (currently a string) n to type long,
                    '    only if it passes the test
    end if
End Function

You must ensure that n in the calling routine is of type Variant, else you will encounter an error.

Upvotes: 1

Related Questions