Variable equal to formula with a declared variable-CodePudding

Sorry if the title is confusing, not sure how to phrase this. Basically I want to write a formula which takes a date from a cell specified by the user (myDate) and the address of a starting cell (FirstCell) and based on that:

Sets a range (myArea) which starts from the specified cell and extends for a number of cells equal to the number of days in the month and year of the date we selected (numDays).
Counts cells in that range that do not have a blank interior (there is no conditional formatting in the sheet) and returns that number.

For example let's say I have the date 1/4/2022 and April in 2022 has 5 days but my table has 6 columns. I want to define a range based on the length of this specific month so that the number of cells which do not have a blank interior is equal to 1 and not 2. And I want this to be reproducible for different months.

Image with an example

The 2nd point is done and works with a simple user-specified range, the 1st point is the one giving me trouble because I don't want it to return anything in the sheet. The specific issue is setting numDays but there may be other errors I didn't catch - basically I tried to transplant the excel function solution to counting this into VBA but I'm pretty sure I'm getting the syntax wrong and/or this is not doable. Couldn't find anything that would answer my question on here, when I try to use the function it returns #VALUE! in the spreadsheet.

Function SPECIALDAYS (FirstCell as Range, myDate as Date)
   Dim myCell as Range
   Dim myArea as Range
   Dim numDays as Integer

   numDays = Application.Evaluate("Day(Eomonth(" & myDate & ",0))")
   Set myArea = Range(FirstCell, FirstCell.Offset(0, numDays-1))
   
   For Each myCell In myArea
      If myCell.Interior.ColorIndex <> -4142 Then
         SPECIALDAYS=SPECIALDAYS 1
      End If
   Next myCell
End Function

CodePudding user response：

Instead of using Evaluate and formula use a pure VBA solution with WorksheetFunctions:

numDays = Day(Application.WorksheetFunction.EoMonth(Date, 0))

See WorksheetFunction.EoMonth method.