Excel VBA Functions
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.
Sub WorksheetFunction_Example()
'start the code as “WorksheetFunction”
'When you put a dot (.) it will display the functions available
Range("B9").Value = WorksheetFunction.Sum(Range("B2:B8"))
End Sub
Output
| Subjects | Marks |
| Hindi | 78 |
| English | 65 |
| Math | 98 |
| SST | 45 |
| Computer | 67 |
| Science | 87 |
| Sanskrit | 88 |
| Total | 528 |
