CODEWITHSUNDEEP
vbaexcelworksheet-function
Rhyfelwr
Rhyfelwr

Reputation: 329

Trying to get the number of Calendar Days/Workdays/Weeks within a set period

I am trying to create a script that gets the count of Business days, Calendar days and Weekdays within a set period (23rd of Previous month and 23rd of Current month).

I have the following script where I tried to use Worksheet Functions but it doesn't work, I get

"Object variable or With block variable not set"

error, what gives?

Sub DayCounts()
    Dim cYear As String
    Dim pMon As String
    Dim cpMon As String
    Dim start_date As String
    Dim end_date As String
    Dim mySh As Worksheet
    Dim wf As WorksheetFunction

    Set mySh = Sheets("prod_log_manual")

    cYear = Format(Date, yyyy) 'get year
    pMon = Format(Date - 1, mm) 'get previous month
    cMon = Format(Date, mm) 'get current month

    start_date = cYear & pMon & 24 '23th of previous month
    end_date = cYear & cMon & 23 '23rd of current month

    mySh.Range("P7") = wf.NetworkDays(start_date, end_date) 'get number of workdays in period
    mySh.Range("P8") = wf.Day(start_date, end_date) 'get number of calendar days in period
    mySh.Range("P9") = mySh.Range("P8").Value / 7 'get number of weeks within period
End Sub

Upvotes: 1

Views: 107

Answers (4)

Shai Rado
Shai Rado

Reputation: 33682

You can use start_date and end_date as Date, instead of String, and it will simplify and shorten your code by a lot.

Code

Option Explicit

Sub DayCounts()

    Dim start_date As Date ' use Date variables
    Dim end_date As Date ' use Date variables
    Dim mySh As Worksheet
    Dim wf As WorksheetFunction

    Set mySh = Sheets("prod_log_manual")

    ' set the worksheetfunction object
    Set wf = WorksheetFunction

    end_date = DateSerial(Year(Date), Month(Date), 23) ' 23rd of current month
    start_date = DateAdd("m", -1, end_date) ' 23th of previous month

    mySh.Range("P7") = wf.NetworkDays(start_date, end_date) ' get number of workdays in period
    mySh.Range("P8") = wf.Days(start_date, end_date) ' get number of calendar days in period

    mySh.Range("P9") = mySh.Range("P8").Value / 7 'get number of weeks within period

    ' can also use DateDiff with "w", which is weeks as the interval
    mySh.Range("P9") = DateDiff("w", start_date, end_date)

End Sub

Upvotes: 2

user4039065
user4039065

Reputation:

First, correct the date collection per Vityata's response.

You might want to use actual dates, not string concatenations.

mySh.Range("P7") = wf.NetworkDays(dateserial(clng(cYear)+cbool(clng(pmon)=12), clng(pmon), 24), dateserial(clng(cYear), clng(cmon), 23)) 'get number of workdays in period

NetworkDays is inclusive. Should you be using 23 for both? Shouldn't the previous be 24 or the current be 22?

Upvotes: 1

Pᴇʜ
Pᴇʜ

Reputation: 57683

The error

"Object variable or With block variable not set"

tells you that the variable wf is not set yet, which means you declared it

Dim wf As WorksheetFunction

but it's still empty because you didn't initialize it with

Set wf = Application.WorksheetFunction

This is what you should have done

Dim wf As WorksheetFunction
Set wf = Application.WorksheetFunction
mySh.Range("P7") = wf.NetworkDays(start_date, end_date) 'get number of workdays in period
mySh.Range("P8") = wf.Days(start_date, end_date) 'get number of calendar days in period

Note that it is wf.Days not wf.Day as Jeeped pointed out in the comment.

or use WorksheetFunction. instead of wf. or set WorksheetFunction

mySh.Range("P7") = WorksheetFunction.NetworkDays(start_date, end_date) 'get number of workdays in period
mySh.Range("P8") = WorksheetFunction.Days(start_date, end_date) 'get number of calendar days in period

And probably have a look at Vityata's answer which will solve your next issue you will run into after you solved this one.

Upvotes: 3

Vityata
Vityata

Reputation: 43585

Write Option Explicit on the top of the module. Thus, code like:

cYear = Format(Date, yyyy) 'get year
pMon = Format(Date - 1, mm) 'get previous month
cMon = Format(Date, mm) 'get current month

will start telling you that something is wrong. E.g., yyyy, mm are not defined as variables, which would give you the idea that they should be either variables or passed as string like this:

cYear = Format(Date, "yyyy")

Upvotes: 3

Related Questions