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

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


228 million

Select

Activate

5 Answers
5

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.

Application.ScreenUpdating

Application.ScreenUpdating = False

Application.ScreenUpdating = True

I changed this line

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


to

Range("C4:HL27").Value = 1


but it's still mistery for me why the macro calculates different if I use commandbutton

ThisWorkbook.Worksheets("YourSheetName").Range(…)

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(…)

With

Worksheets("YourSheetName").Range(…)

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

ScreenUpdating

EnableEvents

Calculation

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.

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


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.