Hi

I have a procedure which performs various calculations and writes a calculated value to a cell within a loop. For some reason the procedure will get so far before displaying the runtime error: -2147417848 (80010108) Method 'Value' of object 'Range' failed.

The error occurs when trying to set the range's value property. This seems to happen at a random iteration in the loop within the procedure.

The range is valid since I can access it's other properties (e.g. address), and I can read the current value of the cell also. The value I am attempting to write to the cell is also perfectly valid and no different (in terms of decimal places or size) to the current value of the cell.

Now this is where it gets weirder! While in debug mode, if I attempt to set the cell's value via the immediate window, I get the following error: -2147417848 (80010108) Automation error The object invoked has disconnected from its clients.

At this point, Excel gets itself into a state where, if I'm in a worksheet and hit F9 to calculate, Excel crashes!

I simply do not understand why this is happening. There's nothing 'rocket science' about what I'm trying to do. I'm working with some of Excel's most basic objects as I have done since I started doing VBA some years ago! This error seems to randomly happen. Sometimes, the procedure will complete without ANY errors!

Apologies for my tone, but I am very frustrated with Excel at the moment. I would be sincerely grateful if anyone can offer any insight into this. FYI, I am using Excel 2007.

Much Appreciated!

Keith

Below is the procedure, which is essentially a bi-section method. The bold lines are where the error happens. I figure the more info I give, the more likely someone will be able to help.

Sub RunSeek(wks As Worksheet)

    Dim i As Integer
    Dim benchmark As Double
    Dim Tolerance As Double
    Dim RequiredRuns As Double
    Dim LowGuess As Double
    Dim HighGuess As Double
    Dim MidGuess As Double
    Dim MidAns As Double
    Dim MagErr As Double

    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False

    benchmark = Timer
    Tolerance = 0.001

    For i = 0 To 10
        If wks.Cells(5 + i, 2) = 0 Then
            wks.Cells(5 + i, 7) = 0
        Else
            RequiredRuns = wks.Cells(5 + i, 2)
            LowGuess = 1 - 1 / wks.Cells(5 + i, 5)
            HighGuess = 1
            MidGuess = (LowGuess + HighGuess) / 2
            wks.Cells(5 + i, 7).Value = MidGuess
            wks.Range(wks.Name & "CalcRange" & i + 1).Calculate
            MidAns = wks.Cells(5 + i, 8)
            MagErr = Abs(RequiredRuns - MidAns)

            Do Until MagErr < Tolerance
                If MidAns < RequiredRuns Then
                    LowGuess = MidGuess
                Else
                    HighGuess = MidGuess
                End If

                MidGuess = (LowGuess + HighGuess) / 2
                wks.Cells(5 + i, 7).Value = MidGuess
                wks.Range(wks.Name & "CalcRange" & i + 1).Calculate
                MidAns = wks.Cells(5 + i, 8)
                MagErr = Abs(RequiredRuns - MidAns)
            Loop

        End If
    Next i

    Application.ScreenUpdating = True
    MsgBox (Timer - benchmark)

End Sub

Keith Smith,

How about:
wks.Cells(5 + i, 7) = MidGuess


Have a great day,
Stan

stanleydgromjr

Windows Vista Business and Excel 2003, 2007.

Hi Stan

Thanks for the reply!

No, that makes no difference. I simply get the error 'Method 'Default' of object 'Range' failed'.