Prev: Merging of rows.
Next: Hyperlink Function
From: Chechu on 5 Jun 2010 09:17
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
From: joel on 5 Jun 2010 10:26

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

From: Chechu on 5 Jun 2010 10:56
On Jun 5, 11:26 am, joel <joel.4c3...(a)thecodecage.com> wrote:
> 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

 | 
Pages: 1
Prev: Merging of rows.
Next: Hyperlink Function