How to Create Function in Excel VBA - Step by Step Guide

by Imran Khan on Friday, September 06, 2019 in Excel VBA Macro

If you have used excel, you also probably used some excel formula like sum, average, count etc.

But do you know that these formula are pre-loaded functions in excel?

Yes, these all are functions and you can also create your own functions in excel using vba coding.

Creating function for you will be very easy because we will guide you step by step. After completing this post, you will be able to create your own functions according to your need.

Let’s start….

We will create a function to add two numbers.

Step 1: Open New Excel File and Press key Alt+F11. It will open Microsoft Visual Basic for Applications Window.

Step 2: Go to Insert >> Module menu. You will see new window in which you have to write function.

Step 3: Copy below code and paste in the code window:

‘Here is function to add two numbers!

Function addtwo(x, y)

addtwo = x + y

End Function

Step 4: Go to File menu and Click on last option "Close and Return to Microsoft Excel" or press Ctrl+Q.

That’s all. You function is ready. Now question is, how to use this function. So go to next step.

Step 5: Now in Excel sheet, write function like formula in cell A1.

= addtwo(3,5)

Step 6: Press Enter, and you will see the result as 8.

Congratulations!!!

You have created add-in function. So, below are some points you have to lookup.

1. You have to tell the function, what you want to input. Like “(x,y)” following the function name.

2. You tell the function with a line that (“addtwo”) is equal to calculation (“x+y”).

3. VBA automatically understand the script coding, comment and function. Like after adding the script line, when you will press enter it will automatically color-coded the text and completed the syntax.

For example: the line “End Function” will automatically insert when you write write Function and function name in the first line. Because VBA understand its script and syntax structure.

Step 7: After adding the code, save the file. Go to File menu and click on Save or press Ctrl+S.