Built-In Functions

In this blog post we cover some commonly used built-in functions that VBA coders need to know.

String Functions

• Len(s) – returns the length of String s.
• Left(s, n) – returns a substring of s that is n chars long from the left of the string s.
• Right(s, n) – returns a substring of s that is n chars long from the right of the string s.
• Mid(s,nb,ne) – returns a substring of s from characters nb to ne, inclusive.

Sub testStrings()
    Debug.Print Len("Hello World")
    Debug.Print Left("Hello World", 10)
    Debug.Print Right("Hello World", 7)
    Debug.Print Mid("Hello World", 7, 10)
End Sub

Conversion

• CInt( anything ) – converts anything into an Integer type (if possible).
• Cdbl( anything ) – converts anything into an Double type (if possible).
• Clng( anything ) – converts anything into an Long type (if possible).
• CStr( anything ) – converts anything into a String.
• CDate(string) – converts a string to a Date type (if possible).

If any of the conversion functions are passed a variable that cannot be parsed – e.g. CInt(“oioi!”) – a Type Mismatch error occurs.

Sub testConversions()
Dim i As Integer, d As Double, l As Long, s As String
    i = 19
    d = 12.6
    l = 32768
    s = "42.001"
    
    ' to display the answers provided by the conversion functions we have to
    'CStr() all the number variables 10   or VBA will throw a Type Mismatch error
    ' so just to prove that CStr works we'll do it first
    Debug.Print "First test CStr on all types"
    Debug.Print "CStr(i) = '" + CStr(i) + "'" ' '42'
    Debug.Print "CStr(d) = '" + CStr(d) + "'" ' '42.001'
    Debug.Print "CStr(l) = '" + CStr(l) + "'" ' '42'
    Debug.Print "CStr(s) = '" + CStr(s) + "'" ' '42.001'
    Debug.Print ""
    Debug.Print "Second, CInt"
    Debug.Print "CInt(i) = " + CStr(CInt(i)) ' 19
    Debug.Print "CInt(d) = " + CStr(CInt(d)) ' 13
    Debug.Print "CInt(l) = Overflow Error. Integers are valued <32768"
    Debug.Print "CInt(s) = " + CStr(CInt(s)) ' 42
    Debug.Print ""
    Debug.Print "Third, CDbl"
    Debug.Print "CDbl(i) = " + CStr(CDbl(i))
    Debug.Print "CDbl(d) = " + CStr(CDbl(d))
    Debug.Print "CDbl(l) = " + CStr(CDbl(l))
    Debug.Print "CDbl(s) = " + CStr(CDbl(s))
    Debug.Print ""
    Debug.Print "Fourth, CLng"
    Debug.Print "CLng(i) = " + CStr(CLng(i)) ' 19
    Debug.Print "CLng(d) = " + CStr(CLng(d)) ' 13
    Debug.Print "CLng(l) = " + CStr(CLng(l)) ' 32768
    Debug.Print "CLng(s) = " + CStr(CLng(s)) ' 42
    
End Sub

Date and Time Functions

Date and time functions are quite complex due to the nature of dates.  VBA has a special way of handling dates by putting # around them, for example dMyDate = #18-Dec-2012#. Here are some of the functions to help with dates.

• Date () – returns  the current date.
• Now() – returns the current date and time.
• DateSerial(year, month, day) – returns a Date object if parameters are valid.
• Year(date) – returns the year of date as an integer.
• Month(month) – returns the month of date as an integer, 1-12.
• Day(Day) – returns the day of date as an integer, 1-31.
• DateDiff(interval, date, date) – date are dates, interval is day, month, year, etc.
• DateAdd(interval, number, date) – add to date intervals multiplied by number

Date Intervals

In the above interval refers to one of the following:

Note: The Date function returns the current date (as defined by your operating system) so the results you get from the following example will be different from the results we obtained.

Sub testDateTime()
    Debug.Print Date
    Debug.Print Now()
    Debug.Print DateSerial(2012, 12, 18)
    Debug.Print Year(Date)
    Debug.Print Month(Date)
    Debug.Print Day(Date)
    Debug.Print DateAdd("d", 421, Date)
    Debug.Print DateDiff("d", Date, #1/1/2020#)
End Sub

Is Functions

When inspecting whether a variable has a value we usually use the equals = operator, but equals does not work if a variable is null, empty or is nothing. Nor can equals be used to interrogate the variable for its type.  There are special ‘Is’ operators which provide that functionality.

• IsDate(anything) – returns true if the variable is a date.
• IsArray(anything)  – return true if the variable is an array.
• IsNull(anything)  – returns true if the variable is Null.
• IsEmpty(anything)  – returns true when the variable is uninitialized.
• IsObject(anything)  – returns true when the variable is an Object.
• TypeName(anything) – returns a string.

IsDate and IsEmpty

Sub dateAndEmptyFunctions()
    Dim myDate
    
    Debug.Print IsDate(myDate)
    Debug.Print IsEmpty(myDate)
    
    myDate = #12/20/2012#
    Debug.Print IsDate(myDate)
    Debug.Print IsEmpty(myDate)

End Sub

IsArray and IsNull

We use the IsArray function to determine whether an variable is an array.

Sub arrayAndNullFunctions()
    Dim myArray As Variant
    myArray = Array("first_name", "surname", "dob", "town", Null)
    
    Debug.Print IsArray(myArray)
    Debug.Print IsNull(myArray(0))
    Debug.Print IsNull(myArray(1))
    Debug.Print IsNull(myArray(2))
    Debug.Print IsNull(myArray(3))
    Debug.Print IsNull(myArray(4))

End Sub

IsObject and TypeName

We use the IsObject function to determine whether a variable is an object.

Sub objectAndTypeNameFunctions()
    Dim varA, varB As Object, varC As Date, varD As DAO.Recordset
    
    Debug.Print
    Debug.Print "isObject(varA) = "; CStr(IsObject(varA)); Tab; "TypeName(varA) = "; TypeName(varA)
    Debug.Print "isObject(varB) = "; CStr(IsObject(varB)); Tab; "TypeName(varB) = "; TypeName(varB)
    Debug.Print "isObject(varC) = "; CStr(IsObject(varC)); Tab; "TypeName(varC) = "; TypeName(varC)
    Debug.Print "isObject(varD) = "; CStr(IsObject(varD)); Tab; "TypeName(varD) = "; TypeName(varD)

End Sub

Built-in functions are an integral part of VBA and getting to grips with them will help to improve your coding.

Related Posts