Out of stack space and value of object range errors

Question

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

How many columns do you have? Try declaring NumProjects and LastCol As long. — findwindow
– findwindow, Commented Jun 9, 2016 at 22:25
It looks like your For loop is doing: F44 = F2 + F43; G44 = G2 + G43; ... up to NumProjects. If that's what you intended, having non-numeric data in rows 2 or 43 could cause a problem. As an aside, instead of calling WorksheetFunction.Sum you could just add the two numbers: .Value = Range("E2").Offset(0, i) + Range("E43").Offset(0, i) — xidgel
– xidgel, Commented Jun 9, 2016 at 22:50
@xidgel - I believe that the sum is a formula syntax error. The OP probably wants to sum the range of Range("E2:E43").Offset(0, i). — user4039065
– user4039065, Commented Jun 9, 2016 at 23:03
You never use the Target range. Do you want this to run every single time any cell is changed? — BruceWayne
– BruceWayne, Commented Jun 9, 2016 at 23:23

barrowc · Accepted Answer · 2016-06-09 23:06:20Z

2

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

answered Jun 9, 2016 at 23:06

barrowc

10.7k2 gold badges47 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Enigmativity Over a year ago

This works, but I think it would be worth warning the OP that this also means other events that he might be relying on won't fire either.

Steve Price Over a year ago

Thank you! I also had to change my method for finding the range to sum, but I figured it out.

user6432984 · Accepted Answer · 2016-06-09 23:24:06Z

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

Collectives™ on Stack Overflow

Out of stack space and value of object range errors

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related