Excel VBA: NetworkDays Error 2015
I have the this bit of code in a VBA class which is to workout the number of days between a property of the class (a date) and today’s date.
Dim EmailDate As Date
EmailDate = Me.Email.DateReceived
Debug.Print EmailDate, Date
Debug.Print NetworkDays(EmailDate), Date, Range("BankHolidays"))
When I run it I get the following output
23/04/2010 19/05/2010
[GetMacroRegId] 'NETWORKDAYS' <
[GetMacroRegId] 'NETWORKDAYS' -> '699990072' >
Error 2015
I have tested it in a module, using dummy data, and get the correct answer. Can anyone see why this would be giving an error in a class?
I have referenced atpvbaen.xls.
Edit: I have found that when I run the code through a menu option I have created on the menu bar it fails, but when I run it via a button or through the VB Editor it works fine. Lo开发者_开发问答oks like it is something to do with the menu.
Thanks, Martin
Are you sure you pasted the code correctly into your question? NetworkDays takes 3 arguments, not one, so:
Debug.Print NetworkDays(EmailDate), Date, Range("BankHolidays"))
should be:
Debug.Print NetworkDays(EmailDate, Date, Range("BankHolidays"))
When you are running from Menu you are running at application level context. So Range("BankHolidays") is not resolved properly if the range is not defined or defined in muliple open workbooks. Try using
activeworkbook.activesheet.Range("BankHolidays")
That should resolve the name.
精彩评论