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
Output in immediate window:11
Hello Worl
o World
World

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
Output in immediate window

First test CStr on all types
CStr(i) = ’19’
CStr(d) = ‘12.6’
CStr(l) = ‘32768’
CStr(s) = ‘42.001’Second, CInt
CInt(i) = 19
CInt(d) = 13
CInt(l) = Overflow Error. Integers are valued <32768
CInt(s) = 42Third, CDbl
CDbl(i) = 19
CDbl(d) = 12.6
CDbl(l) = 32768
CDbl(s) = 42.001Fourth, CLng
CLng(i) = 19
CLng(d) = 13
CLng(l) = 32768
CLng(s) = 42

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:

Untitled-27

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
Output in immediate window:
27/12/2012
27/12/2012 22:50:08
18/12/2012
2012
12
27
21/02/2014
2561

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
Output in immediate window:Output in immediate window:False
True
True
False

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
Output in immediate window:True
False
False
False
False
True

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
Output in immediate window:isObject(varA) = False TypeName(varA) = Empty
isObject(varB) = True TypeName(varB) = Nothing
isObject(varC) = False TypeName(varC) = Date
isObject(varD) = True TypeName(varD) = Nothing

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

Related Posts

Database Functions
Writing A Custom Function

One comment

Leave a Reply

Your email address will not be published. Required fields are marked *

Visit Us On TwitterVisit Us On FacebookVisit Us On Youtube