Built-in Functions in Excel Visual Basic for Applications (VBA)

Before Excel 2003, you could get a complete list of Visual Basic Functions by looking in Visual Basic Help > Contents > Functions.

In Excel 2010, entering “Functions” into the Help Search box produces nothing useful.  Using a specific category, e.g. “Math Functions”, may be more productive.  Please be aware of what references to “Excel Worksheet Functions” mean.  They refer to the regular =SUM(), =COUNT() etc. built-in Functions in the Worksheet.  Meanwhile, Excel VBA Functions seem to have become a secret!

From Excel 2016, the situation is even worse (if that were possible).  Microsoft has put the help online –too bad if you have no internet!  Their online Help has a section “Office VBA Reference” that gives an overview of VBA programming in Office.  Further down there is the topic “Functions (Visual Basic for Applications)”.  That lists all the functions with no explanation of what they do.  And then has the gall to ask “Was this page helpful?”!

It is simpler to abandon Microsoft’s alleged “Help” and do a Google search for “Excel VBA Functions”. That turns up several useful sites including this site.

Here is a possible pitfall.  Be aware of what Help means when it refers to “WorksheetFunction.something”.   This is for using VBA to call a Function in the Excel Worksheet, not a function in VBA.  The VBA Functions can differ from the Worksheet functions.  They often have different names.  They can also behave differently.

To make your life easier, we list below, the Excel VBA Functions we have found most useful:

1.        Mathematical Built-in Functions

Useful Math Built-in functions include:

• Int and Fix: Integer (whole) value of a number.  The two treat negatives in opposite ways: Int(-8.4) = -9 (truncated smaller), Fix(-8.4)= -8 (towards zero).
• Abs: Absolute (negatives become positive)
• Sgn: Sign of number (-1 or +1 or 0)
• Log: Natural logarithm of a number (to base e)
• Exp: Exponent (antilogarithm) of a number (e to the power of the number)
• Rnd: A Random number between 0 and 1.
• Trigonometric functions: Sin, Cos, Tan, Atn.

2.        Text Built-in Functions – String Manipulation

As in Excel worksheet formulas, you can concatenate (add together) strings using the ampersand (&).  Literal strings should be in double quotes (“”).

Visual Basic is very tolerant of the concatenation of text with non-text strings.  This allows you for example to write string expressions like:

Msg$ = “Must be paid within ” & CreditPeriod& & _
  ” days after ” & daInvoice

where CreditPeriod& is a numeric variable and daInvoice is a date variable.  Note the blanks inside the quotes, and the underscore for a continuation line.

String functions that return strings come in two forms, those that end in a dollar sign ($) and those that do not.  The former ones return a string, the latter a variant.  If you are assigning the result of the function to a string, your code will be slightly quicker and more efficient if you use the “dollar” version.  For example, use Left$() instead of Left().

Some of the more-frequently-used string functions:

• Len(string) – Returns the length of the string
• Left$(string, n) – Returns the leftmost n characters of a string argument.
• Right$(string, n) – Returns the rightmost n characters of a string argument
• Mid$(string, start, length) – Returns a string that is part of another string.  Length (and the comma before it) is optional: If omitted or too long, you get the rest of the string.
• Lcase$(string) and Ucase$(string).  Returns string converted to lower or upper case, respectively.
• Trim$(string) – Returns the string with leading and trailing blanks removed
• InStr(start, Bigstr, Smallstr).  Returns the number of the first letter where Smallstr occurs within Bigstr.  Start (and the comma after it) is optional.  Read as “In string Bigstr, find Smallstr”.  This returns zero if Smallstr was not found in Bigstr.
• Format$(expression, fmt).  Formats a number, date, time, or string according to the format expression “fmt”.  The symbols you can use, are:

3.        Symbols Used by The Format$ Function

3.1      Numeric Format Symbols

The Format$ function uses the following symbols in the format expression:

0            Display a digit or a zero.

#            Display either a digit or nothing.

. (full stop)                 Decimal place holder (even if the decimal on your PC is a comma, as set in Windows Control Panel > Region > Additional settings).

.%          Show number as a Percentage

, (comma)                  Thousand separator (even if your Windows Thousands separator is not a comma).

E- E+      Scientific format, e.g. #0.00E+#0

/             Date separator between day, month, and year (depends on International setting).

– + $ ( ) space            Display the character itself.

\             To display any other character, precede it with a backslash (\).  Or enclose it in double quotation marks (” “).

A format expression for numbers can have one to four sections separated by semicolons (;).  The first section applies to positive values.  The second is for negative values, the third for zeros, and the fourth for Null or Empty values.

3.2      Date and Time Format Symbols

d            Display the day as a number without a leading zero (1-31).

dd          Display the day as a number with a leading zero (01-31).

ddd        Display the day as an abbreviation (Sun-Sat).

dddd      Display the day as a full name (Sunday-Saturday).

ddddd    Display the complete date (day, month, and year) formatted as per the Short Date Format in Control Panel > Region > Additional settings.  The US default Short Date Format is m/d/yy.

dddddd  Display the complete date (day, month, and year) formatted as per the Long Date Format in Control Panel > Region > Additional settings.  The US default Long Date Format is mmmm dd, yyyy.

w            Display the day of the week as a number (1-7).

ww          Display the week of the year as a number (1-54).

m or mm Display the month as a number without/with a leading zero.  If m or mm immediately follows h or hh, the minute rather than the month displays.

mmm      Display the month as an abbreviation (Jan-Dec).

mmmm   Display the month as a full name (January-December).

q            Display the quarter of the year as a number (1-4).

y            Display the day of the year as a number (1-366).

yy           Display the year as a two-digit number (00-99).

yyyy       Display the year as a four-digit number (0100-9999).

h or hh    Display the hour as a number without/with leading zeros

n or nn    Display the minute as a number without/with leading zeros.

s or ss    Display the second as a number without/with leading zeros.

ttttt         Display a time serial number as a complete time (including hour, minute, and second) formatted using the time separator defined by the Time Format in Windows Control Panel > Region > Additional settings.  A leading zero is displayed if the Leading Zero option is selected and the time is before 10:00 A.M. or P.M.  The default time format is h:nn:ss.

AM/PM or am/pm                 Use the 12-hour clock and display AM or am with any hour before noon; display PM or pm with any other hour.

A/P or a/p                  Short form of AM/PM or am/pm: One letter only.

The following are examples of date and time formats.

m/d/yyyy                   12/7/2023

dd-mmm-yy               07-Dec-23

d\ mmmm                  7 December

h:nn                           2:50

d/m/yy hh:nn              14/7/23 09:50

4.        Date/Time Calculations and Built-in Functions

As in the Excel worksheet, VBA stores dates internally as numbers.  The integer part is the number of days since 30 Dec 1899.  The fraction is the time of day, e.g. 0.5 is 12 noon.  For the number of days between two dates, simply subtract one date from the other.

For more complex date calculations, use DateAdd, DateDiff, or one of the other functions:

• CVDate – converts some other data type, e.g. string, to a date
• Date$ – converts a date to a string
• Date() – Today’s Date
• Now() –  Today’s Date and /Time
• DateAdd(interval, number, date) – adds the specified number of intervals to a date. The “interval” parameter can be “m” = month, “yyyy” = Year, “q” = Quarter, “y” = Day of year, “d” = Day, “ww“ = Week, “h” = Hour.
• Day(), Month(), Year() give that particular part of the date.
• IsDate checks for a valid date (returns True or False)
• DateDiff(interval, date1, date2) gives the number of intervals from Date1 (earlier) to Date2 (later).  The “interval” parameter is the same as for DateAdd’s interval.
• Weekday(date) gives a number representing the day of the week of date, where 1 = Sunday.

To find out if a date is between two dates, use the keyword Between:

If daPosted Between #01/11/2001# And #31/12/2001# Then

Note that Between is inclusive.  Notice the hash signs (#) enclosing dates.

5.        Conversion Functions

These convert one data type to another:

• Cbool, Cbyte, Ccur, Cdate, Cdbl, Cint, CLng, Csng, Cstr, CVar and CVErr all convert a number to the specified type
• Chr         Character from ASCII code number
• ChrB      Byte from ASCII code number
• Fix         Convert to integer
• Format$ Convert number to string according to format (see previous section)
• Hex        Hexadecimal String from number
• Int          Convert to integer
• Oct         String octal value of a number.
• QBColor VB RGB colour code for QB colour number
• RGB       Number representing an RGB colour value
• Str$        Convert number to string
• Val         Convert String to number
• Asc        ASCII number of first character in string
• AscB      ASCII number of first byte in string

You might use Format and Val, but most type conversions occur as required and do not need a specific conversion function.

6.        Inspection Functions

These give information about variables.  You would use them to examine the parameters passed to a VB Procedure (Sub or Function).

• IsArray, IsDate, IsEmpty, IsError, IsMissing, IsNull, IsNumeric, IsObject all return true or false depending on the type of variable.
• Lbound       Lower bound of an array
• Ubound      Upper bound of an array
• TypeName   Main Type of variable (e.g.. “Integer”)
• VarType      subtype of variable (e.g.. vbLong)

7.        Immediate IF

The syntax of the Immediate If is:

IIf(expr, truepart, falsepart)

where:

expr                  is the Expression you want to check
truepart             is the Value or expression returned if expr is True (-1)
falsepart           is the Value or expression returned if expr is False (0)

Warning! Both TruePart and FalsePart are evaluated no matter which one is required, thus the expression

IIf(A = 0, 9999, B / A)

will return an error, not 9999, if A = 0.  To demonstrate, try:

8.        Tip – Using the Microsoft Access Expression Builder

Do you have Microsoft Access (if you have Microsoft Office Professional or Premium)?  If so, you can get a leg up into built-in functions by looking through the Expression Builder.  It is accessible by clicking the build button […] from New Query:

Unfortunately, Excel has nothing similar.  You can find functions listed in Excel’s Object Browser (F2) (see this earlier post), though you cannot combine them as Expression Builder does.