VBA - How to use the workday function with a named range for bankholidays

Jason Campbell

Board Regular
Joined
Mar 22, 2016
Messages
77
Dear all,

Really new to VBA and trying to use the workday function to calculate the stipulated days (including bankholidays named range) to give me the final 'Due date' for a initial 'Statement of Need' received date.

I keep getting a Compile error:'Statement invalid outside Type block'

my VBA code is as follows:

Sub DateTest()

Dim SonDate As Date
Dim DueDate As Date
Dim Sent Date As Date
wf As WorksheetFunction

Set wf = Application.Worksheetfunction

SonDate = Range("A2").Value
DueDate = wf.WorkDay("A2", 15, bankholidays)
SentDate = Range("C2").Value

If SentDate > DueDate Then
MsgBox "Due date exceeded! Due date was " & DueDate

ElseIf SentDate <= DueDate Then
MsgBox "Response sent within appropriate time frame."

End If

End Sub

Any pointers/assistance you could give would be welcomed. I'm a VBA virgin, so please explain in simple terms :)

Many thank,

Jason
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
Jason

To get rid of that specific error you need to use this to declare wf.
Code:
Dim wf As WorksheetFunction

There are some other problems though, specifically here.
Code:
DueDate = wf.WorkDay("A2", 15, bankholidays)
If 'A2' and 'bankholidays' are actually meant to refer to ranges you would need to use this.
Code:
DueDate = wf.WorkDay(Range("A2"), 15, Range("bankholidays"))
 
Upvote 0
Norie,

Thanks, that has worked a treat!

Quick further question. In trying to figure out how to use a worksheet function in VBA, I've had to do the usual of searching the internet to fined an example to patch into what I am trying to write.

I noted that the bit I copied from the internet declared the variable 'wf' as a WorksheetFunction, which I later used by showing the value of my variable 'DueDate' to equal 'wf.WorkDay(Range("A2"), 15,Range("B2"))

Is it not possible to just declare my variable 'DueDate' as WorksheetFunction and then make the following statement 'DueDate = WorkDay(Range("A2"), 15,Range("B2))'

I may be getting a little muddled, but any enlightening reasoning would be welcomed.

Thanks again,

Jason
 
Upvote 0
Jason

Don't see how that would work, isn't DueDate a date variable that holds the result of a function?
 
Upvote 0
Norie,

Ah...I see what you are getting at. So first I have to declare a variable (wf) to contain the result of the workday worksheet function, the results of which would then be populated into the DueDate variable.
 
Upvote 0
No, that's not right - in the code you posted wf is being declared as a WorksheetFunction object from which you can access the available worksheet functions.

To be honest the only reason I can see for wf being used/declared is as an abbreviation, personally I would do it like this.
Code:
DueDate = Application.WorkDay(Range("A2"), 15, Range("bankholidays"))
This is actually kind of an abbreviation, the full code would look like this.
Code:
DueDate = Application.WorksheetFunction.WorkDay(Range("A2"), 15, Range("bankholidays"))

One advantge of using Application.functionname is that if there is a problem with the function an error value will be returned instead of a runtime error being triggered.
 
Upvote 0
Norie,

Thanks, that has cleared up a lot of confusion. My logic was as per the syntax that you have written, however I was confused somewhat by the syntax I had copied from the internet and later tweaked.
 
Upvote 0
Hi Norie,

With your assistance, I have finally got my DateTest procedure to work, as per below. There is however, one further questions I hope you may be able to assist me with.

How can I write code for the procedure to recognize when data has been input into range("C3") and the user has hit the "Enter" keystroke, at which point the middle two ElseIf statements are run to test whether the sent date falls within or outside the required due date generated by the workday function and the "X" or "OK" value is placed in range("D3")?

I have currently assigned a button to run the procedure, however I have found that once I have entered the sent date, I have to click the button again for the middle two Elseif statements to test the value of range("C3") and populate range("D3").

Code:
Sub DateTest()
Dim SonDate As Date
Dim DueDate As Date
Dim SentDate As Date

       SonDate = Range("A3").Value
       DueDate = Application.WorksheetFunction.WorkDay(Range("A3"), 15, Range("bankholidays"))
       SentDate = Range("C3").Value
    
    If Range("A3") = "" Then
        MsgBox "Input SON received date!"
        
        ElseIf Range("C3") <> "" And Range("C3") <= Range("B3") Then
            Range("D3").Value = "OK"
            MsgBox "Response time OK!"
                        
        ElseIf Range("C3") <> "" And Range("C3") > Range("B3") Then
            Range("D3").Value = "X"
            MsgBox "Response time Failure!"
                  
        ElseIf Range("A3") <> "" Then
            Range("B3") = DueDate
            MsgBox "Input Sent Date and click button again."
                                                   
    End If
    
End Sub

Jason
 
Upvote 0
Hi Norie,

With your assistance, I have finally got my DateTest procedure to work, as per below. There is however, one further questions I hope you may be able to assist me with.

How can I write code for the procedure to recognize when data has been input into range("C3") and the user has hit the "Enter" keystroke, at which point the middle two ElseIf statements are run to test whether the sent date falls within or outside the required due date generated by the workday function and the "X" or "OK" value is placed in range("D3")?

I have currently assigned a button to run the procedure, however I have found that once I have entered the sent date, I have to click the button again for the middle two Elseif statements to test the value of range("C3") and populate range("D3").

Code:
Sub DateTest()
'Dim SonDate As Date
Dim DueDate As Date
'Dim SentDate As Date

       'SonDate = Range("A3").Value
       DueDate = Application.WorksheetFunction.WorkDay(Range("A3"), 15, Range("bankholidays"))
       'SentDate = Range("C3").Value
    
    If Range("A3") = "" Then
        MsgBox "Input SON received date!"
        
        ElseIf Range("C3") <> "" And Range("C3") <= Range("B3") Then
            Range("D3").Value = "OK"
            MsgBox "Response time OK!"
                        
        ElseIf Range("C3") <> "" And Range("C3") > Range("B3") Then
            Range("D3").Value = "X"
            MsgBox "Response time Failure!"
                  
        ElseIf Range("A3") <> "" Then
            Range("B3") = DueDate
            MsgBox "Input Sent Date and click button again."
                                                   
    End If
    
End Sub

Jason
I've just noted that I didn't need the SonDate OR SentDate variables!
 
Upvote 0

Forum statistics

Threads
1,223,099
Messages
6,170,107
Members
452,302
Latest member
TaMere

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top