OFFSET behaving oddly

[Guest]

In the continuing vain effort solve the old "how can I concatenate an entire
range of cells" question, I have been trying to trick certain Excel functions
by passing parameters that aren't strictly valid. In the process, I have come
up with a fluke in the OFFSET function that I cannot explain and I thought
perhaps one of you could shed some light. (Excel 2003, SP2). To reproduce
this, enter the following values in the corresponding cells:

A1: a
A2: b
A3: c

In cell B1, enter this formula (NOT array-entered):
=OFFSET($A$1,0,0,{3,2,1})

Now copy down to B2 ad B3.

The results I get for B1, B2, B3 are... a, b, c

And if I copy it down to cell B4 (or, more precisely, on any row other than
1, 2, or 3), I get #VALUE!

Yes, I know the array is an invalid [height] argument, but still,
interesting, no? Anybody have any thoughts on this?

Ryan

 

[bplumhoff]

Hi Ryan,

(Idea: http://www.mcgimpsey.com/excel/udfs/multicat.html, this one
works also on arrays)


Option Explicit


'********************************************
'Purpose: Concatenate all cells in a range or
' array
'Inputs: vP - range/array to be concatenated
' sDelim - optional delimiter to be
' inserted between text parts
'Returns: Concatenated string
'*****************************************
Function MultiCat( _
ByRef vP As Variant, _
Optional ByVal sDelim As String = "") _
As String
Dim vE As Variant
For Each vE In vP
MultiCat = MultiCat & sDelim & vE
Next vE
MultiCat = Mid(MultiCat, Len(sDelim) + 1)
End Function


HTH,
Bernd

 

[Guest]

Thanks Bernd,

I actually already have a similar concatenation udf I've written myself. I
was just trying to come up with a way of tricking Excel into doing it without
the help of a udf.

Ryan