visual basic excel remove scientific notation code example

Example: visual basic excel remove scientific notation

' NOTE: this function is for Microsoft Excel only (language: VBA)

'===============================================================================
'>> RemoveScientificNotation([rCellsToFormat])
'===============================================================================
' Changes numbers in the CellsToFormat from scientific notation to whole numbers
' Note: if there are letters in the range, those cells are ignored
'
' rCellsToFormat(Range):   The cells containing the numbers you want to format
'===============================================================================
Sub z_RemoveScientificNotation(rCellsToFormat As Range)

    Dim sFunct As String: sFunct = "z_RemoveScientificNotation"
    
    Debug.Print Format(DateTime.Now, "hh:mm:ss") & _
        " INFO " & sFunct & "| " & _
        "Running.."
        
    '*********************************
    ' VALIDATIONS and declarations
    '*********************************
    '(DECLARATIONS)
    Dim wbInit As Workbook: Set wbInit = ActiveWorkbook
    Dim wsInit As Worksheet: Set wsInit = ActiveSheet
    
    Dim rCurCell As Range
    
    Dim sNonNumerics As String
    
    Dim iNonNumCnt As Integer: iNonNumCnt = 0
    
    Dim sErrMsg As String
    
    On Error GoTo ErrHandling
    
    '(SETTINGS)
    
    '(VALIDATIONS)
    'A) Ensures the cells are set
    
    '--(A)
    If (rCellsToFormat Is Nothing) Then
    
        Set rCellsToFormat = Selection
    
    End If
    
    '---------------------------------
    '               WORK
    '---------------------------------
    '1) Convert the cells to whole numbers (loop through each cell)
    '   a)  Confirms that the cell is numeric
    '   b)  Changes the format of that cell
    
    'Z) Reactivate the initial workbook/worksheet
        
    '--(1)
    For Each rCurCell In rCellsToFormat
    
        '--(1.a)
        If (IsNumeric(rCurCell.Value)) Then
        
            '--(1.b)
            rCurCell.NumberFormat = "0"
            
        Else
            
            iNonNumCnt = iNonNumCnt + 1
            
            sNonNumerics = sNonNumerics & rCurCell.Address & "," & vbNewLine
        
        End If
    
    Next
    
    
    If iNonNumCnt > 0 Then
    
        sNonNumerics = Left(sNonNumerics, Len(sNonNumerics) - 3)
        
        MsgBox _
            Title:="Non-Numeric Cells Detected" _
            , Prompt:=sNonNumerics _
            , Buttons:=vbExclamation
    
    End If
    
    
    Debug.Print Format(DateTime.Now, "hh:mm:ss") & _
        " INFO " & sFunct & "| " & _
        "Successfully Completed"
    
    '--(Z)
    wbInit.Activate
    wsInit.Activate
            
    Exit Sub
    
    '-----------v-----------DEBUG INFO-----------v-----------
            
ErrHandling:
    
        Debug.Print Format(DateTime.Now, "hh:mm:ss") & _
            " INFO " & sFunct & "| " & _
            " -> Failed"
        
        MsgBox _
            Title:="Errors in the function: " & sFunct, _
            Prompt:=Err.Description _
            & vbNewLine & sErrMsg, _
            Buttons:=vbCritical
        
End Sub