WorksheetFunction.WorkDay - Error

mihiri

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2019
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?

VBA Code:
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
 

Attachments

  • Error.PNG
    Error.PNG
    23.7 KB · Views: 4
Last edited by a moderator:

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2019
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Nov 24, 2020
Messages
3
Office Version
  1. 2019
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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
56,655
Office Version
  1. 365
Platform
  1. Windows
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?
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,638
Messages
5,626,038
Members
416,155
Latest member
bokboxes

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
Top