In the previous lesson you got familiar with functions in Visual Basic – now you know how they work, you are able to create your own functions, define input arguments and assign their results to the variables.

However, for many common operations in Visual Basic you do not need to create new functions since there is a lot of built-in functions in VBA which can be easily used in the code.

Below you can find detailed descriptions of all the most important VBA built-in functions. To avoid confusion, some useful functions were purposely omitted since they use concepts which have not been introduced to you in this course yet (e.g. arrays). Those functions will be successively discussed in further parts of this course).

After reading the description of each function it is recommended to test its performance by typing some examples in the Immediate window.

Most of built-in functions have their equivalents among traditional Excel functions that are being used in worksheets. However, they can sometimes differ from each other with syntax or with the way of performance which has been pointed out in their descriptions.

Built-in sections were divided into the following categories:

• Text functions
• Numeric functions
• Date & time functions
• Data conversion functions
• Worksheet functions

Below you can find hyperlinks to text functions discussed:

• Combining strings (&)
• Character’s ASCII code (Asc)
• Character with the given ASCII code (Chr)
• Text formatting (Format)
• Checking if one string contains another (InStr)
• Switching characters to the upper/lower case (UCase, LCase)
• Extracting a substring from the string (Left, Right)
• Extracting in-between part of a string (Mid)
• Removing redundant spaces (LTrim, RTrim, Trim)
• Length of a string (Len)
• Replacing a piece of string with another one(Replace)
• Reversed string (StrReverse)
• Repeating a string n-times (String)
• Name of the month or the day of the week (MonthName, WeekdayName)

To combine two or more text variables together you should join them with & character.

If you combine other types of variables with that character they will automatically be converted into text variables.

Asc function returns the ASCII code of the character given as a char argument.

If a text argument char is longer than one character then Asc function returns the ASCII code of the very first character of that variable. So, for example the result of Asc("a") function will be the same as the result of Asc("abc") function.

The function will throw an error if an empty string or non-existing variable is given as argument.

The equivalent of Asc function among worksheet functions is =ASC(text) function

Chr function returns the character corresponding to the ASCII code given as argument.

Despite the fact that in VBA documentation “Long” type of variable as an argument of this function was given, suggesting it has very wide range of values, assigning as an argument the number out of range of 0-255 will generate an error. This is because ASCII array contains only the characters numbered from 0 to 255.

This function formats the given argument Expression into the form determined by Format argument.

Argument Format is optional, that means you can omit it, however, using this function without it makes no sense since it returns the same value which was given as Expression argument.

If the Expression argument passed to the function is of a String type then function always returns the original value regardless of the value of the parameter Format.

Parameter Expression will be modified only if a number or a date is passed.

Below you can find the list of values Format argument can represent along with the description of the result returned by the function.

Format function has no equivalent within worksheet functions.

InStr function checks if the text given as an argument Substring is contained within the text given as BaseText argument. Checking starts from the character given in the Start parameter.

If Substring text is not found within BaseText text the function returns 0. If the function finds searched text within the text being searched through it returns the position where it was found.

If a non-numeric value or non-positive number is given as Start parameter the function will throw an error.

The equivalent of InStr function within worksheet functions is =FIND(find_text, within_text, start_num).

Notice the difference between the syntax of a worksheet function FIND and InStr function in VBA – they have different orders of arguments.

LCase function returns the value of an argument text written in lower case, meanwhile UCase returns the value of that argument in upper case.

If a number is given as an argument the function returns the same value, however the result is not a number any more but a text representing numeric value (the difference between a number and a text representing a number was discussed in lesson 4).

The equivalent of LCase function in worksheet functions is =LOWER(text).

The equivalent of UCase function in worksheet functions is =UPPER(text).

Both functions above - Left and Right - return a piece of the parameter text starting from the left and right side of the string respectively.

The length of a text returned depends on the number given as an argument length.

The value of the parameter length must be positive or equal zero. In case negative number is given as this parameter, the function will throw an error.

If the value of argument length exceeds the length of text parameter, the function will return simply the text parameter.

For example the following code – Left("abc", 5) would mean that you want to extract the first five characters from the string abc starting from the left side. However this text consists of only three characters, so the function will return the whole value of text parameter - abc.

The equivalent of Left function in worksheet functions is =LEFT(text, length).

The equivalent of Right function in worksheet functions is =PRAWY(text,length).

Mid function returns a fragment of a text. Unlike Left and Right functions – which enable you to extract a fragment starting from the beginning or the end of a string – this function returns a piece of the base text starting from the given character.

The function requires two mandatory arguments to be declared:

• text– text parameter that is used as the base for extracting substring,
• start - number parameter; it must be positive; if you give negative value or zero the function will throw an error; parameter start defines at which point of the base text the result string should start (e.g. if start is equal to 3, it means the result should start at the third character of the base string).
  If the value of parameter length is longer than the length of the given text parameter then the function will return an empty string.

The function Mid has also an optional argument len. It defines how long should be the result string.

The value of len argument must not be negative number.

If len = 0 the function will return an empty string.

If parameter len is omitted, the function returns a fragment of a text starting from the character defined by start parameter till the end of the base text.

The equivalent of Mid function in worksheet functions is =MID(text, start_num, num_chars).

All the functions above return a text value which is the same as the value of an input parameter text except that the spaces placed before or after the string are removed from the text.

LTrim and RTrim functions remove only the spaces placed before/after the argument text, while Trim removes both of them (except for the redundant spaces in the middle of a text).

Both LTrim and RTrim functions have no equivalents in worksheet functions. However there is an equivalent of Trim function: =TRIM(text).

Despite the same name, there is a significant difference between these two functions (=TRIM(text) and Trim). The first one, worksheet function, removes also the spaces inside the text. For instance if you assign the text " redundant   spaces  " as an input parameter (notice that unnecessary spaces were put at the beginning (1 space), in the middle (3) and at the end (2) of that string) the function will also delete them and will return simply redundant spaces. If you follow the same set of instructions with Trim function in VBA language, it would remove unnecessary spaces at the beginning and at the end of that text without modifying the inside of the text. Therefore, the final value would be redundant   spaces (3 spaces in the middle).

Len function returns the length of a given argument text.

This function generates an error, if the given parameter is not a text value.

The equivalent of Len function in worksheet functions is =LEN(text).

Replace function enables to replace a fragment of the text with another one.

The function requires definition of three mandatory arguments:

• Expression: text argument representing the text in which the change is to be made;
• Find: text argument representing a fragment to be changed;
• Replace: text argument representing a fragment to replace the fragment given as Find parameter.

For instance, for such definition of function: Replace("text text text", "x", "xx") the compiler will find all the appearances of substring x within the base string text text text and it will replace them with "xx" substrings. The result of such defined function will be texxt texxt texxt.

Function Replace enables to declare two optional arguments:

• Start: numeric argument; its value must be positive; it works the same as in Mid function: it indicates the number of character within the given parameter Expression where the result will begin; for example if you passed 5 as this parameter then the function will work as usually but additionally it will cut the first four characters of the base string.
• Count: numeric argument; its value must be positive; it defines how many times the text Find has to be replaced with the text Replace. If you modify above example of that function to the following form: Replace("text text text", "x", "xx", 1, 2) – basically if you assign the value of 2 to the Count argument (i.e. the change has to be made exactly two times) then the result of this function will be texxt texxt text. Although you can find three appearances of x within the Expression argument, with Count argument you specify that only the first two occurrences should be changed to xx. The rest of them should be skipped.

The equivalent of Replace within worksheet functions is SUBSTITUTE(text, old_text, new_text, instance_num).

However, there is a slight difference between these two functions, specifically their last arguments (instance_num in worksheet function and Count in VBA function). As it was just mentioned, in VBA function Replace this argument specifies how many appearances (starting from the beginning of a text) of searched text are to be changed to the other text. In worksheet function SUBSTITUTE parameter instance_num defines which exact appearance has to be replaced (for example the value of 2 assigned to this argument means that only the second appearance of searched text will be changed to other text).

StrReverse reverses the text given as an argument.

StrReverse has no equivalent within worksheet functions.

String function returns a single character given in the parameter Character repeated as many times as the parameter Number.

Argument Character should be a single character. If it will be longer than one character the function will consider only the first one.

The function will throw an error if parameter Number is negative or equals zero or when Character is an empty string.

Function String has no equivalent within worksheet functions.

The closest performance presents REPT(text,number_times), however, it is more powerful since it enables repeating the text of every length (not only a single character).

Functions MonthName and WeekdayName have no equivalents within worksheet functions.

Below you can find links to the numeric functions discussed in this lesson:

• Basic operations
• Absolute value of a number (Abs)
• Trigonometric functions (Atn, Cos, Sin, Tan)
• Number e and its powers (Exp)
• Natural logarithm (Log)
• Sign of the number (Sgn)
• Square root (Sqr)
• Rounding the numbers (Round)
• The integer part of a number (Int)
• Conversion between numeric systems (Oct, Hex)
• Generating random numbers (Rnd)

VBA operators supporting elementary mathematical operations look just like those in a traditional Excel spreadsheet:

OPERATION	DESCRIPTION
a^b	rising number a to the power b
-a	switching the sign of the number a
a*b	multiplying a by b
a/b	dividing a by b
a\b	integer division of a by b (only the integer part of a result is returned)
a Mod b	returns the remainder of division a by b
a+b	adding a to b
a-b	subtracting b from a

The order of operations is identical as in the table above. Multiplication and division operations are equal, similar to addition and subtraction operations and the order of their performance depends on the order in whole formula.

To change the order of operations you can put the operation, which should have higher priority, in the parentheses.

Function Abs returns the absolute value of a number, so for positive numbers and for zero it returns exactly the same value as argument Number, while for negative numbers it returns the value of argument Number multiplied by -1. The returned value has the same type as the type specified in the argument Number.

As an argument Number the value of a numeric type must be given. Passing other type of data to the argument (e.g. text) will generate an error.

The equivalent of function Abs within the Excel worksheet functions is =ABS(Number).

These functions return respectively: the inverse arcus tangent, cosinus, sinus oraz tangent of the argument Number.

Number given as an argument Number is specified in radians.

Value given in the Number parameter must be of a numeric type. If argument of other type is passed, an error will be thrown.

The equivalents of these trigonometric functions in the worksheet are respectively: =ATAN(number), =COS(number), =SIN(number) and =TAN(number).

Function Exp returns e number raised to the power defined in the argument number.

Argument number must be of a numeric type. Passing other type of value to the argument will cause an error.

The equivalent of the function Exp in worksheet is function with the same name =EXP(power).

Function Log returns the natural logarithm of the number given as an argument number.

Argument number must be a positive number. If you pass negative number, zero or non-numeric value the function will raise an error.

The equivalent of the function Log in worksheet is function =LN(number).

Function Sgn returns the sign of the number given as an argument number.

If argument number is negative function returns -1.

If the argument number equals 0 function returns 0.

If argument number is positive function returns 1.

Argument number must be of a numeric type. Passing other type of value to the argument will generate an error.

The equivalent of the function Sgn in worksheet is function =SIGN(Number).

Function Sqr function returns a square root of a number given as a number argument.

As an argument number must be given non-negative number. Passing other type of value to the argument (e.g. negative number or a string) will generate an error.

The equivalent of the function Sqr in worksheet is function =SQRT(number).

Function Round rounds the number given as a Number argument.

Argument Number must be of a numeric type. Passing other type of data as a parameter number will cause an error.

An optional argument NumDigitsAfterDecimal specifies how many decimal places you want to round the given number to. NumDigitsAfterDecimal argument must be non-negative number. If the argument NumDigitsAfterDecimal is not specified while invoking the function the number is rounded off to integer.

The equivalent of the function Round in worksheet is function =ROUND(number; num_digits).

However, there are some differences between those two functions.

Parameter num_digits in worksheet function is mandatory, while parameter NumDigitsAfterDecimal in VBA function is optional and can be omitted.

Worksheet function Round is also slightly more powerful, since it allows to pass negative value as num_digits in order to round the given value to hundreds, thousands etc.

Function Int returns only integer part of the given number.

Do not confuse function Int with Round. Function Round rounds the given number either up or down depending on the value of the fractional part, while function Int always rounds down the number, no matter what the fractional part is.

The equivalent of the function Int within worksheet functions is =INT(number).

Functions Oct and Hex convert the parameter number respectively to the number in octal and hexadecimal system.

Argument number must be of a numeric type. Passing other type of value will cause an error to be generated.

The values returned by Oct and Hex functions are of text type.

Neither Hex nor Oct has its equivalents within the worksheet functions.

Function Rnd returns a random number from the range 0-1.

The values generated by function Rnd are not really random, if you really need random numbers in your application you should not rely on this function.

The equivalent of the function Rnd within the worksheet functions is =RAND().

Below you can find the references to the date and time functions discussed in this lesson:

• Creating date and time (DateSerial, TimeSerial)
• Current date and time (Date, Time, Now, Timer)
• Date or time components (Year, Month, Day, Hour, Minute, Second)
• Day of week (Weekday)
• Adding up the dates (DateAdd)
• Difference between two dates (DateDiff)
• Calculating number of a day, week etc. in the calendar year (DatePart)

Functions DateSerial and TimeSerial create date or time based on the given date-time parts (year, month, day, hour, minute, second).

For instance the record DateSerial(2010, 12, 31) will return the date of 31st December 2010, while the result of TimeSerial(15, 8, 12) will be the time 15:08:12.

Both functions described above are flexible, since the values of the elements being passed to the function do not have to be limited to their maximum accepted values.

For example, while creating a date by using DateSerial function the number of a month does not have to be from the range 1-12 (despite the fact there are only twelve months), but it can take higher or even negative values as well. Likewise the number of days does not have to be from range 1-31, the number of hours may exceed the 1-24 range and the number of minutes and seconds may also exceed the range of 1-60.

In the case when one of the components of date or time exceeds the allowable range, the preceding component is being modified.

Let's assume we call this function with the parameter month equal to 14: DateSerial(2000, 14, 1). Since there are only 12 months during the year the number of months corresponds to one year and two months. Therefore, the number of years specified in this example is increased by one, while the number of months takes a value of 2 (number of months that remained after dividing initial number of months by 12) and the whole record is equivalent to the following one: DateSerial(2001, 2, 1).

There is a similar case when trying to create time using TimeSerial function. If you call this function like that: TimeSerial(13, 75, 21) the number of minutes has been set at 75 which is equivalent to one full hour and 15 remaining minutes and that is why the result of this function will be 14:15:21.

Similarly, in the case when negative number is given as an argument, proper number is subtracted from the date or time element preceding the component with a negative value.

For instance in the record TimeSerial(14, -5, 0) number of minutes is defined as -5. As you might guess it is equivalent to the deduction of five minutes from the time 14:00:00, so the result of this function will be the time 13:55:00.

It looks a little bit different if you pass negative value to the DateSerial function. This is because hours, minutes and seconds can have a 0 value and it is nothing special, whereas the minimum value for months and days is 1.

Therefore, while invoking this function like that: DateSerial(2000, 0, 1), despite the fact the number of months is not negative but equals 0, the program will interpret it as if it had to subtract one month from the date of 2000-01-01 and it will return the date of 1999-12-01.

Invoking this function with months parameter equals to -1, will cause the program to subtract two months from the base date and return the date of 1999-11-01.

The equivalents of DateSerial and TimeSerial functions in the worksheet are respectively: =DATE(year,month,day) and =TIME(hour,minute,second).

Date function returns date representing current system date.

Time function returns current system time.

Now function is a combination of Date and Time functions and returns the value representing current system date and time.

Timer function returns the number of seconds that have elapsed since the midnight.

The functions above can also be used in code without parenthesis.

At the time of writing this (i.e. June 26, 2014 at 13:53) these functions have returned the following results:

FUNCTION	RESULT	NOTES
Date()	2014-06-26
Time()	13:53:28
Now()	2014-06-26 13:53:28
Timer()	5008,08	That many seconds have elapsed since the midnight till 13:53:28

The equivalent of Date function in the worksheet is function =TODAY().

The equivalent of No function in the worksheet is function =NOW().

Both Time and Timer functions have no equivalents in the worksheet functions.

The functions mentioned above allow you to obtain information about specific components of the given date or time.

Parameter Date must be of a date type (or a number, because as explained in lesson 4, each date has its numeric equivalent; in this case you should remember that the given number should range from -657 434 to 2 958 465).

Following Excel worksheet functions are the equivalents for above-mentioned functions:
=YEAR(serial_number),
=MONTH(serial_number),
=DAY(serial_number),
=HOUR(serial_number),
=MINUTE(serial_number),
=SECOND(serial_number).

Weekday function returns the number of the weekday based on the date specified as Date argument.

Additionally, this function allows you to specify an optional argument called FirstDayOfWeek, which indicates what day of the week should be considered as the first day of the week.

FirstDayOfWeek argument must be of one of the following values (these values must be entered without the quotes; they are called enumerations and will be discussed later in the course):

VALUE	FIRST DAY OF THE WEEK
vbUseSystemDayOfWeek	system settings are used
vbMonday	Monday
vbTuesday	Tuesday
vbWednesday	Wednesday
vbThursday	Thursday
vbFriday	Friday
vbSaturday	Saturday
vbSunday	Sunday

The equivalent of this function in the worksheet functions is function =WEEKDAY(serial_number, [return_type]).

DateAdd function allows you to add up the appropriate number of days, weeks, months, etc. to the date specified as Date argument.

The function consists of three mandatory parameters:

• Interval - text type argument; determines units of time in which the period you add is defined. The following table shows the possible values for the Interval argument with an explanation which unit of time they refer to. Giving as the Interval parameter the value outside the table will generate the following error: Run-time error ‘5’: Invalid procedure call or argument. W poniższej tabelce przedstawiono wartości jakie może przyjmować argument Interval wraz z wyjaśnieniem, jakiej jednostki czasu dotyczą.
  Próba podania jako argumentu Interval wartości spoza tej tabelki zakończy się wygenerowaniem błędu Run-time error '5': Invalid procedure Call or argument.

  INTERVAL	UNITS OF TIME BEING ADDED
  "yyyy"	Years
  "q"	Quarters
  "m"	Months
  "ww"	Weeks
  "d"	Days
  "h"	Hours
  "n"	Minutes
  "s"	Seconds

• Number - text type argument determining how many date units are to be added to the base date.
  Parameter Number can be either of negative (in this case given period of time is subtracted from the base date) or positive value.
• Data - date type argument representing the base date to which given period of time will be added.

DateAdd function has no equivalent in the worksheet functions.

DateDiff function allows to calculate difference between two dates expressed in various time units – years, months, weeks, days, hours, etc.

• Interval - string type argument; defines time units in which time difference will be returned.
  Values allowed for the Interval parameter and types of time units assigned to each of them are shown below:

  INTERVAL	UNIT OF TIME
  "yyyy"	Year
  "q"	Quarter
  "m"	Month
  "ww"	Week
  "d"	Day
  "h"	Hour
  "n"	Minute
  "s"	Second

Giving as the Interval argument the value outside the table will generate the following error: Run-time error ‘5’: Invalid procedure call or argument.
• Date1 and Date2 - date type arguments, representing the dates between which the difference is to be returned.
  Value of Date1 is subtracted from the value of Date2.