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
Sub UndoPercent(objPivotTable As Object, strNewFormat As String)
'Purpose: Removes percent format in data cells.
'Assumptions: The Pivot Table that is to be modified is
'activated, and strNewFormat is a valid format string
'Effects: Changes the format of the data cells to
'strNewFormat from percent
'Inputs: PivotTable object that is already activated,
'new numeric format (strNewFormat)
'Return Values: Modified Pivot Table

'See SPSS Objects Help topic
' "String Description of Numeric Formats"
'or experiment with GetFormatString.
'Posted by John Bauer to SPSSX-L on Jan 23,2001 

  Dim strFormat As String
  Dim lngRow As Long, lngCol As Long
  Dim objDataCells As ISpssDataCells

  Set objDataCells = objPivotTable.DataCellArray

  With objDataCells
    For lngRow = 0 To .NumRows - 1
      For lngCol = 0 To .NumColumns - 1
        If Not IsNull (.ValueAt (lngRow, lngCol)) Then
          strFormat = .NumericFormatAt (lngRow, lngCol)
          If strFormat = "##.#%" Then
            .NumericFormatAt (lngRow, lngCol) = strNewFormat
            'convert to proportions (divide by 100):
            'remove the following line if unwanted
            .ValueAt(lngRow, lngCol) = CStr$(.ValueAt(lngRow, lngCol)/100)
          End If
        End If
      Next
    Next
    End With
  objPivotTable.Autofit

End Sub