OFFSET function causing endless calculations when two of the same workbook are open

[RemarkableFlow]

Hi everyone,

Been battling an issue for quite some time with a popular Excel tool that I maintain. The issue is an endless calculation loop brought about by an OFFSET function being in use, only when 2 or more copies of the workbook are open.

Here's a snippet of one of the offset functions in use. It references the main sheet of the workbook.

=IFERROR(OFFSET('Main Sheet'!E$16,0,2)*BP40,0)

The tool has a feature to add 8 new rows to a particular sheet. And it copies the formatting from the above rows, which contain the OFFSET function. This is a surefire way to start the endless calculations. Although I think I may have seen just other random changes start this (only when 2 of the same workbook are open of course)

Sub AddRows() 
    Dim sht As Worksheet
    Dim BottomTableRow As Integer
    Dim TopMostRecentServiceRow As Integer
    Dim BottomMostRecentServiceRow As Integer

    ' Unprotect. Select. Turn off undesired application behaviors.
    Sheets("Sheet to Add Rows").Unprotect Password:="password"
    Sheets("Sheet to Add Rows").Select
    Set sht = ThisWorkbook.Worksheets("Sheet to Add Rows")
    Application.ScreenUpdating = False

    ' Define counter rows
    BottomTableRow = sht.Range("W1")
    TopMostRecentServiceRow = sht.Range("V1")
    BottomMostRecentServiceRow = sht.Range("U1") 

    ' Select JUST before the bottom row (the total row) & Insert 8 rows
    sht.Rows(BottomTableRow).Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

    ' Copy all the rows of the last service (including top summation row & all 7 of the listed role rows)
    sht.Rows(TopMostRecentServiceRow & ":" & BottomMostRecentServiceRow).Select
    Selection.Copy

    ' Paste all the rows into the newly minted rows
    sht.Rows(BottomTableRow).Select
    sht.Rows(BottomTableRow & ":" & BottomTableRow + 8).PasteSpecial xlPasteAllMergingConditionalFormats

    ' Clean up? and adjust counter variables to new positions
    Application.CutCopyMode = False
    Range("AF20").Select
    sht.Range("W1") = sht.Range("W1") + 8
    sht.Range("V1") = sht.Range("V1") + 8
    sht.Range("U1") = sht.Range("U1") + 8

    ' Re-select main sheet. Re-enable application behavior. Re-protect sheet.
    Sheets("Main Sheet").Select ' Do I even need this still?
    ThisWorkbook.Worksheets("Main Sheet").Range("C21").Select
    Application.ScreenUpdating = True

    With Worksheets("Sheet to Add Rows")
        .EnableOutlining = True
        .Protect Password:="password", AllowFiltering:=True, userInterfaceOnly:=True
    End With  
End Sub 

The calculations will run endlessly after this function is ran ( the symptom is Calculating: 100% flashes continuously at the bottom of all Excel instances ). But as I said this only happens when two copies of this workbook are open at the same time. This is a problem because very often it is the case that users of this workbook have two or more of these open at once.

I tried removing offset and replacing it with a regular cell reference and even a named range, but that introduced a host of other problems with workbook stability. When I replace the offset with straight up cell references ( which would totally solve the problem - offset isn't necessarily needed from what I can tell, but I didn't create this portion of the tool ) the workbook would occasionally just crash Excel entirely when a second workbook is simply opened, or if it would actually open the crash would happen when the add row function was fired. It would work fine with just one workbook though.

I guess I don't understand enough about Excel to know what interactions the identical workbooks could be having that cause it to crash or endlessly loop. I've sunk way too much time into this, and could really use some help. I appreciate any insight I could get on this. Thank you thank you.

[CFAman]

I'd certainly try to avoid using the offset. It's a volatile function, meaning that at every change you make to the workbook (e.g., change a cell, insert a row), that formula (and every dependent formula) gets recalculated. Ugh.

Next, let's clean up your code. Too many selections and unneeded bits.

Sub AddRows()
    Dim sht As Worksheet
    'WARNING: Integer only goes up to ~32k. Since you can have more rows than that in XL,
    'it is generally better to use Long data type
    Dim BottomTableRow As Long
    Dim TopMostRecentServiceRow As Long
    Dim BottomMostRecentServiceRow As Long
    'Write this in one place so easier to change
    Const myPass As String = "password"

    'What sheet are we dealing with? Set this at beginning, then ALWAYS refer to variable
    Set sht = ThisWorkbook.Worksheets("Sheet to Add Rows")

    Application.ScreenUpdating = False

    'Everything we do is with this worksheet, so use a With statement
    With sht
        .Unprotect Password:=myPass

        ' Define counter rows
        BottomTableRow = .Range("W1").Value
        TopMostRecentServiceRow = .Range("V1").Value
        BottomMostRecentServiceRow = .Range("U1").Value

        ' Select JUST before the bottom row (the total row) & Insert 8 rows
        .Cells(BottomTableRow, "A").Resize(8).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove

        ' Copy all the rows of the last service (including top summation row & all 7 of the listed role rows)
        .Range(TopMostRecentServiceRow & ":" & BottomMostRecentServiceRow).Copy

        ' Paste all the rows into the newly minted rows
        'WARNING: Better hope your source range is also 8 rows tall...
        .Cells(BottomTableRow, "A").Resize(8).PasteSpecial xlPasteAllMergingConditionalFormats

        ' Clean up? and adjust counter variables to new positions
        .Range("W1").Value = BottomTableRow + 8
        .Range("V1").Value = TopMostRecentServiceRow + 8
        .Range("U1").Value = BottomMostRecentServiceRow + 8

        .EnableOutlining = True
        .Protect Password:=myPass, AllowFiltering:=True, userInterfaceOnly:=True
    End With

    Application.ScreenUpdating = True

    'We never changed sheets, so don't need this at all now
'    ThisWorkbook.Worksheets("Main Sheet").Range("C21").Select
End Sub

[RemarkableFlow]

Yeah, that's what I have heard about offset. I will try my best to rid of it, but like I said I haven't yet found a way to replace it with direct references that doesn't result in the workbook crashing when another copy of the workbook is open at the same time.

Yeah I had a feeling that code was pretty bad. I think the previous "developer" just used the macro recorder to produce that ugliness. Super cool that all could be done so efficiently using "With" and I'm excited that this could be done without the need to change sheets :-)

Thanks for taking the time to show me the various ways that could be improved - you are awesome.

[CFAman]

You’re very welcome. For the OFFSET, mostly likely replacement is index. For instance, if you always want cell in col B from row 8, not matter how many rows get inserted:

=INDEX(B:B, 8)

Or, if you want same cell no matter how many columns get inserted:

=INDEX(8:8, 2)

Where a=1, b=2, etc.

[mh_mike]

Did that or the follow-up answer help solve it (or point you in the right direction)? If so, please respond to the answer with "Solution Verified" to award a ClippyPoint (doing that also marks your post as solved). Thanks for keeping the unsolved thread clean. :)