An Error but not!

C

cable

Access For My Sins
Local time
Today, 05:43
Joined
Mar 11, 2002
Messages
226
I've got some code that generates an excel spreadsheet from scratch.

When I run it, it sometimes falls over with the following error message:
Run-time error '-2147417851 (80010105)':
Method '_Default' of object 'Range' failed
Click to expand...
but, and this is what I don't understand, pressing F5 continues, it then might fall over slightly later but again F5 continues.

It doesn't always fall over on the same line but the lines that it does are all referencing excel objects.

What I can't understand is why F5 continues...unless its a timeout or something?

Anybody had this before or any ideas on how to go about sorting it?
 
SJ McAbney said:
What's the code? :rolleyes:
Click to expand...
well I didn't post any because
a) its random (but the line does use excel objects (normally range)
b) it continues anyway...so I thought the error code might mean something to someone
c) the code is large...I could post a section though if you think that will help
Code: 
Dim oExcel As Excel.Application
Dim oWB As Excel.Workbook
Dim oWS As Excel.Worksheet
Dim oRng As Excel.Range
Set oExcel = New Excel.Application
Set oWB = oExcel.Workbooks.Add

oExcel.DisplayAlerts = False
For Each oWS In oWB.Sheets
    If oWS.Name <> "Sheet1" Then oWS.Delete
Next
oExcel.DisplayAlerts = True

Set oWS = oWB.Sheets("Sheet1")
oWS.Name = "Profile"
.
.SNIP
.
'Profit & Loss - Level Yield
'---------------------------
SysCmd acSysCmdClearStatus
SysCmd acSysCmdInitMeter, "Profit & Loss (Level Yield)...", iPeriods
I = iR + iLen - 2
I = I + 2
'title
oWS.Range("B" & I) = "Profit & Loss - Level Yield"
oWS.Range("B" & I).Font.Bold = True
I = I + 1

iStart = I
iP = 0
iLen = 13
iR = iStart - iLen
iC = 0

'store P&L new start ref
rPLNew = "E" & iR + iLen

'total
oWS.Range("C" & iR + iLen) = "Total"
'period 0
oWS.Range("D" & iR + iLen) = "0"
'clear sum string array
For I = 1 To 10
    sSum(I) = "="
Next I

'loop
MainRS.MoveFirst
Do Until MainRS.EOF
    iP = iP + 1
    If iP Mod giCOLS = 1 Then
        iR = iR + iLen
        'format section
        oWS.Range("B" & iR + 2 & ":" & rI2C(5 + giCOLS - 1) & iR + iLen).NumberFormat = "#,##0.00;[Red](#,##0.00)"
    
        oWS.Range("B" & iR) = "Period"
        oWS.Range("B" & iR & ":" & rI2C(5 + giCOLS - 1) & iR).Font.Italic = True
        oWS.Range("B" & iR & ":" & rI2C(5 + giCOLS - 1) & iR).Font.Bold = True
        oWS.Range("B" & iR & ":" & rI2C(5 + giCOLS - 1) & iR).Font.Size = 8
        oWS.Range("B" & iR & ":" & rI2C(5 + giCOLS - 1) & iR).NumberFormat = "#0"
        
        oWS.Range("B" & iR + 1) = "Date"
        oWS.Range("B" & iR + 1 & ":" & rI2C(5 + giCOLS - 1) & iR + 1).Font.Italic = True
        oWS.Range("B" & iR + 1 & ":" & rI2C(5 + giCOLS - 1) & iR + 1).Font.Bold = True
        oWS.Range("B" & iR + 1 & ":" & rI2C(5 + giCOLS - 1) & iR + 1).Font.Size = 8
        oWS.Range("B" & iR + 1 & ":" & rI2C(5 + giCOLS - 1) & iR + 1).NumberFormat = "mm/yy"
        oWS.Range("B" & iR + 1 & ":" & rI2C(5 + giCOLS - 1) & iR + 1).Borders(xlEdgeBottom).LineStyle = xlContinuous
        
        oWS.Range("B" & iR + 2) = "Mortgage Balance"
        sSum(1) = sSum(1) & "+SUM(D" & iR + 2 & ":" & rI2C(5 + giCOLS - 1) & iR + 2 & ")"
        
        oWS.Range("B" & iR + 3) = "Rate Applying"
        
        oWS.Range("B" & iR + 4) = ""
        
        oWS.Range("B" & iR + 5) = "Yield"
        sSum(4) = sSum(4) & "+SUM(D" & iR + 5 & ":" & rI2C(5 + giCOLS - 1) & iR + 5 & ")"
        oWS.Range("B" & iR + 5 & ":" & rI2C(5 + giCOLS - 1) & iR + 5).Borders(xlEdgeTop).LineStyle = xlDouble
        oWS.Range("B" & iR + 5 & ":" & rI2C(5 + giCOLS - 1) & iR + 5).Borders(xlEdgeBottom).LineStyle = xlDouble
        
        oWS.Range("B" & iR + 6) = "Yield - Capitalized"
        sSum(5) = sSum(5) & "+SUM(D" & iR + 6 & ":" & rI2C(5 + giCOLS - 1) & iR + 6 & ")"
        
        oWS.Range("B" & iR + 7) = "Yield - Up Front Income"
        sSum(6) = sSum(6) & "+SUM(D" & iR + 7 & ":" & rI2C(5 + giCOLS - 1) & iR + 7 & ")"
    
        oWS.Range("B" & iR + 8) = "Yield - Up Front Expense"
        sSum(7) = sSum(7) & "+SUM(D" & iR + 8 & ":" & rI2C(5 + giCOLS - 1) & iR + 8 & ")"
    
        oWS.Range("B" & iR + 9) = "Yield - Exit Fees"
        sSum(8) = sSum(8) & "+SUM(D" & iR + 9 & ":" & rI2C(5 + giCOLS - 1) & iR + 9 & ")"
        
        oWS.Range("B" & iR + 10) = "Yield - Interest"
        sSum(9) = sSum(9) & "+SUM(D" & iR + 10 & ":" & rI2C(5 + giCOLS - 1) & iR + 10 & ")"
        
    End If
    iC = 4 + ((iP - 1) Mod giCOLS)

    'period
    oWS.Range(rI2C(iC) & iR) = iP - 1
    'date
    oWS.Range(rI2C(iC) & iR + 1) = MainRS!PeriodDate
    
    'mortgage balance
    '   =opening bal (or advance if ip=1)
    If iP = 1 Then
        oWS.Range(r(iC, iR + 2)) = "=" & rAdd(rFind(rProfile, giCOLS, 14, iP), 2, -1)
    Else
        oWS.Range(r(iC, iR + 2)) = "=" & rAdd(rFind(rProfile, giCOLS, 14, iP), 1, -1)
    End If
    
    'rate applying
    '   =yield*12/mort balance
    If iP > 1 Then
        oWS.Range(r(iC, iR + 3)) = "=" & r(iC, iR + 5) & "*12/" & r(iC, iR + 2)
    Else
        oWS.Range(r(iC, iR + 3)) = 0
    End If
    
    'yield
    '   =mort bal/tot mort bal*p&l spread total
    'rAdv1 = rAdd(rFind(rProfile, giCOLS, 14, 1), 2)
    oWS.Range(r(iC, iR + 5)) = MainRS!Yield
    
    'capitalized
    oWS.Range(r(iC, iR + 6)) = MainRS!YieldCap
    
    'up front income
    oWS.Range(r(iC, iR + 7)) = MainRS!YieldStart
    
    'exit income
    oWS.Range(r(iC, iR + 8)) = MainRS!YieldEnd
    
    'up front expense
    oWS.Range(r(iC, iR + 9)) = MainRS!YieldComp
   
    'yield int
    oWS.Range(r(iC, iR + 10)) = MainRS!YieldInt
    
    MainRS.MoveNext
    SysCmd acSysCmdUpdateMeter, iP
Loop
'Total Section
oWS.Range("C" & iR) = "Total"
oWS.Range("C" & iR).Font.Italic = False
oWS.Range("C" & iR).Font.Bold = True

For I = 1 To 9
    If sSum(I) <> "=" And Not IsNull(sSum(I)) Then
        oWS.Range("C" & iR + 1 + I) = sSum(I)
    End If
Next I
'rate applying total is special
oWS.Range("C" & iR + 3) = "=" & ("C" & iR + 5) & "*12/" & ("C" & iR + 2)

oWS.Range("B" & iStart - 1 & ":" & rI2C(5 + giCOLS - 1) & iR + iLen - 2).Interior.Color = glBLUE
oWS.Range("C" & iStart - 1 & ":C" & iR + iLen - 2).Borders(xlEdgeLeft).LineStyle = xlSingle
oWS.Range("C" & iStart - 1 & ":C" & iR + iLen - 2).Borders(xlEdgeRight).LineStyle = xlSingle
.
.SNIP
.
'clean up and save
'=================
'autofit and protect
oWS.Columns("A:" & rI2C(5 + giCOLS - 1)).AutoFit  'autofit the columns
'fix col b
oWS.Range("C1").Select
oExcel.ActiveWindow.FreezePanes = True

oWS.Range("A1").Select          'select the top left cell

'oWS.Protect "password"          'protect the current sheet
'oWB.Protect "password"         'we don't need to look the entire book though

SysCmd acSysCmdClearStatus

oExcel.DisplayAlerts = False

If Exists(sFileName) Then Kill sFileName
Call oWB.Close(SaveChanges:=True, FileName:=sFileName)

Set oWB = Nothing
oExcel.Quit
Set oExcel = Nothing
I've include the excel object setup and the tidy up as well as a sample of a typical section, there about 5 of these, nothing massively differnent, just more lines.
 
You must log in or register to reply here.

Similar threads

P
Variable assignment not working.
2
Replies
22
Views
1,470
pdanes
P
zelarra821
List all song features
2
Replies
22
Views
2,001
zelarra821
zelarra821
Gasman
Solved Open Excel workbook, but have WorkBook_Open code run
Replies
19
Views
1,822
Gasman
Gasman
A
Solved Nz has started throwing up an error message
2
Replies
22
Views
2,076
The_Doc_Man
The_Doc_Man
A
Numerical ID vs Alphanumerical ID: practical advice needed on best way forward from where I am
Replies
19
Views
3,003
Cotswold
Cotswold

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom