Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?

share|improve this question

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

share|improve this answer
Your googling skills belies your programming instincts. – surfasb Dec 15 '11 at 22:14
@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. ;) – Ƭᴇcʜιᴇ007 Dec 16 '11 at 1:23
Yeah, I probably should of made the statement "... belies your lack of progamming instincts:)" The contradiction would of made the irony more apparent. – surfasb Dec 16 '11 at 5:15

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)

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .