UDF returns the same value everywhere

Question

I am trying to code in moving average in vba but the following returns the same value everywhere.

Function trial1(a As Integer) As Variant
    Application.Volatile
    Dim rng As Range
    Set rng = Range(Cells(ActiveCell.Row, 2), Cells(ActiveCell.Row - a + 1, 2))
    trial1 = (Application.Sum(rng)) * (1 / a)
End Function

My code does not seem to work. It returns the same value for all the cells. If i remove the application.volatile line, I have to manually press F2 and enter for each cell to get the correct answer. I figured the reason is that the function does not recognize there is a change in the dynamic range but do not know how to fix it. — Aashwiin Roghan, Mar 11 '16 at 02:10
I tested it and it doesnt return the same value everywhere, it seems to do what it should. The issue with your question is that we dont know exactly what you want and we should refer to your code to understand what you are doing. But your code might be wrong. — Thomas G, Mar 12 '16 at 12:22
From what I see : **1.** You select a cell, trigger the macro that call the function Trial1 and pass it an integer value, first question: how is this value fixed? Do you hardcoded it or do you take it from another cell or userinput? **2.** In the function you create a range of cells that is **fixed on Column B**, the bottom of your range is the row where the selected cell is, the top of your range is the selected cell's row minus the integer value passed to your function plus 1. Is that all correct ? — Thomas G, Mar 12 '16 at 12:22

score 7 · Answer 1 · 2016-03-12T11:17:01.060

The ActiveCell property does not belong in a UDF because it changes. Sometimes, it is not even on the same worksheet.

If you need to refer to the cell in which the custom UDF function resides on the worksheet, use the Application.Caller method. The Range.Parent property can be used to explicitly identify the worksheet (and avoid further confusion) in a With ... End With statement.

Function trial1(a As Integer) As Variant

    Application.Volatile
    Dim rng As Range
    with Application.Caller.Parent
        Set rng = .Range(.Cells(Application.Caller.Row, 2), _
                         .Cells(Application.Caller.Row - a + 1, 2))
        trial1 = (Application.Sum(rng)) * (1 / a)
    end with

End Function

You've applied the Application.Volatile¹ method but allowed the range to be averaged to default to the ActiveSheet property by not explcitly specifying the parent worksheet.

The average is computed with the Excel Application object returning a SUM function's result and some maths. The same could have been returned in one command with the worksheet's AVERAGE function but blank cells would be handled differently.

        trial1 = Application.Average(rng)

¹ _{Volatile functions recalculate whenever anything in the entire workbook changes, not just when something that affects their outcome changes.}

Just wondering why you open a bounty on the question of another person that you have answered, that's weird :-D — Thomas G, Mar 12 '16 at 11:32
I did the same one [here](http://stackoverflow.com/questions/31472816/expanding-column-cells-for-each-column-cell/31594569#31594569) and the responses was good enough to mark another one as the 'accepted answer'. This needs more exposure; it was wasting away for 2 days and the topic is a good one which deserves a good answer. (besides, I have plenty of rep and not much else to do with it) — , Mar 12 '16 at 11:37
I tend to use `Application.Volatile False` rather than `Application.Volatile` on its own. I wasn't even aware that the syntax you're using would work! Meanwhile, your point about the ActiveCell property is valid, and the most useful part of any answer to this peculiar question: I would have very little to add. — Nigel Heffernan, Mar 16 '16 at 17:58

Florent B. · Answer 2 · 2016-04-16T17:15:51.110

I believe that Application.ActiveCell is not what you should be using here. Application.ThisCell would be more appropriate assuming that "a" is the size of the subset and that the dataset is 1 column on the right. Moreover, I would simply use "WorksheetFunction.Average" instead of "Application.Sum" and I would add "Application.Volatile" so the average is recalculated whenever an update occurs on the worksheet.

So one solution to your issue would be:

Public Function Trial1(a As Integer) As Variant
  Application.Volatile
  Trial1 = WorksheetFunction.Average(Application.ThisCell(1, 2).Resize(a))
End Function

Another solution here would be to use an array formula entered with Control/Shift/Enter:

Public Function MovAvg(dataset As Range, subsetSize As Integer)
  Dim result(), subset As Range, i As Long
  ReDim result(1 To dataset.Rows.count, 1 To 1)
  Set subset = dataset.Resize(subsetSize)

  For i = 1 To dataset.Rows.count
    result(i, 1) = WorksheetFunction.Average(subset.offset(i - 1))
  Next

  MovAvg = result
End Function

And to use this array function:

Select the range where all the results will be written (should be the size of your dataset)
Type "=MovAvg(A1:A100, 2)" where A1:A100 is the source of the data and 2 the size of the subset
Press Ctrl+Shift+Enter

Good catch on [Application.ThisCell](https://msdn.microsoft.com/en-us/library/office/ff834969.aspx). Without a typedef on [Application.Caller](https://msdn.microsoft.com/en-us/library/office/ff193687.aspx), this may be the better solution. — , Mar 13 '16 at 14:51

score 3 · Answer 3 · answered Mar 14 '16 at 03:00

It's kind of strange to me for a UDF to calculate moving average given a number. If this UDF is to be used within the Worksheet, I believe you would put it next to existing data and if you want to change the size of the range for average amount, you update them manually?

Assuming you can name a Range "MovingAverageSize" to store the size of the range to calculate the average, and the average amount on the right of the existing data, consider below:

Range C2 is named MovingAverageSize
Data stored from B3 and downwards
Moving Average result is stored 1 column on the right of the data
If the data is less than MovingAverageSize, the SUM function adjusts accordingly
Any calculation error occurs with result in zero
Every time MovingAverageSize changes value, it triggers a Sub to update the formulas (Codes are placed in the Worksheet object rather than normal Module)
Alternatively, you can change the code to place the MovingAverage to same column of the MovingAverageSize, so you can have a few different size comparing next to each other.

Code in Worksheet Object:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 1 Then
        If Target.Address = ThisWorkbook.Names("MovingAverageSize").RefersToRange.Address Then UpdateMovingAverage Target
    End If
End Sub

Private Sub UpdateMovingAverage(ByRef Target As Range)
    Dim oRngData As Range, oRng As Range, lSize As Long, lStartRow As Long
    Debug.Print "UpdateMovingAverage(" & Target.Address & ")"
    If IsNumeric(Target) Then
        lSize = CLng(Target.Value)
        If lSize <= 0 Then
            MsgBox "Moving Average Window Size cannot be zero or less!", vbExclamation + vbOKOnly
        Else
            ' Top Data range is "B3"
            Set oRngData = Target.Parent.Cells(3, "B") ' <-- Change to match your top data cell
            lStartRow = oRngData.Row
            ' Set the Range to last row on the same column
            Set oRngData = Range(oRngData, Cells(Rows.Count, oRngData.Column).End(xlUp))
            Application.EnableEvents = False
            For Each oRng In oRngData
                If (oRng.Row - lSize) < lStartRow Then
                    oRng.Offset(0, 1).FormulaR1C1 = "=iferror(sum(R[" & lStartRow - oRng.Row & "]C[-1]:RC[-1])/MovingAverageSize,0)"
                Else
                    oRng.Offset(0, 1).FormulaR1C1 = "=iferror(sum(R[" & 1 - lSize & "]C[-1]:RC[-1])/MovingAverageSize,0)"
                End If
            Next
            Application.EnableEvents = True
            Set oRngData = Nothing
        End If
    End If
End Sub

Sample data and screenshots

A very thorough analysis; the methods introduced are very valid. — , Mar 19 '16 at 03:37

gavi · Answer 4 · 2016-03-19T16:37:39.763

A UDF should only access a range when it is passed as a parameter. Also, you should eliminate Application.Volatile because (1) your calculation is deterministic and not volatile, (2) Excel will re-calculate automatically your UDF whenever any cell in the input range changes, and (3) because the 'volatile' attribute in a UDF can make a model very slow so it should avoided when not necessary. So, for a moving average, the correct formula is:

Public Function SpecialMovingAverage(Rng as Excel.Range) As Double
    Dim denominator as Integer
    denominator = Rng.Cells.Count
    if Denominator = 0 then SpecialMovingAverage = 0: exit function 
    ' write your special moving average logic below
    SpecialMovingAverage = WorksheetFunction.Average(Rng) 
End Function

Note: I changed the answer following two comments because I initially did not see that the question was after a moving average (maybe the question was changed after my answer, or I initially missed the UDF's stated objective).

You made some good points; especially how you stressed that this sort of UDF should have the range to be processed passed along as a parameter. — , Mar 18 '16 at 20:00
You might want to recheck your formula, the result is incorrect for a subset > 2. You should also remove an input range, one is enough. Moreover, while it's a good practice to only use a provided range with an UDF, there's some case where it is necessary like for instance if the purpose here was to compute a weighted moving average. — Florent B., Mar 19 '16 at 04:14

MikeC · Answer 5 · 2016-03-19T15:08:05.217

I believe

Your trial1() function is in one or more cells, as a part of a formula or by itself
You want those cells to be recalculated whenever the user changes any cell on the worksheet

For this, you'd need to identify the cell where the change happened. This cell is not given by

A. ActiveCell - because that is the cell the cursor is on when the calculation starts; it could be anywhere but not on the cell that was changed

B. Application.ThisCell - because that returns the cell in which the user-defined function is being called from, not the cell that changed

The cell where the change happened is passed to the Worksheet's Change event. That event is triggered with an argument of type Range - the range that changed. You can use that argument to identify the cell(s) that changed and pass that to trial1(), possibly through a global variable (yeah, I know).

I tried this in a worksheet and it works, so let me know your results.

Good summation with fresh insights and a proposed solution. – Mar 18 '16 at 23:51 — , Mar 18 '16 at 23:51

UDF returns the same value everywhere

5 Answers5

Linked

Related