Excel VBA occasional 'Type mismatch' in extensive for loop

Question

I have extensive nested for loops that would take about 3 hours to run through to the end.

Inside the loop I write and read to sheet cells.

But every 15 mins (pretty regular frequency) I get a type mismatch error.

The code is too extensive to post in its entirity here but this is a representative snip of the types of operations I am doing on it:

    Set msDesign = msApp.Design
    Dim base_x(1 To 5) As Double, base_y(1 To 5) As Double, base_z(1 To 5) As Double
    '================================================================
    '======================== Apply vars section ====================
    '================================================================
    ' Measure Hull
    MeasureHull (fname)
    
    ' Read Master Section Points ================== HARDCODED =============
    GetPoints
    For ro = 1 To 3
        base_x(ro) = Module1.CtrlPointX(1, ro, 3)
        base_y(ro) = Module1.CtrlPointY(1, ro, 3)
        base_z(ro) = Module1.CtrlPointZ(1, ro, 3)
    Next ro
    
    ' Calculate other section points ==================== HARDCODED ============
    For cl = 1 To 2
        For ro = 1 To 3
            x_norm = Module1.CtrlPointX(1, ro, cl) / base_x(ro)
            Sheet3.Cells(9, 2 + cl) = x_norm
            yscale = Sheet3.Cells(10, 2 + cl)
            zscale = Sheet3.Cells(11, 2 + cl)
            Debug.Print ("Debug - fname =" & Right(fname, 20) & "  / yscale= " & yscale & "  / base_y= " & base_y(ro) & "  / zscale= " & zscale)
            msDesign.Surfaces(1).SetControlPoint ro, cl, Module1.CtrlPointX(1, ro, cl), base_y(ro) * yscale, (base_z(ro) - Range("_base_draft")) * zscale + Range("_base_draft")
        Next ro
    Next cl
    ' Rescale the surfaces in y
    yscale2 = Sheet3.Cells(13, 3)
    msDesign.Surfaces(1).ReScale 1, yscale2, 1, 0, 0, Range("_base_draft")
    
    CorrectFB

If I enter debug mode and press play the code continues to run without errors for the next 15 mins.

My goal is to run through the loop, without skipping any iterations and without stopping due to type mismatch errors.

There is a very similar issue here: Excel VBA: Type Mismatch

I suppose the error is caused by an excel sheet value returning an N/A error as in the reference question. Instead of just failing an IF statement and bypassing that iteration as proposed on that question I would like to deal with the issue and run through that iteration without the need for my input on the middle of the loop.

Handling `#N/A` errors is [well-documented](https://stackoverflow.com/questions/5143402/checking-for-n-a-in-excel-cell-from-vba-code) on SO. — BigBen, Mar 03 '22 at 14:21
Sure Ben, I believe the reference question I included also provides a solution for 'handling' the error. I suppose my question is if there is a solution that does not involve skipping the calculation. There is nothing wrong on the sheet by the time I enter debug mode and the error is gone. I am assuming its N/A but who knows, its an intermitent issue. — Thiago Marinho, Mar 05 '22 at 10:47

Excel VBA occasional 'Type mismatch' in extensive for loop

0 Answers0