07 - Built-in functions


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

Combine strings

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.

Examples:

FUNCTIONRESULTNOTES
"a" & "b"ab
1 & 212
1 & "a" & True1aTrue

Character’s ASCII code

 
Function Asc(char As String) As Integer

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.

Examples:

FUNCTIONRESULTNOTES
Asc("a")97
Asc("A")65
Asc("Abc")65
Asc("")errorstring given as an argument must be at least one character long

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

A character with a given ASCII code

 
Function Chr(CharCode As Long) As String

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.

Examples:

FUNCTIONRESULTNOTES
Chr(34)"
Chr(97)a
Chr(256)errornumber from range 0-255 must be given as a parameter

The equivalent of Chr function among worksheet functions is =CHAR(number).

Text formatting

 
Function Format(Expression, Optional Format As String) As String

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.

FORMATDESCRIPTION
General Datedate formatted by your Windows settings
Long Datedate with full month name, e.g. 16 October 2010
Medium Datedate with short month name, e.g. 16-Oct-10)
Short Datedate in format yyyy-mm-dd, e.g. 2010-10-16
Long Timetime in format hh:mm:ss, e.g. 21:04:25
Medium Timetime in format hh:mm in 12-hour system, e.g. 09:04
Short Timetime in format hh:mm in 24-hour system, e.g. 21:04
mm/dd/yyyydate in format mm-dd-yyyy, e.g. 10-16-2010)
dd/mm/yydate in format dd-mm-yy, e.g. 16-10-10)
####number without thousand separator nor decimal part, e.g. 1500)
#,###number with thousand separator but without decimal part, e.g. 1 500)
####.0number without thousand separator but with one decimal place, e.g. 1500,0
#,###.00number with thousand separator and two decimal places, e.g. 1 500,00

Examples:

FUNCTIONRESULTNOTES
Format("a", "General Date")atext values are returned without any modification
Format(40000, "General Date")2009-07-06it can be different on your computer since it depends on regional settings
Format(0.25, "Long Time")06:00:00
Format(12534.23, "#,###.0")12 534,2
Format(12534.23, "####.00")12534,23

Format function has no equivalent within worksheet functions.

Checking if one string contains another

 
Function InStr(Start As Long, BaseText As String, Substring As String) As Long

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.

Examples:

FUNCTIONRESULTNOTES
InStr("a", "text", "ex")errorparameter Start is not a number
InStr(0, "text", "ex")errorparameter Start is not a positive number
InStr(1, "ex", "text")0text text is not found in the text ex
InStr(1, "text", "ex")2substring ex was found in the base string text at the second character
InStr(3, "text", "ex")0substring ex is found in base string text, but parameter Start equals 3, that means the base string should be searched from the third character
InStr(2, "xyzxyz", "xyz")4substring xyz is found at the fourth character in the base string xyzxyz

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.

Switching characters to the upper/lower case

 
Function LCase(text As String) As String
 
Function UCase(text As String) As String

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).

Examples:

FUNCTIONRESULTNOTES
LCase(0)0
LCase("abc")abc
LCase("ABc")abc
UCase("ABC")ABC
UCase("abc")ABC

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

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

Extracting a substring from the string

 
Function Left(text As String, length As Long) As String
 
Function Right(text As String, length As Long) As String

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.

Examples:

FUNCTIONRESULTNOTES
Left("abc", -1)errorargument dlugosc musi być liczbą dodatnią lub zerem
Left("abc", 0)empty string
Left("abc", 2)ab
Left("abc", 10)abcparameter length exceeds the length of the parameter text, so the whole parameter text is returned
Right("abc", 2)bc
Right("text", 3)ext

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

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

Extracting in-between part of a string

 
Function Mid(text As String, start As Long, Optional len As Long) As String

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.

Examples:

FUNCTIONRESULTNOTES
Mid("Worksheet", -1)errorparameter start cannot be negative number
Mid("Worksheet", 1, -1)errorparameter len cannot be negative number
Mid("Worksheet", 3, 2)rksubstring of the base text Worksheet starting from the third character, two characters long
Mid("Worksheet", 3)rksheetsubstring of the base text Worksheet starting from the third character; parameter len is not defined, so the result contains all the characters to the end of the base text
Mid("Worksheet", 3, 10)rksheetsubstring of the base text Worksheet starting from the third character. The result should be ten characters long. However, there are only seven characters from the given start point to the end of the base text, so those seven characters only are returned
Mid("Worksheet", 10, 2)the result is empty string; the function should return a piece of text from the 10. character, but the base text is only 9 characters long, so empty string is returned instead

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

Removing redundant spaces

 
Function LTrim(tekst As String) As String
 
Function RTrim(tekst As String) As String
 
Function Trim(tekst As String) As String

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).

Examples:

FUNCTIONRESULTNOTES
Trim("abc")abc
Trim(" abc")abc
Trim(" abc ")abc
Trim(" asda asd ")asda asd

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).

Length of a string

 
Function Len(text As String) As Long

Len function returns the length of a given argument text.

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

In any other function if you pass a numeric value instead of a text one, it will be automatically converted into text. However it does not happen in this function. So if you declare a number as an argument, the compiler will not treat it as a text, instead it will display an error.

Examples:

FUNCTIONRESULTNOTES
Len(1)errorthe given parameter is a number instead of text
Len("")0
Len("text")4

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

Replacing a piece of string with another

 
 
Function Replace(Expression As String, Find As String, Replace As String, _
    Optional Start As Long, Optional Count As Long) As String

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.

Examples:

FUNCTIONRESULTNOTES
Replace("text text text", "x", "xx") texxt texxt texxt all the occurrences of substring x in the base text were replaced with xx
Replace("text text text", "t", "") ex ex ex all the occurrences of the character t in the base string were replaced by empty string
Replace("text text text", "t", "", 1, 2) ex text text occurrences of character t are to be replace with empty string. However, there is one limit in this case - parameter Count is equal to 2, that means only two first occurrences of character t will be replaced

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).

Reversed string

 
Function StrReverse(text As String) As String

StrReverse reverses the text given as an argument.

Examples:

FUNCTIONRESULTNOTES
StrReverse(10)01
StrReverse("abc")cba
StrReverse("Excel")lecxE
StrReverse("text")txet

StrReverse has no equivalent within worksheet functions.

Repeating a string n times

 
Function String(Number As Long, Character) As String

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.

Examples:

FUNCTIONRESULTNOTES
String(5, "")errorargument Znak nie może być pustym ciągiem znaków
String(4, "a")aaaa
String(0, "a")
String(5, "abc")aaaaa

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).

Name of the month or the day of the week

 
Function MonthName(Month As Long, Optional Abbreviate As Boolean) As String
 
 
Function WeekdayName(Weekday As Long, Optional Abbreviate As Boolean, _
    FirstDayOfWeek As vbDayOfWeek = vbUseSystemDayOfWeek) As String

The functions described above return:
  • MonthName - the name of the given month based on the parameter Month
  • WeekdayName - the day of the week based on the parameter Weekday.

Parameter Month can take value from range 1-12 and Weekday from range 1 to 7. If value given in those parameters are not in the ranges given above, function will throw an error.

Parameter Abbreviate of a logical type defines whether the whole name of the month/day of the week is to be returned (Abbreviate = False) or only a three-letter abbreviation(Abbreviate = True).

When using function WeekdayName you can additionally define which day of the week is taken as the first one. As an argument FirstDayOfWeek one of the following values should be declared (listed below). These values should be written without quotation marks; these are so called enumerations that will be discussed later during this tutorial:

VALUEFIRST DAY OF THE WEEK
vbUseSystemDayOfWeekuse system settings
vbMondayMonday
vbTuesdayTuesday
vbWednesdayWednesday
vbThursdayThursday
vbFridayFriday
vbSaturdaySaturday
vbSundaySunday

Examples:

FUNCTIONRESULTNOTES
MonthName(0, True)error
WeekdayName(9, False)error
MonthName(3, False)March
MonthName(4, True)Apr
WeekdayName(1, True, vbMonday)Mon
WeekdayName(2, False, vbSunday)Monday

Functions MonthName and WeekdayName have no equivalents within worksheet functions.

Numeric functions

Basic operations

VBA operators supporting elementary mathematical operations look just like those in a traditional Excel spreadsheet:
OPERATIONDESCRIPTION
a^brising number a to the power b
-aswitching the sign of the number a
a*bmultiplying a by b
a/bdividing a by b
a\binteger division of a by b (only the integer part of a result is returned)
a Mod breturns the remainder of division a by b
a+badding a to b
a-bsubtracting 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.

Absolute value of a number

 
Function Abs(Number)

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.

Examples:

FUNCTIONRESULTNOTES
Abs("a")errorargument a is not numeric
Abs(0)0
Abs(1)1
Abs(1.23)1.23
Abs(-1.23)1.23

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

Trigonometric functions

 
Function Atn(Number) As Double
 
Function Cos(Number) As Double
 
Function Sin(Number) As Double
 
Function Tan(Number) As Double

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.

Examples:

FUNCTIONRESULTNOTES
Atn(0)0
Atn(1)0.785...
Cos(0)1
Cos(1)0.540...
Sin(0)0
Sin(1)0.841...
Tan(0)0
Tan(1)1.557...

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

Number e and its powers

 
Function Exp(number) As Double

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.

Examples:

FUNCTIONRESULTNOTES
Exp(0)1
Exp(1)2.718...
Exp(-1)0.368...

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

Natural logarithm

 
Function Log(number As Double) As Double

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.

Examples:

FUNCTIONRESULTNOTES
Log(0)errorparameter number is not a positive number
Log(1)0
Log(10)2.303...

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

Note that there is also function =LOG within the worksheet functions. However, it works differently, since it can return a logarithm with any base value not only natural logarithm.

Sign of the number

 
Function Sgn(number As Double) As Integer

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.

Examples:

FUNCTIONRESULTNOTES
Sgn("a")errornumeric value must be given to the function
Sgn(0)0
Sgn(-1)-1
Sgn(-5)-1
Sgn(0.23423)1
Sgn(1)1
Sgn(1500)1

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

Square root

 
Function Sqr(number As Double) As Double

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.

Examples:

FUNCTIONRESULTNOTES
Sqr(-1)errorparameter number must not be negative
Sqr(0)0
Sqr(4)2
Sqr(9)3

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

Rounding the numbers

 
Function Round(number As Double, Optional NumDigitsAfterDecimal As Long) As Double

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.

Examples:

FUNCTIONRESULTNOTES
Round("a")errorparameter number is not numeric
Round(2,-1)errorparameter NumDigitsAfterDecimal cannot be negative
Round(2.5)3parameter NumDigitsAfterDecimal is not specified so the number is rounded off to integer
Round(2.51, 1)2.5
Round(2.1484, 2)2.15

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.

The integer part of a number

 
Function Int(number As Double) As Long

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.

Examples:

FUNCTIONRESULTNOTES
Int(1)1
Int(1.3)1
Int(1.9)1

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

Conversion between numeric systems

 
Function Oct(number) As String
 
Function Hex(number) As String

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.

Examples:

FUNCTIONRESULTNOTES
Hex(15)F
Hex(214)D6
Oct(8)10
Oct(214)326

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

Generating random numbers

 
Function Rnd() As Double

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().

Date and time functions

Creating date and time

 
Function DateSerial(Year As Integer, Month As Integer, Day As Integer) As Date
 
Function TimeSerial(Hour As Integer, Minute As Integer, Second As Integer) As Date

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.

Examples:

FUNCTIONRESULTNOTES
DateSerial(2000, 1, 3)03-01-2000
DateSerial(2000, 16, 7) 2001-04-07
DateSerial(2000, 16, 43)2001-05-13
DateSerial(2000, 0, 24)1999-12-24
DateSerial(2000, -5, -5)1999-06-25
TimeSerial(13, 12, 15)13:12:15
TimeSerial(13, 75, 30)14:15:30
TimeSerial(13, 75, 75)14:16:15
TimeSerial(13, -10, 21)12:50:21
TimeSerial(13, -5, -5)12:54:55

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

Current date and time

 
Function Date() As Date
 
Function Time() As Date
 
Function Now() As Date
 
Function Timer() As Single

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:
FUNCTIONRESULTNOTES
Date()2014-06-26
Time()13:53:28
Now()2014-06-26 13:53:28
Timer()5008,08That 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.

Date or time components

 
Function Year(Date As Date) As Integer
 
Function Month(Date As Date) As Integer
 
Function Day(Date As Date) As Integer
 
Function Hour(Date As Date) As Integer
 
Function Minute(Date As Date) As Integer
 
Function Second(Date As Date) As Integer

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).

Examples:

FUNCTIONRESULTNOTES
Year(#2010-04-21 15:10:10#)2010
Month(#2010-04-21#)4
Day(#2010-04-21#)21
Hour(#2010-04-21 15:10:10#)15
Minute(#12:41:13#)41
Second(#12:41:13#)13

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).

Day of week

 
Function Weekday(Date As Date, Optional FirstDayOfWeek As VbDayOfWeek) As Integer

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):
VALUEFIRST DAY OF THE WEEK
vbUseSystemDayOfWeeksystem settings are used
vbMondayMonday
vbTuesdayTuesday
vbWednesdayWednesday
vbThursdayThursday
vbFridayFriday
vbSaturdaySaturday
vbSundaySunday

Examples:

FUNCTIONRESULTNOTES
Weekday(#2010-10-17#, vbSunday)1
Weekday(#2010-10-17#, vbMonday)7
Weekday(#2010-10-18#, vbMonday)1

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

Adding up the dates

 
Function DateAdd(Interval As String, Number As Double, Date As Date) As Date

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.
    INTERVALUNITS 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.

Examples:

FUNCTIONRESULTNOTES
DateAdd("yy", 5, #2010-10-17#)errorInterval parameter has the value yy which is not on the list of acceptable values
DateAdd("yyyy", 1, #2010-10-17#)2011-10-17Interval parameter has the value yyyy, so the time being added is measured in years; Number argument has the value 1, so the function will add one year to the base date specified in the Data argument - 17-10-2010
DateAdd("m", 2, #2010-10-17#)2010-12-17Interval parameter has the value m, so the time being added is measured in months; Number argument is equal 2, so the function will add two months to the base date specified in the Data parameter - 17-10-2010
DateAdd("ww", 5, #2010-10-17#)2010-11-21Interval parameter has the value ww, so the time being added is measured in weeks; Number argument is equal 5, so the function will add five weeks to the base date specified in the Data parameter - 17-10-2010

DateAdd function has no equivalent in the worksheet functions.

Difference between two dates

 
 
 
Function DateDiff(Interval As String, Date1 As Date, Date2 As Date, _
    Optional FirstDayOfWeek As vbDayOfWeek = vbSunday, _
    Optional FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1) As Long

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:
    INTERVALUNIT 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.

What is more, DataDiff function has also two optional arguments determining how the full week should be defined (relevant only while calculating the difference in weeks, which is done by passing value ww as a parameter Interval.

First of these arguments is FirstDayOfWeek that defines the day treated as the beginning of the week.

FirstDayOfWeek parameter must be of one of the following values (these values should be put without quotation marks; they are so called enumerations, and will be explained in further lessons of this course):
VALUEFIRST DAY OF THE WEEK
vbUseSystemDayOfWeeksystem settings will be used
vbMondayMonday
vbTuesdayTuesday
vbWednesdayWednesday
vbThursdayThursday
vbFridayFriday
vbSaturdaySaturday
vbSundaySunday

The second optional argument is FirstWeekOfYear that defines the week treated as the beginning of the year.

Parameter FirstWeekOfYear must be of one of the following values (these values should be put without quotation marks; they are so called enumerations):

VALUEFIRST WEEK OF THE YEAR
vbFirstFourDaysthe first week having at least four days of January is treated as the first week of the year; so if 1st of January is Friday, 2nd of January is Saturday and 3rd of January is Sunday, this week is considered to be the last week of the previous year and the next week is the first week of the new year
vbFirstFullWeekthe first week including only January days is treated as the first day of the year; so if 31st of December was on Monday then whole week is considered to be the last week of the previous year and the next week is treated as the first week of the year
vbFirstJan1the first week of the year is the week where 1st of January appears; even if the January 1st is on Sunday, this week is considered to be the first week of the new year
vbUseSystemdefault system settings

Examples:

FUNCTIONRESULTNOTES
DateDiff("ww", #2010-10-16#, #2010-10-17#, vbSunday)1 2010-10-16 is Saturday and 2010-10-17 is Sunday; FirstDayOfWeek = vbSunday means that Sunday is treated as the first day of the week and that is why new week begins on 17th of October while 16th of October is in the previous week – thus the difference between weeks is 1
DateDiff("ww", #2010-10-16#, #2010-10-17#, vbMonday)0 Similar to the first example but the first day of the week is Monday, that is why 16th and 17th of October are in the same week (thus the difference between weeks is 0)
DateDiff("m", #2010-11-01#, #2010-10-17#)-1
DateDiff("m", #2010-10-17#, #2010-11-01#)1
DateDiff("yyyy", #2000-01-01#, #2010-10-17#)10

DateDiff function has no equivalent in the worksheet functions.

Calculating number of a day, week etc. in the calendar year

 
 
 
Function DatePart(Interval As String, Data As Date, _
    Optional FirstDayOfWeek As vbDayOfWeek = vbSunday, _
    Optional FirstWeekOfYear As VbFirstWeekOfYear = vbFirstJan1) As Integer

DatePart function allows to determine on which day of year, day of month etc. falls the given date.

DatePart is composed of two mandatory arguments:
  • Interval - string parameter; determines time units the result of function will be expressed in.
    Possible values of Interval argument together with explanations which time unit they are assigned to are shown in the table below:
    INTERVALRETURNED VALUE
    "yyyy"year number
    "q"quarter number
    "m"month number
    "ww"week number (in year)
    "y"day number (in year)
    "d"day number (in month)
    "w"day number (in week; based on the value of optional parameter FirstDayOfWeek)
    "h"Hour
    "n"Minute
    "s"Second
    If you try to pass the value out of this list, compiler will throw error Run-time error '5': Invalid procedure Call or argument
  • Data - date-time parameter used as a base date for the calculation

There are also two optional parameters in the function DatePart: FirstDayOfWeek and FirstWeekOfYear.

First of these arguments - FirstDayOfWeek - defined the day treated as the beginning of the week.

FirstDayOfWeek argument must be of one of the following values (these values should be put without quotation marks; they are so called enumerations, and will be explained in further lessons of this course):
VALUEFIRST DAY OF WEEK
vbUseSystemDayOfWeeksystem settings are used
vbMondayMonday
vbTuesdayTuesday
vbWednesdayWednesday
vbThursdayThursday
vbFridayFriday
vbSaturdaySaturday
vbSundaySunday

The second optional argument is FirstWeekOfYear, defining the week treated as the beginning of the year.

FirstWeekOfYear parameter must be of one of the following values (these values should be put without quotation marks; they are so called enumerations):
VALUEFIRST WEEK OF THE YEAR
vbFirstFourDaysthe first week having at least four days of January is treated as the first week of the year; so if 1st of January is Friday, 2nd of January is Saturday and 3rd of January is Sunday, this week is considered to be the last week of the previous year and the next week is the first week of the new year
vbFirstFullWeekthe first week including only January days is treated as the first day of the year; so if 31st of December was on Monday then whole week is considered to be the last week of the previous year and the next week is treated as the first week of the year
vbFirstJan1the first week of the year is the week where 1st of January appears; even if the January 1st is on Sunday, this week is considered to be the first week of the new year
vbUseSystemdefault system settings

Examples:

FUNCTIONRESULTNOTES
DatePart("w", #2010-10-17#, vbMonday)7returns the day of the week for 17th October, assuming that Monday is treated as the first day of the week
DatePart("ww", #2010-10-17#)43returns the number of week in the year in which containing the date of 17th October
DatePart("ww", #2010-10-17#, , vbFirstJan1)42returns the number of week in the year in which the date of 17th October took place, assuming that the first week of the year is the week with January 1st.
DatePart("yyyy", #2010-10-17#)2010returns year number
DatePart("y", #2010-10-17#)290returns number of day in the year for the date of 17th October

DatePart function have no equivalent in workbook functions.

Data conversion and information functions

The purpose of conversion functions is to convert a value in a specified type of data into the same value but expressed in other type. Information functions can be used to obtain information about variables themselves.

The most useful conversion and information functions are listed below:

Conversion to logic type (Boolean)

 
Function CBool(Expression) As Boolean

Function CBool returns logical value True or False basing on the value of input parameter Expression.

Function can accepts a few types of data:
  • values of Boolean type (True or False) - in such case the function returns value equal to the input parameter Expression
  • values of any numeric type - function returns False if the given value is equal to 0 and True otherwise (even if the value is negative)
  • text True or False (in various casing variants since this conversion is case-insensitive) - function returns the value of Boolean type corresponding to the given text.
    For any other strings, function CBool throws an error: Run-time error '13': Type mismatch.

If any value not listed above is passed as an input parameter Expression, function throws error: Run-time error '13': Type mismatch..

Reassuming, function CBool returns False for the following values of input parameter Expression:
  • value of Boolean type equal to False
  • value of numeric type equal to 0
  • text False in any case variant

True is returned for the following values of input parameter Expression:
  • value of Boolean type equal to True
  • value of numeric type different than 0 (even if it is negative value)
  • text True in any case variant

Examples:

FUNCTIONRESULTNOTES
CBool("a")errorInvalid value of Expression argument; the only acceptable text values are False and True
CBool("False")False
CBool("True")True
CBool(0)False
CBool(1)True
CBool(-1)True
CBool(0.1)True

Conversion to text type (String)

 
Function CStr(Expression) As String

Function CStr returns text equivalent of the given Expression

Input parameter Expression can be of any primitive data type.

Examples:

FUNCTIONRESULTNOTES
CStr(10)10
CStr(0.23)0.23
CStr(False)False
CStr(#2010-01-01#)2010-01-01

Conversion into numeric types

 
Function CByte(Expression) As Byte
 
Function CCur(Expression) As Currency
 
Function CDbl(Expression) As Double
 
Function CInt(Expression) As Integer
 
Function CLng(Expression) As Long
 
Function CSng(Expression) As Single

All the functions listed above convert given parameter Expression into the corresponding numeric type (i.e. function CByte converts into Byte type etc).

Input parameter Expression can take numeric, date or text representation of a number value, however it must be limited to the range of the returned result type.

For example, for CByte function, which returns the result of Byte type, only a number or text representation of a number from the range 0-255 can be given as input parameter Expression, because this is the range covered by this data type. CInt function can take numbers and text representations of numbers from the range from -32 768 to 32 767, etc.

Examples:

FUNCTIONRESULTNOTES
CByte("a")errorthe only accepted text values of the Expression argument are text representations of numbers
CByte("300")error300 is a text representation of a number, however the result would exceed acceptable range of Byte type (0-255)
CByte(100.23)100
CInt(1000)1000
CInt(50000)errorthe result (50000) would exceed acceptable range of Integer type (–32 768 to 32 767)

Conversion to date and time

 
Function CDate(Expression) As Date

CDate function converts Expression argument into a date or time value.

As an input parameter Expression can be given any number from range –657 434 to 2 958 465 as well as text representation of numbers or dates. If any other value is passed as input parameter, function will throw an error: Run-time error '13': Type mismatch.

Examples:

FUNCTIONRESULTNOTES
CDate("x")errorthe only acceptable text value of the Expression argument is text representation of date or number.
CDate(-700000)errorthe given number exceeds the acceptable range
CDate("2010-01-01")2010-01-01
CDate(40000)2009-07-06
CDate("40000")2009-07-06

Checking variable types

 
Function TypeName(VarName) As String

TypeName function returns string describing type of variable or value given as VarName argument.

TypeName function is very flexible and does not generate errors even if non-existent variable is given as an argument – in this case it just returns text Empty.

The only time this function throws error is when it is invoked without input parameter specified.

Examples:

FUNCTIONRESULTNOTES
TypeName()errorTypeName function cannot be called without arguments definition
TypeName(nieistniejaca)Emptywhen non-existent variable is passed as input parameter, function returns text Empty
TypeName(#2010-01-01#)Dateplain value can be also given as an input argument; in this case date is given, so function returns text Date
TypeName("a")String
TypeName(1)Long
TypeName(1.1)Double

Checking if a variable is a number

 
Function IsNumeric(Expression) As Boolean

IsNumeric function checks if a variable or a value given as Expression argument is a number. The function returns value of Boolean type: True for numeric expressions and False for non-numeric.

The function returns True also for the text representations of numbers. However, for dates the function returns False, even though every date has its own numeric representation.

IsNumeric is very flexible and it generates an error only while trying to invoke it without defining the argument.

A very odd property of this function is that it returns True if a non-existing variables is given as an input parameter.

Examples:

FUNCTIONRESULTNOTES
IsNumeric(xyz)Truefor non-existent arguments the function returns True!
IsNumeric("a")False
IsNumeric("1")Truegiven value is a text but it is also a text representation of number 1
IsNumeric(#2010-01-01#)Falsefunction returns False for dates values
IsNumeric(100)True

Checking if value is a date

 
Function IsDate(Expression) As Boolean

IsDate function checks if a variable or a value given as Expression argument is a date. The function returns Boolean type value: True for dates and their text representations and False for other values.

Despite the fact that every date has its numeric representation and in many situations compiler converts automatically numbers into dates corresponding to them, IsDate function returns False for numeric values.

Function IsDate function is as flexible as previously mentioned IsNumeric function and it generates an error only while trying to invoke it without passing the argument.

In contrast to IsNumeric function, when giving a non-existent variable as an argument the function returns False.

Examples:

FUNCTIONRESULTNOTES
IsDate(xyz)Falsefor non-existent arguments the function returns False
IsDate(40000)Falsedespite the fact that each number represents a date, when giving a number as an argument the function returns False.
IsDate("2010-01-01")True
IsDate(2010-01-01)True

Worksheet functions

If you just begun your adventure with VBA but you are familiar with worksheet functions we have good news for you. When creating macros in VBA you can also use most of the traditional worksheet functions such as e.g.: =VLOOKUP(), =MAX(), =MIN() etc.

In VBA you can use all worksheet functions except for those ones having their equivalents among VBA built-in functions (most of them has been described above).

To use a worksheet function in VisualBasic you have to append Applicaton.WorksheetFunction. before the name of the function:
 
value = Application.WorksheetFunction.[FunctionName](parameters)

When you type Applicaton.WorksheetFunction. instruction in the VBA editor (please notice the dots after each of the keywords), it will display a drop-down list containing names of all available functions, as shown in the picture below:

Excel worksheet functions in VBA

While using worksheet functions in VBA you need to pay attention if parameters passed to a function are of the same types as required by this function.

For some functions, when you type its name and open parenthesis VBA editor displays a frame with a hint on the required types of arguments. The picture below shows an example of using in VBA worksheet function =ASC() that returns the ASCII code of character. For this function VBA editor displays a hint indicating that given argument must be of the text type.

VBA editor - hint for worksheet function

However, for other functions VBA editor displays only the information about how many parameters is required without any hint about their types. The picture below shows an example of such a function: =VLOOKUP(). For this function VBA editor displays only information about three required and one optional argument without specifying their types.

Function Vlookup used in VBA

You can find a detailed description of functions and required parameters by double clicking the name of a function in Insert function window (it was explained in the previous lesson how to open Insert function window).

Translation:
Artur Krawczyński