• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Something informative

@NarayanK, Thanks for posting this as it helps confirm behaviour of certain functions. Strangely CELL("width") is returning a two element array. The second element of this array sometimes returns FALSE and sometimes TRUE. Does anyone have any ideas what this is?

I'm not sure what the purpose is but the fact that the CELL function can retun an array with a particular combination of inputs means that the usual array evaluation of arguments doesn't happen. This explains why you need the IF(1,+...) construction to force array evaluation of the second argument or for the first argument something like T(IF(1,{"width","col"})).

Several other functions behave similarly...

The INDEX function needs N(IF(1,...)) in the second or third arguments to array evaluate argumennts. INDEX can return an array when the second or third argument is zero.

The VLOOKUP / HLOOKUP functions need T(IF(1,+...)) or similar in the first argument to return an array. Somewhat like CELL, these functions can return a one element array when the third argument is a single cell reference.

It appears that what is needed to force array evaluation is to pass the array as a variant so that it doesn't get converted to a value. The IF(1,...) construction is converting the argument to a variant array and when passed to a function that takes a variant argument the array gets passed as if it was a value. This trick was discovered several years ago by piny on a chinese language forum.
 
For anyone interested, it turns out the second element of CELL("width",A1) determines whether the column is set to use the standard column width. So

=INDEX(CELL("width",A1),2)

is equivalent to Range("A1").UseStandardWidth in VBA and to GET.CELL(16,A1) (second element) in the old XLM macro language.

It's also worth noting that the method in the linked post only determines whether the column width is less than one unit which is not necessarily the same as whether the column is hidden since CELL("width",ref) returns a truncated integer. A workaround is to replace CELL("width",ref) with GET.CELL(16,ref) using a defined name formula - perhaps surprisingly the previous array formula also works with this replacement inside a defined name.
 
Back
Top