PDA

View Full Version : Redim Multidimensional Array and perform worksheet-based calculations



fboehlandt
10-09-2008, 02:03 AM
Hi everyone,
I've got the following code to calculate the F-stat of ANOVA. I require the sumsquared of all the mean deviations. In total I have 2 by 2 by4 input parameters (16 in total). Thus, I have a three-dimensional array (Squareddev), which I need to add up:



Sub testanova()

Dim wb As Workbook
Dim ws As Worksheet
Set wb = ThisWorkbook
Set ws = wb.Worksheets("Sheet1")

With ws
Mean1 = .Range("B24")
Mean2 = .Range("B25")
Mean3 = .Range("C24")
Mean4 = .Range("C25")
Obs1 = .Range("B29")
Obs2 = .Range("B30")
Obs3 = .Range("C29")
Obs4 = .Range("C30")
End With

Result = ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)
ws.Select
Range("F30").Select
Selection.Value = Result
End Sub

Function ANOVA(Mean1, Mean2, Mean3, Mean4, Obs1, Obs2, Obs3, Obs4)

Dim SSAB, MSAB As Double
Dim Mean_AB(1 To 4)
Dim Obs_AB(1 To 4)
Dim Mean_A(1 To 2)
Dim Mean_B(1 To 2)

Mean_AB(1) = Mean1
Mean_AB(2) = Mean2
Mean_AB(3) = Mean3
Mean_AB(4) = Mean4

Obs_AB(1) = Obs1
Obs_AB(2) = Obs2
Obs_AB(3) = Obs3
Obs_AB(4) = Obs4

Mean_A(1) = (Mean1 * Obs1 + Mean2 * Obs2) / (Obs1 + Obs2)
Mean_A(2) = (Mean3 * Obs3 + Mean4 * Obs4) / (Obs3 + Obs4)
Mean_B(1) = (Mean1 * Obs1 + Mean3 * Obs3) / (Obs1 + Obs3)
Mean_B(2) = (Mean2 * Obs2 + Mean4 * Obs4) / (Obs2 + Obs4)

Mean = (Mean1 * Obs1 + Mean2 * Obs2 + Mean3 * Obs3 + Mean4 * Obs4) / (Obs1 + Obs2 + Obs3 + Obs4)

ReDim Squareddev(1 To 4, 1 To 2, 1 To 2)
For i = 1 To 4
For j = 1 To 2
For k = 1 To 2
Squareddev(i, j, k) = (Obs_AB(i)) * (Mean_AB(i) - Mean_A(j) - Mean_B(k) + Mean) ^ 2
Next k
Next j
Next i

ANOVA = WorksheetFunction.Sum(Squareddev) <--

End Function


This code fails on the line indicated. If the variable Squareddev is redimensioned for two dimensions only (like row and col), it works. What can I do to prevent error message: 'type mismatch'? Any help is greatly appreciated...

Bob Phillips
10-09-2008, 02:35 AM
SUM is not a ?d function, so it just won't work.