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.

Last Date Of Month Excel Formula

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

  • Find the Last Day of any Month

    Simple Microsoft Excel formula to find the last day of any given month. ' =DATE(YEAR(A1),MONTH(A1)+1,0) ' ...

  • Handy VBA To Calculate Person Age

    This handy VBA procedure is just to show you how we can calculate and display someone Age. To do this, of course we need to know their date of birth first, then simply using datediff and mod vba functions we can calculate how old is he/she.Sub PersonAge() Dim iYears As Integer, iMonths...

  • Excel VBA Add Set of Worksheets Automatically

    There is a time when we must create a set of Excel Worksheets templates on a regular basis in our work. For example, a set of Excel Worksheet for each month of the year or may be based on the type of work we should done in certain time base. Here a simple example of how...

  • Sum Unique/Distinct Values in Excel

    Usually in the sequential database system, we can SUM only unique values in table column by adding all the values from SELECT DISTINCT query result only. Based on the same principle, we can also create a simple VBA function in Microsoft Excel by adding the values only available in a collection of unique values we have...

  • VBA Delete Excel Rows Based on Certain Date

    Let say I have a bunch of formatted data in my Excel sheet, and in Column A I have a dates. Then I wanna filter all the data base on certain date, for example 1 Jan 2009, and delete all others data before that date. The algorithm is like this: I’ll create a loop from the...

Valuable Resources

If you found this page useful, please consider bookmark it using social media or add a link to this page.

  • Print
  • Digg
  • Sphinn
  • del.icio.us
  • Facebook
  • Mixx
  • Google Bookmarks
  • connotea
  • Diigo
  • email
  • Fark
  • Identi.ca
  • Live
  • MisterWong
  • MySpace
  • Netvibes
  • NewsVine
  • PDF
  • Ping.fm
  • Propeller
  • Reddit
  • Simpy
  • StumbleUpon
  • Technorati
  • Twitter
  • Wikio
  • Yahoo! Bookmarks

Incoming search engine terms: dateadd excel, how to create excel formula, HOW TO GENERATE THE DATE USING EXCEL, how to derive dateadd in excel column, how to create a formula in excel, how to create a date column with formulas, how to call a VBA macro in an excel formula, how do i create a formula showing a age using the first date in the year only, how create a formula and use it in macro, excel visual basic call a public function, how to give a cell a specific formula in excel 2007 macros, how to use Auto_Run in Excel 2003, ms excel dateadd vba, vba to add SUM() formula to cell, vba public macro

»