Excel2000 VBA: First workday of month

Hi

What is the best way to calculate 1st workday of any month (holidays must be
counted too)?

At moment I have a solution, where a list of dates for some amount of years
is created, with additional column where all dates are labeled as "workday",
"weekend", or "holiday". The code is looking for 1st workday in this list,
starting from 1st of month.

Probably a slightly better solution will be, where the list contains only
holidays. The code will be look for 1st date to be not weekend and not
present in holidays list in given month.

On worksheet, I can easily calculate the 1st workday of month using WORKDAY
function {=WORKDAY(DATE(Year, Month, 0),1,Holydays)}. But it seems, that I
can't use functions from Analysis Toolpack in VBA! Am I right about this, or
is there a way?


Arvi Laanemets

try using the eomonth function then +1
also you may need to nest in a vlookup if statement to check whether that
date returned is holiday (from the list of holiday dates).

"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> What is the best way to calculate 1st workday of any month (holidays must
> be
> counted too)?
>
> At moment I have a solution, where a list of dates for some amount of
> years
> is created, with additional column where all dates are labeled as
> "workday",
> "weekend", or "holiday". The code is looking for 1st workday in this list,
> starting from 1st of month.
>
> Probably a slightly better solution will be, where the list contains only
> holidays. The code will be look for 1st date to be not weekend and not
> present in holidays list in given month.
>
> On worksheet, I can easily calculate the 1st workday of month using
> WORKDAY
> function {=WORKDAY(DATE(Year, Month, 0),1,Holydays)}. But it seems, that I
> can't use functions from Analysis Toolpack in VBA! Am I right about this,
> or
> is there a way?
>
>
> Arvi Laanemets
>
>

dte = DateSerial(Year(Date),Month(Date)-1,0)
v = Application.Run "ATPVBAEN.xla!Workdate" dte,1,Range("HolyDays")

if ATPVBAEN is loaded in tools addins. (analysis toolpak for VBA)
--
Regards,
Tom Ogilvy



"Arvi Laanemets" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> What is the best way to calculate 1st workday of any month (holidays must
be
> counted too)?
>
> At moment I have a solution, where a list of dates for some amount of
years
> is created, with additional column where all dates are labeled as
"workday",
> "weekend", or "holiday". The code is looking for 1st workday in this list,
> starting from 1st of month.
>
> Probably a slightly better solution will be, where the list contains only
> holidays. The code will be look for 1st date to be not weekend and not
> present in holidays list in given month.
>
> On worksheet, I can easily calculate the 1st workday of month using
WORKDAY
> function {=WORKDAY(DATE(Year, Month, 0),1,Holydays)}. But it seems, that I
> can't use functions from Analysis Toolpack in VBA! Am I right about this,
or
> is there a way?
>
>
> Arvi Laanemets
>
>

Thanks!


"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> dte = DateSerial(Year(Date),Month(Date)-1,0)
> v = Application.Run "ATPVBAEN.xla!Workdate" dte,1,Range("HolyDays")
>
> if ATPVBAEN is loaded in tools addins. (analysis toolpak for VBA)

It must be! I think I'll include a check routine for this into procedure.
And here my second question. I can check for Analysis Toolpack being
installed through
.....
Application.AddIns(i).Name = "ANALYS32.XLL" And
Application.AddIns(i).Installed = True
....

where all AddInns are checked until Analysis Toolpack is found (or not
found). I didn't find any way to check for Analysis Toolpack directly.
Exists there such a way at all?


Arvi Laanemets

I left out a comma - all arguments to the ATP function, event the first, are
set off by commas since they are all arguments to Application.Run


dte = DateSerial(Year(Date),Month(Date)-1,0)
v = Application.Run "ATPVBAEN.xla!Workdate",dte,1,Range("HolyDays")

--
Regards,
Tom Ogilvy


"Arvi Laanemets" <[email protected]> wrote in message
news:u$oUR%[email protected]...
> Thanks!
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:[email protected]...
> > dte = DateSerial(Year(Date),Month(Date)-1,0)
> > v = Application.Run "ATPVBAEN.xla!Workdate" dte,1,Range("HolyDays")
> >
> > if ATPVBAEN is loaded in tools addins. (analysis toolpak for VBA)
>
> It must be! I think I'll include a check routine for this into procedure.
> And here my second question. I can check for Analysis Toolpack being
> installed through
> ....
> Application.AddIns(i).Name = "ANALYS32.XLL" And
> Application.AddIns(i).Installed = True
> ...
>
> where all AddInns are checked until Analysis Toolpack is found (or not
> found). I didn't find any way to check for Analysis Toolpack directly.
> Exists there such a way at all?
>
>
> Arvi Laanemets
>
>

OK, just noticed I misspelled Workday [ bad day :-( ]. Here is a tested
version to demonstrate - demonstrated in the immediate window:

dte = DateSerial(Year(Date),Month(Date)-1,0)
v = Application.Run( "ATPVBAEN.xla!Workday", dte, 1, Range("HolyDays"))
[GetMacroRegId] 'WORKDAY' <
[GetMacroRegId] 'WORKDAY' -> '2142634039' >
? format(v,"mmm dd,yyyy")
May 02,2005

the lines with [GetMacroRegId] are produced by the ATPVBAEN and are just
debris.

--
Regards,
Tom Ogilvy


"Tom Ogilvy" <[email protected]> wrote in message
news:Ovq%[email protected]...
> I left out a comma - all arguments to the ATP function, event the first,
are
> set off by commas since they are all arguments to Application.Run
>
>
> dte = DateSerial(Year(Date),Month(Date)-1,0)
> v = Application.Run "ATPVBAEN.xla!Workdate",dte,1,Range("HolyDays")
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Arvi Laanemets" <[email protected]> wrote in message
> news:u$oUR%[email protected]...
> > Thanks!
> >
> >
> > "Tom Ogilvy" <[email protected]> wrote in message
> > news:[email protected]...
> > > dte = DateSerial(Year(Date),Month(Date)-1,0)
> > > v = Application.Run "ATPVBAEN.xla!Workdate" dte,1,Range("HolyDays")
> > >
> > > if ATPVBAEN is loaded in tools addins. (analysis toolpak for VBA)
> >
> > It must be! I think I'll include a check routine for this into
procedure.
> > And here my second question. I can check for Analysis Toolpack being
> > installed through
> > ....
> > Application.AddIns(i).Name = "ANALYS32.XLL" And
> > Application.AddIns(i).Installed = True
> > ...
> >
> > where all AddInns are checked until Analysis Toolpack is found (or not
> > found). I didn't find any way to check for Analysis Toolpack directly.
> > Exists there such a way at all?
> >
> >
> > Arvi Laanemets
> >
> >
>
>

Hi Tom

I tried your solution now, and after some modifying it worked as needed. My
code (it reads indefinite number of column headers from row 1 starting from
F1 in a single-sheet source workbook - text strings in format "yyyy-mm" -
and saves them into an array as datestrings of 1st workday of according
month) is now:
.....
Dim arrMonths() As Variant
....
varSource = [SourceWorkbook]
rngHolidays = [Holidays]
.....
ColNum = Workbooks(varSource).Sheets(1).UsedRange.Columns.Count
.....
ReDim arrMonths(1 To ColNum-5) As Variant
j = 0
For i = 6 To ColNum
j = j + 1
xMonth = Workbooks(varSource).Sheets(1).Range("A1").Offset(0,
i - 1)
dte = DateSerial(Mid(xMonth, 1, 4), Mid(xMonth, 6, 2), 0)
xDate = Application.Run("ATPVBAEN.xla!Workday", dte, 1,
rngHolidays)
arrMonths(j) = Format(xDate, "mm-dd-yy")
Next i
.....

(And at start I missed, that not Analysis Toolpack, but Analysis Toolpack
for VBA is needed)


Thanks again!
--
Arvi Laanemets
( My real mail address: arvil<at>tarkon.ee )



"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> OK, just noticed I misspelled Workday [ bad day :-( ]. Here is a tested
> version to demonstrate - demonstrated in the immediate window:
>
> dte = DateSerial(Year(Date),Month(Date)-1,0)
> v = Application.Run( "ATPVBAEN.xla!Workday", dte, 1, Range("HolyDays"))
> [GetMacroRegId] 'WORKDAY' <
> [GetMacroRegId] 'WORKDAY' -> '2142634039' >
> ? format(v,"mmm dd,yyyy")
> May 02,2005
>
> the lines with [GetMacroRegId] are produced by the ATPVBAEN and are just
> debris.
>
> --
> Regards,
> Tom Ogilvy
>
>
> "Tom Ogilvy" <[email protected]> wrote in message
> news:Ovq%[email protected]...
>> I left out a comma - all arguments to the ATP function, event the first,
> are
>> set off by commas since they are all arguments to Application.Run
>>
>>
>> dte = DateSerial(Year(Date),Month(Date)-1,0)
>> v = Application.Run "ATPVBAEN.xla!Workdate",dte,1,Range("HolyDays")
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "Arvi Laanemets" <[email protected]> wrote in message
>> news:u$oUR%[email protected]...
>> > Thanks!
>> >
>> >
>> > "Tom Ogilvy" <[email protected]> wrote in message
>> > news:[email protected]...
>> > > dte = DateSerial(Year(Date),Month(Date)-1,0)
>> > > v = Application.Run "ATPVBAEN.xla!Workdate" dte,1,Range("HolyDays")
>> > >
>> > > if ATPVBAEN is loaded in tools addins. (analysis toolpak for VBA)
>> >
>> > It must be! I think I'll include a check routine for this into
> procedure.
>> > And here my second question. I can check for Analysis Toolpack being
>> > installed through
>> > ....
>> > Application.AddIns(i).Name = "ANALYS32.XLL" And
>> > Application.AddIns(i).Installed = True
>> > ...
>> >
>> > where all AddInns are checked until Analysis Toolpack is found (or not
>> > found). I didn't find any way to check for Analysis Toolpack directly.
>> > Exists there such a way at all?
>> >
>> >
>> > Arvi Laanemets
>> >
>> >
>>
>>
>
>