Networkdays Excel v2000-->v2007

[mjpearson]

I wrote a very simple VBA function in Excel 2000 and it uses a call to NETWORKDAYS. I had to enable the Analysis TookPak and it works well.

I sent the workbook to a friend to admire however, he's using Excel 2007 and has problems with my VBA function. I added the attached code to resolve but still....no luck. Any idea what I'm doing wrong?

VB6 = Val(Application.VBE.Version) < 6.5

If VB6 Then
  number_of_days = [atpvbaen.xls].NETWORKDAYS( _
      event_start_time, _
      event_end_time, _
      Sheets(seasons_sheet_name).Range(holiday_table_name))
Else
  number_of_days = WorksheetFunction.NETWORKDAYS( _
      event_start_time, _
      event_end_time, _
      Sheets(seasons_sheet_name).Range(holiday_table_name))
End If

mike

 

[AnotherHiggins]

What error does your friend get? On what line?

My first guess would be that it's just a problem of missing references.... You have references turned on that he doesn't even have in 2007, and at the same time he'll need to enable the 2007 version of those references.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mjpearson]

Thanks "anotherhiggins",

I called my friend and he said something about the NETWORKDAYS being the problem.

>>references....problem of missing references....

Okay, you've stumped me. Are you talking about "[atpvbaen.xls]."? I read where this is a library reference to Analysis TookPak. From what I've read, the Analysis TookPak is now integral to Excel 2007 so, I removed the external library call. And I added the VB6 to allow it to run in either version (found this tip on the Internet as well).

Thanks for the help.

 

[AnotherHiggins]

Within the VBE (Visual Basic Editor), make sure that you're in the module containing your macro. Go to Tools > References (note that this option will be grayed out if you're editing a macro).

In that list, you'll see a ton of options, a few of which are checked off.

An Excel 2003 macro might contain references to things like:
- Microsoft Excel 11.0 Object Library
- Microsoft Office 11.0 Object Library
- Microsoft Access 11.0 Object Library

In 2007 there are corresponding references, but they are different:
- Microsoft Excel 12.0 Object Library
- Microsoft Office 12.0 Object Library
- Microsoft Access 12.0 Object Library

It would be easier possible to troubleshoot the problem if you tell us the actual error message your friend is getting.

[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.

 

[mjpearson]

I went to another friend's house to test with Excel 2003 and found part of the problem. It now works on 2003 and now to test on 2007:

Originally I had the following:

Early_version = Val(Application.VBE.Version) < 6.5
If Early_version Then
  number_of_days = [atpvbaen.xls].NETWORKDAYS( .....
Else
  number_of_days = WorksheetFunction.NETWORKDAYS(....

I changed it to

Early_version = Val(Application.Version) < 12
If Early_version Then
  number_of_days = [atpvbaen.xls].NETWORKDAYS( .....
Else
  number_of_days = WorksheetFunction.NETWORKDAYS(....

 

[mjpearson]

I called my friend today and he indicates that it's working. Hurray, the code above fixed the problem.

I'm almost starting to understand this VBA stuff but I better not get too cocky. I'm sure there's a whole lot more to learn.

Thanks for the advice and consult.

Mike

 

[AnotherHiggins]

I'm glad you got it sorted out.

And thanks for taking the time to post the solution that worked for you. That might help another member in the future!



[tt][blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ 181-2886 before posting.