Passing Parameters to Userdefined Functions
 

I want to create a userdefined function that will be assigned to parameters:
1. a cell containing a string, 2. a range of cells containing a variety of
data. The contents of the string will be parsed and then compared with the
range of data. The actual processing is not the problem, and I can easily
create the passing mechanism for the string variable, but I dont know how to
pass an area of the spreadsheet to the function. I have tried something
like:

function dosomething(names as string, paramarray DataArea)
code here
end func

It compiles and seems to work, but nothing appears in the DataArea variable
(or if it does I cant access it!).

Any ideas gratefully received.

Peter Morris

Passing Parameters to Userdefined Functions
 

Assuming your range is a contiguous range of cells:

Function Dosomething(names as string, rng as Range)
Dim cell as Range
for each cell in rng
if cell.value = names then


--
Regards,
Tom Ogilvy

"Peter M" wrote in message
...
I want to create a userdefined function that will be assigned to
parameters:
1. a cell containing a string, 2. a range of cells containing a variety of
data. The contents of the string will be parsed and then compared with
the
range of data. The actual processing is not the problem, and I can easily
create the passing mechanism for the string variable, but I dont know how
to
pass an area of the spreadsheet to the function. I have tried something
like:

function dosomething(names as string, paramarray DataArea)
code here
end func

It compiles and seems to work, but nothing appears in the DataArea
variable
(or if it does I cant access it!).

Any ideas gratefully received.

Peter Morris

Passing Parameters to Userdefined Functions
 

Tom,

Thanks for that. I am now trying to use the data passed, and am struggling
on two and I suspect related counts. I firstly tried to define an array to
hold the data, but couln't extract the number of columns or rows in the
range.

Using the variables from your example, I tried:

rng.rows
rng.columns

but these appear to fail. How do I refer to individual cells within the
range, would rng.cells(1,3) find the first row, third column for example?

Grateful for any advice.

Peter M
"Tom Ogilvy" wrote in message
...
Assuming your range is a contiguous range of cells:

Function Dosomething(names as string, rng as Range)
Dim cell as Range
for each cell in rng
if cell.value = names then


--
Regards,
Tom Ogilvy

"Peter M" wrote in message
...
I want to create a userdefined function that will be assigned to
parameters:
1. a cell containing a string, 2. a range of cells containing a variety
of
data. The contents of the string will be parsed and then compared with
the
range of data. The actual processing is not the problem, and I can
easily
create the passing mechanism for the string variable, but I dont know
how
to
pass an area of the spreadsheet to the function. I have tried something
like:

function dosomething(names as string, paramarray DataArea)
code here
end func

It compiles and seems to work, but nothing appears in the DataArea
variable
(or if it does I cant access it!).

Any ideas gratefully received.

Peter Morris

Passing Parameters to Userdefined Functions
 

rng.rows.count

rng.columns.count

to refer to a specific cell

rng(1,1) would be first row, first column

rng(2,10) would be second row, first column

rng(rng.rows.count,rng.columns.count) would be the lower right

Note that this method of address is anchored to the upper left corner of the
range - so you can refer to cells beyond the range defines

? Range("A1:B4")(5,15).Address
$O$5

As an example.

Putting in Cells is equivalent:

? Range("A1:B4").Cells(5,15).Address
$O$5


--
Regards,
Tom Ogilvy


Peter M wrote in message
...
Tom,

Thanks for that. I am now trying to use the data passed, and am
struggling
on two and I suspect related counts. I firstly tried to define an array
to
hold the data, but couln't extract the number of columns or rows in the
range.

Using the variables from your example, I tried:

rng.rows
rng.columns

but these appear to fail. How do I refer to individual cells within the
range, would rng.cells(1,3) find the first row, third column for example?

Grateful for any advice.

Peter M
"Tom Ogilvy" wrote in message
...
Assuming your range is a contiguous range of cells:

Function Dosomething(names as string, rng as Range)
Dim cell as Range
for each cell in rng
if cell.value = names then


--
Regards,
Tom Ogilvy

"Peter M" wrote in message
...
I want to create a userdefined function that will be assigned to
parameters:
1. a cell containing a string, 2. a range of cells containing a
variety
of
data. The contents of the string will be parsed and then compared
with
the
range of data. The actual processing is not the problem, and I can
easily
create the passing mechanism for the string variable, but I dont know
how
to
pass an area of the spreadsheet to the function. I have tried
something
like:

function dosomething(names as string, paramarray DataArea)
code here
end func

It compiles and seems to work, but nothing appears in the DataArea
variable
(or if it does I cant access it!).

Any ideas gratefully received.

Peter Morris