Computing the ChiSquare

Question

I am writing a user-defined function in excel vba. So this new function:

1. takes 4 input value
2. some calculation to generate into 8 numbers. ( 2 arrays - each array has 4 numbers)
3. do a chisquare test
4. return 1 output value

Code:

Sub test()
    Dim A, B, C, D As Variant
    A = 33
    B = 710
    C = 54
    D = 656

    'Observed Value
    Dim O_A As Variant
    Dim O_B As Variant
    Dim O_V As Variant
    Dim O_D As Variant

    'Define Observer Value
    O_C_A = 'Some Calucation'
    O_C_B = 'Some Calucation'
    O_T_C = 'Some Calucation'
    O_T_C = 'Some Calucation'

    'Expected Value
    Dim E_C_A As Variant
    Dim E_C_B As Variant
    Dim E_T_C As Variant
    Dim E_T_D As Variant

    'Define Expected Value
    E_C_A = 'Some Calucation'
    E_C_B = 'Some Calucation'
    E_T_C = 'Some Calucation'
    E_T_D = 'Some Calucation'

    'Create array(2x2)
    Dim Chi_square_result As Variant

    Dim my_array(1, 1)
    my_array(0, 0) = O_C_Mesaurement
    my_array(0, 1) = O_C_Balance
    my_array(1, 0) = O_T_Measurement
    my_array(1, 1) = O_T_Balance

    Dim my_array2(1, 1)
    my_array2(0, 0) = E_C_Mesaurement
    my_array2(0, 1) = E_C_Balance
    my_array2(1, 0) = E_T_Measurement
    my_array2(1, 1) = E_T_Balance

    'Create a chi square test formula'
    Dim formula(1 To 5) As String
    formula(1) = "CHITEST("
    formula(2) = my_array
    formula(3) = ","
    formula(4) = my_array2
    formula(5) = ")"

    'Chi Square
    Chi_square_result = evaluate(Join(formula, ""))
end sub

It gives a runtime error '13', saving type mismatch. This is because of the concatenation of the formula.

Answer 1

The evaluate is expecting worksheet cell ranges. Use the Excel Application object or WorksheetFunction object to compute the function within VBA.

This proofs out.

Dim dbl As Double
Dim my_array1(1, 1)
my_array1(0, 0) = 1
my_array1(0, 1) = 2
my_array1(1, 0) = 3
my_array1(1, 1) = 4

Dim my_array2(1, 1)
my_array2(0, 0) = 2
my_array2(0, 1) = 3
my_array2(1, 0) = 4
my_array2(1, 1) = 5

dbl = Application.ChiTest(my_array1, my_array2)
Debug.Print dbl

Result from the VBE's Immediate window: 0.257280177154182.