Application.WorksheetFunction. (Excel 2003 SP2)

Topic

New Reply

I would like to find the week number. I thought it would be easy to use the worksheet weeknum function from the analysis toolpack add in, rather than writting my own. However when I use Application.WorksheetFunction, the weeknum function is not available. Is there a way to use worksheet functions from add ins?

Thanks.

Reply | Quote

Replies

You can set a reference to the atpvbaen.xla and then use Weeknum or you could use Format(date, "ww")

Reply | Quote

> Format(date, “ww”)

Mo’ better, and mine wasn’t quite right.

Reply | Quote

Thanks. I did have a reference set. If I had stopped trying to use Application.Workbookfunctions and simply used the object browser I would have found what I was looking for.

Reply | Quote

Which works fine as long as you’re interested in US weeknumbers…

Reply | Quote

? You lost me – I thought week numbers were the same on all current CE Gregorian Calendars – what are the alternatives?

Reply | Quote

In the US, week 1 always starts on January 1, regardless of which day of the week that is.
In many European countries, the definition from ISO 8601 is used: weeks always start on a Monday, and week 1 is the week containing at least 4 days of the year (in other words, the week containing January 4). Hence, week 1 may start anywhere from December 29 of the previous year to January 4 of the current year.

Reply | Quote

(Edited by JohnBF on 11-Sep-07 08:38. Argument order)

Not all built in worksheet functions are available to Excel.Application.WorksheetFunction.

You might try VBA functions such as (untested):

myweeknum = datediff(“ww”, dateserial(year(mydate), 1, 0), mydate)

Reply | Quote