开发者

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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜