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

  • Thread starter Thread starter Andy B
  • Start date Start date
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.
 
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.
 
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) -
 
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)
 
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
 
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 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...
 
Back
Top