+ Reply to Thread
Results 1 to 3 of 3

XLM, or, Why won't MSFT provide useful information functions?

  1. #1
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,124

    XLM, or, Why won't MSFT provide useful information functions?

    This will start off as a rant. Excel still has a Transition formula evaluation setting even though the IBM ceased selling Lotus 1-2-3 more than a decade and a half ago. Nothing other than MSFT's indifference or laziness precludes a setting for Traditional formula evaluation. What would I add which was nontraditional? Damn near everything Lotus Development Corp added to 1-2-3's @CELL function in Release 3 in 1989. 3 decades ago it was possible to get another cell's formula using @CELL("formula",X99). It took MSFT more than 2 decades to add FORMULATEXT.

    Why FORMULATEXT when there was a perfectly useful GET.CELL(6,...)?

    One of my greater frustrations with MSFT and Excel is MSFT continuing refusal to make all XLM information functions usable in standard worksheet cell formulas. OK, not all of them since CALLER would probably trigger circular recalc and LAST.ERROR would serve no purpose. However, I have a fair few workbooks which use names defined using GET.WORKBOOK(1,!$1:$1048576).

    I dread MSFT deprecating XLM and thus removing its information functions.

  2. #2
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    ÁSoft Office 365. Learning Excel all over again!!
    Posts
    27,139

    Re: XLM, or, Why won't MSFT provide useful information functions?

    I do hope you feel better for that!! I still use some XLM stuff, but readily confess that I don't remember all its functionality anymore... That's too many Birthdays, however.
    Glenn



  3. #3
    Valued Forum Contributor hrlngrv's Avatar
    Join Date
    07-06-2004
    Location
    Northern California
    MS-Off Ver
    2K, 2003, 2010, O365
    Posts
    1,124

    Re: XLM, or, Why won't MSFT provide useful information functions?

    Most of the XLM information functions' return values can be provided by VBA udf wrappers around certain object properties. However, VBA is much slower than XLM, especially for things like GET.WORKBOOK(1,!A1), which returns an array of all sheet names from the workbook which contains the !A1 reference, so the workbook containing the Name or Macro Sheet containing the formula calling that XLM function. In VBA, Excel's object model has a Sheets collection property for Workbook objects, but no direct access to sheet names. Thus, in VBA it's necessary to ReDim an array as return vlaue, iterate through the workbook's Sheets collection, storing all the sheet names in the array.

    If we can use XLM information functions in defined names, and those defined names can be used in worksheet cell formulas, why can't XLM information functions be used directly in worksheet cells functions? TOO MANY FUNCTIONS was an argument which went out the window decades ago with the appearance of Bessel functions in Excel.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 5
    Last Post: 03-28-2017, 10:09 AM
  2. Hello and Thanks for all the Great Information you Provide
    By frank_AL in forum Hello..Introduce yourself
    Replies: 1
    Last Post: 10-30-2015, 02:54 PM
  3. Replies: 7
    Last Post: 05-08-2015, 01:52 PM
  4. Provide list of information if match to the summary
    By dondonordas in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 03-25-2014, 07:44 AM
  5. Looking for a formula to provide an outcome from a listing of information
    By s.tara91 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 06-25-2013, 02:27 AM
  6. [SOLVED] MSFT Excell
    By Pennsylvania in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 03-10-2006, 11:55 AM
  7. MSFT Excel
    By carinaburton06@yahoo.com in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-15-2005, 12:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1