[Dave Peterson[_3_]]

I'm not quite sure how an array formula using concatenate would work. I would
think that if you were concatenating strings, you'd want a string when you're
done. (And the results in just one cell.)

But say you wanted to extract just the values in a range that all started with a
lower case a.

You could do something like this:

Option Explicit
Function myArray(rng As Range) As Variant

Dim myCell As Range
Dim iCtr As Long
Dim myTempArray() As String

iCtr = -1
For Each myCell In rng.Cells
If Left(myCell.Value, 1) = "a" Then
iCtr = iCtr + 1
ReDim Preserve myTempArray(0 To iCtr)
myTempArray(iCtr) = myCell.Value
End If
Next myCell

If iCtr < 0 Then
myArray = CVErr(xlErrRef)
Else
If Application.Caller.Rows.Count = 1 Then
myArray = myTempArray
Else
myArray = Application.Transpose(myTempArray)
End If
End If

End Function

Your function returns a variant--this can include an array.

You could use this function by selecting (say) c1:c30 and putting this in C1 and
hitting shift-ctrl-enter:

=myArray(a1:a30)

Some way you build an array and then you plop it back into a bunch of cells. Or
even one cell. Try that same formula, but only select D1. Still hit
ctrl-shift-enter, but this time, hit F2, then F9 to see what's really in the
cell.

onedaywhen wrote:

I want to write my own array function (UDF). I'm talking about the
type of function that takes an array as an argument, is "array
entered" with ctrl+alt+shift and returns a single value.

Say I wanted to write an array version of the native worksheet
function CONCATENATE which is *NOT* an array function i.e. array
entering has not effect:

{=CONCATENATE(A1:A9)}

would only return A1.

So here's a UDF that will do the job:

Public Function MyConcatenate(ByRef Rng As Variant) As String

Dim strConcat As String
Dim rngCell As Range

For Each rngCell In Rng

strConcat = strConcat & rngCell.Text

Next

MyConcatenate = strConcat

End Function

It works for a range of cells but it clearly isn't an array formula.

At this point I realised that, although I know how to use one, I don't
know how to write one so really I don't know what an array formulas
is!

Can anyone explain the internal workings of an array formula? It would
greatly help my understanding if someone could post an array version
of the CONCATENATE function.

Many thanks.

--

Dave Peterson