Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
EOMONTH compatibility issue (workaround?)
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
EOMONTH compatibility issue (workaround?)
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/sh...d.php?t=207360 http://www.thecodecage.com/forumz |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
EOMONTH compatibility issue (workaround?)
On Jun 5, 11:26*am, joel 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/sh...d.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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Compatibility Issue | Excel Discussion (Misc queries) | |||
Compatibility Issue | Excel Discussion (Misc queries) | |||
Compatibility issue? | Excel Programming | |||
could the #NAME? error be a compatibility issue? | New Users to Excel | |||
Code compatibility issue | Excel Programming |