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

Originally Posted by RiccardoS89

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

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

function myudf(rangeinput as range)
'I can either use rangeinput as is to access individual values
'or, if I want to read the input range into a VBA variant/array
Dim arrayinput as variant
arrayinput=rangeinput.value
'do stuff with the input
myudf=output
end function

called from the spreadsheet =myudf(A1:G1)
Does that help any?

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

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.