Pass values instead of range to VBA Function

Question

I am creating VBA functions in Excel 2007. One has a prototype:

Function ToLevelCode_Range(val As Double, ByRef R As Range)

I can call it this way just fine and it gets the correct result:

=ToLevelCode_Range(B2, Categories!D1:D4)

There is an instance, where I want to the second parameter to be a specific set of values:

=ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 })

This results in #VALUE, and I can't even get to the first breakpoint in my function.

I am programmatically generating the spreadsheet, and the number of values in the array is variable, so I can't do it with a function with 5 parameters, etc. I don't know if VBA has a variable argument list syntax, but that would be an option.

How can I get this to work?

Answer 1

ByRef means that you have to pass a reference to (the address of) an existing object as the argument, so it can work with that actual object; vs. ByVal which means you'd pass a value, and that value would be copied into the receiving argument.

So you can't pass a list of values as a reference, you need to create a Range object that holds your values, and then pass the (reference to that) object as that argument.

The problem here is that a Range object in Excel is a representation of an actual set of Cells, but I believe the NamedRange class will allow you to programmatically create a 'virtual' Range object, with virtual cells that contain your values, but I've never used it personally. :)

I believe this question over at StackOverflow is (basically) the same as yours, and worth checking out:

Microsoft Excel: Programmatically create a hidden named range

Answer 2

If you change the function parameters to

Function ToLevelCode_Range(val As Double, R As Variant)

it will accept both a range and an array

Of cource you will need to handle the different data types in the function code

When called as =ToLevelCode_Range(B2, Categories!D1:D4) R will be type Variant/Object/Range

When called as =ToLevelCode_Range(B2, { 0.38, 0.78, 1.18, 1.58 }) R will be type Variant/Variant(1 to 4)