EOMONTH compatibility issue (workaround?)

[Chechu]

Hi, it’s me again needing an advice. Excel 2003: I am using the
EOMONTH (ATP) formula in a file. My Excel is in English, but my users
have different languages. I need to replace the EOMONTH formula. Now,
as far as I understand, I have at least three options:

1) Workbook_Open event to find and replace EOMONTH (Ron’s code
http://www.rondebruin.nl/atp.htm) => I Can not use this one, since my
users may or may not have ATP installed and I can not force them to
manually install it (corporate policy, not under my control). In my
English version I had the code to automatically install it, as
follows:

Private Sub Workbook_Open()
Calculate
If AddIns("Analysis ToolPak").Installed = False Or AddIns("Analysis
ToolPak - VBA").Installed = False Then
Calculate
AddIns("Analysis ToolPak").Installed = True
AddIns("Analysis ToolPak - VBA").Installed = True
End If
Calculate
End Sub

Works in XL English, but it won’t work if other language. Any
workaround????

2) Replace EOMONTH with =DATE(YEAR(start_date),MONTH(start_date)+months
+1,0)

3) Use this UDF (www.adamslim.com):
Function AdsEndOfMonth(ByVal InStartdate As Double, ByVal InMonths As
Integer)
'replaces the EOMONTH function
'by Adam Slim

AdsEndOfMonth = DateSerial(Year(InStartdate), Month(InStartdate) +
InMonths + 1, 0)
End Function

If I have to use option 2 or option 3, which one should do a better
job in terms of performance??? I have 10.000+ cells using EOMONTH, and
performance is important in this file.

I will really appreciate your advises, comments, pros/cons.

Thanks,
Cecilia

 

[joel]

In general using simple worksheet functions on a worksheet is mor
efficient that calling a UDF VBA function. There is an overhea
associated with calling a VBA function and VBA code is less eficien
that worksheet function in memory and speed.

Some worksheet functions use a lot of memory resources and slow down
worksheet such as SUMPRTODUCT and using array functions (ones where yo
put a curly bracket {} around the function).

VBA Macros macros can be used to speed up a workbook since workshee
functions get automaticaly updated every time you make new entries on
worksheet while VBA code can be run be run manually. There are lots o
exceptions to these generalizations so use them only as guidelines.

 

[Chechu]

In general using simple worksheet functions on a worksheet is more
efficient that calling a UDF VBA function.  There is an overhead
associated with calling a VBA function and VBA code is less eficient
that worksheet function in memory and speed.

Some worksheet functions use a lot of memory resources and slow down a
worksheet such as SUMPRTODUCT and using array functions (ones where you
put a curly bracket {} around the function).

VBA Macros macros can be used to speed up a workbook since worksheet
functions get automaticaly updated every time you make new entries on a
worksheet while VBA code can be run be run manually.  There are lots of
exceptions to these generalizations so use them only as guidelines.

--
joel
------------------------------------------------------------------------
joel's Profile:http://www.thecodecage.com/forumz/member.php?u=229
View this thread:http://www.thecodecage.com/forumz/showthread.php?t=207360

http://www.thecodecage.com/forumz

Thanks for your comments Joel. So based on it, it seems that option 2
(replace EOMONTH with formula =DATE(YEAR(start_date),MONTH(start_date)
+months
+1,0) ) should be more efficient??
Thank you,
Cecilia