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
Asked
Active
Viewed 1,425 times
0
Peter Mortensen
- 30,738
- 21
- 105
- 131
user3876120
- 5
- 3
2 Answers
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
cherry_bueno
- 208
- 3
- 11
-
My A1 got value of date(2015,4,1), the function return a value of 32, when format changed to date, the value shown being 1/2/1900. – user3876120 Jun 25 '15 at 16:43
-
Seems it take A1 as zero value. – user3876120 Jun 25 '15 at 16:51
-
Also can I use relative notation in the function like RC[-1] ? – user3876120 Jun 25 '15 at 16:54
-
WorkSheetFunction.EOMONTH(Range("A1"), 0) + 1 do the trick, but if i want not to refer to A1 but a cell left to the active cell, what should be placed instead of Range("A1") ? – user3876120 Jun 25 '15 at 17:53
0
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
Peter Mortensen
- 30,738
- 21
- 105
- 131
user2092957
- 311
- 3
- 5
-
Is the first line containing code all literal code? Why `as =`? Is there an implied or default data type? – Peter Mortensen Dec 09 '22 at 21:31
-
OK, the OP has left building: *"Last seen more than 4 years ago"*. Perhaps somebody else can chime in? – Peter Mortensen Dec 09 '22 at 21:33