Updated May 16, 2023
VBA Function in Excel (Table of Contents)
VBA Function in Excel
We also have functions in VBA that are completely inbuilt in it. Almost all Excel functions can also be used in VBA with nearly the same syntax. And if there is any change in the syntax, we will be able to see that also when we are about to use the VBA function. To declare a function in VBA, open a Private-End procedure and select the data type per functional need.
Visual Basic
VBA stands for Visual Basic for an application. VBA is normally a programming language of excel which is embedded in an individual Microsoft application, i.e. Excel or Access, where we can do the automation of several tasks, which is also called as “MACRO” in excel. The Visual Basic application comes with several command buttons and other functions, making automation easier for the user.
VBA User Defined Function (UDF)
A User-defined function is just a function we can create ourselves in VBA for automation purposes.
This user-defined function is a code module attached to the worksheet where the user will type the coding.
How to Use VBA Function in Excel?
By default, we can find the VBA function in Excel under the “DEVELOPER” tab; Excel does not have this menu. So to display it, we need to manually add this “DEVELOPER” menu by following the below steps.
- Go to the File menu. Click on the Options menu, which has highlighted in Yellow Color.
- Once we click on the option, we will get the below dialogue box.
- Select Customize Ribbon option, and we will get the below dialogue box as follows.
- We can see add or remove the ribbon manually in the customize ribbon option. Here we can see that the “Right-hand side” of the customize ribbon “DEVELOPER” Tab is not checked. Checkmark that “DEVELOPER” Tab and click OK
- Once you checkmark the “DEVELOPER” Tab, it will get added to the Excel menu, which is displayed as follows.
Now we can see that the “Developer” Tab is added, and it has the features like VISUAL BASIC, MACROS, etc.
Adding VBA in Excel
The VBA workbook can be easily added in Excel by following the steps below.
- Open the Excel workbook. Use the shortcut key ALT+F11 to open Visual Basic Editor, also called VBE in excel, shown in the screenshot below.
- Right-click on your workbook name in the “Project VBAProject” pane (at the top left corner of the editor window) and select Insert -> Module from the context menu, as shown below.
- Now we can see that a new module has been added as MODULE 1 on the left-hand side of the panel.
- On the right-hand side where we can use the UDF function using VBA.
The function of Visual Basic
In Visual Basic, we will use the various function. A function consists of Visual Basic statements enclosed between a start and an end function. This function performs a task and returns controls to the calling code. If it returns the control, it will also return a value to the specific code.
VBA Function
Visual Basic contains many built-in functions like Message Function, a String and Text Function, an Information Function, a Conversion function, a Date and Time, Math and Trig Functions, and a Financial function.
We will see the mainly used VBA function, which is listed below.
- Input Box- This displays an input dialogue box
- Msg Box- This displays a Message box, i.e., an alert message box
- Format- This applies format for the specific string.
- Len- Which returns the length of the string
- Is Array- This will check for the supplied value in an array.
- Is Date- This will check for the supplied expression in date format.
- Replace – This will replace the specific string with another string.
- Is Empty – This will check for the specific variant is empty or not
- Is Null – This will check for the supplied value is null or not
- Date – Returns the current date.
- Month – Returns the month number.
- Now – Returns the current date and time.
- Year – Returns the current year.
Examples of VBA Function in Excel
VBA Function is very simple to use. Let us now see how to use the VBA function in Excel with the help of some examples.
Example #1
Addition of Two Numbers
In this example, we will see how to add two numbers using the VBA function by using a simple programming language step by step as follows.
- Open the Excel workbook. Use the shortcut key ALT+F11 to open the VBE editor. We get the VBE editor as follows.
- Go to Insert Menu and select module.
Once we click on the module, a new module will be added to a worksheet Where we will write the VBA function coding on the right-hand side of the General page.
Now to get the result of adding two numbers, we have to use the below coding as follows
Function addtwo(arg1, arg2)
addtwo = arg1 + arg2
End Function
Here in the above coding, we have named the function “addtwo,” and the coding will be displayed as:
Now in the Excel sheet, we can use the “addtwo” function to add the new number as:
The result is:
Example #2
Date Function
In VBA, the date function is a built-in function, DATE(), which returns the date and time; we will now use the date function to get the date in the particular sheet. In VBA, we can use UDF to get the result.
Insert a new Module, as we have discussed above in Example 1.
After clicking on the module, it adds a new one to the worksheet. We then write the VBA function code on the General page’s right-hand side.
Sub todayDate()
Range("A1").Value = Date
End Sub
Here in the above coding, we have named the function as “todayDate,” and the coding will be displayed as:
Hit the RUN button or Press F5 to execute the code
This code will return the current date in cell A1. Once executed, the result will appear as follows:
Example #3
VBA Message Box
Visual Basic normally has two message box function
- The Msg Box method.
- The Msg Box Function.
The MsgBox Method
The MSGBOX method is one of the VBA languages that display a msgbox for the given string; it usually has the ok button so the user can see the MSGBOX and continue with the specified task.
The MsgBox Function
The MSGBOX Function returns value normally; this MSGBOX function will be a question type like Yes or No or Abort or Continue.
We will see both the MSG box with the below examples as follows.
Msg Box method Example:
Insert a new Module, as we have discussed above in Example 1.
After clicking on the module, a new one appears in the worksheet. We then write the VBA function code on the right-hand side of the General page.
Sub Firstmsg()
MsgBox "Hello"
End Sub
In the below screenshot, we have written coding to display the MSG BOX as Hello.
Hit the RUN button or Press F5 to execute the code.
We will get the below output.
We can see the HELLO message which comes from the MSG box.
Msg Box Function Example:
In this example, we will see how to use the MSG BOX function using vbYesNoCancel
Insert a new Module, as we have discussed above in Example 1.
Once you click on the module, a new module appears in the worksheet. You can then write the VBA function code on the General page’s right-hand side.
Sub Firstmsg()
MsgBox "Welcome User or Guest?", vbYesNoCancel, "Login?"
End Sub
In the below screenshot, we have written coding to display the MSG BOX. Here in the MSG BOX function, we have given YES or NO option to check whether the end user is a User or Guest.
Hit the RUN button or Press F5 to execute the code
After execution, we will get the below MSGBOX as follow
Things to Remember About VBA Function
- In VBA, we have many built-in functions where it can be used in the automation process.
- While applying multiple MACROS functions, ensure we have assigned a specific key to run it.
- Name each module in VBA to avoid confusion in coding.
Recommended Articles
This has been a guide to Excel VBA Function. Here we discuss how to use VBA Function in Excel, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –
