Lesson#173: Steps to make a Custom function in VBA

You can create a Custom function in VBA in Microsoft Excel by following these steps:

1. Open the VBA editor: In Excel, press Alt + F11 to open the VBA editor.
2. Create a new module: In the VBA editor, go to the Insert menu and select Module. This will create a new module where you can write your VBA code.
3. Define the function: In the new module, type the word “Function” followed by the name you want to give to your function. Then, type “()” and press Enter. This will create a skeleton for your function.

For example, to create a function called “MyFunction,” you would type the following:

Function MyFunction()

End Function

4. Add your code: Between the “Function” and “End Function” lines, type the VBA code that you want to execute when the function is called. You can use any VBA statements, functions, or operators to build your code.

For example, to create a function that adds two numbers together, you could use the following code:

Function MyFunction(x As Double, y As Double) As Double
MyFunction = x + y
End Function

5. Test the function: To test your function, go back to the main Excel window and type the function name followed by the arguments in a cell. For example, to test the “MyFunction” function, you could type “=MyFunction(5, 10)” in a cell. This will call the function and return the result, which in this case would be 15.

By following these steps, you can create a custom function in VBA in Excel. You can then use this function like any other Excel formula by calling it in a cell or using it in other VBA code.