VBA: Excel crashes with a bigger amount of data

Hello everybody.

I have a problem with one of my macro: with a bigger amount of data then usual, Excel crashes without reporting the type of error.

I have isolated the part of the code which causes the matter, and it should be the following.



Sub add_zero()

    Application.ScreenUpdating = False


Dim lrt As Long
lrt = Worksheets("Records").Cells(Rows.Count, "A").End(xlUp).Row

For Each cellnum In Range("BF2:BH" & lrt)
If cellnum.Offset(0, 3).Value = "Z" Then
     cellnum.Value = "0" & Trim(Str(cellnum))
End If

Next



    Application.ScreenUpdating = True

End Sub

Open in new window


In this case the amount of data is:
lrt (number of rows) = 2731
populated columns = 32

If I divide the data by 3 (about 1000 rows), the macro works correctly.

Thank you for any suggestion.
Paolo CrossiAdministrative employeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
That's not an especially large amount of data.

If you don't get an error message, what exactly does happen?

This probably won't help but add Dim cellnum As Range
0
Martin LissOlder than dirtCommented:
Please attach your workbook with enough data to make it crash.
1
Martin LissOlder than dirtCommented:
I see the image you posted. In the future if you want someone to see an image you should click the little icon to the right of the 'CODE' icon in the area above where you type your text in a new post.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Martin LissOlder than dirtCommented:
Another thing you can try is this. After line 9 in your code add

Debug.Print lrt

Run your code (which I assume will crash) and then, after you reopen Excel, go to Visual Basic by pressing Alt+F1, and the pressing Ctri+G to open the Immediate Window. The last line in that widow should show the number of the line it was processing when it crashed. Is there anything unusual about that line?
0
NorieAnalyst Assistant Commented:
Does it make a difference if you set calculation to manual at the start and set it back to automatic at the end?
Sub add_zero()
Dim cellnum As Range
Dim lrt As Long

    lrt = Worksheets("Records").Cells(Rows.Count, "A").End(xlUp).Row

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual

    For Each cellnum In Worksheets("Records").Range("BF2:BH" & lrt)
        If cellnum.Offset(0, 3).Value = "Z" Then
             cellnum.Value = "0" & Trim(Str(cellnum))
        End If
    Next

    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True

End Sub

Open in new window

0
Paolo CrossiAdministrative employeeAuthor Commented:
Here the file.
Records.xlsx
0
Martin LissOlder than dirtCommented:
Sorry, but I need an xlsm version that contains your code.
0
Paolo CrossiAdministrative employeeAuthor Commented:
With the code.
Records.xlsm
0
Martin LissOlder than dirtCommented:
Change Str to CStr in this line

cellnum.Value = "0" & Trim(CStr(cellnum))
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roy CoxGroup Finance ManagerCommented:
Try saving as an xlsb file, which is designed for larger workbooks.
0
Martin LissOlder than dirtCommented:
Try saving as an xlsb file, which is designed for larger workbooks.
No need. While good information, my solution works.
0
Fabrice LambertFabrice LambertCommented:
Instead of iterating cell by cell (wich is slow), what about copying your cell's value in a 2D array, compute them, then transfert them back.
Sample code:
Dim wb As Excel.Workbook
Set wb = ThisWorkbook

Dim ws As Excel.Worksheet
Set ws = wb.Worksheets(1)

    '// get cells value in a 2D array
Dim data() as variant
data = ws.range("A1:F450").value

    '// perform whatever operation with the data array here


    '// transfert the array back to cells
ws.range("A1:F450").value = data

Open in new window

Side notes:
Avoid the global Worksheets collection like the pest, because it implicitly refer to ActiveWorkbook.Worksheets.
ActiveWorkbook, like any "active" object is user dependant and by nature chaotic. As a developper, you don't want to use chaotic objects.
Same syndroms with the global Range, Cells, Shapes, Charts collections, and worst with the Selection object.
0
Paolo CrossiAdministrative employeeAuthor Commented:
@ Martin Liss:
Yes, with
CStr 

Open in new window

instead of
Str

Open in new window

, it also works with bigger amount of data. So, the solution is managing the format (in this case from expression to string).

But I don't think
cellnum as range

Open in new window

 could work (I detect an error about cellnum should be variant or object).

@ Fabrice Lambert:
I'm going to test your different strategy. Thank's for the cue.
0
Martin LissOlder than dirtCommented:
Changing Dim cellnum As Range to Dim cellnum As Variant or Dim cellnum As Object is a bad idea. If you are still getting an error then it is your data and it can undoubtably be corrected with a change in the code. What's in the cell when you get the error? If it's something like #REF! then changing your For/Next loop to this will probably fix it.

For Each cellnum In Range("BF2:BH" & lrt)
    If cellnum.Offset(0, 3).Value = "Z" Then
        If Not WorksheetFunction.IsError(cellnum) Then
            cellnum.Value = "0" & Trim(Str(cellnum))
        End If
    End If

Next

Open in new window

1
Fabrice LambertFabrice LambertCommented:
Changing Dim cellnum As Range to Dim cellnum As Variant or Dim cellnum As Object is a bad idea.

Open in new window

+1
Always choose a data type adapted to your variables, and the more precise, the better.

Variant or Object data types should be choosed whenever you have no other choice.
0
Paolo CrossiAdministrative employeeAuthor Commented:
Thank's everybody.
0
Martin LissOlder than dirtCommented:
You’re welcome and I’m glad I was able to help.

If you expand the “Full Biography” section of my profile you’ll find links to some articles I’ve written that may interest you.

Marty - Microsoft MVP 2009 to 2017
              Experts Exchange Most Valuable Expert (MVE) 2015, 2017
              Experts Exchange Top Expert Visual Basic Classic 2012 to 2017
              Experts Exchange Top Expert VBA (current)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
crashing

From novice to tech pro — start learning today.