WorksheetFunction.WorkDay - Error

[mihiri]

Hi There,

I have a macro where I am using WorksheetFunction.WorkDay function.

Below code works fine in my PC (that uses Office 365 Pro Plus) but giving an error when it is run in my colleagues PC (that uses Office 2013)

The error is Run-time error '1004': Unable to get the WorkDay property of the WorkSheetFunction class.

Could someone please help?

Sub Copy_SEDOL_Report()

Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim wsHolidays As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
Dim iWarnColor As Integer
Dim rng As Range
Dim rngCell As Variant
Dim tdate As Date
Dim BDate As String
Dim Holiday(46) As Date
  
  
Holiday(0) = DateSerial(2020, 12, 25)
Holiday(1) = DateSerial(2020, 12, 26)
Holiday(2) = DateSerial(2020, 12, 28)
Holiday(3) = DateSerial(2021, 1, 1)
Holiday(4) = DateSerial(2021, 1, 2)
Holiday(5) = DateSerial(2021, 1, 4)
Holiday(6) = DateSerial(2021, 2, 1)
Holiday(7) = DateSerial(2021, 2, 6)
Holiday(8) = DateSerial(2021, 2, 8)
Holiday(9) = DateSerial(2021, 4, 2)
Holiday(10) = DateSerial(2021, 4, 5)
Holiday(11) = DateSerial(2021, 4, 25)
Holiday(12) = DateSerial(2021, 4, 26)
Holiday(13) = DateSerial(2021, 6, 7)
Holiday(14) = DateSerial(2021, 10, 25)
Holiday(15) = DateSerial(2021, 12, 25)
Holiday(16) = DateSerial(2021, 12, 26)
Holiday(17) = DateSerial(2021, 12, 27)
Holiday(18) = DateSerial(2021, 12, 28)
Holiday(19) = DateSerial(2022, 1, 1)
Holiday(20) = DateSerial(2022, 1, 2)
Holiday(21) = DateSerial(2022, 1, 2)
Holiday(22) = DateSerial(2022, 1, 3)
Holiday(23) = DateSerial(2022, 1, 4)
Holiday(24) = DateSerial(2022, 1, 31)
Holiday(25) = DateSerial(2022, 2, 6)
Holiday(26) = DateSerial(2022, 2, 7)
Holiday(27) = DateSerial(2022, 4, 15)
Holiday(28) = DateSerial(2022, 4, 18)
Holiday(29) = DateSerial(2022, 4, 25)
Holiday(30) = DateSerial(2022, 6, 6)
Holiday(31) = DateSerial(2022, 10, 24)
Holiday(32) = DateSerial(2022, 12, 25)
Holiday(33) = DateSerial(2022, 12, 26)
Holiday(34) = DateSerial(2022, 12, 27)
Holiday(35) = DateSerial(2023, 1, 1)
Holiday(36) = DateSerial(2023, 1, 2)
Holiday(37) = DateSerial(2023, 1, 3)
Holiday(38) = DateSerial(2023, 1, 30)
Holiday(39) = DateSerial(2023, 2, 6)
Holiday(40) = DateSerial(2023, 4, 7)
Holiday(41) = DateSerial(2023, 4, 10)
Holiday(42) = DateSerial(2023, 4, 25)
Holiday(43) = DateSerial(2023, 6, 5)
Holiday(44) = DateSerial(2023, 10, 23)
Holiday(45) = DateSerial(2023, 12, 25)
Holiday(46) = DateSerial(2023, 12, 26)


Set wsCopy = Workbooks("Cash Holdings.xlsm").Worksheets("NAM_SEDOL_Report")
Set wsDest = Workbooks("Cash Holdings.xlsm").Worksheets("HC_NAM_SEDOL_Report")

tdate = Date
BDate = Application.WorksheetFunction.WorkDay(tdate, -1, Holiday)


'1. Find last used row in the copy range based on data in column A
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "A").End(xlUp).Row

'2. Find first blank row in the destination range based on data in column A
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "A").End(xlUp).Offset(1).Row

'3. Clear contents of existing data range
'wsDest.Range("A2:I" & lDestLastRow).ClearContents
    wsDest.Range("A2:I200").ClearContents
    '4. Copy & Paste Data
wsCopy.Range("A2:I" & lCopyLastRow).Copy
wsDest.Range("A2").PasteSpecial Paste:=xlPasteValues


'Get date copied
wsDest.Range("C2:C" & lCopyLastRow) = Format(BDate, "YYYYMMDD")

End Sub

 

[Joe4]

Welcome to the Board!

The first thing you should do is verify that they have all the correct VBA library/references selected.

First go to the computer where it works (yours), open up the VBA Editor, and under the "Tools" menu, select "References". Note all the references that you have selected.
Now, do the same on your colleagues computer. Are any missing? If so, find it and select it (note that since they are using an older version of Excel, the version number may be a little different, but the text of the reference should be the same).

 

[mihiri]

Thanks Joe

I checked my colleagues references and he has the same list as mine. Still it doesn't work

Welcome to the Board!

The first thing you should do is verify that they have all the correct VBA library/references selected.

First go to the computer where it works (yours), open up the VBA Editor, and under the "Tools" menu, select "References". Note all the references that you have selected.
Now, do the same on your colleagues computer. Are any missing? If so, find it and select it (note that since they are using an older version of Excel, the version number may be a little different, but the text of the reference should be the same).

 

[Joe4]

Are you trying to run it on the exact same data file?

Does the Workday function work for them in a regular formula (right on the sheet, not in VBA)?
I know some of the Advanced functions are part of certain Add-Ins. I cannot remember if Workday is one of them.
Check to see which Excel Add-Ins you have installed, and check to see which ones they have.

 

[mihiri]

Sorry for the delay.

I verified all add-ins and all are the same as what I have. My colleague is also now migrated to Offie 365 (Just like my PC)

but still the same error.

Is there an alternative to workday function with holidays?

 

[Joe4]

The only advice I can give you is now that they have migrated, double-check to make sure that they still have all the same VBA references selected, and all the same add-ins installed. And make sure that that you are testing it out on the exact same file with the exact same data (as the one that works on your system). If all those things are the same, I cannot see any reason why it would not work, outside of different system settings (i.e. you are using commas to separate formula arguments, and they are using semi-colons).

Perhaps try playing around with the "WORKDAYS.INTL" function instead, and see if you have any more luck using that one instead?

WORKDAY.INTL Function

The Excel WORKDAY.INTL function takes a date and returns the "next" working day, based on a given offset value. Unlike the WORKDAY function, WORKDAY.INTL allows you to customize which days are considered weekends (non-working days). You can use WORKDAY.INTL function to calculate completion...

exceljet.net

Here is an example of someone who used it in VBA: How can I call Workdays.intl from vba (macro) in office 2010

Unfortunately, I know of no other easy alternative. You would either have to create a fairly complex function or your own User Defined Function in VBA to do this.