Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.