#VALUE error for ranges in subprograms

[MODELXLS]

I have an error when handling ranges. I have a function that returns a calculated value, like this

public function varcalc(a as range, b as range) as double
'description:
'read range a
'perform calculations
'write partial results to range b
'calculate variance and return as varcalc
end function

The function works very well when called from a macro. However, I also need to use it in a cell by calling

=varcalc(selected range a, selected range b)

The functions reads the range a properly but comes with the #VALUE error upon writing to range b.

I cannot figure out where is the problem, maybe with the scope or permissions? Any help would be appreciated. Thank you

 

[Norie]

You can't use a UDF to write to a range.

 

[MODELXLS]

Thank you Norie for such a quick reply. What you wrote may indicate a significant limitation of Excel programming. The reason of using the ranges in the first place was that the native functions MMULT and MINVERSE, which I employed, work on ranges. I am a new convert from a high-level language and am straggling with the limitation of Excel for numerical modeling.

 

[Joe4]

I don't think there is any limitation here, I think you are just using the wrong tool.
UDFs are link any other native Excel functions, they can only return a value.
If you want to do something to affect a range, you would not use a Function (UDF), you would use a Sub Procedure instead (sometimes called a "Macro" by people).

 

[MODELXLS]

Thank you Joe4. You are right. I have already verified it works from a macro, as I stated in my original question. The reason I wanted to call also as =UDF was that I was using it in the SOLVER, which needs a physical cell on a worksheet.

 

[Joe4]

Note that you can call UDFs from Procedures (Macros).
So you can use them in combintaion with each other.