Undo Scientific (##.#E±##) Notation

Sub UndoSciNotation(objPivotTable As Object, strNewFormat As String)
'Purpose: Removes scientific notation 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 scientific notation
'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.

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

        Set objDataCells = objPivotTable.DataCellArray

        'changing to percent format will cause numeric errors!
        If strNewFormat = "##.#%" Then
                Exit Sub
        End If

        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 = "#.# ; #.##E-#" Or strFormat = "#.##E+##" Then
                                                .NumericFormatAt (lngRow,lngCol) = strNewFormat
                                        End If
                                End If
                        Next
                Next
        End With

        objPivotTable.Autofit

End Sub