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

[Jason Campbell]

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

 

[Norie]

Jason

To get rid of that specific error you need to use this to declare wf.

Dim wf As WorksheetFunction

There are some other problems though, specifically here.

DueDate = wf.WorkDay("A2", 15, bankholidays)

If 'A2' and 'bankholidays' are actually meant to refer to ranges you would need to use this.

DueDate = wf.WorkDay(Range("A2"), 15, Range("bankholidays"))

 

[Jason Campbell]

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

 

[Norie]

Jason

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

 

[Jason Campbell]

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.

 

[Norie]

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.

DueDate = Application.WorkDay(Range("A2"), 15, Range("bankholidays"))

This is actually kind of an abbreviation, the full code would look like this.

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.

 

[Jason Campbell]

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.

 

[Jason Campbell]

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").

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

 

[Jason Campbell]

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").

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!