WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Learn VBA » User Defined Function in VBA

User Defined Function in VBA

Excel VBA User Defined Function (UDF)

Microsoft serves us with many built-in functions to speed up the work in excel. However using VBA coding we can create our own functions and those functions are technically called “User-Defined Functions” (UDF). They are also called as “custom functions” in excel VBA.

Any formula which can be accessed from the worksheet with a piece of code is called as UDF. In simple terms, any formula which is not built-in but available in Excel is called User Defined Functions.

VBA Function Return

How to Create User-Defined Functions?

Even though UDF is part of our module, they are not part of our regular Subroutine in VBA. This is called a Function Procedure in VBA. Like how we start the macro coding with the word SUB similarly, we need to start this by using the word “Function.” Sub procedure has start and End, similarly Function Procedure to has Start and End.

You can download this VBA User Defined Function Excel Template here – VBA User Defined Function Excel Template

Example #1 – Create a Simple UDF Sum Function

We will create our own SUM function in excel by adding up two numbers.

  • To start the coding, start the word “Function” in one of the modules.

VBA Function Return Example 1

  • Like how we name the macro similarly, we need to give a name to our Function as well. This name is used as a formula name.

VBA Function Return Example 1-1

Unlike subroutine, we cannot simply hit enter to create a procedure, but we need to mention arguments here.

For example, look at the below syntax of the worksheet function SUM.

SUM Formula.

Number 1, Number 2 are arguments of the function SUM.

  • Similarly, we need to mention our arguments.

User Defined Function in Excel VBA Example 1-2

Here I have declared the arguments as “x as Integer” and “y as Integer.” Since we add numerical value, we need to assign the data type as a numerical data type only.

After declaring the arguments, I have assigned the return type of the Integer too because the result given by the Function “OurSum” is also a numerical value.

  • Now inside the function, we need to mention the formula we are going to use. Here we need to use the function to start with.

User Defined Function in Excel VBA Example 1-3

We mentioned the formula name “OurSum” should add x and y.

Popular Course in this category
Sale
VBA Training (3 Courses, 12+ Projects)
4.6 (247 ratings)
3 Courses | 12 Hands-on Projects | 43+ Hours | Full Lifetime Access | Certificate of Completion
View Course
  • Ok, save the code and go to the worksheet.

VBA Function Return Example 1-4

  • I have entered a few numbers here. Open equal sign and start typing OurSum. You can see the formula name appearing here.

VBA Function Return Example 1-5

  • Like how we select cells similarly select two cells separately.

User Defined Function in Excel VBA Example 1-6

  • After selecting two cells, hit the enter key to have results.

VBA Function Return Example 1-7

So it works exactly the same as our regular SUM function.

  • Ok, now I will change the numbers.

User Defined Function in Excel VBA Example 1-8

  • Now again, I will apply the function we have created.

VBA Function Return Example 1-9

Oh!!! We got error values except for the first cell.

You must be thinking about why we got an error.

If you observe the cell A2 and B2, we have values 48170 and 21732. Since we have assigned the data type as Integer, it cannot hold any number more than 32767. This is the reason we got the excel error as #NUM!.

Now I will add numbers that are less than 32767.

VBA Function Return Example 1-10

In the above image, all the numbers are integer numbers, i.e., less than 32767.

In the first cell, we got the results. But in the second cell, i.e., C2 cell, we got the error value as #VALUE!.

Even though both the numbers are less than 32767, we still got the error.

This is because we have declared the end result also as Integer.

User Defined Function in Excel VBA Example 1-11

So when we add 16000 and 17229, we will get a value like 33229, which is more than the integer data type limit of 32767. So the end result is an error value.

Example #2 – Create a Function to Test Logical Values

We will create one more function to test the logical values. We need a function that returns “Good” if the number is >=60, and we need the result as “Bad” if the number is <=50.

Step 1: Start the Function Procedure and give a name to the procedure.

User Defined Function in Excel VBA Step 1

Step 2: Give the argument as TestScore as Integer.

VBA Function Step 2

Step 3: Our end result should be either “Good” or “Bad,” so the result should be in String.

VBA Function Step 3

Step 4: First, we need to test whether the score is >=60 or not. Use IF condition to test.

VBA Function Step 4

Step 5: If the test score is >=60, we need the function to return the result as “Good.” So TestResult = “Good.”

If Condition

Step 6: If the test score is less than 60, then the function result should be “Bad.”

Else Condition

Ok, we are done.

  • Save the code and go to the worksheet.

Example 2 (Excel Data)

I have entered a few numbers here as a test score.

  • Apply the function we have created to arrive results.

Check TestResult

  • Select the cell and hit enter.

Logical Test Result

This is what UDF is all about.

Recommended Articles

This has been a guide to User Defined Function in VBA. Here we learn how to create a user-defined function (UDF) in VBA along with examples and a downloadable excel template. Below are some useful excel articles related to VBA –

  • VBA GetObject
  • VBA INSTRREV
  • Variable Declaration in VBA
  • String Functions in VBA
7 Shares
Share
Tweet
Share
VBA Training (3 Courses, 12+ Projects)
  • 3 Courses
  • 12 Hands-on Projects
  • 43+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

WallStreetMojo

Free Excel Course

Excel functions, Formula, Charts, Formatting creating excel dashboard & others

* Please provide your correct email id. Login details for this Free course will be emailed to you

Login

Forgot Password?

WallStreetMojo

Download VBA User Defined Function Excel Template

New Year Offer - VBA Training Course (6 courses, 35+ hours video) View More