2

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
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
Danhadnagy
  • 19
  • 9
  • 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 Jul 24 '18 at 09:50
  • 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 Jul 24 '18 at 10:24

4 Answers4

2

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.

Vityata
  • 42,633
  • 8
  • 55
  • 100
  • Does the screenupdate function store the data and then display everything at once instead of calculate one, display one, calculate one, display one and so on? – Mentos Jul 24 '18 at 09:43
  • 1
    @Lutscha - the screenupdate stops the screen from updating until it is turned on. Still, 228 million cells are quite a lot even with ScreenUpdating off. – Vityata Jul 24 '18 at 09:46
  • You are right thanks. But I don't understand if run the macro by F8 the result is only 5232 then I run it through the command button and it's 228 millions. – Danhadnagy Jul 24 '18 at 09:47
  • @Danhadnagy - strange indeed. What is the result in the MsgBox when you run the `TestMe()` sub from my answer? – Vityata Jul 24 '18 at 09:48
  • @Vityata *"228 million cells"* is only true if the worksheet has no values below `HL4`. Write a value in `HL5` and it's only `436` cells. – Pᴇʜ Jul 24 '18 at 09:48
  • @Pᴇʜ - yes. But with 436 the PC would not stop. And with 228 it does. – Vityata Jul 24 '18 at 09:49
  • @Vityata he said he has `5232` and even that shouldn't stop it. I tested it with more than 30000 and it didn't stop. It's probably some formulas pointing to that range that re-calculate on every value change that makes it run out of memory. – Pᴇʜ Jul 24 '18 at 09:55
  • 2
    @Vityata if I run through the button it's 228 million if I run from the editor 5232 I still don't understand why the result is different – Danhadnagy Jul 24 '18 at 09:58
  • @Peh in the table C4:HL27 all cells are filled. I just changed the command to Range("C4:HL27").Value = 1 – Danhadnagy Jul 24 '18 at 09:59
  • 1
    @Danhadnagy - from the editor the result is different, because it refers to different Worskheet parent. Make sure that you always refer to the parent worksheet, otherwise it takes the sheet in which the code is written or the activesheet. – Vityata Jul 24 '18 at 09:59
  • @Danhadnagy you need to define which sheet your range is on: `ThisWorkbook.Worksheets("YourSheetName").Range(…)` otherwise Excel might take the wrong sheet! – Pᴇʜ Jul 24 '18 at 10:01
0

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

Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
0

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.

Olly
  • 7,749
  • 1
  • 19
  • 38
-1

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
CLR
  • 11,284
  • 1
  • 11
  • 29