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