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.
2.When you want to use custom functions in Excel, you have to save the spreadsheet as an .xlsm file.
If you do not, you will get this warning.
3.To create a custom Excel function, go to the Developers menu, and click Visual Basic.
4.The screen looks like this.
5.Right-click the Microsoft Excel Object, and select Insert Module.
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.
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.
8.Now you can use your function in the spreadsheet. Just reference it like you would any other function.
9.Here is the resulting calculation.
