太阳帆数据 Sunfine Data

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).