Passing Parameters to Userdefined Functions

  • Thread starter Thread starter Peter M
  • Start date Start date
P

Peter M

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
 
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
 
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
 
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
 
Back
Top