Combine mean and SD

 

Download as .sbs Shows as plain text

 

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
'Begin Description 
'Purpose: To combine the Means and SD numbers into the same column as Means ± SD
'Assumptions: The currently designated Output Window contains at least one such Table. 
'The Means And SD column are assumed To be the last 2 columns of the table.
'This script is called from syntax by using a line such as:
'SCRIPT "path\CombineMeanAndSD.SBS".
'Raynald Levesque rlevesque@videotron.ca 2000/10/09
'End description

Option Explicit

Sub main()

' Declare object variables
Dim objOutputDoc As ISpssOutputDoc
Dim objOutputItems As ISpssItems
Dim objOutputItem As ISpssItem
Dim objPivotTable As PivotTable

'Continue the program only if an output document exists.
If objSpssApp.Documents.OutputDocCount > 0 Then
'Get the currently designated output document items collection.
	Set objOutputItems = objSpssApp.GetDesignatedOutputDoc.Items
	Else
	MsgBox "There are no Output window!"
    Exit Sub
End If


Dim intItemCount 	As Integer      	    'number of output items
Dim intItemType 	As Integer  	        'type of item (see SpssType property)
Dim strLabel 		As String 	            'Item label
Dim intIndex 		As Integer

' Iterate through output items
' If type = PivotTable, call CombineMeanAndSD
	intItemCount = objOutputItems.Count()
	For intIndex = 0 To intItemCount - 1
		Set objOutputItem = objOutputItems.GetItem(intIndex)
		intItemType = objOutputItem.SPSSType()
		strLabel = objOutputItem.Label

		If intItemType = SPSSPivot Then
			Set objPivotTable = objOutputItem.Activate()
			Call CombineMeanAndSD(objPivotTable)
			objOutputItem.Deactivate
		End If
	Next
End Sub


Sub CombineMeanAndSD(objPivotTable As Object) 
'Purpose: 
'Assumptions: The Pivot Table that is to be modified is activated, and strNewFormat is a valid format string
'Effects: If Means and Std Deviations are the last 2 columns of the table, then combine them
' 			hide the former Std Deviation column, else do nothing 
'Inputs: PivotTable object that is already activated
'Return Values: Modified Pivot Table
		
	Dim lngRow 		As Long, lngCol As Long	
	Dim objDataCells As ISpssDataCells
	Dim ObjColumnLabels As ISpssLabels
	Dim lngNumRows As Long
	Dim lngNumColumns As Long
	Dim strTemp 	As String
	Set objDataCells = objPivotTable.DataCellArray
	Set ObjColumnLabels=objPivotTable.ColumnLabelArray() 
	On Error GoTo errHandler

	' Get the column labels object and the number of columns
	Set objColumnLabels = objPivotTable.ColumnLabelArray
	lngNumColumns = objColumnLabels.NumColumns
	
	' ColumnLabelArray is a 2-dimensional array. Test whether the last 2 labels
	' are Mean and Std Deviation
	If (objColumnLabels.ValueAt(1,lngNumColumns - 2)&(objColumnLabels.ValueAt(1,lngNumColumns - 1))="MeanStd Deviation") Then 
		With objDataCells
				For lngRow = 0 To .NumRows - 1 
					strTemp=Int(.ValueAt (lngRow, .NumColumns - 2)+.5) & " ± " & Int(.ValueAt (lngRow, .NumColumns - 1)+.5)
					.ValueAt (lngRow, .NumColumns - 2) = strTemp 
				Next
		End With
		objColumnLabels.ValueAt(1,lngNumColumns - 2)="   Mean ± SD   "

		'Hide SD label and data column
		objColumnLabels.HideLabelsWithDataAt(1, lngNumColumns - 1)
	End If
	objPivotTable.Autofit
Exit Sub
errHandler:
	Debug.Print "err= ";Err.Number; " Description= ";Err.Description
	Resume Next
End Sub
Related pages

...

Navigate from here