Embed Excel Formula in VBA

I need to calculate the absolute week and have found a formula to do so. Is there a way to embed/execute this in VBA?

TRUNC(((StartDate-DATE(YEAR(StartDate),1,0))+6)/7) where StartDate is the date which you are trying to find the corresponding week number (for example Now().

Thanks but that will not work.

Please Login or Register  to view this content.

It appears that the way excel returns the week number is dependent upon when the first day of the week is. A week always begins on a Monday, and the international standards organization defines week 1 as beginning on the Monday of the first week of the year that contains a Thursday. Said differently, week 1 is the first week (beginning on Monday) that has at least four days.

I need to be able to report the absolute week number. The formula does that but I need it in VBA. I guess I can figure it out in code but was hoping to take the easy way out and just imbed the formula (lazy I know!)

You could use:

Please Login or Register  to view this content.

Well that was too easy! I should have known better. Thank you for the solution.