how do I pass myfunction(A2:A4) instead of myfunction("A2:A4")

A

Andy B

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.
 
J

JLGWhiz

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.
 
A

Andy B

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) -
 
C

Chip Pearson

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)
 
E

egun

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
 
E

egun

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
 
A

Andy B

Thank you.

Chip Pearson said:
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)
Click to expand...
 
Top