CODEWITHSUNDEEP
vbaexcel
Steve Price
Steve Price

Reputation: 11

Out of stack space and value of object range errors

I am trying to sum columns with data after a change is made to the worksheet. The columns that contain data have text in Row 1.

The first time I test it, I get

Run-time error '28': Out of stack space

The second time I test it, I get

Run-time error '-2147417848 (80010108)': Method 'Value' of object 'Range' failed, and then Excel crashes.

Sub Worksheet_Change(ByVal Target As Range)

Dim LastCol As Integer
Dim NumProjects As Integer
Dim i As Integer

With ActiveSheet
    LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
End With

NumProjects = LastCol - 5

For i = 1 To NumProjects
    Range("E44").Offset(0, i).Value = WorksheetFunction.Sum(Range("E2").Offset(0, i), Range("E43").Offset(0, i))
Next i

End Sub

Upvotes: 1

Views: 357

Answers (2)

user6432984
user6432984

Reputation:

The problem that you have with your code is that you are using the Worksheet_Change event. When a user changes a value on the worksheet your Macro is put on the process stack. Then your Macro changes 20? values putting 20 copies of your Macro on the stack. Then those 20 copies change 400 values putting 400 new copies on the stack. Before you know it your out of stack space. BOOM!!! Using the Worksheet_SelectionChange might give you the desired result and won't cause the recursion that Worksheet_Change does.

It'll be more efficient to use array formulas anyway. Run this code once to insert the array formulas.

Sub insertArrayFormula()
    Application.ScreenUpdating = False
    Dim LastCol As Integer
    With ActiveSheet
        LastCol = .Cells(1, .Columns.count).End(xlToLeft).Column - 5
        Range(Cells(44, 5), Cells(44, LastCol)).FormulaArray = "=Sum($E$2:$E$43)"
    End With
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

barrowc
barrowc

Reputation: 10679

You're changing the value of a cell inside the Worksheet_Change event. This change causes the Worksheet_Change event to fire again and keeps doing so until Excel crashes.

To get round this, you need to disable and then re-enable events:

Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

' do stuff

Application.EnableEvents = True

End Sub

Upvotes: 2

Related Questions