VBA Validation Module

Paladine 1 Tallied Votes 269 Views Share

VBA Validation Module

Attribute VB_Name = "basValidation"

Option Explicit
'    Name:     basValidationFunctions

'    Purpose:  each function will perform a primitive datatype
'              validation and return a boolean value

'    Note:     The variant data type is used for all parameters as
'              the form sets the value property for a text/combo box
'              to NULL if the user does not supply data.  Variant is
'              the only data type that stores the NULL value.
'
'              BCS 240/340 Validation Code Re - Instructor: Shane Bell


Public Function RequiredInteger(ByVal varNumber As Variant) As Boolean

'    Validate a required integer field

'    Criteria: cannot be null, must be a whole number

'    establish error handler

'     On Error GoTo RequiredInteger_Err

'    Assume invalid data

     RequiredInteger = False

'    Check criteria

     If IsNumeric(varNumber) Then
          If CDbl(varNumber) = CLng(varNumber) Then
               RequiredInteger = True
          End If
     End If

'RequiredInteger_Exit:
'
'     Exit Function
'
'RequiredInteger_Err:
'
'     Call ErrorHandler("RequiredInteger", "basValidationFunctions")
'     End

End Function

Public Function RequiredNumber(ByVal varNumber As Variant) As Boolean

'    Validate a required numeric field

'    Criteria: cannot be null, must be a number

'    establish error handler

'     On Error GoTo RequiredNumber_Err

'    Assume invalid data

     RequiredNumber = False

'    Check criteria

     If IsNumeric(varNumber) Then
          RequiredNumber = True
     End If

'RequiredNumber_Exit:
'
'     Exit Function
'
'RequiredNumber_Err:
'
'     Call ErrorHandler("RequiredNumber", "basValidationFunctions")
'     End

End Function

Public Function RequiredDate(ByVal varDate As Variant) As Boolean

'    Validate a required date field

'    Criteria: cannot be null, must be a date

'    establish error handler

'     On Error GoTo RequiredDate_Err

'    Assume invalid data

     RequiredDate = False

'    Check criteria

     If Not IsNumeric(varDate) Then
          If IsDate(varDate) Then
               RequiredDate = True
          End If
     End If
'
'RequiredDate_Exit:
'
'     Exit Function
'
'RequiredDate_Err:
'
'     Call ErrorHandler("RequiredDate", "basValidationFunctions")
'     End

End Function

Public Function RequiredText(ByVal varText As Variant) As Boolean

'    Validate a required text field

'    Criteria: cannot be null, cannot be a date, cannot be a number

'    establish error handler

'     On Error GoTo RequiredText_Err

'    Assume invalid data

     RequiredText = False

'    Check criteria

     If Not IsNull(varText) Then
          If Not IsDate(varText) Then
               If Not IsNumeric(varText) Then
                    RequiredText = True
               End If
          End If
     End If

'RequiredText_Exit:
'
'     Exit Function
'
'RequiredText_Err:
'
'     Call ErrHandler("RequiredText", "basValidationFunctions")
'     End

End Function

Public Function RequiredAlphaNumeric(ByVal varAlphaNumeric As Variant) As Boolean

'    Validate a required text field

'    Criteria: cannot be null, cannot be a date

'    establish error handler

'     On Error GoTo RequiredAlphaNumeric_Err

'    Assume invalid data

     RequiredAlphaNumeric = False

'    Check criteria

     If Not IsNull(varAlphaNumeric) Then
          If Not IsDate(varAlphaNumeric) Then
               RequiredAlphaNumeric = True
          End If
     End If

'RequiredAlphaNumeric_Exit:
'
'     Exit Function
'
'RequiredAlphaNumeric_Err:
'
'     Call ErrorHandler("RequiredAlphNumeric", "basValidationFunctions")
'     End

End Function

Public Function NotRequiredInteger(ByVal varNumber As Variant) As Boolean

'    Validate a not required integer field

'    Criteria: must be a whole number or null

'    establish error handler

'     On Error GoTo NotRequiredInteger_Err

'    Assume invalid data

     NotRequiredInteger = False

'    Check criteria

     If IsNull(varNumber) Then
          NotRequiredInteger = True
     Else
          If IsNumeric(varNumber) Then
               If CDbl(varNumber) = CLng(varNumber) Then
                    NotRequiredInteger = True
               End If
          End If
     End If

'NotRequiredInteger_Exit:
'
'     Exit Function
'
'NotRequiredInteger_Err:
'
'     Call ErrorHandler("NotRequiredInteger", "basValidationFunctions")
'     End

End Function

Public Function NotRequiredNumber(ByVal varNumber As Variant) As Boolean


'    Validate a not required numeric field

'    Criteria: must be a number or null

'    establish error handler

'     On Error GoTo NotRequiredNumber_Err

'    Assume invalid data

     NotRequiredNumber = False

'    Check criteria

     If IsNull(varNumber) Then
          NotRequiredNumber = True
     Else
          If IsNumeric(varNumber) Then
               NotRequiredNumber = True
          End If
     End If
'
'NotRequiredNumber_Exit:
'
'     Exit Function
'
'NotRequiredNumber_Err:
'
'     Call ErrorHandler("NotRequiredNumber", "basValidationFunctions")
'     End

End Function

Public Function NotRequiredDate(ByVal varDate As Variant) As Boolean


'    Validate a not required date field

'    Criteria: must be a date or null

'    establish error handler

'     On Error GoTo NotRequiredDate_Err

'    Assume invalid data

     NotRequiredDate = False

'    Check criteria

     If IsNull(varDate) Then
          NotRequiredDate = True
     Else
          If IsDate(varDate) Then
               NotRequiredDate = True
          End If
     End If

'NotRequiredDate_Exit:
'
'     Exit Function
'
'NotRequiredDate_Err:
'
'     Call ErrorHandler("NotRequiredDate", "basValidationFunctions")
'     End

End Function

Public Function NotRequiredText(ByVal varText As Variant) As Boolean


'    Validate a not required text field

'    Criteria: can be null or any other value except a date or a number

'    establish error handler

'     On Error GoTo NotRequiredText_Err

'    Assume invalid data

     NotRequiredText = False

'    Check criteria

     If IsNull(varText) Then
          NotRequiredText = True
     Else
          If Not IsDate(varText) Then
               If Not IsNumeric(varText) Then
                    NotRequiredText = True
               End If
          End If
     End If

'NotRequiredText_Exit:
'
'     Exit Function
'
'NotRequiredText_Err:
'
'     Call ErrorHandler("NotRequiredText", "basValidationFunctions")
'     End

End Function

Public Function NotRequiredAlphaNumeric(ByVal varAlphaNumeric As Variant) As Boolean


'    Validate a required text field

'    Criteria: cannot be a date

'    establish error handler

     On Error GoTo NotRequiredAlphaNumeric_Err

'    Assume invalid data

     NotRequiredAlphaNumeric = False

'    Check criteria

     If IsNull(varAlphaNumeric) Then
          NotRequiredAlphaNumeric = True
     Else
          If Not IsDate(varAlphaNumeric) Then
               NotRequiredAlphaNumeric = True
          End If
     End If

'NotRequiredAlphaNumeric_Exit:
'
'     Exit Function
'
'NotRequiredAlphaNumeric_Err:
'
'     Call ErrorHandler("NotRequiredAlphaNumeric", "basValidationFunctions")
'     End

End Function
pat_dohc -1 Newbie Poster

VBA Validation Module

hi,,
i am a student currently doing a project on an automation program. the function of a program is to validate a user from ecel. this is by checking whether the the user's id no. from the 1st spreadsheet exists in the second spreadsheet...is it possible..i tried to search everywhere for the code...but failed to get anything related...please respond

commented: why you need to reply to a code snippet posted 7 years back. -1
yathish.b10 0 Newbie Poster
Sub demo()
    Dim strDate As String
    strDate = "12/31/2011" 'Date to be validated
    MsgBox OKdate(strDate) 'Function call to validate date
End Sub

Function OKdate(S) 'eight digits MM/dd/yyyy
  Dim mn, dt, yr
  On Error Resume Next
  mn = left(S, 2) ' First 2 digits of the string
  dt = Mid(S, 4, 2) ' Mid 2 digits of the string
  yr = Right(S, 4) ' Last 4 digits of the string
  
  If ((mn > 12) Or (dt > 31) Or (yr > CInt(Year(Now)))) Then
    Err = 1
    GoTo label
  End If
  OKdate = CDate(mn & "/" & dt & "/" & yr)
label:
  OKdate = (Err = 0)
End Function
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.