+ Reply to Thread
Results 1 to 6 of 6

Explanation of excel arrays / references for programmers?

  1. #1
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Explanation of excel arrays / references for programmers?

    Is there any resource out there that explains how references and arrays work in excel for more technical people, or even specifically programmers?

    All I've been able to on the internet is articles with generic examples, suitable so grandma accountant can take a look at them and press the right 3 buttons and be done with it.

    No one actually tries to explain how any of it actually works, they just tell you how to use it!

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Explanation of excel arrays / references for programmers?

    This video is a good place to start.

    https://www.youtube.com/results?sear...agic+trick+759
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Re: Explanation of excel arrays / references for programmers?

    Frankly, this video is barely any better than the usual drivel.

    But I am slowly piecing together bits and pieces for myself.

    Doesn't excel have reference docs somewhere?
    I'm talking about these types of docs:
    https://developer.mozilla.org/en-US/...ript/Reference
    https://en.cppreference.com/w/
    https://secure.php.net/manual/en/

    Maybe I'm just spoiled, but holy crap, is it hard to find information on how excel works.

  4. #4
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    15,803

    Re: Explanation of excel arrays / references for programmers?

    What kind of documentation are you looking for? I am sure that Microsoft will keep the deep, dark secrets of how Excel works unshared in any kind of public way -- you know how they are about licensing and piracy. Your examples suggest that you are more trying to get documentation for...something else.

    My first reaction to your other examples is that you are looking for solid documentation on Excel's object model. This is my go to reference for documentation about Excel's object model: https://docs.microsoft.com/en-us/off...l/object-model This reference is written with the assumption that you are accessing Excel's object model with VBA, so the examples are all in VBA. I don't know anything about accessing Excel's objects and methods from other programming languages, but would assume that it is possible, and that the interface with the object would be -- at least conceptually -- the same.

    In the OP, you specifically mention references and arrays (assuming you mean how to interact with single or multi-cell objects in a spreadsheet). One or more cells in spreadsheet is a Range object. What you can do to or with a range object is described in the Range Object help page: https://docs.microsoft.com/en-us/off....range(object).

    Are those the kinds of things you are looking for?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  5. #5
    Registered User
    Join Date
    06-06-2006
    Location
    English(UK)
    MS-Off Ver
    2019
    Posts
    93

    Re: Explanation of excel arrays / references for programmers?

    Kind of.

    It's not the object model I'm interested in, as much as the data model.

    I've been collecting pieces of the puzzle, but it's stupid that there isn't a good place where this information exists in one organized place.

    For example:
    What is an array in Excel?
    Well, evidently it is a 2-dimensional data structure.

    How do you make an array?
    References and certain functions return an array. One may also define an array via a literal.

    What is the syntax for array literals?
    You delimit the array using curly braces {}. Comma "," is used as a column separator, semicolon ";" is used as a row separator. E.g. {1,2;3,4}

    What operations can be performed on arrays?
    Not sure of the semantics of operating on arrays. That's a piece I'm still missing.

    What is a reference?
    A means to access some data. Dereferencing a reference yields an array.

    How do name definitions fit in this whole mess?
    I have no clue...

    Another thing I discovered is something called a "reference operator".
    Evidently there's 3 of them: colon, comma, space.
    And note here that comma here is semantically a very different operator from its use in array literals. Kind of like "+" in javascript for example, where, when applied to strings "+" signifies concatenation and when applied to numbers it signifies addition.

    THAT is the kind of reference I am looking for. I mean, compare what resources are easily findable for excel with this for example.

    I am certain there's details I'm still missing, but this is what I've been able to discover so far.

    For example, I've not been able to find a good definition of when an expression evaluates to a reference and when it evaluates to a value. Because evidently that depends on context.
    Example: Contrast this:
    =MATCH(1, INDIRECT(refAsString))
    with this:
    =MATCH(1, A1)
    A1 =INDIRECT(refAsString)

    And what types expressions are permissible where, and what types are not?

    As a programmer, this is information you will able to find in any language reference right off the bat.
    Where's the same for excel?
    Last edited by martix; 11-12-2018 at 10:18 PM.

  6. #6
    Forum Expert
    Join Date
    05-01-2014
    Location
    California, US
    MS-Off Ver
    Excel 2010
    Posts
    1,795

    Re: Explanation of excel arrays / references for programmers?

    Quote Originally Posted by martix View Post
    What is the syntax for array literals?
    You delimit the array using curly braces {}. Comma "," is used as a column separator, semicolon ";" is used as a row separator. E.g. {1,2;3,4}j
    Caveat lector.... That detail depends on regional settings for your computer. If you use a comma for the decimal point, I believe "\" is used for the column separator.

    (Don't bet your life on that. I'm not bothering to double-check which character replaces comma for that purpose.)


    Quote Originally Posted by martix View Post
    Another thing I discovered is something called a "reference operator".
    Evidently there's 3 of them: colon, comma, space.
    But unfortunately, those range operators have very limited usage, at least in versions of Excel up to and including 2010.

    For example, we can write IRR((B1,B3,B5)), but we cannot write XIRR((B1,B3,B5),(A1,A3,A5)).

    -----

    Part of the problem (learning features of Excel) is that Excel help pages have become difficult to find, and they have been homogenized to the point of being useless, if not incorrect, in some cases.

    I was able to locate the help page that explains these reference operators; to wit:
    https://www.excelfunctions.net/excel-operators.html#ReferenceOperators.

+ 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. [SOLVED] Find a specific sum for two different arrays..(more explanation in the post)
    By bach1229 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 10-03-2015, 08:21 AM
  2. [SOLVED] Explanation on Arrays
    By NeedForExcel in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-02-2013, 06:58 PM
  3. VBA store references with arrays
    By fuatt in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-25-2013, 07:57 AM
  4. lookup arrays - relative references?
    By jmhultin in forum Excel General
    Replies: 2
    Last Post: 08-09-2009, 07:11 PM
  5. [SOLVED] Programmers available
    By StevenS in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-15-2005, 05:05 AM
  6. [SOLVED] Arrays and range references in VBA
    By Stewart in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 08-22-2005, 01:05 PM
  7. job openings for programmers
    By [email protected] in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-11-2005, 02:06 AM

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