日付の操作は毎回調べるので、ここに記します。
---------------------------------------
'指定日の月初日を取得する
Public Function fnc_xlGetDateFirstofMonth(ByVal d As Date)
Dim dRes As Date
dRes = DateSerial(Year(d), Month(d), 1) 'DateSerialです。
fnc_xlGetDateFirstofMonth = dRes
End Function
'指定日の月末日を取得する
Public Function fnc_xlGetDateLastofMonth(ByVal d As Date)
Dim dRes As Date
dRes = DateSerial(Year(d), Month(d) + 1, 1) - 1
fnc_xlGetDateLastofMonth = dRes
End Function
--------------------------------------
'実行
Sub testDate()
MsgBox "月初日は " & _
fnc_xlGetDateFirstofMonth(Now())
MsgBox "月末日は " & _
fnc_xlGetDateLastofMonth(Now())
End Sub
-----------------------------------------
Excel数式では
=TEXT(DATE(YEAR(NOW()),MONTH(NOW()),"1"),"yyyy/mm/dd")
2019/12/01
=TEXT(DATE(YEAR(NOW()),MONTH(NOW())+1,"1")-1,"yyyy/mm/dd")
2019/12/31