VBA function for multiple comparison
In SAS, lsmeans treatment /pdiff of PROC mixed or GLM output mean values and a matrix of probability values of all pair wise comparison. However, we have to create the traditional comparison table by ourselves. In order to automate the process, I developed a VBA function to do the job. The source code is the following. There are three parameters as the input of the function. The first one is a column vector. The second parameter is a square matrix that includes probability values of all pairwise comparison. The third one is optional, it is the significant level, with a default value of 0.05. The results will be a column vector with a equal length of the input mean vector. If you do not know how to use vector and matrix in Excel, please look at Excel help and learn it. It is very useful when you work with array, matrix, and vector. The basic operation is select continuous cells of a column or a row and enter a formula and then press down SHIFT key and ENTER key at same time. In this way, we are working with a function that operate a vector or a matrix.
Option Explicit Option Base 1 Function MultComp (subIndex As Object, Pmatrix As Object, dptional alpha As Double = 0.05) Dim myresult ( ) Dim NumSub As Integer Dim Subj ectl, Subj ect2, pValue NumSub = UBound(subIndex(), 1) - LBound(subIndex(), 1) + 1 Dim Px, Py Px = UBound(Pmatrix(), 1) - LBound(Pmatrix(), 1) + 1 Py = UBound(Pmatrix() , 2) - LBound(Pmatrix ( ) , 2) + 1 If Px <> Py Then MsgBox "The matrix is not synmatric" Exit Function End If If Px <> NumSub + 1 Then MsgBox "The size of matrix is not compatible with the number of subjects" Exit Function End If 'Do comparison Dim i, j, k, l, startletter ReDim myresult (NumSub, 1) For i = 1 To NumSub myresult (i, 1) = "" Next k = 1 i = 1 If alpha <= 0.01 Then startletter = 64 Else startletter = 96 While (i <= NumSub) If i = 1 Then myresult(1, 1) = myresult(1, 1) & Chr(start1etter + k) Subject1 = subIndex (i) For l = i - 1 To 1 Step -1 Subject2 = subIndex (l) pValue = Val (Pmatrix(Subject1 + 1, Subject2 + 1) ) If pValue <= alpha Then Exit For Else myresult (l, 1) = myresult (l, 1) & Chr (startletter + k) End If Next For j = i + 1 To NumSub Subject2 = subIndex ( j ) pValue = Val (Pmatrix(Subject1 + 1, Subject2 + 1) ) If pValue <= alpha Then k = k + 1 myresult (j, 1) = myresult (j, 1) & Chr (startletter + k) i = j - 1 Exit For Else myresult (j, 1) = myresult (j, 1) & Chr (startletter + k) If j = NumSub Then i = j + 1 End If Next i = i + 1 Wend MultComp = myresult End Function
Tip for the function: In this function, we use matrix and vectors. The returned result is in an column vector. In the function we have to declare a two dimension array with the second index as 1. If the returned vector is in a row, we only need to declare a one dimension array. If a function returns a matrix, you have to declare a two dimension array, like dim res(x,y).
[...] VBA function for multiple comparison [...]