CODEWITHSUNDEEP
excelvariancevba
Minh Phúc Huỳnh
Minh Phúc Huỳnh

Reputation: 31

Calculate variance for array in VBA

I have a trouble with VBA. I need to calculate variance for array. But array in for loops, and if I use Next i it will increment i by 1. So I always have to face with error messages from 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 Sub checks my 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

Upvotes: 0

Views: 7692

Answers (2)

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15561

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

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

Upvotes: 0

Alexander Bell
Alexander Bell

Reputation: 7918

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

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.

Hope this will help.

Upvotes: 2

Related Questions