0

I am trying to write a function in VBA that computes a time-weighted average, but the #VALUE! error appears when I try to use the function in my worksheet.

While trying to debug I have been able to validate my approach to change CurRPM by iterating CurOffset and using CurRng = rng.Offset(CurOffset, 0). The effect of changing CurRng is to iterate down the RPM column, checking if the cell under CurRng is either empty, the same as CurRPM, or non-empty and different from CurRPM.

Public Function TIMEAVERAGE(rng As Range) As Long

'Initializing Variables
Dim CurTime As Integer
Dim CurRPM As Integer
Dim CurSum As Integer
Dim CurOffset As Integer
CurOffset = 0
CurSum = 0

'The input cell is the first RPM
'The cell to the left of the input cell is the second RPM
CurTime = rng.Offset(0, -1)
CurRPM = rng

'Keep calculating as long as the cell below CurRng isn't empty
While IsEmpty(CurRng.Offset(1, 0)) = False
    'If the cell below has the same RPM, look at next cell
    If CurRng.Offset(1, 0) = CurRPM Then
        CurOffset = CurOffset + 1
        CurRng = rng.Offset(CurOffset, 0)
    Else:
        'Otherwise, add the previous RPM's contribution to the average
        'CurTime, as shown, is the start of the previous RPM
        CurSum = CurSum + CurRPM * (CurRng.Offset(0, -1) - CurTime)
        'Change CurRPM and CurTime for the new RPM
        CurRPM = CurRng.Offset(1, 0)
        CurTime = CurRng.Offset(0, -1)
        CurOffset = CurOffset + 1
        CurRng = rng.Offset(CurOffset, 0)
    End If

Wend

'Add the contributions of the final RPM
CurSum = CurSum + CurRPM * (CurRng.Offset(0, -1) - CurTime)

'Return the final TIMEAVERAGE
TIMEAVERAGE = CurSum / CurRng.Offset(0, -1)

End Function

The desired result from this program is returning 150 if the user enters =TIMEAVERAGE(B2) for the cells below. Instead, a #VALUE! error is returned.

       A       B        
 ________________    
1|    Time | RPM
2|    0    | 100
3|    1    | 100
4|    2    | 100
5|    3    | 200
6|    4    | 200
7|    5    | 200
8|    6    | 200
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
eoncarlyle
  • 1
  • 2
    A user defined function (UDF) always returns `#VALUE!` if there is an unhandled error in the function. To debug it make an additional procedure that calls `Debug.Print TIMEAVERAGE(Worksheets("MySheet").Range("B2"))` then go through the code step by step using F8 to see where you get any errors. Tell us which errors you get and where. – Pᴇʜ Jan 16 '19 at 14:13
  • 2
    `CurRng` is neither declared nor initialized – FunThomas Jan 16 '19 at 14:14
  • 1
    Please note that if `CurRng` is meant to be a `Range` object. Then `CurRng = rng.Offset(CurOffset, 0)` won't work because a UDF is **not** allowed to change other cell's values. • I recommend to activate `Option Explicit`: In the VBA editor go to *Tools* › *Options* › *[Require Variable Declaration](https://www.excel-easy.com/vba/examples/option-explicit.html)* to force proper variable declaration. – Pᴇʜ Jan 16 '19 at 14:18
  • 1
    @PEH: Probably the OP meant to `Set` the variable – FunThomas Jan 16 '19 at 14:29
  • `..a UDF is not allowed to change other cell's values...` @Pᴇʜ That is not true ;) – Siddharth Rout Jan 16 '19 at 15:08
  • I believe you are using the formula as `=TIMEAVERAGE(A2)` in Cell B2. But with `CurTime = rng.Offset(0, -1)`, your formula will give an error for Cell B2 as you are trying to access a column which is on the left of Col A which doesn't exist. – Siddharth Rout Jan 16 '19 at 15:13
  • @SiddharthRout But not without any fancy tricks. I only see the trick doing it [indirectly](https://stackoverflow.com/questions/12501759/vba-update-other-cells-via-user-defined-function). But any direct cell value change like `Cells(1, 1) = "AAAA"` will cause `#VALUE!` – Pᴇʜ Jan 16 '19 at 15:13
  • @Pᴇʜ: No Fancy Tricks.. :D A simple `.Evaluate`... You may want to see [This](https://stackoverflow.com/questions/23433096/using-a-udf-in-excel-to-update-the-worksheet) – Siddharth Rout Jan 16 '19 at 15:15
  • @SiddharthRout Well not that *fancy* but still tricked and no direct change of a cell value. To do this you first have to learn/understand that any *direct* value change will not work. – Pᴇʜ Jan 16 '19 at 15:20
  • I saw the observation made by @FunThomas and added `Dim CurRng As Range` after initializing `CurOffset` as well as `CurRng = rng` immediately before the while loop. during the debugging suggestsed by @PEH, I received this dialogue box: run-time error '91': Object variable r With block variable not set, with the `CurRng = rng` line highlighted. This error is also producted if this highlighted line is replaced with CurRng = rng.Offset(CurOffset, 0). Is this related to the second comments by @FunThomas and @PEH? – eoncarlyle Jan 16 '19 at 15:35
  • To clarify, this is the subroutine I have used for debugging:Sub `TimeAverageDebug()` `Debug.Print TIMEAVERAGE(Worksheets("Testbed").Range("B2"))` `End Sub` – eoncarlyle Jan 16 '19 at 15:37
  • 1
    If `CurRng` is a `Range` it means it is an *object* and for objects you have to use `Set` like `Set CurRng = rng`. – Pᴇʜ Jan 16 '19 at 15:41
  • 1
    I was able to fix the problem, by using @FunThomas and @PEH's advice to use `Set`. I replaced the `CurRng = rng.Offset(CurOffset, 0)` lines with `Set CurRng = rng.Offset(CurOffset, 0)`. – eoncarlyle Jan 16 '19 at 15:47

0 Answers0