ActiveCell VBA bugs out and crashes Excel completely

Question

I cannot figure out why this happens (it only happens intermittently, but always with the same function)...

I am inserting a few copied rows wherever the user happens to be, and checking if a page break is necessary between them.

I managed to recover Excel after it crashed one time, and found it stalling on a line with an ActiveCell reference. I apostrophe'd the line out, and the code continued successfully line by line to the next ActiveCell reference.

I reopened the template file with the code in it, and it worked just fine.

I know it's bad practice to use ActiveCell, but I don't know how to get around it in this case - I need to add the rows right where the user is.

Should I do something like this?

Dim R As Range    
Set R = ActiveCell.Address

Will that keep the original ActiveCell address or will it dynamically update as the code runs and the ActiveCell changes?

Your help is much appreciated!

[Edit]: Code (please excuse untidiness it's in development):

    Sub InsertArea()

    'Dimension variables
    Dim SR
    Dim Rng2 As Range
    Dim i, j, PB1 As Integer
    Dim Crit() As String
    Dim w As Worksheet

    i = 2
    j = 0
    PB1 = 0
    Set Rng = Nothing

    'NEW PAGE

    'Check for page height breach

    'Assign PB1 to selection row
    PB1 = Selection.Row

    'Reset i to 1
    i = 1

    'Loop how many extra blank rows you want below the bottom spec on a page
    Do Until i = 17

        'If there's a page break above row i
        If Rows(PB1).Offset(i, 0).EntireRow.PageBreak <> xlPageBreakNone Then

            'Copy blank row
            Range("A1000:A1006").EntireRow.Copy

            Selection.EntireRow.Insert Shift:=xlDown

            'Insert page break just above the new area
            Rows(PB1).Offset(4, 0).PageBreak = xlPageBreakManual

            Selection.Offset(7, 0).Select

            i = 17

        Else

            'Increment i to prevent infinite loop
            i = i + 1

        End If

    Loop




    'INSERT NEW AREA


    'Copy blank new area
    ActiveWorkbook.Names("Temp_NewArea").RefersToRange.EntireRow.Copy

    'Paste (insert) that line by shifting cell up, so target cell remains in the new blank row


BUGS HERE


    ActiveCell.EntireRow.Insert Shift:=xlUp




    'ASSIGN NEW AREA WITH A NEW NAME


    SR = ActiveWorkbook.Names("Spec1").RefersToRange.Address

    'Amend selection to Quoted Specifications
    ActiveCell.Offset(2, 7).Resize(4, 1).Select


    'ADD THE NEW AREA TO SPECIFIED_RANGES

    'Add that specified range to string SR, comma separated
    SR = SR & ":" & Range("Quote_End").Offset(-3, 1).Address

    'Create/Overwrite (by default) Specified_Areas range using string SR
    ActiveWorkbook.Names.Add "Specified_Ranges", "=" & SR

    ActiveCell.Offset(4, -7).Activate

    Application.EnableEvents = True
    Application.ScreenUpdating = True
    Application.CutCopyMode = False

    End Sub

Answer 1

Instead of using ActiveCell throughout the code, you should immediately assign it to a variable and utilize that variable instead.

Sub InsertArea()

    'Dimension variables
    Dim SR
    Dim Rng2 As Range
    Dim i, j, PB1 As Integer
    Dim Crit() As String
    Dim w As Worksheet

    'Then declare your cell in question
    Dim myCell As Range
    Set myCell = ActiveCell

This will prevent cases where the ActiveCell inadvertently changes while the code is running.

Also, within your declarations

Dim i, j, PB1 As Integer

i & j are declared as type Variant, while PB1 is declared as type Integer. While it is perfectly acceptable to declare more than one variable on a single line, these declarations must be done explicitly, such as:

Dim i As Integer, j As Integer, PB1 As Integer

- OR -

Dim i%, j%, PB1%

The % is the VB symbol for Integer, and may be used in declaring variables.

Posted as answer per OP request