Functions in VBA

Validation

VBA provide rich collection of methods, functions can help user to build various own customized solutions. I am going to give some examples of VBA functions which developer usually looks for while developing programs.

VarType

This method provides datatype that a variable holds. VBA offers various constants to validate conditions like given below:

Constants

• vbEmpty 0
• vbNull 1
• vbInteger 2
• vbLong 3
• vbSingle 4
• vbDouble 5
• vbCurrency 6
• vbDate 7
• vbString 8
• vbObject 9
• vbError 10

Checking a valid integer data type

Sub CheckDataType()
    If VarType(my_variable) = vbInteger Then
		MsgBox"Integer"
	Else
		MsgBox"Not Integer"
	End If
End Sub

Check valid Integer Value Method – 1

Public Function ValidateNumber(oValue As String) As Boolean
    If IsNumeric(oValue) Then
        ValidateNumber = True
    Else
        ValidateNumber = False
    End If
End Function

This method returns true/false based on value provided as input to the parameter oValue.

Using TypeName Method-2

Public Function GetDataType(oValue As String) As String
	Dim i As Integer 
	Dim j As Long

	i = 250
	j = 2500000

	If TypeName(i) = "Integer" Then
		GetDataType = "Variable ""i"" is an integer."
	Else
		GetDataType = "Variable ""i"" is a: " & TypeName(i)
	End If

	If TypeName(j) = "Integer" Then
		GetDataType = "Variable ""j"" is an integer."
	Else
		GetDataType = "Variable ""j"" is a: " & TypeName(j)
	End If
End Function

Email Address

Public Sub CheckValidEmail()
    Dim oEmail As String
    oEmail = InputBox("Enter Email Address : ", "E-Mail Address")
     ' Check email syntax
    If IsEmailValid(oEmail) Then
        MsgBox oEmail & " is a valid e-mail"
    Else
        MsgBox oEmail & " is not a valid e-mail"
    End If
End Sub

Public Function EmailValidateFunction(oEmailAddress As String) As Boolean
    Dim oStringArray As Variant
    Dim oItem As Variant
    Dim i As Long
    Dim c As String
    Dim isValdAddress As Boolean
    isValdAddress = True
    
	'identify position of @ in the string	
    i = Len(oEmailAddress) - Len(Application.Substitute(oEmailAddress, "@", ""))
    If i <> 1 Then 
		EmailValidateFunction = False
		Exit Function
	End If
    ReDim oStringArray(1 To 2)
    oStringArray(1) = Left(oEmailAddress, InStr(1, oEmailAddress, "@", 1) - 1)
    oStringArray(2) = Application.Substitute(Right(oEmailAddress, Len(oEmailAddress) - Len(oStringArray(1))), "@", "")
    For Each oItem In oStringArray
        If Len(oItem) <= 0 Then
            isValdAddress = False
            EmailValidateFunction = isValdAddress
            Exit Function
        End If
        For i = 1 To Len(oItem)
            c = LCase(Mid(oItem, i, 1))
            If InStr("abcdefghijklmnopqrstuvwxyz_-.", c) <= 0 And Not IsNumeric(c) Then
                isValdAddress = False
				'Return
                EmailValidateFunction = isValdAddress
                Exit Function
            End If
        Next i
        If Left(oItem, 1) = "." Or Right(oItem, 1) = "." Then
            isValdAddress = False
			'Return
            EmailValidateFunction = isValdAddress
            Exit Function
        End If
    Next oItem
    If InStr(oStringArray(2), ".") <= 0 Then
        isValdAddress = False
		'Return
        EmailValidateFunction = isValdAddress
        Exit Function
    End If
    i = Len(oStringArray(2)) - InStrRev(oStringArray(2), ".")
    If i <> 2 And i <> 3 Then
        isValdAddress = False
		'Return
        EmailValidateFunction = isValdAddress
        Exit Function
    End If
    If InStr(oEmailAddress, "..") > 0 Then
        isValdAddress = False
		'Return
        EmailValidateFunction = isValdAddress
        Exit Function
    End If
	'Return
    EmailValidateFunction = isValdAddress
End Function

Regular Expression

To validate a mail we can use another method which provides short code to achieve same goal mentioned in previous example. RegExp is a scripting library provided by Microsoft which we need to bring in our project by adding as reference as shown below:

Code example

Public Sub CheckEmail()
    Dim txtEmail As String
    txtEmail = InputBox("Enter Email Address:", "E-Mail Address")
     ' Check e-mail syntax
    If IsEmailValid(txtEmail) Then
        MsgBox txtEmail & " is a valid e-mail"
    Else
        MsgBox txtEmail & " is not a valid e-mail"
    End If
End Sub

Public Function IsEmailValid(ByVal strEmailAddress As String) As Boolean
    On Error GoTo Errh
    
    Dim objRegExp As New RegExp
    Dim blnIsValidEmail As Boolean
    
    objRegExp.IgnoreCase = True
    objRegExp.Global = True
    objRegExp.Pattern = "^([a-zA-Z0-9_\-\.]+)@[a-z0-9-]+(\.[a-z0-9-]+)*(\.[a-z]{2,3})$"
    
    blnIsValidEmail = objRegExp.Test(strEmailAddress)
    IsEmailValid = blnIsValidEmail
      
    Exit Function
    
Errh:
	If Err.Number <> 0 Then
    IsEmailValid = False
		MsgBox "Error#:  " & Err.Number & vbCrLf & vbCrLf & Err.Description
	End If
End Function

Null or Empty string

Public Function IsNullOrEmpty(oValue As String) As Boolean
    If IsEmpty(oValue) Then
        IsNullOrEmpty = True
    Else
        IsNullOrEmpty = False
    End If
End Function

Len Function

Public Function IsNullOrEmpty(oValue As String) As Boolean
    If Len(oValue)>0 Then
        IsNullOrEmpty = False
    Else
        IsNullOrEmpty = True
    End If
End Function

IsDate Function

Public Function IsValidDate(oDate As Date) As Boolean
    If IsDate(oDate) Then
        IsValidDate = True
    Else
        IsValidDate = False
    End If
End Function

Choose Function

Excel CHOOSE function returns a value from a list of values which should be a variant Parameter Array.

Syntax

=CHOOSE(Index As Single, ParamArray Choice() As Variant)

Code example

Function TestChoose() As String
    TestChoose = Choose(2, "Test", "Test1", "Test2")
End Function

Output

Test1

Asc() Function

Function Test(oChar As String) As String
    Test = Asc(oChar)
End Function

Output

65

Concatenate or &

Function Test(oVal1 As String, oVal2 As String) As String
    Test = oVal1 & oVal2
End Function

FORMAT() Function

This function is very useful function in VBA which holds the capability to perform various formatting on a string.

Sub CallingMethod()
	''Date Formatting in VBA
	Debug.Print "Full Date " & Format(Now, "DD-MMM-YYYY")
	Debug.Print "Date With Time " & Format(Now, "DD-MMM-YYYY: hh:mm:ss")
	Debug.Print "Short Day " & Format(Now, "DDD")
	Debug.Print "Long Day " & Format(Now, "DDDD")
	Debug.Print "Month Number " & Format(Now, "MM")
	Debug.Print "Short Month " & Format(Now, "MMM")
	Debug.Print "Long Month " & Format(Now, "MMMM")
	Debug.Print "Hours " & Format(Now, "HH")
End Sub

Output

Full Date 09-Dec-2017
Date With Time 09-Dec-2017: 18:35:40
Short Day Sat
Long Day Saturday
Month Number 12
Short Month Dec
Long Month December
Hours 18

There are many inbuilt functions listed below:

• INSTR() : Returns the position of the first occurrence of a substring in a string.
• INSTRREV() : Returns the position of the first occurrence of a string in another string, starting from the end of the string.
• LCASE() : Converts string in Lower Case.
• LEFT() : Returns a substring from a string, starting from the left-most character.
• LEN() : Returns length of a string.
• LTRIM() : Removes leading spaces from a string.
• MID() : Extracts a substring from a string (starting at any position).
• REPLACE() : Replaces a sequence of characters in a string with another set of characters.
• RIGHT() : Extracts a substring from a string starting from the right-most character.
• RTRIM() : Removes trailing spaces from a string.
• SPACE() : Returns a string with a specified number of spaces.
• SPLIT() : Used to split a string into substrings based on a delimiter.
• STR() : Returns a string representation of a number.
• STRCOMP() : Returns an integer value representing the result of a string comparison.
• STRCONV() : Returns a string converted to uppercase, lowercase, proper case or Unicode.
• STRREVERSE() : Returns a string whose characters are in reverse order.
• TRIM() : Returns a text value with the leading and trailing spaces removed.
• UCASE() : Converts string in Upper Case.
• VAL() : Returns the numbers found in a string.
• ISERROR(): Helps checking error.
• ISNULL() : Helps getting Object State.
• UBOUND() : Returns upper boundary of an Array.
• LBOUND() : Returns lower boundary of an Array.

Next>> GetObject Vs. CreateObject VBA

Share on Facebook

Tweet