+ Reply to Thread
Results 1 to 9 of 9

Array reference in a cell: difference using or not array formulas

  1. #1
    Registered User
    Join Date
    07-04-2019
    Location
    Gothenburg, Sweden
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Unhappy Array reference in a cell: difference using or not array formulas

    Hi,

    I need to check a thing that I guess might result stupid for most of you here.
    Couldn't find any online guide that has helped so far.

    It's about using a cell to refer to an array, example A1:G1.

    If I take for instance cell F5=A1:G1, then I get the value in the above mentioned array corresponding to column F. In other words, this formula just retrieves ONE CELL's value and it's not connected to the entire array anymore? Could you confirm this statement or please explain me how it really works? I have noticed that this works when the array is one dimensional, while the formula gives an error if you refer to more dimension or if you get outside the definition field of the array (like if I had taken cell L5, for instance.

    Using an array formula will instead show only the first value of the array, F5={A1:G1}, so in this case A1. The cell contains though the entire array, am I right?

    Thanks a lot in advance,

    Cheers, Riccardo

  2. #2
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array reference in a cell: difference using or not array formulas

    Hi,

    I'm afraid it's not at all clear what you mean here.

    With respect to a single-cell formula (as opposed to a multi-cell array formula, which is a different matter), an array of values is never 'contained' within, nor can be transferred to, the cell containing that formula. Rather that array is stored 'in-formula', from which, if desired, may be output single values into actual worksheet cells.

    If you attempt to 'evaluate' a formula which returns an array of values - i.e. output a result to an actual worksheet cell - then you will not succeed; instead, only the first entry in that array will be returned. A formula such as

    =A1:G1

    placed within a single cell, is completely redundant (whether committed with CTRL+SHIFT+ENTER or not), since a single cell can never return an array of values. Whereas something like:

    =MAX(A1:G1)

    for example, makes perfect sense.

    Regards
    Click * below if this answer helped

    Advanced Excel Techniques: http://excelxor.com/

  3. #3
    Registered User
    Join Date
    07-04-2019
    Location
    Gothenburg, Sweden
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Array reference in a cell: difference using or not array formulas

    Thanks a lot for this inputs.

    I've got the confirmation I needed. Showing the first value of the array it's just what it does (if you use CTRL+SHIFT+ENTER).
    That "object cell" that has been (improperly) connected to that array does not contain any other value but the first one of the array.
    A cell cannot contain more than one "argument" in excel? Even if that cell is an argument for a VBA function that wants an array as input?

    Thanks,

  4. #4
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array reference in a cell: difference using or not array formulas

    Quote Originally Posted by RiccardoS89 View Post
    A cell cannot contain more than one "argument" in excel? Even if that cell is an argument for a VBA function that wants an array as input?
    Perhaps you have to be more specific with your definition of 'argument' here. Or maybe give a practical example to illustrate what you mean.

    Regards

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

    Re: Array reference in a cell: difference using or not array formulas

    I agree with XOR LX -- we need to know a little bit more about the context for your question before we can give a good answer.

    Even if that cell is an argument for a VBA function that wants an array as input?
    This suggests that your context involves a UDF that wants an array as input. Back when I was first learning to code VBA UDFs, I found that Excel could not send an array to VBA. It seems that Excel can only send three types of things to VBA:
    1) A double (but not an array of doubles), which, of course, VBA can readily convert to other numeric data types like Long or Single or Integer.
    2) A string, which would not usually undergo any type conversions while being passed to VBA.
    3) A range object.

    If I were coding a UDF where I wanted to pass the array of values in A1:G1 to that UDF, I would code the UDF to receive a Range object
    Please Login or Register  to view this content.
    called from the spreadsheet =myudf(A1:G1)
    Does that help any?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  6. #6
    Registered User
    Join Date
    07-04-2019
    Location
    Gothenburg, Sweden
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Array reference in a cell: difference using or not array formulas

    Thanks a lot. This helped actually and I have for sure a better understanding.
    Sorry if I haven't been able to explain my doubt before.

    I ll get straight to the last question:
    Supposing I have a cell that refers to an array, let's say cell F3 = A1:G1. The retrieved value, displayed by the cell, will be:
    - A3 if I entered without ctrl-shift
    - A1 if I entered with ctrl-shift

    Regardless of what that value is on the spreadsheet...
    If I target that cell with a VBA function, or with an external plug-in, whatsoever, what I will have as information?
    - the value of F3 in the spreadsheet?
    - the string "=A1:G1"?

    Hope this clarify my doubts
    Thanks

  7. #7
    Forum Expert XOR LX's Avatar
    Join Date
    04-18-2013
    Location
    Turin, Italy
    MS-Off Ver
    Office 365
    Posts
    7,742

    Re: Array reference in a cell: difference using or not array formulas

    Ah, I see what you're saying now.

    If you have the (non-array) formula in cell F3:

    =A1:G1

    the reason the value in F1 is returned is that Excel employs implicit intersection of the range you specify (A1:G1) with the column in which the formula lies (F:F). So entering the same formula but in cell B3 would return the value in B1.

    According to exceljet.net (see here), any array formula explicitly disables this behaviour. I'm not sure if that's the correct description or not, though clearly with array-entry any formula referencing a range will always return the first value in that range, irrespective of the cell in which the formula lies.

    Regards

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

    Re: Array reference in a cell: difference using or not array formulas

    If I target that cell with a VBA function, or with an external plug-in, whatsoever, what I will have as information?
    - the value of F3 in the spreadsheet?
    - the string "=A1:G1"?
    To be sure I understand, you have a cell with the formula =A1:G1 in it. As observed and explained, this formula will return one value to the cell. When you pass that cell to a UDF, what your UDF's variable contains will depend on exactly what data type the argument is.

    a) If the argument is typed as a numeric data type (double, long, etc.), then the variable will contain that single value stored in the cell.
    b) If the argument is typed as string, then the variable will contain that value converted to a text string.
    c) If the argument is typed as range or variant, then the argument will contain a Range object referencing that cell. Your UDF will then be able to read any of the property values that apply to that single cell range object (range object help file with the list of properties: https://docs.microsoft.com/en-us/off....Range(object) ). This obviously includes the cell's value (the .value property), but would also include the formula string (.formula property) and a whole list of other properties.

    The easiest way to see what you would have in different scenarios is to use VBA's debugging tools (specifically the locals window http://www.cpearson.com/Excel/DebuggingVBA.aspx ). As I explain in this tutorial (https://www.excelforum.com/tips-and-...uild-udfs.html ), one of the first things I do when developing a UDF is to add a stop statement to the top of the procedure so that it will enter debug mode. Once you get into debug mode, you can look in the locals window and see exactly what your argument contains. Hope that helps.
    Last edited by MrShorty; 07-05-2019 at 11:29 AM.

  9. #9
    Registered User
    Join Date
    07-04-2019
    Location
    Gothenburg, Sweden
    MS-Off Ver
    Office365 ProPlus
    Posts
    4

    Re: Array reference in a cell: difference using or not array formulas

    Couldn't have been clearer than this. Thanks a lot for the explanation

+ 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] Search For Array Formulas by Cell Reference
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 04-04-2015, 04:34 AM
  2. Question about using Evaluate with an array of formulas not an array formula
    By Mr_Bill in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-19-2014, 03:26 PM
  3. Replies: 0
    Last Post: 08-01-2013, 04:26 PM
  4. Array Formulas = Circular Reference Error?
    By tekman in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-31-2011, 07:08 PM
  5. Using cell reference in array formula
    By willow2008 in forum Excel General
    Replies: 2
    Last Post: 11-10-2009, 04:12 PM
  6. Replies: 6
    Last Post: 12-30-2008, 06:52 AM
  7. Array of formulas (regional difference)
    By ken4capitola in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 11-19-2005, 08:40 PM

Tags for this Thread

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