Eomonth in Excel

Question

How can I use this function in a user-defined function? I just cannot use a reference to a cell.

Function SONxMonth()
   SONxMonth = EOMONTH(A1, 0) + 1
End Func

Answer 1

You need to use the WorksheetFunction which are used as a container for Microsoft Excel worksheet functions that can be called from Visual Basic.

Function SONxMonth() SONxMonth = WorksheetFunction.EoMonth(Range("A1"), 0) End Func

Answer 2

If you always want the function to do the task on the cell to the left you can use a named formula and skip the VBA code. That is, you are assuming the argument is in the cell to the left - a common situation in excelcell.

Set the cursor in cell B1 and then define the name

SONxMonth as =EOMONTH(A1, 0) + 1

It will now always reference the argument from the left hand cell.

You have created in RC notation this:

=EOMONTH(RC[-1], 0) + 1