Custom formula/function using VBA

Excel has lots of built-in functions which covers the requirements of almost all the standard calculations. However, these built-in functions are not sufficient. Many a times we come to a situation where we want to do some calculations which are not offered by excel built-in function.


Here I will show you how to create a custom function using VBA.



In the example below I have created a function in VBA to derive week name. Start dates and end dates for each week is defined in the separate worksheet (“Week master” in this example).


Following are the steps to create a custom function….
1.       Open the file in which you want to insert custom function
2.       Save the file as Excel Macro enabled file. i.e. .xlsm
3.       Insert new sheet (week master) and insert start dates, end dates and week names. Refer to the screenshot below…






4.       Go to VB Editor of the file. Developer à Visual Basics (follow http://excelhelpbyriteshpatel.blogspot.com/2011/09/enable-developer-tab-in-excel.html to enable Develop ribbon if you can’t find it) or press Alt + F11
5.       Insert new module in the file
6.       Copy below mentioned code in newly added module

'---------------------------------------------------------------------------------

Public Function GetWeekName(dtDate As Date) As String

Dim nRow As Integer

Dim strWeek As String

strWeek = "NA"

With Worksheets("Week master")

    nRow = 2

    While .Cells(nRow, 1).Value <> ""

       

        If dtDate >= CDate(.Cells(nRow, 1).Value) And _

        dtDate <= CDate(.Cells(nRow, 2).Value) Then

            strWeek = .Cells(nRow, 3).Value

        End If

        nRow = nRow + 1

    Wend

End With

GetWeekName = strWeek
End Function
'---------------------------------------------------------------------------------

7.       That’s it, custom function is ready. Now use it in any formula to derive week name. To use this function, type “=getweekname(Cell address having date)” in the cell where you want the weekname.
For e.g. if Cell A1 contains "2-Sept-11" then =getweekname(A1) will return "Current Week" as result.

Above, I have explained how to create custom function in Excel VBA using simple example. You can modify this script or create new script as per your requirements and use it in your file.

Please note that, custom function will work only in the file in which the VBA code is written.