UDF返回相同的值到处 [英] UDF returns the same value everywhere

问题描述

 函数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）
结束函数
  

解决方案

ActiveCell属性不属于UDF，因为它会更改。有时，它甚至不在同一个工作表上。

如果您需要引用自定义UDF函数驻留在工作表上的单元格，请使用 Application.Caller 方法。可以使用 Range.Parent属性来明确标识工作表（并避免进一步混淆）在 With ... End With语句。

 函数trial1（a As Integer）As Variant 
 
 Application.Volatile 
 Dim rng As Range 
 with Application.Caller.Parent 
设置rng = .Range（.Cells（Application.Caller.Row，2），_ 
 .Cells（Application.Caller.Row  -  a + 1， 2））
 trial1 =（Application.Sum（rng））*（1 / a）
结束
 
结束函数
  

您已经应用了 Application.Volatile ¹方法，但允许将范围平均为默认值为 ActiveSh eet属性通过不明确指定父工作表。

平均值是使用 Excel Application对象返回一个 SUM功能的结果和一些数学。在一个命令中可以使用工作表的 AVERAGE函数返回同样的命令但空白单元格的处理方式不同。

  trial1 = Application.Average（rng）
  





---

¹_{当整个工作簿中的任何内容发生变化时，易失性函数将重新计算，而不是只是当影响其结果的事情发生变化时。}

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

解决方案

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.}

这篇关于UDF返回相同的值到处的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！