What is a function?

A function is also called a procedure, but it is not a sub procedure, it’s a function procedure. You have already been using some function in Excel, a mathematical function, like sum, max, min, average, Vlookup. Most of the functions you’re used to using in Excel are built in. But sometimes the requirement that we’re after does not entirely code for the built-in functions.

In VBA, the limitations of the function are you cannot record them. A procedure we can record, and thus, the code is automatically created for us. But a function must be hand-written by ourselves. 

To create a function, you use the Function keyword, followed by a function name and parentheses. Unlike a sub procedure, because a function returns a value, you must specify the type of value the function will produce. We will briefly review how to create our customized function in the following tutorial.

Instead of creating your function, you can use one of those that comes with the VBA language. This language provides a very extensive library of functions and offers many built-in functions that can be used in your code.

Function Categories

We will review the most popular built-in functions of VBA. Select the function name, to go to a full description of the function with examples of use.

1. VBA Text Functions

Function Description
Format This function applies a format to the given expression and returns the output as a string.
InStr It is used to return a position of a substring with the String.
InStrRev It is used to return the position of a substring within a string, seeking from right to left.
Left This function returns a left substring from the start of a supplied string.
Len It returns the length of the given string.
LCase This function converts the given string to lower case text.
LTrim It is used to remove the spaces from the given string.
Mid This function is used to returns a substring from the middle of the given string.
Replace It replaces a substring within the given string.
Right This function returns a substring from the end of the given string.
RemoveVowels This function is used to remove the vowels (a,e,I,o,u) from the specified string.
RTrim It is used to remove the trailing spaces from the string.
Space This function creates a string with the specified Datenumber of spaces.
StrComp It is used to compare two strings and return an integer value based on the comparison.
StrConv This function converts a string into a specified format.
String It creates a string comprising of several repeated characters.
StrReverse This function reverses the given string.
Trim This function removes the leading spaces from the String.
UCase This function is used to convert the string to upper case.

2. Informative Functions

Function Description
IsArray This function returns a Boolean value True if the supplied variable is an array.
IsDate This function checks whether the given expression is a date or not and returns a Boolean value True if the supplied variable is a Date.
IsEmpty This function returns a Boolean value True if the variable is Empty.
IsError This function returns a Boolean value True if the specified expression signifies an error.
IsMissing It is used to test if an optional argument to a procedure is missing.
IsNull This function returns a Boolean value True if the variable is Null.
IsNumeric This function returns a Boolean value True if the variable is numeric.
IsObject This function tests if a supplied variable represents an object variable

3. VBA Flow Functions

Function Description
Choose This function is used to select a value from the given list of arguments.
If It is used to evaluate an expression and returns one of two values (based on whether the expression calculates to True or False)
Switch This function is used to evaluate a list of Boolean expressions and returns a value associated with the first true expression.

4. VBA Error Handling Functions

Function Description
CVErr This function generates an Error data type for a given error code.
Error It is used to Return the error message based on a specified error code.

5. VBA Conversion Functions

Function Description
Asc This function returns an integer representing the ASCII code for the specified character.
CBool It is used to convert an expression to a Boolean data type.
CByte It is used to convert an expression to a Byte data type.
CCur It is used to convert an expression to a Currency data type.
CDate It is used to convert an expression to a Date data type.
CDbl It is used to convert an expression to a Double data type.
CDec It is used to convert an expression to a Decimal data type.
Chr This function returns the character value for the given character code.
Cint It converts an expression to an integer data type.
CLng It converts an expression to a Long data type.
CSng It converts an expression to a Single data type.
CStr It converts an expression to a String data type.
CVar It converts an expression to a Variant data type.
FormatCurrency This function returns a string value after applying a currency format to the specified expression.
FormatDateTime This function returns a string value after applying a date/time format to the specified expression.
FormatNumber This function returns a string value after applying a number format to the specified expression.
FormatPercent This function returns a string value after applying a percentage format to the specified expression.
Hex This function returns a string value after converting a numeric value to hexadecimal notation.
Oct This function returns a string value after converting a numeric value to octal.
Str This function is used to convert a numeric value to string.
Val This function is used to convert the specified string to a numeric value

6. Date & Time Functions

Function Description
Date This function returns the current date.
DateAdd This function adds a time interval to a date or time.
DateDiff This function is used to return the number of intervals between two dates and/or times
DatePart This function returns a part (day, month, year) of the specified date/time.
DateSerial It returns a Date from the given year, month and day number.
DateValue This function returns a Date from a String representation of a date/time.
Day It is used to return the day number (from 1 to 31) for the specified date.
Hour This function returns the hour component for the specified time.
Minute This function is used to return the minute component for the specified time.
Month This function is used to return the month number (from 1 to 12) for the given date.
MonthName This function is used to return the month name for the given month number (from 1 to 12).
Now This function returns the current date and time.
Second This function returns the second component for the given time.
Time This function returns the current time.
Timer It is used to return the number of seconds that have passed since midnight.
TimeSerial This function returns a Time from a supplied hour, minute and second.
TimeValue This function is used to return a Time from a String representation of a date/time.
Weekday It is used to return an integer (from 1 to 7), representing the weekday for the given date.
WeekdayName This function returns the weekday name for a specified integer (from 1 to 7).
Year This function returns the year of a supplied date.

7. Math & Trig Functions

Function Description
Abs This function returns the absolute value for the given number.
Atn It calculates the arctangent of a specified number.
Cos This function calculates the cosine for the given angle.
Exp This function calculates the value of ex for a specified value of x.
Fix It is used to truncate a number to an integer.
Int This function returns the integer portion of a number.
Log This function calculates the natural logarithm for the given number.
Rnd This function generates a random number between 0 and 1.
Round This function rounds a number to the given number of decimal places.
Sgn This function returns an integer representing the arithmetic sign of a number.
Sin It is used calculate the sine for the given angle.
Tan It is used to calculate the tangent of a specified angle.
Sqr This function returns the square root of a number.

8. VBA Array Functions

Function Description
Array This function creates an array, containing a specified set of values.
Filter This function is used to return a subset of a given string array, based on specified criteria.
Join This function joins several substrings into a single string.
LBound This function returns the lowest subscript for a dimension of an array.
Split It is used to split a Text String into several Substrings.
UBound This function returns the highest subscript for a dimension of an array.

Excel Worksheet Functions

VBA worksheet functions are usually faster than normal VBA code written to achieve the same results. Therefore, it is advisable to have a good knowledge of how to use worksheet functions in VBA. It helps you create VBA applications faster than you’d be able to create without them.

Worksheet function in VBA enables you to refer to a specific worksheet, normally when we craft a module in VBA the code executes in the currently active sheet of the workbook but if we want to execute the code in the specific worksheet we use worksheet function, this function has various uses and applications in VBA.

Generally, when you use Application, and your function produces an error it will return the error value.  But if the WorksheetFunction method is used, VBA will automatically throw a run time error.  One can handle the VBA error, but it’s is usually advisable to avoid the error in the first place.

In order to call worksheet functions using VBA, the WorksheetFunction object is used. The WorksheetFunction object is contained within the application object.

Example 1: Write a macro demonstrating the use of Worksheet Function.

Output

Subjects Marks
Hindi 78
English 65
Math 98
SST 45
Computer 67
Science 87
Sanskrit 88
Total 528
VBA Functions

Pin It on Pinterest

Share This