VBA, Excel keeps crashing

[jrwrita]

As I am stepping through this section of the code it works but by the time it gets to a certain row it crashes my excel.
Is there a better way to write to my excel sheet? It writes a value cell by cell, not sure why its crashing!

 For j = 1 To nCols
            If fromTop Then writeVal = arr(i, j) Else writeVal = arr(UBound(arr, 1) - i + 1, j)
            thisWS.Cells(startRow + i - 1, startCol + j - 1).Value = writeVal
        Next j

 

[hotabae]

My first thoughts to debugging something like this is to figure out where it keeps crashing.

Add a debug print line like this and keep an eye on your immediate window while stepping to see ~roughly where the program crashes

 For j = 1 To nCols
            If fromTop Then writeVal = arr(i, j) Else writeVal = arr(UBound(arr, 1) - i + 1, j)
            thisWS.Cells(startRow + i - 1, startCol + j - 1).Value = writeVal
            [COLOR=#ff0000]Debug.Print "i: " & i & " j: " & j[/COLOR]
        Next j

Once you know which line/iteration is crashing, step right up until the line before the problem and then check all the variable values (either with immediate window or hovering mouse over the variables). From there, you can hopefully figure out what should happen on the "problem line".

 

[jrwrita]

Thanks alot for this, seems like everytime im stepping through the code the problem line can be on different lines,
one time it iwll be i23 j18 and something else the next time

 

[hotabae]

Do you have the standard optimization code floating around somewhere?

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = FALSE

I'd also be curious to know if there was a correlation between how fast you step through and how far you are able to progress

I.e. - If you step through really fast, the last "i" value is smaller than if you had stepped slowly.

(This was the case for me a while back and I never figured out the cause)

Also... as an extra check, it's prolly not a bad idea to restart the PC

 

[jrwrita]

Its kind of interesting, as I am stepping through each next J im getting quite far...then when I just run the whole thing, crash.
I put your Calc manual and turn off screen updating within the if statement, still crashing.
Any other ideas? Thanks so much for ideas

 

[hotabae]

I've had troubles like this in the past, but the only reasoning behind it was that stepping through slowly gave excel more time to process the operations.

Running, rather than stepping caused a buildup in the process queue and then it would crash. As far as fixing it goes, I'm afraid I'm mostly out of ideas.

How did you assign/build the Arr() array? Did you write a range to it?

 

[jrwrita]

The sub is like so, arr() is a variable I assigned as variant ,

Public Sub myexcel (arr() As Variant, startCell As Range, fromTop As Boolean, nRows As Long, nCols As Long)
   Dim i As Long, j As Long, startRow As Long, startCol As Long
    Dim thisWS As Worksheet
    Dim writeVal As Variant
    
    Set thisWS = startCell.Worksheet
    
    startRow = startCell.Row
    startCol = startCell.column
    
    'clear
    For i = 1 To nRows
        For j = 1 To nCols
            thisWS.Cells(startRow + i - 1, startCol + j - 1).Value = ""
        Next j
    Next i
    
    'write
    For i = 1 To WorksheetFunction.Min(nRows, UBound(arr, 1))
        For j = 1 To nCols
            If fromTop Then writeVal = arr(i, j) Else writeVal = arr(UBound(arr, 1) - i + 1, j)
            thisWS.Cells(startRow + i - 1, startCol + j - 1).Value = writeVal
        Next j
    Next i