VBA Syntax for declaring a Function<\/h3>\nFunction Function_name(list of parameters)\r\n\r\nStatement1\r\n.\r\n.\r\n.\r\nStatement_n\r\n\r\nEnd Function<\/pre>\nVBA Syntax for declaring a sample Function<\/h3>\nPrivate Function FunctionName (ByVal argument_1 As Integer, ByVal argument_2 As Integer)\r\n\r\ntotal=argument_1+argument_2\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n\n- \n
Private Function functionName() –<\/h5>\n<\/li>\n<\/ul>\n
Here the sub declares a function named \u201cfunctionName\u201d and starts the body of the function. To specify the scope of the function, the keyword \u201cprivate\u201d is used.<\/p>\n
\n- \n
ByVal argument_1 As Integer, ByVal argument_2 As Integer –<\/h5>\n<\/li>\n<\/ul>\n
This declares two parameters of integer data type named argument_1 and argument_2<\/p>\n
functionName=argument_1+argument_2<\/p>\n
This evaluates the two numbers and assigns the value to the functionName.<\/p>\n
End Sub –<\/h4>\n
This denotes the end of the function body.<\/p>\n
Function demonstrated with Example:<\/h3>\n
Functions are also the same like subroutines. The one main difference between the function and subroutine is that the function returns a value whenever it is called whereas the subroutine does not return a value when it is called.<\/p>\n
Now let’s see how to create a function which accepts two numbers and returns the product of the numbers.<\/p>\n
The below steps are the processes on how to call functions in vba.<\/p>\n
1: Design the user interface and set the properties as per the requirement for the user controls.<\/p>\n
2: Add the function in the program.<\/p>\n
3: Mention the client event code for the button which calls the subroutine.<\/p>\n
4: Application testing.<\/p>\n
Step by step process<\/h3>\n
Step – 1: Designing the user interface and setting the properties<\/p>\n
Design the user interface by setting the name properties to btnDisplayProduct and the caption to DataFlair_Function.<\/p>\n
Your GUI appears as follows:<\/p>\n
![\"gui](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/10\/gui-dataflair-functions.png\")
<\/a><\/p>\nStep 2: Adding the function.<\/p>\n
Open the code window by clicking on the view code option under the developer tab. Add the following function code in the VBE editor<\/p>\n
Code:<\/h4>\nPrivate Function DataFlair_Function(ByVal firstnum As Integer, ByVal secondnum As Integer)\r\n\r\nDataFlair_Function = firstnum * secondnum\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n\n- \n
Private Function DataFlair_Function –<\/h5>\n
This declares a private function \u201cDataFlair_Function\u201d that accepts two integer parameters.<\/li>\n
- \n
ByVal firstnum As Integer, ByVal secondnum As Integer –<\/h5>\n
This declares two parameter variables and those are \u2018firstnum\u2019 and \u2018secondnum\u2019.<\/li>\n
- \n
productNum = firstnum * secondnum –<\/h5>\n
This accepts the two integer values, multiplies it and assigns the product value to the productNum.<\/li>\n<\/ul>\n
Step 3: Calling the function from the command button click event.<\/p>\n
\n- Open the code window by clicking on the view code option under the developer tab.<\/li>\n
- This opens the code editor window.<\/li>\n<\/ul>\n
Add the following code in the code editor for the click event of the btnDisplayProduct command button.<\/p>\n
Code:<\/h4>\nPrivate Sub btnDisplayProduct ( )\r\n\r\nMsgBox DataFlair_Function (2,100)\r\n\r\nEnd Sub<\/pre>\nCode Explanation<\/h4>\n\n- \n
MsgBox DataFlair_Function(2,100) –<\/h5>\n<\/li>\n<\/ul>\n
This calls the function DataFlair_Function<\/strong> and passes in 2 and 100 as the parameters. The function returns the product of the two numbers i.e., 200.<\/p>\nThe first part of the code lines are the function code and the second part of the code lines are the click event code which calls the function from the command button.<\/p>\n
Press the save button to save the changes and close the editor window.<\/p>\n
Step 4: Testing the Application<\/p>\n
To test the application, follow the steps:<\/p>\n
Step 1: Go to the Developer tab.<\/p>\n
Note: Check whether the design mode is on or off. If it is on, then the application wouldn\u2019t work.<\/p>\n
Step 2: Click on the Design Mode to turn it off.<\/p>\n
Step 3: Click on the newly created button.<\/p>\n
The following output appears.<\/p>\n
![\"output\"](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/10\/output-1.png\")
<\/a><\/p>\nUser Defined Functions in Excel<\/h3>\n
Excel has many collections of functions. In many situations those functions are more than enough to complete the task. If not, then the user can create a own function and that is called a user defined or custom excel function. The user can access a user defined function just like any other excel function.<\/p>\n
Now, let\u2019s create a function called SUM_ODD that finds the sum of the odd numbers of a randomly selected range.<\/p>\n
Code:<\/h4>\nFunction SUM_ODD(rng As Range)\r\n\r\nDim cell As Range\r\n\r\nFor Each cell In rng\r\n\r\nIf cell.Value Mod 2 = 0 Then\r\n\r\nSUM_ODD= SUM_ODD + cell.Value\r\n\r\nNext cell\r\n\r\nEnd If\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, the code works on any cell on the worksheet and it sums up all the odd values in the cell range as we have not mentioned any specific cell range.<\/p>\n
Note: Any function written and accessed by the user, apart from the built in functions are known as user defined functions.<\/p>\n
Understanding the Scope of a User Defined Function in Excel<\/h3>\n
There are two scopes in a function and they are:<\/p>\n
\n- Public Scope<\/li>\n
- Private Scope<\/li>\n<\/ul>\n
1. Public scope<\/h4>\n
In public scope, the function is available for all the sheets in the workbook as well as all the procedures across all modules in the sheet. This is useful when the user wants to call a function from a subroutine.<\/p>\n
2. Private Scope<\/h4>\n
In private scope, the function is available only in the module in which it is provided. The user can\u2019t use it in other modules. The user won\u2019t see it in the list of functions in the spreadsheet. For example, if the function name is \u2018 DataFlair()\u2019, and the user type function in excel after the \u201c=\u201d sign, it will not show the function name. But still the user can use it if they enter the formula name.<\/p>\n
Calling an User Defined Function from Other Workbooks<\/h3>\n
If the user has a function in a workbook, then the user can call the function in other workbooks also. There are different ways to do it and they are:<\/p>\n
\n- Create an Add-In<\/li>\n
- Save the function in Personal Macro Workbook<\/li>\n
- Refer the function from another worksheet<\/li>\n<\/ul>\n
1. Create an Add-in<\/h4>\n
After creating and installing an add-in, the user will have the custom function in it available in all the workbooks.<\/p>\n
Imagine the user has created a custom function \u2013 \u2018GetNumeric\u2019 and the user wants it in all the workbooks. In order to do that the user has open a new workbook and have the function code in a module in the new workbook. Now, follow the steps to install the function in Excel.<\/p>\n
1: Go to the File tab and click on the \u201cSave As\u201d option.<\/p>\n
2: Change the file extension to .xlam.<\/p>\n
Note: The name the user assigns to the file will be the name of the add-in. The user can also notice that the path of the file where it gets saved automatically changes.<\/p>\n
3: Open a new Excel workbook<\/p>\n
4: Click on the Excel Add-ins option under the Developer tab.<\/p>\n
![\"add](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/10\/add-ins.png\")
<\/a><\/p>\n5: In the Add-ins dialogue box, browse and locate the file that you saved with the function.<\/p>\n
6: After check marking on the function, click the ok button.<\/p>\n
Note: Finally, the add-in has been activated and the user can use the custom function in all the workbooks.<\/p>\n
2. Referencing the function from another workbook<\/h4>\n
If the user wants to refer to the function from another workbook, then that workbook has to be opened.<\/p>\n
3. Syntax to refer a function from another workbook:<\/h4>\n
= \u2018Workbook_Name\u2019!Function_Name(Cell_number)<\/strong><\/p>\nHere, in the workbook_name place the user has to place the workbook name of the function. Function_name refers to the name of the function and the cell number denotes the cell number.<\/p>\n
Excel Inbuilt Functions vs VBA User Defined Function<\/h3>\n
There are few advantages of using Excel in-built functions over custom functions created in VBA and they are:<\/p>\n
\n- VBA functions are quite slower than the inbuilt functions.<\/li>\n
- When the user creates a report using VBA functions, and if they send it to another user, then they need not have to worry about whether the macros are enabled or not.<\/li>\n
- Note: In some cases, the other users get scared by looking at the warning symbol in the yellow bar and that warning is simply a request to the user to enable the macros.<\/li>\n
- With inbuilt Excel functions, the user doesn’t need to worry about the file extensions. If the user has macros or user-defined functions in the workbook, then they need to save it in .xlsm.<\/li>\n
- It\u2019s better to use a user-defined function if the inbuilt formula is huge and complicated.<\/li>\n
- When the user has to get something specifically done then that can not be done by Excel inbuilt functions.<\/li>\n<\/ul>\n
Volatile Functions in VBA<\/h3>\n
The user defined functions in excel are not volatile by default. When any of the arguments in the function changes, they perform the task and recalculate again. A volatile function recalculates, whenever calculation occurs in any part of the worksheet. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nFunction DATAFLAIR(cell As Range)\r\nDATAFLAIR = cell.Value + cell.Offset(1, 0).Value\r\nApplication.Volatile\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, we have created a function called DATAFLAIR and this function returns the sum of the selected cell and the cell below that particular selected cell.<\/p>\n
Note: The code is non volatile when you don’t enter the \u201cApplication. Volatile\u201d statement in the code. The non-volatile function is not recalculated when any other cell on the sheet changes and it recalculates only when the arguments in the function changes.<\/p>\n
Output:<\/h4>\n
![\"excel](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/10\/volatile.png\")
<\/a><\/p>\nThe \u201cDATAFLAIR(A2)\u201d is called in the D8 cell and this is how we call the function generally in the sheet.<\/p>\n
Note: If the user first creates the non-volatile function then the user needs to enter the function again to make it volatile after adding the required statements in the code.<\/p>\n
By Ref and ByVal<\/h3>\n
The user can pass the arguments to a function procedure by reference or by value. The VBA passes arguments by reference as default. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nDim num As Integer\r\nnum = 2\r\nMsgBox Double(num)\r\nMsgBox num<\/pre>\nCode Explanation:<\/h4>\n
Here, the code calls the function num. It’s the result of the second MsgBox that we are looking for and this function needs to be placed into a module. Now, open the module and add the below function code.<\/p>\n
Function Code:<\/h3>\nFunction Double(ByRef num As Integer) As Integer\r\nnum = num * num\r\nDouble = num\r\nEnd Function<\/pre>\nOutput:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
![\"calc](\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2021\/10\/calc-op.png\")
<\/a><\/p>\nByVal:<\/h4>\n
Now, let’s see an example of replacing ByRef with ByVal.<\/p>\n
Code:<\/h4>\nFunction calc(ByVal num As Integer) As Integer\r\nnum = num * num\r\ncalc = num\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
The user refers to the original value when the passing arguments are by reference. The value of num is generally changed in the function and as a result the second n=message box displays the changed value. If the user passes the arguments by value then they are passing a copy to the function and hence the original value does not change. As a result, the second message box displays the original value.<\/p>\n
Output:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nInputBox<\/span><\/td>\n It displays a dialog box where the user can enter the input<\/span><\/td>\n<\/tr>\n\nMsgBox<\/span><\/td>\n It displays a message box<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n2. VBA Text Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nFormat<\/span><\/td>\n It returns the result as a string and it applies a format to an expression.<\/span><\/td>\n<\/tr>\n\nInStr<\/span><\/td>\n It returns the substring position in a string.<\/span><\/td>\n<\/tr>\n\nInStrRev<\/span><\/td>\n It returns the substring position in a string searching from right to left.\u00a0<\/span><\/td>\n<\/tr>\n\nLeft<\/span><\/td>\n It returns the substring from the beginning of a provided string.<\/span><\/td>\n<\/tr>\n\nLen<\/span><\/td>\n It returns the length of the provided string.<\/span><\/td>\n<\/tr>\n\nLCase<\/span><\/td>\n It converts the string elements to lower text cases.<\/span><\/td>\n<\/tr>\n\nLTrim<\/span><\/td>\n It removes the extra spaces in the string.\u00a0<\/span><\/td>\n<\/tr>\n\nMid<\/span><\/td>\n It returns the substring from the middle of a string.<\/span><\/td>\n<\/tr>\n\nReplace<\/span><\/td>\n It replaces a substring with the new string.<\/span><\/td>\n<\/tr>\n\nRight<\/span><\/td>\n It returns a substring from the end of the string.<\/span><\/td>\n<\/tr>\n\nRTrim<\/span><\/td>\n It removes the extra spaces from the right side of the string.<\/span><\/td>\n<\/tr>\n\nSpace<\/span><\/td>\n It creates a string consisting of a specified number space.<\/span><\/td>\n<\/tr>\n\nStrComp<\/span><\/td>\n It compares the two strings and returns the result in an integer representing the comparison.\u00a0<\/span><\/td>\n<\/tr>\n\nStrConv<\/span><\/td>\n It converts a string to a particular format mentioned.\u00a0<\/span><\/td>\n<\/tr>\n\nString<\/span><\/td>\n It creates a sequence of characters in a string.\u00a0<\/span><\/td>\n<\/tr>\n\nStrReverse<\/span><\/td>\n It reverses the string.<\/span><\/td>\n<\/tr>\n\nTrim<\/span><\/td>\n It removes the spaces in the beginning and in the ending of the string.\u00a0<\/span><\/td>\n<\/tr>\n\nUCase<\/span><\/td>\n It converts the string to upper case characters.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n3. VBA Information Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nIsArray<\/span><\/td>\n It tests whether the provided variable is an array or not.<\/span><\/td>\n<\/tr>\n\nIsDate<\/span><\/td>\n It tests whether the provided expression is a date or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsEmpty<\/span><\/td>\n It tests whether the provided variant is empty or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsError<\/span><\/td>\n It tests whether the provided expression is an error or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsMissing<\/span><\/td>\n It tests whether an optional argument procedure is missing or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsNull<\/span><\/td>\n It tests whether the expression is null or not.<\/span><\/td>\n<\/tr>\n\nIsNumeric<\/span><\/td>\n It tests whether the expression is numeric or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsObject<\/span><\/td>\n It tests whether the variable represents an object variable.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n4. VBA Error Handling Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nCVErr<\/span><\/td>\n It produces an error data type for a provided error code.<\/span><\/td>\n<\/tr>\n\nError<\/span><\/td>\n It returns the error message corresponding to the provided error code.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n5. VBA Program Flow Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nChoose<\/span><\/td>\n It selects a value from the argument list.<\/span><\/td>\n<\/tr>\n\nllf<\/span><\/td>\n It returns one of the two values by evaluating an expression. It evaluates to true or false.\u00a0<\/span><\/td>\n<\/tr>\n\nSwitch<\/span><\/td>\n It returns the value associated with the first true expression by evaluating a list of boolean expressions.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n6. VBA Conversion Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nAsc<\/span><\/td>\n It returns an integer representing the code for a provided character.\u00a0<\/span><\/td>\n<\/tr>\n\nCBool<\/span><\/td>\n It converts an expression to a boolean data type.<\/span><\/td>\n<\/tr>\n\nCByte<\/span><\/td>\n It converts an expression to a byte data type.\u00a0\u00a0<\/span><\/td>\n<\/tr>\n\nCCur<\/span><\/td>\n It converts an expression to a currency data type.<\/span><\/td>\n<\/tr>\n\nCDate<\/span><\/td>\n It converts an expression to a date data type.<\/span><\/td>\n<\/tr>\n\nCDbl<\/span><\/td>\n It converts an expression to a double data type.<\/span><\/td>\n<\/tr>\n\nCDec<\/span><\/td>\n It converts an expression to a decimal data type.<\/span><\/td>\n<\/tr>\n\nChr<\/span><\/td>\n It returns the character corresponding to the provided character code.<\/span><\/td>\n<\/tr>\n\nClnt<\/span><\/td>\n It converts an expression to an integer data type.<\/span><\/td>\n<\/tr>\n\nCLng<\/span><\/td>\n It converts an expression to a long data type.<\/span><\/td>\n<\/tr>\n\nCSng<\/span><\/td>\n It converts an expression to a single data type.<\/span><\/td>\n<\/tr>\n\nCStr<\/span><\/td>\n It converts an expression to a string data type.<\/span><\/td>\n<\/tr>\n\nCVar<\/span><\/td>\n It converts an expression to a variant data type.\u00a0<\/span><\/td>\n<\/tr>\n\nFormatCurrency<\/span><\/td>\n It converts the expression to currency format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatDateTime<\/span><\/td>\n It converts the expression to date and time format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatNumber<\/span><\/td>\n It converts the expression to number format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatPercent<\/span><\/td>\n It converts the expression to percentage format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nHex<\/span><\/td>\n It converts the numeric value to hexadecimal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nOct<\/span><\/td>\n It converts the numeric value to octal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nStr<\/span><\/td>\n It converts a numeric value to a string.<\/span><\/td>\n<\/tr>\n\nVal<\/span><\/td>\n It converts a string to a numeric value.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n7. VBA Date and Time Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nDate<\/span><\/td>\n It returns the current date.<\/span><\/td>\n<\/tr>\n\nDateAdd<\/span><\/td>\n It adds an interval between the date or time.<\/span><\/td>\n<\/tr>\n\nDateDiff<\/span><\/td>\n It returns the number of intervals between the two dates or time.<\/span><\/td>\n<\/tr>\n\nDatePart<\/span><\/td>\n It returns a part of a date or time<\/span><\/td>\n<\/tr>\n\nDateSerial<\/span><\/td>\n It returns the date from the provided date details.<\/span><\/td>\n<\/tr>\n\n
VBA Syntax for declaring a sample Function<\/h3>\nPrivate Function FunctionName (ByVal argument_1 As Integer, ByVal argument_2 As Integer)\r\n\r\ntotal=argument_1+argument_2\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n\n- \n
Private Function functionName() –<\/h5>\n<\/li>\n<\/ul>\n
Here the sub declares a function named \u201cfunctionName\u201d and starts the body of the function. To specify the scope of the function, the keyword \u201cprivate\u201d is used.<\/p>\n
\n- \n
ByVal argument_1 As Integer, ByVal argument_2 As Integer –<\/h5>\n<\/li>\n<\/ul>\n
This declares two parameters of integer data type named argument_1 and argument_2<\/p>\n
functionName=argument_1+argument_2<\/p>\n
This evaluates the two numbers and assigns the value to the functionName.<\/p>\n
End Sub –<\/h4>\n
This denotes the end of the function body.<\/p>\n
Function demonstrated with Example:<\/h3>\n
Functions are also the same like subroutines. The one main difference between the function and subroutine is that the function returns a value whenever it is called whereas the subroutine does not return a value when it is called.<\/p>\n
Now let’s see how to create a function which accepts two numbers and returns the product of the numbers.<\/p>\n
The below steps are the processes on how to call functions in vba.<\/p>\n
1: Design the user interface and set the properties as per the requirement for the user controls.<\/p>\n
2: Add the function in the program.<\/p>\n
3: Mention the client event code for the button which calls the subroutine.<\/p>\n
4: Application testing.<\/p>\n
Step by step process<\/h3>\n
Step – 1: Designing the user interface and setting the properties<\/p>\n
Design the user interface by setting the name properties to btnDisplayProduct and the caption to DataFlair_Function.<\/p>\n
Your GUI appears as follows:<\/p>\n
<\/a><\/p>\nStep 2: Adding the function.<\/p>\n
Open the code window by clicking on the view code option under the developer tab. Add the following function code in the VBE editor<\/p>\n
Code:<\/h4>\nPrivate Function DataFlair_Function(ByVal firstnum As Integer, ByVal secondnum As Integer)\r\n\r\nDataFlair_Function = firstnum * secondnum\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n\n- \n
Private Function DataFlair_Function –<\/h5>\n
This declares a private function \u201cDataFlair_Function\u201d that accepts two integer parameters.<\/li>\n
- \n
ByVal firstnum As Integer, ByVal secondnum As Integer –<\/h5>\n
This declares two parameter variables and those are \u2018firstnum\u2019 and \u2018secondnum\u2019.<\/li>\n
- \n
productNum = firstnum * secondnum –<\/h5>\n
This accepts the two integer values, multiplies it and assigns the product value to the productNum.<\/li>\n<\/ul>\n
Step 3: Calling the function from the command button click event.<\/p>\n
\n- Open the code window by clicking on the view code option under the developer tab.<\/li>\n
- This opens the code editor window.<\/li>\n<\/ul>\n
Add the following code in the code editor for the click event of the btnDisplayProduct command button.<\/p>\n
Code:<\/h4>\nPrivate Sub btnDisplayProduct ( )\r\n\r\nMsgBox DataFlair_Function (2,100)\r\n\r\nEnd Sub<\/pre>\nCode Explanation<\/h4>\n\n- \n
MsgBox DataFlair_Function(2,100) –<\/h5>\n<\/li>\n<\/ul>\n
This calls the function DataFlair_Function<\/strong> and passes in 2 and 100 as the parameters. The function returns the product of the two numbers i.e., 200.<\/p>\nThe first part of the code lines are the function code and the second part of the code lines are the click event code which calls the function from the command button.<\/p>\n
Press the save button to save the changes and close the editor window.<\/p>\n
Step 4: Testing the Application<\/p>\n
To test the application, follow the steps:<\/p>\n
Step 1: Go to the Developer tab.<\/p>\n
Note: Check whether the design mode is on or off. If it is on, then the application wouldn\u2019t work.<\/p>\n
Step 2: Click on the Design Mode to turn it off.<\/p>\n
Step 3: Click on the newly created button.<\/p>\n
The following output appears.<\/p>\n
<\/a><\/p>\nUser Defined Functions in Excel<\/h3>\n
Excel has many collections of functions. In many situations those functions are more than enough to complete the task. If not, then the user can create a own function and that is called a user defined or custom excel function. The user can access a user defined function just like any other excel function.<\/p>\n
Now, let\u2019s create a function called SUM_ODD that finds the sum of the odd numbers of a randomly selected range.<\/p>\n
Code:<\/h4>\nFunction SUM_ODD(rng As Range)\r\n\r\nDim cell As Range\r\n\r\nFor Each cell In rng\r\n\r\nIf cell.Value Mod 2 = 0 Then\r\n\r\nSUM_ODD= SUM_ODD + cell.Value\r\n\r\nNext cell\r\n\r\nEnd If\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, the code works on any cell on the worksheet and it sums up all the odd values in the cell range as we have not mentioned any specific cell range.<\/p>\n
Note: Any function written and accessed by the user, apart from the built in functions are known as user defined functions.<\/p>\n
Understanding the Scope of a User Defined Function in Excel<\/h3>\n
There are two scopes in a function and they are:<\/p>\n
\n- Public Scope<\/li>\n
- Private Scope<\/li>\n<\/ul>\n
1. Public scope<\/h4>\n
In public scope, the function is available for all the sheets in the workbook as well as all the procedures across all modules in the sheet. This is useful when the user wants to call a function from a subroutine.<\/p>\n
2. Private Scope<\/h4>\n
In private scope, the function is available only in the module in which it is provided. The user can\u2019t use it in other modules. The user won\u2019t see it in the list of functions in the spreadsheet. For example, if the function name is \u2018 DataFlair()\u2019, and the user type function in excel after the \u201c=\u201d sign, it will not show the function name. But still the user can use it if they enter the formula name.<\/p>\n
Calling an User Defined Function from Other Workbooks<\/h3>\n
If the user has a function in a workbook, then the user can call the function in other workbooks also. There are different ways to do it and they are:<\/p>\n
\n- Create an Add-In<\/li>\n
- Save the function in Personal Macro Workbook<\/li>\n
- Refer the function from another worksheet<\/li>\n<\/ul>\n
1. Create an Add-in<\/h4>\n
After creating and installing an add-in, the user will have the custom function in it available in all the workbooks.<\/p>\n
Imagine the user has created a custom function \u2013 \u2018GetNumeric\u2019 and the user wants it in all the workbooks. In order to do that the user has open a new workbook and have the function code in a module in the new workbook. Now, follow the steps to install the function in Excel.<\/p>\n
1: Go to the File tab and click on the \u201cSave As\u201d option.<\/p>\n
2: Change the file extension to .xlam.<\/p>\n
Note: The name the user assigns to the file will be the name of the add-in. The user can also notice that the path of the file where it gets saved automatically changes.<\/p>\n
3: Open a new Excel workbook<\/p>\n
4: Click on the Excel Add-ins option under the Developer tab.<\/p>\n
<\/a><\/p>\n5: In the Add-ins dialogue box, browse and locate the file that you saved with the function.<\/p>\n
6: After check marking on the function, click the ok button.<\/p>\n
Note: Finally, the add-in has been activated and the user can use the custom function in all the workbooks.<\/p>\n
2. Referencing the function from another workbook<\/h4>\n
If the user wants to refer to the function from another workbook, then that workbook has to be opened.<\/p>\n
3. Syntax to refer a function from another workbook:<\/h4>\n
= \u2018Workbook_Name\u2019!Function_Name(Cell_number)<\/strong><\/p>\nHere, in the workbook_name place the user has to place the workbook name of the function. Function_name refers to the name of the function and the cell number denotes the cell number.<\/p>\n
Excel Inbuilt Functions vs VBA User Defined Function<\/h3>\n
There are few advantages of using Excel in-built functions over custom functions created in VBA and they are:<\/p>\n
\n- VBA functions are quite slower than the inbuilt functions.<\/li>\n
- When the user creates a report using VBA functions, and if they send it to another user, then they need not have to worry about whether the macros are enabled or not.<\/li>\n
- Note: In some cases, the other users get scared by looking at the warning symbol in the yellow bar and that warning is simply a request to the user to enable the macros.<\/li>\n
- With inbuilt Excel functions, the user doesn’t need to worry about the file extensions. If the user has macros or user-defined functions in the workbook, then they need to save it in .xlsm.<\/li>\n
- It\u2019s better to use a user-defined function if the inbuilt formula is huge and complicated.<\/li>\n
- When the user has to get something specifically done then that can not be done by Excel inbuilt functions.<\/li>\n<\/ul>\n
Volatile Functions in VBA<\/h3>\n
The user defined functions in excel are not volatile by default. When any of the arguments in the function changes, they perform the task and recalculate again. A volatile function recalculates, whenever calculation occurs in any part of the worksheet. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nFunction DATAFLAIR(cell As Range)\r\nDATAFLAIR = cell.Value + cell.Offset(1, 0).Value\r\nApplication.Volatile\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, we have created a function called DATAFLAIR and this function returns the sum of the selected cell and the cell below that particular selected cell.<\/p>\n
Note: The code is non volatile when you don’t enter the \u201cApplication. Volatile\u201d statement in the code. The non-volatile function is not recalculated when any other cell on the sheet changes and it recalculates only when the arguments in the function changes.<\/p>\n
Output:<\/h4>\n
<\/a><\/p>\nThe \u201cDATAFLAIR(A2)\u201d is called in the D8 cell and this is how we call the function generally in the sheet.<\/p>\n
Note: If the user first creates the non-volatile function then the user needs to enter the function again to make it volatile after adding the required statements in the code.<\/p>\n
By Ref and ByVal<\/h3>\n
The user can pass the arguments to a function procedure by reference or by value. The VBA passes arguments by reference as default. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nDim num As Integer\r\nnum = 2\r\nMsgBox Double(num)\r\nMsgBox num<\/pre>\nCode Explanation:<\/h4>\n
Here, the code calls the function num. It’s the result of the second MsgBox that we are looking for and this function needs to be placed into a module. Now, open the module and add the below function code.<\/p>\n
Function Code:<\/h3>\nFunction Double(ByRef num As Integer) As Integer\r\nnum = num * num\r\nDouble = num\r\nEnd Function<\/pre>\nOutput:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
<\/a><\/p>\nByVal:<\/h4>\n
Now, let’s see an example of replacing ByRef with ByVal.<\/p>\n
Code:<\/h4>\nFunction calc(ByVal num As Integer) As Integer\r\nnum = num * num\r\ncalc = num\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
The user refers to the original value when the passing arguments are by reference. The value of num is generally changed in the function and as a result the second n=message box displays the changed value. If the user passes the arguments by value then they are passing a copy to the function and hence the original value does not change. As a result, the second message box displays the original value.<\/p>\n
Output:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nInputBox<\/span><\/td>\n It displays a dialog box where the user can enter the input<\/span><\/td>\n<\/tr>\n\nMsgBox<\/span><\/td>\n It displays a message box<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n2. VBA Text Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nFormat<\/span><\/td>\n It returns the result as a string and it applies a format to an expression.<\/span><\/td>\n<\/tr>\n\nInStr<\/span><\/td>\n It returns the substring position in a string.<\/span><\/td>\n<\/tr>\n\nInStrRev<\/span><\/td>\n It returns the substring position in a string searching from right to left.\u00a0<\/span><\/td>\n<\/tr>\n\nLeft<\/span><\/td>\n It returns the substring from the beginning of a provided string.<\/span><\/td>\n<\/tr>\n\nLen<\/span><\/td>\n It returns the length of the provided string.<\/span><\/td>\n<\/tr>\n\nLCase<\/span><\/td>\n It converts the string elements to lower text cases.<\/span><\/td>\n<\/tr>\n\nLTrim<\/span><\/td>\n It removes the extra spaces in the string.\u00a0<\/span><\/td>\n<\/tr>\n\nMid<\/span><\/td>\n It returns the substring from the middle of a string.<\/span><\/td>\n<\/tr>\n\nReplace<\/span><\/td>\n It replaces a substring with the new string.<\/span><\/td>\n<\/tr>\n\nRight<\/span><\/td>\n It returns a substring from the end of the string.<\/span><\/td>\n<\/tr>\n\nRTrim<\/span><\/td>\n It removes the extra spaces from the right side of the string.<\/span><\/td>\n<\/tr>\n\nSpace<\/span><\/td>\n It creates a string consisting of a specified number space.<\/span><\/td>\n<\/tr>\n\nStrComp<\/span><\/td>\n It compares the two strings and returns the result in an integer representing the comparison.\u00a0<\/span><\/td>\n<\/tr>\n\nStrConv<\/span><\/td>\n It converts a string to a particular format mentioned.\u00a0<\/span><\/td>\n<\/tr>\n\nString<\/span><\/td>\n It creates a sequence of characters in a string.\u00a0<\/span><\/td>\n<\/tr>\n\nStrReverse<\/span><\/td>\n It reverses the string.<\/span><\/td>\n<\/tr>\n\nTrim<\/span><\/td>\n It removes the spaces in the beginning and in the ending of the string.\u00a0<\/span><\/td>\n<\/tr>\n\nUCase<\/span><\/td>\n It converts the string to upper case characters.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n3. VBA Information Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nIsArray<\/span><\/td>\n It tests whether the provided variable is an array or not.<\/span><\/td>\n<\/tr>\n\nIsDate<\/span><\/td>\n It tests whether the provided expression is a date or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsEmpty<\/span><\/td>\n It tests whether the provided variant is empty or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsError<\/span><\/td>\n It tests whether the provided expression is an error or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsMissing<\/span><\/td>\n It tests whether an optional argument procedure is missing or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsNull<\/span><\/td>\n It tests whether the expression is null or not.<\/span><\/td>\n<\/tr>\n\nIsNumeric<\/span><\/td>\n It tests whether the expression is numeric or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsObject<\/span><\/td>\n It tests whether the variable represents an object variable.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n4. VBA Error Handling Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nCVErr<\/span><\/td>\n It produces an error data type for a provided error code.<\/span><\/td>\n<\/tr>\n\nError<\/span><\/td>\n It returns the error message corresponding to the provided error code.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n5. VBA Program Flow Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nChoose<\/span><\/td>\n It selects a value from the argument list.<\/span><\/td>\n<\/tr>\n\nllf<\/span><\/td>\n It returns one of the two values by evaluating an expression. It evaluates to true or false.\u00a0<\/span><\/td>\n<\/tr>\n\nSwitch<\/span><\/td>\n It returns the value associated with the first true expression by evaluating a list of boolean expressions.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n6. VBA Conversion Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nAsc<\/span><\/td>\n It returns an integer representing the code for a provided character.\u00a0<\/span><\/td>\n<\/tr>\n\nCBool<\/span><\/td>\n It converts an expression to a boolean data type.<\/span><\/td>\n<\/tr>\n\nCByte<\/span><\/td>\n It converts an expression to a byte data type.\u00a0\u00a0<\/span><\/td>\n<\/tr>\n\nCCur<\/span><\/td>\n It converts an expression to a currency data type.<\/span><\/td>\n<\/tr>\n\nCDate<\/span><\/td>\n It converts an expression to a date data type.<\/span><\/td>\n<\/tr>\n\nCDbl<\/span><\/td>\n It converts an expression to a double data type.<\/span><\/td>\n<\/tr>\n\nCDec<\/span><\/td>\n It converts an expression to a decimal data type.<\/span><\/td>\n<\/tr>\n\nChr<\/span><\/td>\n It returns the character corresponding to the provided character code.<\/span><\/td>\n<\/tr>\n\nClnt<\/span><\/td>\n It converts an expression to an integer data type.<\/span><\/td>\n<\/tr>\n\nCLng<\/span><\/td>\n It converts an expression to a long data type.<\/span><\/td>\n<\/tr>\n\nCSng<\/span><\/td>\n It converts an expression to a single data type.<\/span><\/td>\n<\/tr>\n\nCStr<\/span><\/td>\n It converts an expression to a string data type.<\/span><\/td>\n<\/tr>\n\nCVar<\/span><\/td>\n It converts an expression to a variant data type.\u00a0<\/span><\/td>\n<\/tr>\n\nFormatCurrency<\/span><\/td>\n It converts the expression to currency format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatDateTime<\/span><\/td>\n It converts the expression to date and time format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatNumber<\/span><\/td>\n It converts the expression to number format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatPercent<\/span><\/td>\n It converts the expression to percentage format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nHex<\/span><\/td>\n It converts the numeric value to hexadecimal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nOct<\/span><\/td>\n It converts the numeric value to octal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nStr<\/span><\/td>\n It converts a numeric value to a string.<\/span><\/td>\n<\/tr>\n\nVal<\/span><\/td>\n It converts a string to a numeric value.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n7. VBA Date and Time Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nDate<\/span><\/td>\n It returns the current date.<\/span><\/td>\n<\/tr>\n\nDateAdd<\/span><\/td>\n It adds an interval between the date or time.<\/span><\/td>\n<\/tr>\n\nDateDiff<\/span><\/td>\n It returns the number of intervals between the two dates or time.<\/span><\/td>\n<\/tr>\n\nDatePart<\/span><\/td>\n It returns a part of a date or time<\/span><\/td>\n<\/tr>\n\nDateSerial<\/span><\/td>\n It returns the date from the provided date details.<\/span><\/td>\n<\/tr>\n\n
Code Explanation:<\/h4>\n\n- \n
Private Function functionName() –<\/h5>\n<\/li>\n<\/ul>\n
Here the sub declares a function named \u201cfunctionName\u201d and starts the body of the function. To specify the scope of the function, the keyword \u201cprivate\u201d is used.<\/p>\n
\n- \n
ByVal argument_1 As Integer, ByVal argument_2 As Integer –<\/h5>\n<\/li>\n<\/ul>\n
This declares two parameters of integer data type named argument_1 and argument_2<\/p>\n
functionName=argument_1+argument_2<\/p>\n
This evaluates the two numbers and assigns the value to the functionName.<\/p>\n
End Sub –<\/h4>\n
This denotes the end of the function body.<\/p>\n
Function demonstrated with Example:<\/h3>\n
Functions are also the same like subroutines. The one main difference between the function and subroutine is that the function returns a value whenever it is called whereas the subroutine does not return a value when it is called.<\/p>\n
Now let’s see how to create a function which accepts two numbers and returns the product of the numbers.<\/p>\n
The below steps are the processes on how to call functions in vba.<\/p>\n
1: Design the user interface and set the properties as per the requirement for the user controls.<\/p>\n
2: Add the function in the program.<\/p>\n
3: Mention the client event code for the button which calls the subroutine.<\/p>\n
4: Application testing.<\/p>\n
Step by step process<\/h3>\n
Step – 1: Designing the user interface and setting the properties<\/p>\n
Design the user interface by setting the name properties to btnDisplayProduct and the caption to DataFlair_Function.<\/p>\n
Your GUI appears as follows:<\/p>\n
<\/a><\/p>\nStep 2: Adding the function.<\/p>\n
Open the code window by clicking on the view code option under the developer tab. Add the following function code in the VBE editor<\/p>\n
Code:<\/h4>\nPrivate Function DataFlair_Function(ByVal firstnum As Integer, ByVal secondnum As Integer)\r\n\r\nDataFlair_Function = firstnum * secondnum\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n\n- \n
Private Function DataFlair_Function –<\/h5>\n
This declares a private function \u201cDataFlair_Function\u201d that accepts two integer parameters.<\/li>\n
- \n
ByVal firstnum As Integer, ByVal secondnum As Integer –<\/h5>\n
This declares two parameter variables and those are \u2018firstnum\u2019 and \u2018secondnum\u2019.<\/li>\n
- \n
productNum = firstnum * secondnum –<\/h5>\n
This accepts the two integer values, multiplies it and assigns the product value to the productNum.<\/li>\n<\/ul>\n
Step 3: Calling the function from the command button click event.<\/p>\n
\n- Open the code window by clicking on the view code option under the developer tab.<\/li>\n
- This opens the code editor window.<\/li>\n<\/ul>\n
Add the following code in the code editor for the click event of the btnDisplayProduct command button.<\/p>\n
Code:<\/h4>\nPrivate Sub btnDisplayProduct ( )\r\n\r\nMsgBox DataFlair_Function (2,100)\r\n\r\nEnd Sub<\/pre>\nCode Explanation<\/h4>\n\n- \n
MsgBox DataFlair_Function(2,100) –<\/h5>\n<\/li>\n<\/ul>\n
This calls the function DataFlair_Function<\/strong> and passes in 2 and 100 as the parameters. The function returns the product of the two numbers i.e., 200.<\/p>\nThe first part of the code lines are the function code and the second part of the code lines are the click event code which calls the function from the command button.<\/p>\n
Press the save button to save the changes and close the editor window.<\/p>\n
Step 4: Testing the Application<\/p>\n
To test the application, follow the steps:<\/p>\n
Step 1: Go to the Developer tab.<\/p>\n
Note: Check whether the design mode is on or off. If it is on, then the application wouldn\u2019t work.<\/p>\n
Step 2: Click on the Design Mode to turn it off.<\/p>\n
Step 3: Click on the newly created button.<\/p>\n
The following output appears.<\/p>\n
<\/a><\/p>\nUser Defined Functions in Excel<\/h3>\n
Excel has many collections of functions. In many situations those functions are more than enough to complete the task. If not, then the user can create a own function and that is called a user defined or custom excel function. The user can access a user defined function just like any other excel function.<\/p>\n
Now, let\u2019s create a function called SUM_ODD that finds the sum of the odd numbers of a randomly selected range.<\/p>\n
Code:<\/h4>\nFunction SUM_ODD(rng As Range)\r\n\r\nDim cell As Range\r\n\r\nFor Each cell In rng\r\n\r\nIf cell.Value Mod 2 = 0 Then\r\n\r\nSUM_ODD= SUM_ODD + cell.Value\r\n\r\nNext cell\r\n\r\nEnd If\r\n\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, the code works on any cell on the worksheet and it sums up all the odd values in the cell range as we have not mentioned any specific cell range.<\/p>\n
Note: Any function written and accessed by the user, apart from the built in functions are known as user defined functions.<\/p>\n
Understanding the Scope of a User Defined Function in Excel<\/h3>\n
There are two scopes in a function and they are:<\/p>\n
\n- Public Scope<\/li>\n
- Private Scope<\/li>\n<\/ul>\n
1. Public scope<\/h4>\n
In public scope, the function is available for all the sheets in the workbook as well as all the procedures across all modules in the sheet. This is useful when the user wants to call a function from a subroutine.<\/p>\n
2. Private Scope<\/h4>\n
In private scope, the function is available only in the module in which it is provided. The user can\u2019t use it in other modules. The user won\u2019t see it in the list of functions in the spreadsheet. For example, if the function name is \u2018 DataFlair()\u2019, and the user type function in excel after the \u201c=\u201d sign, it will not show the function name. But still the user can use it if they enter the formula name.<\/p>\n
Calling an User Defined Function from Other Workbooks<\/h3>\n
If the user has a function in a workbook, then the user can call the function in other workbooks also. There are different ways to do it and they are:<\/p>\n
\n- Create an Add-In<\/li>\n
- Save the function in Personal Macro Workbook<\/li>\n
- Refer the function from another worksheet<\/li>\n<\/ul>\n
1. Create an Add-in<\/h4>\n
After creating and installing an add-in, the user will have the custom function in it available in all the workbooks.<\/p>\n
Imagine the user has created a custom function \u2013 \u2018GetNumeric\u2019 and the user wants it in all the workbooks. In order to do that the user has open a new workbook and have the function code in a module in the new workbook. Now, follow the steps to install the function in Excel.<\/p>\n
1: Go to the File tab and click on the \u201cSave As\u201d option.<\/p>\n
2: Change the file extension to .xlam.<\/p>\n
Note: The name the user assigns to the file will be the name of the add-in. The user can also notice that the path of the file where it gets saved automatically changes.<\/p>\n
3: Open a new Excel workbook<\/p>\n
4: Click on the Excel Add-ins option under the Developer tab.<\/p>\n
<\/a><\/p>\n5: In the Add-ins dialogue box, browse and locate the file that you saved with the function.<\/p>\n
6: After check marking on the function, click the ok button.<\/p>\n
Note: Finally, the add-in has been activated and the user can use the custom function in all the workbooks.<\/p>\n
2. Referencing the function from another workbook<\/h4>\n
If the user wants to refer to the function from another workbook, then that workbook has to be opened.<\/p>\n
3. Syntax to refer a function from another workbook:<\/h4>\n
= \u2018Workbook_Name\u2019!Function_Name(Cell_number)<\/strong><\/p>\nHere, in the workbook_name place the user has to place the workbook name of the function. Function_name refers to the name of the function and the cell number denotes the cell number.<\/p>\n
Excel Inbuilt Functions vs VBA User Defined Function<\/h3>\n
There are few advantages of using Excel in-built functions over custom functions created in VBA and they are:<\/p>\n
\n- VBA functions are quite slower than the inbuilt functions.<\/li>\n
- When the user creates a report using VBA functions, and if they send it to another user, then they need not have to worry about whether the macros are enabled or not.<\/li>\n
- Note: In some cases, the other users get scared by looking at the warning symbol in the yellow bar and that warning is simply a request to the user to enable the macros.<\/li>\n
- With inbuilt Excel functions, the user doesn’t need to worry about the file extensions. If the user has macros or user-defined functions in the workbook, then they need to save it in .xlsm.<\/li>\n
- It\u2019s better to use a user-defined function if the inbuilt formula is huge and complicated.<\/li>\n
- When the user has to get something specifically done then that can not be done by Excel inbuilt functions.<\/li>\n<\/ul>\n
Volatile Functions in VBA<\/h3>\n
The user defined functions in excel are not volatile by default. When any of the arguments in the function changes, they perform the task and recalculate again. A volatile function recalculates, whenever calculation occurs in any part of the worksheet. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nFunction DATAFLAIR(cell As Range)\r\nDATAFLAIR = cell.Value + cell.Offset(1, 0).Value\r\nApplication.Volatile\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
Here, we have created a function called DATAFLAIR and this function returns the sum of the selected cell and the cell below that particular selected cell.<\/p>\n
Note: The code is non volatile when you don’t enter the \u201cApplication. Volatile\u201d statement in the code. The non-volatile function is not recalculated when any other cell on the sheet changes and it recalculates only when the arguments in the function changes.<\/p>\n
Output:<\/h4>\n
<\/a><\/p>\nThe \u201cDATAFLAIR(A2)\u201d is called in the D8 cell and this is how we call the function generally in the sheet.<\/p>\n
Note: If the user first creates the non-volatile function then the user needs to enter the function again to make it volatile after adding the required statements in the code.<\/p>\n
By Ref and ByVal<\/h3>\n
The user can pass the arguments to a function procedure by reference or by value. The VBA passes arguments by reference as default. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\nDim num As Integer\r\nnum = 2\r\nMsgBox Double(num)\r\nMsgBox num<\/pre>\nCode Explanation:<\/h4>\n
Here, the code calls the function num. It’s the result of the second MsgBox that we are looking for and this function needs to be placed into a module. Now, open the module and add the below function code.<\/p>\n
Function Code:<\/h3>\nFunction Double(ByRef num As Integer) As Integer\r\nnum = num * num\r\nDouble = num\r\nEnd Function<\/pre>\nOutput:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
<\/a><\/p>\nByVal:<\/h4>\n
Now, let’s see an example of replacing ByRef with ByVal.<\/p>\n
Code:<\/h4>\nFunction calc(ByVal num As Integer) As Integer\r\nnum = num * num\r\ncalc = num\r\nEnd Function<\/pre>\nCode Explanation:<\/h4>\n
The user refers to the original value when the passing arguments are by reference. The value of num is generally changed in the function and as a result the second n=message box displays the changed value. If the user passes the arguments by value then they are passing a copy to the function and hence the original value does not change. As a result, the second message box displays the original value.<\/p>\n
Output:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nInputBox<\/span><\/td>\n It displays a dialog box where the user can enter the input<\/span><\/td>\n<\/tr>\n\nMsgBox<\/span><\/td>\n It displays a message box<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n2. VBA Text Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nFormat<\/span><\/td>\n It returns the result as a string and it applies a format to an expression.<\/span><\/td>\n<\/tr>\n\nInStr<\/span><\/td>\n It returns the substring position in a string.<\/span><\/td>\n<\/tr>\n\nInStrRev<\/span><\/td>\n It returns the substring position in a string searching from right to left.\u00a0<\/span><\/td>\n<\/tr>\n\nLeft<\/span><\/td>\n It returns the substring from the beginning of a provided string.<\/span><\/td>\n<\/tr>\n\nLen<\/span><\/td>\n It returns the length of the provided string.<\/span><\/td>\n<\/tr>\n\nLCase<\/span><\/td>\n It converts the string elements to lower text cases.<\/span><\/td>\n<\/tr>\n\nLTrim<\/span><\/td>\n It removes the extra spaces in the string.\u00a0<\/span><\/td>\n<\/tr>\n\nMid<\/span><\/td>\n It returns the substring from the middle of a string.<\/span><\/td>\n<\/tr>\n\nReplace<\/span><\/td>\n It replaces a substring with the new string.<\/span><\/td>\n<\/tr>\n\nRight<\/span><\/td>\n It returns a substring from the end of the string.<\/span><\/td>\n<\/tr>\n\nRTrim<\/span><\/td>\n It removes the extra spaces from the right side of the string.<\/span><\/td>\n<\/tr>\n\nSpace<\/span><\/td>\n It creates a string consisting of a specified number space.<\/span><\/td>\n<\/tr>\n\nStrComp<\/span><\/td>\n It compares the two strings and returns the result in an integer representing the comparison.\u00a0<\/span><\/td>\n<\/tr>\n\nStrConv<\/span><\/td>\n It converts a string to a particular format mentioned.\u00a0<\/span><\/td>\n<\/tr>\n\nString<\/span><\/td>\n It creates a sequence of characters in a string.\u00a0<\/span><\/td>\n<\/tr>\n\nStrReverse<\/span><\/td>\n It reverses the string.<\/span><\/td>\n<\/tr>\n\nTrim<\/span><\/td>\n It removes the spaces in the beginning and in the ending of the string.\u00a0<\/span><\/td>\n<\/tr>\n\nUCase<\/span><\/td>\n It converts the string to upper case characters.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n3. VBA Information Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nIsArray<\/span><\/td>\n It tests whether the provided variable is an array or not.<\/span><\/td>\n<\/tr>\n\nIsDate<\/span><\/td>\n It tests whether the provided expression is a date or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsEmpty<\/span><\/td>\n It tests whether the provided variant is empty or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsError<\/span><\/td>\n It tests whether the provided expression is an error or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsMissing<\/span><\/td>\n It tests whether an optional argument procedure is missing or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsNull<\/span><\/td>\n It tests whether the expression is null or not.<\/span><\/td>\n<\/tr>\n\nIsNumeric<\/span><\/td>\n It tests whether the expression is numeric or not.\u00a0<\/span><\/td>\n<\/tr>\n\nIsObject<\/span><\/td>\n It tests whether the variable represents an object variable.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n4. VBA Error Handling Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nCVErr<\/span><\/td>\n It produces an error data type for a provided error code.<\/span><\/td>\n<\/tr>\n\nError<\/span><\/td>\n It returns the error message corresponding to the provided error code.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n5. VBA Program Flow Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nChoose<\/span><\/td>\n It selects a value from the argument list.<\/span><\/td>\n<\/tr>\n\nllf<\/span><\/td>\n It returns one of the two values by evaluating an expression. It evaluates to true or false.\u00a0<\/span><\/td>\n<\/tr>\n\nSwitch<\/span><\/td>\n It returns the value associated with the first true expression by evaluating a list of boolean expressions.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n6. VBA Conversion Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nAsc<\/span><\/td>\n It returns an integer representing the code for a provided character.\u00a0<\/span><\/td>\n<\/tr>\n\nCBool<\/span><\/td>\n It converts an expression to a boolean data type.<\/span><\/td>\n<\/tr>\n\nCByte<\/span><\/td>\n It converts an expression to a byte data type.\u00a0\u00a0<\/span><\/td>\n<\/tr>\n\nCCur<\/span><\/td>\n It converts an expression to a currency data type.<\/span><\/td>\n<\/tr>\n\nCDate<\/span><\/td>\n It converts an expression to a date data type.<\/span><\/td>\n<\/tr>\n\nCDbl<\/span><\/td>\n It converts an expression to a double data type.<\/span><\/td>\n<\/tr>\n\nCDec<\/span><\/td>\n It converts an expression to a decimal data type.<\/span><\/td>\n<\/tr>\n\nChr<\/span><\/td>\n It returns the character corresponding to the provided character code.<\/span><\/td>\n<\/tr>\n\nClnt<\/span><\/td>\n It converts an expression to an integer data type.<\/span><\/td>\n<\/tr>\n\nCLng<\/span><\/td>\n It converts an expression to a long data type.<\/span><\/td>\n<\/tr>\n\nCSng<\/span><\/td>\n It converts an expression to a single data type.<\/span><\/td>\n<\/tr>\n\nCStr<\/span><\/td>\n It converts an expression to a string data type.<\/span><\/td>\n<\/tr>\n\nCVar<\/span><\/td>\n It converts an expression to a variant data type.\u00a0<\/span><\/td>\n<\/tr>\n\nFormatCurrency<\/span><\/td>\n It converts the expression to currency format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatDateTime<\/span><\/td>\n It converts the expression to date and time format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatNumber<\/span><\/td>\n It converts the expression to number format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nFormatPercent<\/span><\/td>\n It converts the expression to percentage format and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nHex<\/span><\/td>\n It converts the numeric value to hexadecimal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nOct<\/span><\/td>\n It converts the numeric value to octal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n\nStr<\/span><\/td>\n It converts a numeric value to a string.<\/span><\/td>\n<\/tr>\n\nVal<\/span><\/td>\n It converts a string to a numeric value.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n7. VBA Date and Time Functions<\/h4>\n
\n\n\nFunction<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n\nDate<\/span><\/td>\n It returns the current date.<\/span><\/td>\n<\/tr>\n\nDateAdd<\/span><\/td>\n It adds an interval between the date or time.<\/span><\/td>\n<\/tr>\n\nDateDiff<\/span><\/td>\n It returns the number of intervals between the two dates or time.<\/span><\/td>\n<\/tr>\n\nDatePart<\/span><\/td>\n It returns a part of a date or time<\/span><\/td>\n<\/tr>\n\nDateSerial<\/span><\/td>\n It returns the date from the provided date details.<\/span><\/td>\n<\/tr>\n\n
Private Function functionName() –<\/h5>\n<\/li>\n<\/ul>\n
Here the sub declares a function named \u201cfunctionName\u201d and starts the body of the function. To specify the scope of the function, the keyword \u201cprivate\u201d is used.<\/p>\n
- \n
- \n
ByVal argument_1 As Integer, ByVal argument_2 As Integer –<\/h5>\n<\/li>\n<\/ul>\n
This declares two parameters of integer data type named argument_1 and argument_2<\/p>\n
functionName=argument_1+argument_2<\/p>\n
This evaluates the two numbers and assigns the value to the functionName.<\/p>\n
End Sub –<\/h4>\n
This denotes the end of the function body.<\/p>\n
Function demonstrated with Example:<\/h3>\n
Functions are also the same like subroutines. The one main difference between the function and subroutine is that the function returns a value whenever it is called whereas the subroutine does not return a value when it is called.<\/p>\n
Now let’s see how to create a function which accepts two numbers and returns the product of the numbers.<\/p>\n
The below steps are the processes on how to call functions in vba.<\/p>\n
1: Design the user interface and set the properties as per the requirement for the user controls.<\/p>\n
2: Add the function in the program.<\/p>\n
3: Mention the client event code for the button which calls the subroutine.<\/p>\n
4: Application testing.<\/p>\n
Step by step process<\/h3>\n
Step – 1: Designing the user interface and setting the properties<\/p>\n
Design the user interface by setting the name properties to btnDisplayProduct and the caption to DataFlair_Function.<\/p>\n
Your GUI appears as follows:<\/p>\n
<\/a><\/p>\nStep 2: Adding the function.<\/p>\n
Open the code window by clicking on the view code option under the developer tab. Add the following function code in the VBE editor<\/p>\n
Code:<\/h4>\n
Private Function DataFlair_Function(ByVal firstnum As Integer, ByVal secondnum As Integer)\r\n\r\nDataFlair_Function = firstnum * secondnum\r\n\r\nEnd Function<\/pre>\n
Code Explanation:<\/h4>\n
- \n
- \n
Private Function DataFlair_Function –<\/h5>\n
This declares a private function \u201cDataFlair_Function\u201d that accepts two integer parameters.<\/li>\n
- \n
ByVal firstnum As Integer, ByVal secondnum As Integer –<\/h5>\n
This declares two parameter variables and those are \u2018firstnum\u2019 and \u2018secondnum\u2019.<\/li>\n
- \n
productNum = firstnum * secondnum –<\/h5>\n
This accepts the two integer values, multiplies it and assigns the product value to the productNum.<\/li>\n<\/ul>\n
Step 3: Calling the function from the command button click event.<\/p>\n
- \n
- Open the code window by clicking on the view code option under the developer tab.<\/li>\n
- This opens the code editor window.<\/li>\n<\/ul>\n
Add the following code in the code editor for the click event of the btnDisplayProduct command button.<\/p>\n
Code:<\/h4>\n
Private Sub btnDisplayProduct ( )\r\n\r\nMsgBox DataFlair_Function (2,100)\r\n\r\nEnd Sub<\/pre>\n
Code Explanation<\/h4>\n
- \n
- \n
MsgBox DataFlair_Function(2,100) –<\/h5>\n<\/li>\n<\/ul>\n
This calls the function DataFlair_Function<\/strong> and passes in 2 and 100 as the parameters. The function returns the product of the two numbers i.e., 200.<\/p>\n
The first part of the code lines are the function code and the second part of the code lines are the click event code which calls the function from the command button.<\/p>\n
Press the save button to save the changes and close the editor window.<\/p>\n
Step 4: Testing the Application<\/p>\n
To test the application, follow the steps:<\/p>\n
Step 1: Go to the Developer tab.<\/p>\n
Note: Check whether the design mode is on or off. If it is on, then the application wouldn\u2019t work.<\/p>\n
Step 2: Click on the Design Mode to turn it off.<\/p>\n
Step 3: Click on the newly created button.<\/p>\n
The following output appears.<\/p>\n
<\/a><\/p>\nUser Defined Functions in Excel<\/h3>\n
Excel has many collections of functions. In many situations those functions are more than enough to complete the task. If not, then the user can create a own function and that is called a user defined or custom excel function. The user can access a user defined function just like any other excel function.<\/p>\n
Now, let\u2019s create a function called SUM_ODD that finds the sum of the odd numbers of a randomly selected range.<\/p>\n
Code:<\/h4>\n
Function SUM_ODD(rng As Range)\r\n\r\nDim cell As Range\r\n\r\nFor Each cell In rng\r\n\r\nIf cell.Value Mod 2 = 0 Then\r\n\r\nSUM_ODD= SUM_ODD + cell.Value\r\n\r\nNext cell\r\n\r\nEnd If\r\n\r\nEnd Function<\/pre>\n
Code Explanation:<\/h4>\n
Here, the code works on any cell on the worksheet and it sums up all the odd values in the cell range as we have not mentioned any specific cell range.<\/p>\n
Note: Any function written and accessed by the user, apart from the built in functions are known as user defined functions.<\/p>\n
Understanding the Scope of a User Defined Function in Excel<\/h3>\n
There are two scopes in a function and they are:<\/p>\n
- \n
- Public Scope<\/li>\n
- Private Scope<\/li>\n<\/ul>\n
1. Public scope<\/h4>\n
In public scope, the function is available for all the sheets in the workbook as well as all the procedures across all modules in the sheet. This is useful when the user wants to call a function from a subroutine.<\/p>\n
2. Private Scope<\/h4>\n
In private scope, the function is available only in the module in which it is provided. The user can\u2019t use it in other modules. The user won\u2019t see it in the list of functions in the spreadsheet. For example, if the function name is \u2018 DataFlair()\u2019, and the user type function in excel after the \u201c=\u201d sign, it will not show the function name. But still the user can use it if they enter the formula name.<\/p>\n
Calling an User Defined Function from Other Workbooks<\/h3>\n
If the user has a function in a workbook, then the user can call the function in other workbooks also. There are different ways to do it and they are:<\/p>\n
- \n
- Create an Add-In<\/li>\n
- Save the function in Personal Macro Workbook<\/li>\n
- Refer the function from another worksheet<\/li>\n<\/ul>\n
1. Create an Add-in<\/h4>\n
After creating and installing an add-in, the user will have the custom function in it available in all the workbooks.<\/p>\n
Imagine the user has created a custom function \u2013 \u2018GetNumeric\u2019 and the user wants it in all the workbooks. In order to do that the user has open a new workbook and have the function code in a module in the new workbook. Now, follow the steps to install the function in Excel.<\/p>\n
1: Go to the File tab and click on the \u201cSave As\u201d option.<\/p>\n
2: Change the file extension to .xlam.<\/p>\n
Note: The name the user assigns to the file will be the name of the add-in. The user can also notice that the path of the file where it gets saved automatically changes.<\/p>\n
3: Open a new Excel workbook<\/p>\n
4: Click on the Excel Add-ins option under the Developer tab.<\/p>\n
<\/a><\/p>\n5: In the Add-ins dialogue box, browse and locate the file that you saved with the function.<\/p>\n
6: After check marking on the function, click the ok button.<\/p>\n
Note: Finally, the add-in has been activated and the user can use the custom function in all the workbooks.<\/p>\n
2. Referencing the function from another workbook<\/h4>\n
If the user wants to refer to the function from another workbook, then that workbook has to be opened.<\/p>\n
3. Syntax to refer a function from another workbook:<\/h4>\n
= \u2018Workbook_Name\u2019!Function_Name(Cell_number)<\/strong><\/p>\n
Here, in the workbook_name place the user has to place the workbook name of the function. Function_name refers to the name of the function and the cell number denotes the cell number.<\/p>\n
Excel Inbuilt Functions vs VBA User Defined Function<\/h3>\n
There are few advantages of using Excel in-built functions over custom functions created in VBA and they are:<\/p>\n
- \n
- VBA functions are quite slower than the inbuilt functions.<\/li>\n
- When the user creates a report using VBA functions, and if they send it to another user, then they need not have to worry about whether the macros are enabled or not.<\/li>\n
- Note: In some cases, the other users get scared by looking at the warning symbol in the yellow bar and that warning is simply a request to the user to enable the macros.<\/li>\n
- With inbuilt Excel functions, the user doesn’t need to worry about the file extensions. If the user has macros or user-defined functions in the workbook, then they need to save it in .xlsm.<\/li>\n
- It\u2019s better to use a user-defined function if the inbuilt formula is huge and complicated.<\/li>\n
- When the user has to get something specifically done then that can not be done by Excel inbuilt functions.<\/li>\n<\/ul>\n
Volatile Functions in VBA<\/h3>\n
The user defined functions in excel are not volatile by default. When any of the arguments in the function changes, they perform the task and recalculate again. A volatile function recalculates, whenever calculation occurs in any part of the worksheet. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\n
Function DATAFLAIR(cell As Range)\r\nDATAFLAIR = cell.Value + cell.Offset(1, 0).Value\r\nApplication.Volatile\r\nEnd Function<\/pre>\n
Code Explanation:<\/h4>\n
Here, we have created a function called DATAFLAIR and this function returns the sum of the selected cell and the cell below that particular selected cell.<\/p>\n
Note: The code is non volatile when you don’t enter the \u201cApplication. Volatile\u201d statement in the code. The non-volatile function is not recalculated when any other cell on the sheet changes and it recalculates only when the arguments in the function changes.<\/p>\n
Output:<\/h4>\n
<\/a><\/p>\nThe \u201cDATAFLAIR(A2)\u201d is called in the D8 cell and this is how we call the function generally in the sheet.<\/p>\n
Note: If the user first creates the non-volatile function then the user needs to enter the function again to make it volatile after adding the required statements in the code.<\/p>\n
By Ref and ByVal<\/h3>\n
The user can pass the arguments to a function procedure by reference or by value. The VBA passes arguments by reference as default. Let\u2019s look at a sample.<\/p>\n
Code:<\/h4>\n
Dim num As Integer\r\nnum = 2\r\nMsgBox Double(num)\r\nMsgBox num<\/pre>\n
Code Explanation:<\/h4>\n
Here, the code calls the function num. It’s the result of the second MsgBox that we are looking for and this function needs to be placed into a module. Now, open the module and add the below function code.<\/p>\n
Function Code:<\/h3>\n
Function Double(ByRef num As Integer) As Integer\r\nnum = num * num\r\nDouble = num\r\nEnd Function<\/pre>\n
Output:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
<\/a><\/p>\nByVal:<\/h4>\n
Now, let’s see an example of replacing ByRef with ByVal.<\/p>\n
Code:<\/h4>\n
Function calc(ByVal num As Integer) As Integer\r\nnum = num * num\r\ncalc = num\r\nEnd Function<\/pre>\n
Code Explanation:<\/h4>\n
The user refers to the original value when the passing arguments are by reference. The value of num is generally changed in the function and as a result the second n=message box displays the changed value. If the user passes the arguments by value then they are passing a copy to the function and hence the original value does not change. As a result, the second message box displays the original value.<\/p>\n
Output:<\/h4>\n
The following output appears when the user clicks on the command button.<\/p>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n InputBox<\/span><\/td>\n It displays a dialog box where the user can enter the input<\/span><\/td>\n<\/tr>\n \n MsgBox<\/span><\/td>\n It displays a message box<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 2. VBA Text Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n Format<\/span><\/td>\n It returns the result as a string and it applies a format to an expression.<\/span><\/td>\n<\/tr>\n \n InStr<\/span><\/td>\n It returns the substring position in a string.<\/span><\/td>\n<\/tr>\n \n InStrRev<\/span><\/td>\n It returns the substring position in a string searching from right to left.\u00a0<\/span><\/td>\n<\/tr>\n \n Left<\/span><\/td>\n It returns the substring from the beginning of a provided string.<\/span><\/td>\n<\/tr>\n \n Len<\/span><\/td>\n It returns the length of the provided string.<\/span><\/td>\n<\/tr>\n \n LCase<\/span><\/td>\n It converts the string elements to lower text cases.<\/span><\/td>\n<\/tr>\n \n LTrim<\/span><\/td>\n It removes the extra spaces in the string.\u00a0<\/span><\/td>\n<\/tr>\n \n Mid<\/span><\/td>\n It returns the substring from the middle of a string.<\/span><\/td>\n<\/tr>\n \n Replace<\/span><\/td>\n It replaces a substring with the new string.<\/span><\/td>\n<\/tr>\n \n Right<\/span><\/td>\n It returns a substring from the end of the string.<\/span><\/td>\n<\/tr>\n \n RTrim<\/span><\/td>\n It removes the extra spaces from the right side of the string.<\/span><\/td>\n<\/tr>\n \n Space<\/span><\/td>\n It creates a string consisting of a specified number space.<\/span><\/td>\n<\/tr>\n \n StrComp<\/span><\/td>\n It compares the two strings and returns the result in an integer representing the comparison.\u00a0<\/span><\/td>\n<\/tr>\n \n StrConv<\/span><\/td>\n It converts a string to a particular format mentioned.\u00a0<\/span><\/td>\n<\/tr>\n \n String<\/span><\/td>\n It creates a sequence of characters in a string.\u00a0<\/span><\/td>\n<\/tr>\n \n StrReverse<\/span><\/td>\n It reverses the string.<\/span><\/td>\n<\/tr>\n \n Trim<\/span><\/td>\n It removes the spaces in the beginning and in the ending of the string.\u00a0<\/span><\/td>\n<\/tr>\n \n UCase<\/span><\/td>\n It converts the string to upper case characters.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 3. VBA Information Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n IsArray<\/span><\/td>\n It tests whether the provided variable is an array or not.<\/span><\/td>\n<\/tr>\n \n IsDate<\/span><\/td>\n It tests whether the provided expression is a date or not.\u00a0<\/span><\/td>\n<\/tr>\n \n IsEmpty<\/span><\/td>\n It tests whether the provided variant is empty or not.\u00a0<\/span><\/td>\n<\/tr>\n \n IsError<\/span><\/td>\n It tests whether the provided expression is an error or not.\u00a0<\/span><\/td>\n<\/tr>\n \n IsMissing<\/span><\/td>\n It tests whether an optional argument procedure is missing or not.\u00a0<\/span><\/td>\n<\/tr>\n \n IsNull<\/span><\/td>\n It tests whether the expression is null or not.<\/span><\/td>\n<\/tr>\n \n IsNumeric<\/span><\/td>\n It tests whether the expression is numeric or not.\u00a0<\/span><\/td>\n<\/tr>\n \n IsObject<\/span><\/td>\n It tests whether the variable represents an object variable.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 4. VBA Error Handling Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n CVErr<\/span><\/td>\n It produces an error data type for a provided error code.<\/span><\/td>\n<\/tr>\n \n Error<\/span><\/td>\n It returns the error message corresponding to the provided error code.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 5. VBA Program Flow Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n Choose<\/span><\/td>\n It selects a value from the argument list.<\/span><\/td>\n<\/tr>\n \n llf<\/span><\/td>\n It returns one of the two values by evaluating an expression. It evaluates to true or false.\u00a0<\/span><\/td>\n<\/tr>\n \n Switch<\/span><\/td>\n It returns the value associated with the first true expression by evaluating a list of boolean expressions.\u00a0<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 6. VBA Conversion Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n Asc<\/span><\/td>\n It returns an integer representing the code for a provided character.\u00a0<\/span><\/td>\n<\/tr>\n \n CBool<\/span><\/td>\n It converts an expression to a boolean data type.<\/span><\/td>\n<\/tr>\n \n CByte<\/span><\/td>\n It converts an expression to a byte data type.\u00a0\u00a0<\/span><\/td>\n<\/tr>\n \n CCur<\/span><\/td>\n It converts an expression to a currency data type.<\/span><\/td>\n<\/tr>\n \n CDate<\/span><\/td>\n It converts an expression to a date data type.<\/span><\/td>\n<\/tr>\n \n CDbl<\/span><\/td>\n It converts an expression to a double data type.<\/span><\/td>\n<\/tr>\n \n CDec<\/span><\/td>\n It converts an expression to a decimal data type.<\/span><\/td>\n<\/tr>\n \n Chr<\/span><\/td>\n It returns the character corresponding to the provided character code.<\/span><\/td>\n<\/tr>\n \n Clnt<\/span><\/td>\n It converts an expression to an integer data type.<\/span><\/td>\n<\/tr>\n \n CLng<\/span><\/td>\n It converts an expression to a long data type.<\/span><\/td>\n<\/tr>\n \n CSng<\/span><\/td>\n It converts an expression to a single data type.<\/span><\/td>\n<\/tr>\n \n CStr<\/span><\/td>\n It converts an expression to a string data type.<\/span><\/td>\n<\/tr>\n \n CVar<\/span><\/td>\n It converts an expression to a variant data type.\u00a0<\/span><\/td>\n<\/tr>\n \n FormatCurrency<\/span><\/td>\n It converts the expression to currency format and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n FormatDateTime<\/span><\/td>\n It converts the expression to date and time format and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n FormatNumber<\/span><\/td>\n It converts the expression to number format and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n FormatPercent<\/span><\/td>\n It converts the expression to percentage format and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n Hex<\/span><\/td>\n It converts the numeric value to hexadecimal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n Oct<\/span><\/td>\n It converts the numeric value to octal value and returns the result as a string.<\/span><\/td>\n<\/tr>\n \n Str<\/span><\/td>\n It converts a numeric value to a string.<\/span><\/td>\n<\/tr>\n \n Val<\/span><\/td>\n It converts a string to a numeric value.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n 7. VBA Date and Time Functions<\/h4>\n
\n\n
\n Function<\/b><\/td>\n Description<\/b><\/td>\n<\/tr>\n \n Date<\/span><\/td>\n It returns the current date.<\/span><\/td>\n<\/tr>\n \n DateAdd<\/span><\/td>\n It adds an interval between the date or time.<\/span><\/td>\n<\/tr>\n \n DateDiff<\/span><\/td>\n It returns the number of intervals between the two dates or time.<\/span><\/td>\n<\/tr>\n \n DatePart<\/span><\/td>\n It returns a part of a date or time<\/span><\/td>\n<\/tr>\n \n DateSerial<\/span><\/td>\n It returns the date from the provided date details.<\/span><\/td>\n<\/tr>\n \n
- \n
- \n