vendredi 5 mai 2017

Checking Dates in Excel VBA Dictionary

I'm currently using a dictionary to hold a list of bank holidays. I want to check if the current-1 date is a bank holiday and if so check if the previous day was a bank holiday and then if 3 days previous was a bank holiday (e.g. Monday is a bank holiday and so was the previous Friday)?

I'm having problems checking for dates in the Dictionary. I can compare strings (declare Date as string so my IF query works) BUT I cant then -1 or -3 from this date because it is a string.

Can anyone advise me of how to check dates in my below code please?

Public currentDate As Long
Public Bankholiday As Boolean
Public DoubleBank As Boolean
Public asdfg As Date
Public currentDay As String
Public BankHolidays As Scripting.Dictionary
Public DiaryDate As String

Then the sub is

Sub GetDate()
'Created to determin if a Bank Holiday needs to be taken into account for 
Date to run

currentDate = Date - 1
Today = Date - 1
currentDay = Format(Date, "dddd") 'writes day out as string e.g. Monday, 
Tuesday....
currentDate = Format(Date, "yyyymmdd") 'set format for SQL query
DiaryDate = Format(Date - 1, "dd/mm/yyyy")

'Set Dictionary Object
Set BankHolidays = CreateObject("Scripting.Dictionary")


'Add dates to Dictionary
BankHolidays.Add "01/01/2017", "B1"
BankHolidays.Add "02/05/2017", "B2"
BankHolidays.Add "14/04/2017", "B3"
BankHolidays.Add "17/04/2017", "B4"
BankHolidays.Add "01/05/2017", "B5"
BankHolidays.Add "29/05/2017", "B6"
BankHolidays.Add "28/08/2017", "B7"
BankHolidays.Add "25/12/2017", "B8"
BankHolidays.Add "26/12/2017", "B9"
BankHolidays.Add "01/01/2018", "B10"
BankHolidays.Add "30/03/2018", "B11"
BankHolidays.Add "02/04/2018", "B12"
BankHolidays.Add "07/05/2018", "B13"
BankHolidays.Add "28/05/2018", "B14"
BankHolidays.Add "27/08/2018", "B15"
BankHolidays.Add "25/12/2018", "B16"
BankHolidays.Add "26/12/2018", "B17"
BankHolidays.Add "04/05/2017", "B18"
BankHolidays.Add "03/05/2017", "B18"

'Check if Todays Date is in the dictionary
If BankHolidays.Exists(DiaryDate) Then
Today = Today - 1
MsgBox "1"
    'For instances of 2 consecutive bankholidays in a row (no weekend in     between)
    If BankHolidays.Exists(Today) Then
        Today = currentDate - 1
        MsgBox "2"
    End If

'For instances of 2 consecutive bankholidays in a row (WITH weekend in     between)
            If BankHolidays.Exists(Today - 2) Then
                Today = currentDate - 1
                MsgBox "3"
            End If

End If

End Sub

Aucun commentaire:

Enregistrer un commentaire