I want to define a function that takes a range in and operates on that range.
I want to use the normal Excel method of defining the range as (A2:A4), not
as a string. (If I use the string format, excel doesn't recalculate the
function when the data in the range changes)

when I define
function myfunction (inrange as Range) , the function call seems to fail.

myfunction( RANGE("A2:A4") )

"Andy B" wrote:

> I want to define a function that takes a range in and operates on that range.
> I want to use the normal Excel method of defining the range as (A2:A4), not
> as a string. (If I use the string format, excel doesn't recalculate the
> function when the data in the range changes)
>
> when I define
> function myfunction (inrange as Range) , the function call seems to fail.

Just an added comment. If you use A1 format for a range, it has to be a
string. Using the Cells format the row and column index numbers are numeric
as are the numbers in the R1C1 format. If you were referring to the $A$1
format, The $ symbol in this case indicates absolute reference, as opposed
to relative reference, and not to be confused with when it used as a type
designation character.

"Andy B" wrote:

> I want to define a function that takes a range in and operates on that range.
> I want to use the normal Excel method of defining the range as (A2:A4), not
> as a string. (If I use the string format, excel doesn't recalculate the
> function when the data in the range changes)
>
> when I define
> function myfunction (inrange as Range) , the function call seems to fail.

as usual, I wasn't too clear. I want to call the function from an excel cell
in a worksheet. In the same manner that you can use SUM(A1:A100), I want to
do myfunction(A1:A100). I am trying to build a more general concatenate
function - concatenate(A1:A100) -

"JLGWhiz" wrote:

> Just an added comment. If you use A1 format for a range, it has to be a
> string. Using the Cells format the row and column index numbers are numeric
> as are the numbers in the R1C1 format. If you were referring to the $A$1
> format, The $ symbol in this case indicates absolute reference, as opposed
> to relative reference, and not to be confused with when it used as a type
> designation character.
>
> "Andy B" wrote:
>
> > I want to define a function that takes a range in and operates on that range.
> > I want to use the normal Excel method of defining the range as (A2:A4), not
> > as a string. (If I use the string format, excel doesn't recalculate the
> > function when the data in the range changes)
> >
> > when I define
> > function myfunction (inrange as Range) , the function call seems to fail.

You could use code similar to the following:

Function Concat(RR As Range) As String
Dim S As String
Dim R As Range
For Each R In RR.Cells
S = S & R.Text
Next R
Concat = S
End Function

Then, call this from a worksheet with

=Concat(A1:A100)

Cordially,
Chip Pearson
Microsoft MVP
Excel Product Group
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)




On Wed, 26 Nov 2008 07:16:05 -0800, Andy B
<(E-Mail Removed)> wrote:

>as usual, I wasn't too clear. I want to call the function from an excel cell
>in a worksheet. In the same manner that you can use SUM(A1:A100), I want to
>do myfunction(A1:A100). I am trying to build a more general concatenate
>function - concatenate(A1:A100) -
>
>"JLGWhiz" wrote:
>
>> Just an added comment. If you use A1 format for a range, it has to be a
>> string. Using the Cells format the row and column index numbers are numeric
>> as are the numbers in the R1C1 format. If you were referring to the $A$1
>> format, The $ symbol in this case indicates absolute reference, as opposed
>> to relative reference, and not to be confused with when it used as a type
>> designation character.
>>
>> "Andy B" wrote:
>>
>> > I want to define a function that takes a range in and operates on that range.
>> > I want to use the normal Excel method of defining the range as (A2:A4), not
>> > as a string. (If I use the string format, excel doesn't recalculate the
>> > function when the data in the range changes)
>> >
>> > when I define
>> > function myfunction (inrange as Range) , the function call seems to fail.

This worked for me, assuming this is what you're trying to do. Note that it
only works for a single selection. It should be easily extended to cases
with multiple selections.
'
' Concatenate all the values in the supplied range of
' cells into a text string in the target cell
'
Public Function ConcatenateCells(InputRange As Range) As String
Dim rng As Range
Dim Cel As Range
Dim tStr As String
'
tStr = ""
'
' (below is not needed, just showing how you can
' figure out which cell contains the function call)
'
Set rng = Application.Caller
'
For Each Cel In InputRange.Cells
tStr = tStr & Cel.Text & " "
Next Cel
'
tStr = Trim(tStr)
'
ConcatenateCells = tStr
'
End Function


HTH,

Eric
-------------------------
If toast always lands butter-side down, and cats always land on their feet,
what happen if you strap toast on the back of a cat and drop it?
Steven Wright (1955 - )


"Andy B" wrote:

> as usual, I wasn't too clear. I want to call the function from an excel cell
> in a worksheet. In the same manner that you can use SUM(A1:A100), I want to
> do myfunction(A1:A100). I am trying to build a more general concatenate
> function - concatenate(A1:A100) -
>
> "JLGWhiz" wrote:
>
> > Just an added comment. If you use A1 format for a range, it has to be a
> > string. Using the Cells format the row and column index numbers are numeric
> > as are the numbers in the R1C1 format. If you were referring to the $A$1
> > format, The $ symbol in this case indicates absolute reference, as opposed
> > to relative reference, and not to be confused with when it used as a type
> > designation character.
> >
> > "Andy B" wrote:
> >
> > > I want to define a function that takes a range in and operates on that range.
> > > I want to use the normal Excel method of defining the range as (A2:A4), not
> > > as a string. (If I use the string format, excel doesn't recalculate the
> > > function when the data in the range changes)
> > >
> > > when I define
> > > function myfunction (inrange as Range) , the function call seems to fail.

P.S. - the call in my test worksheet, in cell C1, was
"=concatenatecells(A1:A26)", and concatenated the alphabet into cell C1, with
spaces between each letter.

Eric

Thank you.

"Chip Pearson" wrote:

> You could use code similar to the following:
>
> Function Concat(RR As Range) As String
> Dim S As String
> Dim R As Range
> For Each R In RR.Cells
> S = S & R.Text
> Next R
> Concat = S
> End Function
>
> Then, call this from a worksheet with
>
> =Concat(A1:A100)
>
> Cordially,
> Chip Pearson
> Microsoft MVP
> Excel Product Group
> Pearson Software Consulting, LLC
> www.cpearson.com
> (email on web site)
>
>
>
>
> On Wed, 26 Nov 2008 07:16:05 -0800, Andy B
> <(E-Mail Removed)> wrote:
>
> >as usual, I wasn't too clear. I want to call the function from an excel cell
> >in a worksheet. In the same manner that you can use SUM(A1:A100), I want to
> >do myfunction(A1:A100). I am trying to build a more general concatenate
> >function - concatenate(A1:A100) -
> >
> >"JLGWhiz" wrote:
> >
> >> Just an added comment. If you use A1 format for a range, it has to be a
> >> string. Using the Cells format the row and column index numbers are numeric
> >> as are the numbers in the R1C1 format. If you were referring to the $A$1
> >> format, The $ symbol in this case indicates absolute reference, as opposed
> >> to relative reference, and not to be confused with when it used as a type
> >> designation character.
> >>
> >> "Andy B" wrote:
> >>
> >> > I want to define a function that takes a range in and operates on that range.
> >> > I want to use the normal Excel method of defining the range as (A2:A4), not
> >> > as a string. (If I use the string format, excel doesn't recalculate the
> >> > function when the data in the range changes)
> >> >
> >> > when I define
> >> > function myfunction (inrange as Range) , the function call seems to fail.
>