What data type does the excel offset function returns ?

ps_yannis · Mar 27th, 2002, 11:31 AM

I am am trying to write a custom vba (a=excel) funtion with the following syntax : 'Choose_PVC_Speed(Service as String, Access_port as integer) as ???' .. so that when I apply Data Validation to a cell by entering to the Validation List Source field the above function i.e "=Choose_PVS_Speed(A1;B1)" it will provide me with a conditional range of values in that particular cell with a pull down menu type of action whose values are determined by the passed variables A1="Service" and B1="Access_Port". What data type should the function be defined as ? I have a hunch that it should return something simillar to the standard excel offset function, which I got it working for a limited number of inputs (unfortunately got the nested if limitation kicking in), but if only I knew what that is

Note also that (preferebly) the full set of values that I want to choose from via the pull down menu is in a different worksheet of the same workbook.

Thanks in advance

Damon Ostrander · Mar 27th, 2002, 02:45 PM

Hi Ps_yannis,

If I understand your problem correctly (and I'll have to admit I'm not sure I do), you want your UDF to return an array of values, which is the array (range of cells) to be used as the list in a cell validation input list so that it becomes the cell's dropdown data entry list. If this is correct, the function must be typed As Variant, and you should assign an array (presumably of strings) to the function name before the end of the function. Then use the function to fill the worksheet range by entering the function as an Excel Array function, using Ctrl-Shift-Enter.

Mark W. · Mar 28th, 2002, 12:07 PM

To quote the Excel Help topic for the OFFSET worksheet function...

"Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned."

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 27th, 2002, 11:31 AM	#1
ps_yannis New Member Join Date: Mar 2002 Location: Greece Posts: 1	I am am trying to write a custom vba (a=excel) funtion with the following syntax : 'Choose_PVC_Speed(Service as String, Access_port as integer) as ???' .. so that when I apply Data Validation to a cell by entering to the Validation List Source field the above function i.e "=Choose_PVS_Speed(A1;B1)" it will provide me with a conditional range of values in that particular cell with a pull down menu type of action whose values are determined by the passed variables A1="Service" and B1="Access_Port". What data type should the function be defined as ? I have a hunch that it should return something simillar to the standard excel offset function, which I got it working for a limited number of inputs (unfortunately got the nested if limitation kicking in), but if only I knew what that is Note also that (preferebly) the full set of values that I want to choose from via the pull down menu is in a different worksheet of the same workbook. Thanks in advance

Mar 27th, 2002, 02:45 PM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Ps_yannis, If I understand your problem correctly (and I'll have to admit I'm not sure I do), you want your UDF to return an array of values, which is the array (range of cells) to be used as the list in a cell validation input list so that it becomes the cell's dropdown data entry list. If this is correct, the function must be typed As Variant, and you should assign an array (presumably of strings) to the function name before the end of the function. Then use the function to fill the worksheet range by entering the function as an Excel Array function, using Ctrl-Shift-Enter.

Mar 28th, 2002, 12:07 PM	#3
Mark W. MrExcel MVP Join Date: Feb 2002 Location: Austin, Texas USA Posts: 11,654	To quote the Excel Help topic for the OFFSET worksheet function... "Returns a reference to a range that is a specified number of rows and columns from a cell or range of cells. The reference that is returned can be a single cell or a range of cells. You can specify the number of rows and the number of columns to be returned."