How to Write a Custom Function in Microsoft Excel

Posted February 9, 2015 by Walker Rowe in Microsoft Excel

how to create custom excel function

Microsoft products have always included Visual Basic. You can use it in Microsoft Word, Access, and Excel. One reason this is true is that Bill Gates and some of his friends wrote Visual Basic while Gates was still at Harvard.  Thus, he felt proud of his accomplishment and wanted to keep it. Gates wrote the floating point arithmetic part of the language. You can use it to create custom functions in Excel.

Visual Basic is how many of us (who are probably older than many of you) first learned how to program.  I learned how to program using a Radio Shack pocket computer that had a 4K memory. That is 4096 bytes or 29 Tweets.  At my first job, my boss wrote a very long Visual Basic program to calculate interest on a loan.  It was funny because he spent weeks on it because he got stuck with a rounding error where, for example, 4/2 = 1.99999999.  Needless to say, you cannot print 1.999999 on a mortgage loan. That rounding error might have been Bill Gates’s fault.

Visual Basic (VB) is probably the world easiest program language. You can stop a running program, make a change, and then let it keep running.   It even helps you with spelling as you write it.

How to Write a Custom Function in Excel

1.To use Visual Basic in Excel, you have to enable the Developer menu.  Click that option under General Options>Customize Ribbon>Developer.

how to create excel custom function

2.When you want to use custom functions in Excel, you have to save the spreadsheet as an .xlsm file.

 

how to create excel custom function

 

If you do not, you will get this warning.

how to create excel custom function

 

3.To create a custom Excel function, go to the Developers menu, and click Visual Basic.

 

how to create excel custom function

 

4.The screen looks like this.

 

how to create excel custom function

 

5.Right-click the Microsoft Excel Object, and select Insert Module.

 

how to create excel custom function

 

6.Then you have an empty screen to write your function. Look elsewhere for a tutorial on the language, but the basic format is as follows:

Function myFunction (arguments) return type

myFunction = some_calculation

End Function

There is no return statement as with other languages.  There is no compile step either.

how to create excel custom function

7.Type your logic in here, and then save the spreadsheet as an .xlsm file.  In this example, I have called my function Commission.  I used number type double as that allows decimal points.  You can leave off these declarations as VB, the world’s easiest programming language, lets you do that.

 

how to create excel custom function

 

8.Now you can use your function in the spreadsheet. Just reference it like you would any other function.

 

how to create excel custom function

 

9.Here is the resulting calculation.

how to create excel custom function

 

About Walker Rowe

Walker Rowe writes a blog about living in rural Chile called 'The Avocado Republic.'
View more articles by Walker Rowe

The Conversation

Follow the reactions below and share your own thoughts.