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?

Solution

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

Comments

[surfasb]

Your googling skills belies your programming instincts.

[Ƭᴇcʜιᴇ007]

@surfasb You know, it took me until now to finally grasp what you meant by that, and how to take that, even after I asked you. :) In this case, my semi-competent googling skills were used to reassure myself I (still) understood pointers in VBA, and to fill out the answer with links. ;)

[surfasb]

Yeah, I probably should of made the statement "... belies your lack of progamming instincts:)" The contradiction would of made the irony more apparent.