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.