Forcing Inf And Nan To 0 Vb.net Excel Interop

Question

I am creating a report in my asp.net application. The report outputs to excel using the excel interop. Some calculations in the report cause excel to display "Inf" or "NaN" I want to force these calculations to zero so that the report displays zero and NOT "Inf" or "NaN"

Answer 1

Are you looking to do this on the Excel side, after the value is populated as "Inf" or "NaN"? If so, running this VBA code will replace all your non-numeric values with 0.

Sub fixValues()
    Dim areaToSearch As Range, cell As Range
    Set areaToSearch = Sheet1.Range("A1:A50")
    For Each cell In areaToSearch
        If IsNumeric(cell.Value) = False Then
            cell.Value = 0
        End If
    Next cell
End Sub