Excel 2010 - Intermittent Run Time Error -2147417848 (80010108)

PatKlaus

New Member
Joined
Jan 3, 2014
Messages
5
I've been beating my head against the wall on this one for a couple weeks now and have finally decided (I'm stubborn, I am) that I'm not going to resolve this one myself. Currently, the specific error is "Method 'Insert" of object 'Range" failed" although it has also manifested as "Automation error The object invoked has disconnected from its clients." While I've searched the various forums, none of the reported instances seem to match this case.

I have a fairly complex workbook which is used by project managers in my company to estimate the initial project costs and to track project financial progress. The Workbook has evolved over many years and through more hands than mine for the macros provided to manipulate the spreadsheet. In its current incarnation, there are 20 worksheets, some of which are routinely hidden from the user. We are finally creating a comprehensive regression test for this workbook and this error was discovered during testing prior to deployment.

To use the workbook, the initial estimate is entered using either the Worksheet Estimate sheet or the MPP Estimate sheet. Various project parameters are entered on one of these sheets including Region & Currency. The failing case is to change the Region & Currency (cell H3) on the Worksheet Estimate sheet, select the Project Overview sheet and click the "Add PCR" button at the top. This seems to fail every time. Another failing case is to change the Estimation Method (cell B14) on the Project Overview sheet from "Worksheet" to "MPP" and click the "Add PCR" button. Both the Region & Currency and Estimation Method are selected from a drop down list using data validation and run no code, so it's extremely difficult to think of any reason this should affect the operation of the macro, and it also fails when a value in a cell without data validation is changed. Passing cases include:
  • Open the workbook, immediately select the Project Overview sheet and click the "Add PCR" button. This works reliably.
  • After changing the Region & Currency on the Worksheet Estimate sheet, use the macro to Add Task Rows, then select Project Overview and click Add PCR.
  • After changing the Estimation Method on the Project Overview sheet, select the MPP Estimate sheet and use the button to Insert Estimated Labor Rows, then select Project Overview and click Add PCR.
Finally, I've tested the failing case on prior versions and they are also failing - whether they have always been failing or not I can't say. Our company has a policy of automatic updates for Windows and Office, so all users should have the same version/service pack of both Windows 7 and Office 2010, and we have seen the failure on at least 2 separate computers. Note: this isn't a normal use case for the workbook since a PCR (Project Change Request) would ordinarily be added long after all the other parameters are set and rows have been added for the original estimate, but if we've found it once I can guarantee one of the other users will find it eventually.

I wanted to attach the spreadsheet, but can't find a button to do that, so here's the failing code. If you want to see the spreadsheet, I can email that.

Code:
Public Sub AddPCR()
'
' Adds a PCR to the Project Overview sheet, the Labor Roll-up sheet and the PCR Summary sheet.
'
    Dim curCol As Integer
    Dim colStr As String
    Dim ColumnRange As String
    Dim doneProcessing As Boolean
    Dim Visible1 As Boolean
    
    ' First, turn off all screen updates and automatic calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Make hidden sheets visible
    Sheets("Tables").Visible = True
    Sheets("Rollup_Tables").Visible = True
    Visible1 = Sheets("Labor Roll-up").Visible   'save the visible status of the sheet to be restored at end of sub
    Sheets("Labor Roll-up").Visible = True
    
    ' Copy the calculation PCR template and paste into the Labor Roll-up sheet
    Sheets("Rollup_Tables").Select
    Range("CalculationPCRTemplate").Copy
    Sheets("Labor Roll-up").Select
    curCol = Range("Last_PCR_Calc_Col").Column
    colStr = ConvertColumnNumberToLetter(curCol)
    ColumnRange = colStr + ":" + colStr
[COLOR=#ff0000]    Range(ColumnRange).Insert Shift:=xlToRight
[/COLOR]    Application.CutCopyMode = False

    ...

There's more to the macro, but it's failing at the line in red text, although that command works in other cases. The really interesting thing is that when I enter debug and look at the worksheet, the command has already been executed when it fails. The new template has been added to the sheet, but it's still highlighting this command as the failure.

Any suggestions will be welcomed!
Regards,
Pat Klaus
 

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
ConvertColumnNumberToLetter returns a string. Here's the code:

Code:
Public Function ConvertColumnNumberToLetter(ByVal ColumnNumber As Integer)
IntegerResult = ColumnNumber \ 26
FractionalResult = (ColumnNumber / 26) - IntegerResult
Remainder = ColumnNumber Mod 26
If IntegerResult = 0 Then
    FirstLetter = ""
ElseIf IntegerResult = 1 And FractionalResult = 0 Then
    FirstLetter = ""
    ConvertColumnNumberToLetter = "Z"
    Exit Function
ElseIf IntegerResult > 1 And FractionalResult = 0 Then
    FirstLetter = Chr(64 + (IntegerResult - 1))
    ConvertColumnNumberToLetter = FirstLetter & "Z"
    Exit Function
Else
    FirstLetter = Chr(64 + IntegerResult)
End If
SecondLetter = Chr(64 + Remainder)
ConvertColumnNumberToLetter = FirstLetter & SecondLetter
End Function
 
Upvote 0
Public Function ConvertColumnNumberToLetter(ByVal ColumnNumber As Integer)

if think this suggests that the return is a variant

try

Public Function ConvertColumnNumberToLetter(ByVal ColumnNumber As Integer) As String

anything coming back is treated as a string
 
Last edited:
Upvote 0
You could also try this version:
Code:
    Dim curCol As Integer
    Dim colStr As String
    Dim ColumnRange As String
    Dim doneProcessing As Boolean
    Dim Visible1 As Boolean
    
    ' First, turn off all screen updates and automatic calculation
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
    ' Make hidden sheets visible
    Sheets("Tables").Visible = True
    Sheets("Rollup_Tables").Visible = True
    Visible1 = Sheets("Labor Roll-up").Visible   'save the visible status of the sheet to be restored at end of sub
    Sheets("Labor Roll-up").Visible = True
    
    ' Copy the calculation PCR template and paste into the Labor Roll-up sheet
    With Sheets("Labor Roll-up")
        curCol = .Range("Last_PCR_Calc_Col").Column
        colStr = ConvertColumnNumberToLetter(curCol)
        ColumnRange = colStr + ":" + colStr
        .Range(ColumnRange).Insert Shift:=xlToRight
        Sheets("Rollup_Tables").Range("CalculationPCRTemplate").Copy Destination:=.Range(ColumnRange)
    End With

Note: what you've posted here shouldn't really require making any sheets visible.
 
Upvote 0
Many thanks for the help. Using the With method does seem to allow it to work reliably (although I'd like to understand why, it's less important than that it works!). I did try adding "As String" to the function and it didn't change the behavior, but will leave it in as it does make it more deterministic.
 
Upvote 0
I have never been a fan of using the Insert method to automatically paste from the clipboard as well - I find it more reliable to insert the destination column, and then copy and paste directly to it.
 
Upvote 0
How do you mean Rory?

what could be a alternative to the following:

Code:
Dim myRange As Range
Set myRange = Selection

With ActiveCell
    Selection.Copy
.Insert Shift:=xlDown
End With
Application.CutCopyMode = False
 
Upvote 0
Like this:
Code:
Dim myRange As Range
Set myRange = Selection

ActiveCell.Insert Shift:=xlDown
myrange.copy activecell
Application.CutCopyMode = False
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top