Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Passing several parameters to OnAction property. | Excel Discussion (Misc queries) | |||
passing arrays to user defined functions | Excel Worksheet Functions | |||
Passing parameters Cel Ref) to Sum function | Excel Discussion (Misc queries) | |||
Arrays parameters in Functions | Excel Worksheet Functions | |||
Passing Parameters through OnAction | Excel Programming |