Why am I getting a 'Value!' error in my VBA function for calculating logarithmic averages?

Question

I'm getting a #Value! error with this. The udf I am creating should return the log average of the 8 cells to the left of the cell in which you type the function. I think the error has to do with how I'm selecting the range over which to loop. Would this work in a udf?

    Set rngValues = Range.FormulaR1C1 = "(RC[-8]:RC[-1])"

I've used R1C1 to call the cells I am operating over but am unsure whether this is the best way to do it. Any helpful suggestions are welcome.

Answer 1

a) You use a UDF (User Defined Function) like any function that is part of Excel: If you want to calculate the sum of some cells, you write the formula =Sum(A1:A10). You can do exactly the same for an own function: Just write =dbax(A1:A10) (adapt to the Range that you really want to use). Excel will pass the Range A1:A10 to the function.
As written in the comments: Remove the Set rngValues = ... line. rngValues is set already, plus the syntax of the statement is invalid.

b) Assuming that your data is okay (all cells contain numeric values), the function should work. However, you are not returning the calculated result: When your function name is dbax, the last statement should be

dbax = 10 * .Log10(lSumofValues / lCountofValues)

Answer 2

If you don't want to pass the range to calculate on then you have to use Application.Caller which returns the cell range of the formula:

Dim rngFunction As Range
Set rngFunction = Application.Caller
        
Dim rngValues As Range
Set rngValues = rngFunction.Offset(, -8).Resize(, 8)

Remove parameter rngValues as Range!

Caveat of this solution: if you change any of the 8 values there the formula won't be re-calculated!.

To me it is not clear why you can't pass the range as parameter like this =dbax(A2:H2). Then the formula would re-calculate if you change any of the values.