Help - Calling Analysis Toolpak Functions from Outlook Macro
 

Hopefully this will be something obvious to you seasoned vba coders but it's
got me tearing my hair out!

I have an outlook macro which goes through an Exchange Server public folder
full of contact
items pulling various bits of data into a new excel spreadsheet. This all
works fine.
I need to use the "EOMONTH" function from the Analysis Toolpack on the sheet
so the formula is entered into the cells by the macro. I have also included
installing the Analysis Toolpack addin and the Analysis Toolpack VBA addin
in my macro code and loading the associated workbooks.
Problem is EOMONTH function is throwing up a #VALUE! error as if the
Analysis Toolpack is not loaded.
Strangely, to clear the #VALUE! error, I have to;
Untick the Analysis Toolpack VBA in the addins list and OK
Re-tick the Analysis Toolpack VBA in the addins list and OK
Copy and paste the formula from any other cell - ie select cell D4 and drag
the fill box down over the following cells - the value error is cleared in
those cells. Then, drag fill the formula back up from d5 to d4 - the value
error is now cleared in D4 also!!

Any help at all will be gratefullt received.

Ed

Help - Calling Analysis Toolpak Functions from Outlook Macro
 

Eddie,
If this is the only function you are using from the ToolPack, why not just
write your EOMONTH and then if the addin is there or not is not a concern.

Public Function EOMonth(argBase As Date, argOffset As Double) As Double
EOMonth = DateSerial(Year(argBase), Month(argBase) + argOffset + 1, 0)
End Function

NickHK

"Eddie McGlone" wrote in message
...
Hopefully this will be something obvious to you seasoned vba coders but
it's
got me tearing my hair out!

I have an outlook macro which goes through an Exchange Server public
folder
full of contact
items pulling various bits of data into a new excel spreadsheet. This all
works fine.
I need to use the "EOMONTH" function from the Analysis Toolpack on the
sheet
so the formula is entered into the cells by the macro. I have also
included
installing the Analysis Toolpack addin and the Analysis Toolpack VBA addin
in my macro code and loading the associated workbooks.
Problem is EOMONTH function is throwing up a #VALUE! error as if the
Analysis Toolpack is not loaded.
Strangely, to clear the #VALUE! error, I have to;
Untick the Analysis Toolpack VBA in the addins list and OK
Re-tick the Analysis Toolpack VBA in the addins list and OK
Copy and paste the formula from any other cell - ie select cell D4 and
drag
the fill box down over the following cells - the value error is cleared in
those cells. Then, drag fill the formula back up from d5 to d4 - the value
error is now cleared in D4 also!!

Any help at all will be gratefullt received.

Ed

Help - Calling Analysis Toolpak Functions from Outlook Macro
 

NickHK, you are a genius!!

Thabks for your help - that works well.

Cheers

Ed
"NickHK" wrote in message
...
Eddie,
If this is the only function you are using from the ToolPack, why not just
write your EOMONTH and then if the addin is there or not is not a concern.

Public Function EOMonth(argBase As Date, argOffset As Double) As Double
EOMonth = DateSerial(Year(argBase), Month(argBase) + argOffset + 1, 0)
End Function

NickHK

"Eddie McGlone" wrote in message
...
Hopefully this will be something obvious to you seasoned vba coders but
it's
got me tearing my hair out!

I have an outlook macro which goes through an Exchange Server public
folder
full of contact
items pulling various bits of data into a new excel spreadsheet. This all
works fine.
I need to use the "EOMONTH" function from the Analysis Toolpack on the
sheet
so the formula is entered into the cells by the macro. I have also
included
installing the Analysis Toolpack addin and the Analysis Toolpack VBA
addin
in my macro code and loading the associated workbooks.
Problem is EOMONTH function is throwing up a #VALUE! error as if the
Analysis Toolpack is not loaded.
Strangely, to clear the #VALUE! error, I have to;
Untick the Analysis Toolpack VBA in the addins list and OK
Re-tick the Analysis Toolpack VBA in the addins list and OK
Copy and paste the formula from any other cell - ie select cell D4 and
drag
the fill box down over the following cells - the value error is cleared
in
those cells. Then, drag fill the formula back up from d5 to d4 - the
value
error is now cleared in D4 also!!

Any help at all will be gratefullt received.

Ed