Excel – Pass values instead of range to VBA Function

microsoft excelmicrosoft-excel-2007vbaworksheet-function

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?

Best Answer

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

Related Solutions

Excel – Convert to Range VBA code

This should do the same as the Convert to Range function

ActiveSheet.ListObjects("TableName").Unlist

Vba – Use of array formulas within VBA

The issue here is that the Sin function expects a single value as a parameter, not an array

Note that VBA function Sin is not the same as worksheet function Sin (and Sin is not a member of the WorkSheetFunction object, so WorksheetFunction.Average(WorksheetFunction.Sin(r)) won't work either)

If you change your udf to

Function MyFunction(r As Variant) As Variant     ' r is the range, e.g. A1:A5 as above  
    MyFunction = WorksheetFunction.Average(r)  
End Function

you will get the average of the specified range, which shows udf's can handle array data.

If you are looking for an efficient method, my advise is to stick with native functions whnever you can (array or otherwise), as a call to a udf, any udf, is much slower than native functions.

Best Answer

Related Solutions

Excel – Convert to Range VBA code

Vba – Use of array formulas within VBA

Related Question