When I use a button to run a macro the excel can't complete it because of few memory

2
votes

My macro set the values of a block of cells to 1 later it sets some of these cells to 0 based on the daily conditions (5232 cells total). I would like to put this macro behind a button, if I run it through the button I got the error message immediately.

Excel cannot complete this task with available resources.
Choose less data or close other applications.

Private Sub CommandButton1_Click()

Dim atado As String
Dim LastRow As Long
Dim i As Long
Dim j As Long
Dim elsoora As Long
Dim utolsoora As Long

Sheets("Maszk").Select
Range("C4", Range("HL4").End(xlDown)).Value = 1
(...)
End Sub
4
vbaexcelmemorycommandbutton
Perhaps, instead of applying the macro to your entire range of cells 228 million as Vityata pointed out you should be looking to re-think your approach and work only with the active data range instead? - Samuel Hulla
The problem with this code is that Select does not activate the sheet (Activate does). But the best solution is to avoid activation and use qualified names as some of the answers and comments suggest. - BrakNicku

4 Answers

2
votes

The code is trying to set values of 228 million cells (probably). This is quite a lot, see yourself. It is a good idea always to refer to the correct worksheet in VBA, otherwise you can get various errors.

Sub TesteMe()
    With Worksheets("SomeName")
        MsgBox .Range("C4", .Range("HL4").End(xlDown)).Cells.Count
    End With
End Sub

However, you can upgrade it a bit, by turing the Application.ScreenUpdating off. Like this: Application.ScreenUpdating = False at the beginning of the code and Application.ScreenUpdating = True at the end of the code.

0
votes

Are there any formulas pointing to that range? If yes, the re-calculation probably causes the memory issue. Set calculation to manual and stop screen updating.

Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

'run your code here
With Worksheets("Maszk") 'fully qualify your range
    .Range("C4", .Range("HL4").End(xlDown)).Value = 1
End With

Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True

Note that you always need to qualify your range to be in a specific worksheet, otherwise Excel might take the wrong worksheet. Therefor use a With statement and start your ranges with a dot. Or qualify each range like Worksheets("YourSheetName").Range(…)

0
votes

There are several things you can "switch off" to speed up code processing - ScreenUpdating, EnableEvents, Calculation. I (re)use this particular routine:

Sub xlQuiet(Optional ByVal bQuiet As Boolean, Optional ByVal sStatusMessage As String)
    On Error GoTo Terminate

    With Application
        .ScreenUpdating = Not bQuiet
        .EnableEvents = Not bQuiet
        .DisplayAlerts = Not bQuiet
        .StatusBar = bQuiet
        If bQuiet Then
            .Calculation = xlCalculationManual
            If Not sStatusMessage = "" Then .StatusBar = sStatusMessage
        Else
            .Calculate
            .Calculation = xlCalculationAutomatic
            DoEvents
        End If
    End With

Terminate:
    If Err Then
        Debug.Print "Error", Err.Number, Err.Description
        Err.Clear
    End if
End Sub

Then I call at the start / end of other routines, like this:

Sub foo()
    xlQuiet True

    With Sheets("Maszk")
        .Range("C4", .Range("HL4").End(xlDown)).Value = 1
    End With

    xlQuiet False
End Sub

Edit: note the way that the range objects are qualified to the stated sheet - so the active / selected sheet becomes irrelevant.

-1
votes

You could write the 1s one row at a time:

Application.ScreenUpdating = False
For Each rw In Range("C4", Range("HL4").End(xlDown)).Rows
    rw.Value = 1
Next
Application.ScreenUpdating = True