What data type does the excel offset function returns ?

[ps_yannis]

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]

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.]

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."