Help - Calling Analysis Toolpak Functions from Outlook Macro

[Eddie McGlone]

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

 

[NickHK]

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]

NickHK, you are a genius!!

Thabks for your help - that works well.

Cheers

Ed