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 builtin functions in VBA which can be easily used in the code.
Below you can find detailed descriptions of all the most important VBA builtin 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 builtin 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.
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:
FUNCTION  RESULT  NOTES 
"a" & "b"  ab  
1 & 2  12  
1 & "a" & True  1aTrue  
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 nonexisting variable is given as argument.
Examples:
FUNCTION  RESULT  NOTES 
Asc("a")  97  
Asc("A")  65  
Asc("Abc")  65  
Asc("")  error  string 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 0255 will
generate an error. This is because ASCII array contains only the characters numbered from 0 to 255.
Examples:
FUNCTION  RESULT  NOTES 
Chr(34)  "  
Chr(97)  a  
Chr(256)  error  number from range 0255 must be given as a parameter 
The equivalent of Chr
function among worksheet functions is =CHAR(number)
.
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.
FORMAT  DESCRIPTION 
General Date  date formatted by your Windows settings 
Long Date  date with full month name, e.g. 16 October 2010 
Medium Date  date with short month name, e.g. 16Oct10) 
Short Date  date in format yyyymmdd, e.g. 20101016 
Long Time  time in format hh:mm:ss, e.g. 21:04:25 
Medium Time  time in format hh:mm in 12hour system, e.g. 09:04 
Short Time  time in format hh:mm in 24hour system, e.g. 21:04 
mm/dd/yyyy  date in format mmddyyyy, e.g. 10162010) 
dd/mm/yy  date in format ddmmyy, e.g. 161010) 
####  number without thousand separator nor decimal part, e.g. 1500) 
#,###  number with thousand separator but without decimal part, e.g. 1 500) 
####.0  number without thousand separator but with one decimal place, e.g. 1500,0 
#,###.00  number with thousand separator and two decimal places, e.g. 1 500,00 
Examples:
FUNCTION  RESULT  NOTES 
Format("a", "General Date")  a  text values are returned without any modification 
Format(40000, "General Date")  20090706  it 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 nonnumeric value or nonpositive number is given as Start
parameter the function will throw an error.
Examples:
FUNCTION  RESULT  NOTES 
InStr("a", "text", "ex")  error  parameter Start is not a number 
InStr(0, "text", "ex")  error  parameter Start is not a positive number 
InStr(1, "ex", "text")  0  text text is not found in the text ex 
InStr(1, "text", "ex")  2  substring ex was found in the base string text at the second character 
InStr(3, "text", "ex")  0  substring 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")  4  substring 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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
Left("abc", 1)  error  argument dlugosc musi być liczbą dodatnią lub zerem 
Left("abc", 0)   empty string 
Left("abc", 2)  ab  
Left("abc", 10)  abc  parameter 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 inbetween 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:
FUNCTION  RESULT  NOTES 
Mid("Worksheet", 1)  error  parameter start cannot be negative number 
Mid("Worksheet", 1, 1)  error  parameter len cannot be negative number 
Mid("Worksheet", 3, 2)  rk  substring of the base text Worksheet starting from the third
character, two characters long 
Mid("Worksheet", 3)  rksheet  substring 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)  rksheet  substring 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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
Len(1)  error  the 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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
String(5, "")  error  argument 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 112 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 threeletter 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:
VALUE  FIRST DAY OF THE WEEK 
vbUseSystemDayOfWeek  use system settings 
vbMonday  Monday 
vbTuesday  Tuesday 
vbWednesday  Wednesday 
vbThursday  Thursday 
vbFriday  Friday 
vbSaturday  Saturday 
vbSunday  Sunday 
Examples:
FUNCTION  RESULT  NOTES 
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:
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 
ab  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.
Absolute value of a 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:
FUNCTION  RESULT  NOTES 
Abs("a")  error  argument 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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
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 nonnumeric value the function
will raise an error.
Examples:
FUNCTION  RESULT  NOTES 
Log(0)  error  parameter 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:
FUNCTION  RESULT  NOTES 
Sgn("a")  error  numeric 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 nonnegative number. Passing other type of value to the argument (e.g. negative number or a string)
will generate an error.
Examples:
FUNCTION  RESULT  NOTES 
Sqr(1)  error  parameter 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 nonnegative number.
If the argument NumDigitsAfterDecimal
is not specified while invoking the function the number is rounded off to integer.
Examples:
FUNCTION  RESULT  NOTES 
Round("a")  error  parameter number is not numeric 
Round(2,1)  error  parameter NumDigitsAfterDecimal cannot be negative 
Round(2.5)  3  parameter 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:
FUNCTION  RESULT  NOTES 
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:
FUNCTION  RESULT  NOTES 
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
returns a random number from the range 01.
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 datetime 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
112 (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 131, the number of hours may exceed the 124 range and the number of minutes and seconds may also exceed
the range of 160.
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 20000101 and it will return
the date of 19991201.
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 19991101.
Examples:
FUNCTION  RESULT  NOTES 
DateSerial(2000, 1, 3)  03012000  
DateSerial(2000, 16, 7)  20010407  
DateSerial(2000, 16, 43)  20010513  
DateSerial(2000, 0, 24)  19991224  
DateSerial(2000, 5, 5)  19990625  
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 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:
FUNCTION  RESULT  NOTES 
Date()  20140626  
Time()  13:53:28  
Now()  20140626 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.
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:
FUNCTION  RESULT  NOTES 
Year(#20100421 15:10:10#)  2010  
Month(#20100421#)  4  
Day(#20100421#)  21  
Hour(#20100421 15:10:10#)  15  
Minute(#12:41:13#)  41  
Second(#12:41:13#)  13  
Following Excel worksheet functions are the equivalents for abovementioned 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):
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 
Examples:
FUNCTION  RESULT  NOTES 
Weekday(#20101017#, vbSunday)  1  
Weekday(#20101017#, vbMonday)  7  
Weekday(#20101018#, 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: Runtime 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 Runtime 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.
Examples:
FUNCTION  RESULT  NOTES 
DateAdd("yy", 5, #20101017#)  error  Interval parameter has the value yy which is not on the list of acceptable values 
DateAdd("yyyy", 1, #20101017#)  20111017  Interval 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  17102010 
DateAdd("m", 2, #20101017#)  20101217  Interval 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  17102010 
DateAdd("ww", 5, #20101017#)  20101121  Interval 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  17102010 
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:
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:
Runtime 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):
VALUE  FIRST DAY OF THE WEEK 
vbUseSystemDayOfWeek  system settings will be used 
vbMonday  Monday 
vbTuesday  Tuesday 
vbWednesday  Wednesday 
vbThursday  Thursday 
vbFriday  Friday 
vbSaturday  Saturday 
vbSunday  Sunday 
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):
VALUE  FIRST WEEK OF THE YEAR 
vbFirstFourDays  the 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 
vbFirstFullWeek  the 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 
vbFirstJan1  the 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 
vbUseSystem  default system settings 
Examples:
FUNCTION  RESULT  NOTES 
DateDiff("ww", #20101016#, #20101017#, vbSunday)  1 
20101016 is Saturday and 20101017 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", #20101016#, #20101017#, 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", #20101101#, #20101017#)  1  
DateDiff("m", #20101017#, #20101101#)  1  
DateDiff("yyyy", #20000101#, #20101017#)  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:
INTERVAL  RETURNED 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 Runtime error '5': Invalid procedure Call or argument
Data
 datetime 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):
VALUE  FIRST DAY OF WEEK 
vbUseSystemDayOfWeek  system settings are used 
vbMonday  Monday 
vbTuesday  Tuesday 
vbWednesday  Wednesday 
vbThursday  Thursday 
vbFriday  Friday 
vbSaturday  Saturday 
vbSunday  Sunday 
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):
VALUE  FIRST WEEK OF THE YEAR 
vbFirstFourDays  the 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 
vbFirstFullWeek  the 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 
vbFirstJan1  the 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 
vbUseSystem  default system settings 
Examples:
FUNCTION  RESULT  NOTES 
DatePart("w", #20101017#, vbMonday)  7  returns the day of the week for 17th October, assuming that Monday is treated as the first day of the week 
DatePart("ww", #20101017#)  43  returns the number of week in the year in which containing the date of 17th October 
DatePart("ww", #20101017#, , vbFirstJan1)  42  returns 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", #20101017#)  2010  returns year number 
DatePart("y", #20101017#)  290  returns 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.
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 caseinsensitive)  function returns
the value of Boolean type corresponding to the given text.
For any other strings, function CBool
throws an error: Runtime error '13': Type mismatch.
If any value not listed above is passed as an input parameter Expression
, function throws
error: Runtime 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:
FUNCTION  RESULT  NOTES 
CBool("a")  error  Invalid 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:
FUNCTION  RESULT  NOTES 
CStr(10)  10  
CStr(0.23)  0.23  
CStr(False)  False  
CStr(#20100101#)  20100101  
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 0255 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:
FUNCTION  RESULT  NOTES 
CByte("a")  error  the only accepted text values of the Expression argument are text representations of numbers 
CByte("300")  error  300 is a text representation of a number, however the result would exceed acceptable range of Byte type (0255) 
CByte(100.23)  100  
CInt(1000)  1000  
CInt(50000)  error  the 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:
Runtime error '13': Type mismatch.
Examples:
FUNCTION  RESULT  NOTES 
CDate("x")  error  the only acceptable text value of the Expression argument is text representation of date or number. 
CDate(700000)  error  the given number exceeds the acceptable range 
CDate("20100101")  20100101  
CDate(40000)  20090706  
CDate("40000")  20090706  
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 nonexistent 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:
FUNCTION  RESULT  NOTES 
TypeName()  error  TypeName function cannot be called without arguments definition 
TypeName(nieistniejaca)  Empty  when nonexistent variable is passed as input parameter, function returns text Empty 
TypeName(#20100101#)  Date  plain 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 nonnumeric.
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 nonexisting variables is given as an input parameter.
Examples:
FUNCTION  RESULT  NOTES 
IsNumeric(xyz)  True  for nonexistent arguments the function returns True! 
IsNumeric("a")  False  
IsNumeric("1")  True  given value is a text but it is also a text representation of number 1 
IsNumeric(#20100101#)  False  function 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 nonexistent variable as an argument the function returns
False.
Examples:
FUNCTION  RESULT  NOTES 
IsDate(xyz)  False  for nonexistent arguments the function returns False 
IsDate(40000)  False  despite the fact that each number represents a date, when giving a number as an argument the function returns False. 
IsDate("20100101")  True  
IsDate(20100101)  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 builtin 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 dropdown list containing names of all available functions, as shown in the picture below:
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.
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.
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