How to create Excel function/formula?
This is the reason why the power of Excel when combined with VBA is almost limitless. When we can’t found any Excel function that suite our need, we can built it easily using Excel VBA.
If Excel macro or SUB procedure is easily created using Record Macro button, and usually build to simplify a complex process to became one simple push button or call, then an Excel function build to return a value from any kind of calculation and can be use and invoke as a formula.
For example, we can create a simple Excel function to get the last date of the month with date as parameter.
Public Function LASTDATEOFMONTH(ByVal TheDate As Variant) As Date
Dim nLastDay As Integer
nLastDay = DatePart("d", DateAdd("d", -1, DateAdd("m", 1, DateAdd("d", _
-DatePart("d", TheDate) + 1, TheDate))))
LASTDATEOFMONTH = DateSerial(Year(TheDate), Month(TheDate), nLastDay)
End Function
See the following result.
The result of the formula shows us that the last date of this month is June 30, 2008.
Using Excel function, we can create any kind of specific formula that’s not available in Excel and need to be used for several time on our Excel project.
Related Entries
External Resources
- Microsoft Excel 2003/2007 Video Tutorials
Step-by-step video guide to mastering Charts, PivotTable, Data Analysis and Macro programming in Microsoft Excel in 5 hours.
- 101 Secrets of Microsoft Excel
Discover 101 of Excels little-known secrets that have been hiding right under your nose.
Tagged with: Excel Formula
Robert
Hi there,
Just a question on VBA functions, say if I created a function for one project, I can’t use the same function on the other project. (unless I copy and paste the code to the module under that project. I’ve noticed that there is a PERSONAL.xls project always in VBA no matter what excel project I’m on. but I tried to copy the function in there it still doesn’t work.
Could you please help?
thank you very much!
Robert
admin
hi robert, please make sure that you use public keyword in the module, like in the example above i use “public function”.
if we state “public” in front of function or sub, that this method can be called from anywhere in the same project.
hope this will help you.
Duncan
Hi, I wonder if you can help me please, I am having a problem with a user form in which I want to calculate a ‘Private Drawing’ perecentage deduction from a Full purchase amount to give a Taxable amount. The user would choose one of a number of perecentages from a list having entered the full price, then click a cmd button for a sub that should take price and deduct (the price times say .1 (10%)), instead it just zeroes the full price, I want the user to see this figure before clicking OK and sending all the other purchase details into the sheet. Sorry if this is long-winded. Thanks Duncan