在 VBA 中计算数组的方差

Calculate variance for array in VBA

提问by Minh Phu?c Huy?nh

I have a trouble with VBA. I need to calculate variance for array. But array in for loops, and if I use Next iit will increment iby 1. So I always have to face with error messages from VBA.

我在使用 VBA 时遇到了麻烦。我需要计算数组的方差。但是for循环中的数组，如果我使用Next i它会增加i1。所以我总是不得不面对来自VBA的错误消息。

Here is my code:

这是我的代码：

Function cal_var(dta As Variant)
Dim N, i, j As Integer
Dim tre() As Double
N = UBound(dta)
Dim vec_var() As Integer
ReDim vec_var(1 To N)
For i = 1 To N
    j = 1
    ReDim tre(1 To i)
    For j = 1 To i
        tre(j) = dta(j)
    Next j
    vec_var(i) = Application.WorksheetFunction.Var_P(tre)
Next i
cal_var = vec_var
End Function

The following Subchecks my function

以下Sub检查我的功能

Sub test()
Dim b(1 To 5) As Integer
Dim a As Double
b(1) = 1
b(2) = 2
b(3) = 3
b(4) = 4
b(5) = 5
a = cal_var(b)
MsgBox a
End Sub

回答by Alexander Bell

Based on the task description, a simple VBA formula will return a variance of the array (bas per your sample):

根据任务描述，一个简单的 VBA 公式将返回数组的方差（b根据您的示例）：

Function cal_var(dta As Variant)
    cal_var = Application.WorksheetFunction.Var_P(dta)
End Function

Sub test()
Dim b(1 To 5) As Integer
Dim a As Double
b(1) = 1
b(2) = 2
b(3) = 3
b(4) = 4
b(5) = 5
a = cal_var(b)
MsgBox a
End Sub

It correctly returns the value of 2.

它正确返回值 2。

Hope this will help.

希望这会有所帮助。

回答by sancho.s ReinstateMonicaCellio

Your subs contain a number of type mismatches, other errors, and unnecessary code.

您的 subs 包含许多类型不匹配、其他错误和不必要的代码。

If I follow you, you mean to obtain a sequence of variances, by partially including elements of your array. For that task, check code below.

如果我跟随您，您的意思是通过部分包含数组元素来获得一系列方差。对于该任务，请检查下面的代码。

' See http://www.cpearson.com/excel/passingandreturningarrays.htm

Function cal_var(dta() As Integer) As Double()
    Dim N, i, j As Integer
    Dim tre() As Double
    N = UBound(dta)
    Dim vec_var() As Double
    ReDim vec_var(1 To N)
    For i = 1 To N
        ReDim Preserve tre(1 To i)
        tre(i) = dta(i)
        vec_var(i) = Application.WorksheetFunction.Var_P(tre)
    Next i
    cal_var = vec_var()
End Function

Sub test()
    Dim b(1 To 5) As Integer
    Dim a() As Double
    b(1) = 1
    b(2) = 2
    b(3) = 3
    b(4) = 4
    b(5) = 5
    a = cal_var(b)

    ' See http://www.mrexcel.com/forum/excel-questions/562103-display-array-msgbox.html
    Dim myArray() As Variant
    Dim txt As String
    Dim i As Long
    For i = LBound(a) To UBound(a)
        txt = txt & a(i) & vbCrLf
    Next i
    MsgBox txt
End Sub

See

看

Passing And Returning Arrays With Functions

使用函数传递和返回数组

Display Array in MsgBox

在 MsgBox 中显示数组