office 2010 - Runtime Error 13

[georgegone]

I have code that is running fine in Excel 2003 but get a runtime error 13 in 2010. The code is breaking at:

For r = 1 To UBound(arr, 1)

Here is the code:

Sub ProcessRenaissance()
Dim Sizes As Variant
Dim arr As Variant
Dim r As Long, c As Long
Dim fso As Object
Dim ts As Object
Dim TheLine As String
Dim TestStr As String
Application.ScreenUpdating = False

If MsgBox(prompt:="Are you sure you want to process the Renaissance payment?", _
Buttons:=vbYesNo + vbQuestion, Title:="Well?") = vbYes Then
'MsgBox "Ok, macro will now begin!"
Sheets("startup").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = "YES"
UpdateRen
Else
MsgBox "Ok, macro aborted!"
Sheets("startup").Select
Range("D7").Select
ActiveCell.FormulaR1C1 = ""
End If

CleanInterface
CreateRSTARSDetailRen
CreateRSTARSHeader

Sheets("header").Select

Sizes = Array(3, 8, 1, 3, 5, 8, 20, 2, 4, 8, 1, 2, 1, 1, 1, 1, 1, 5, 5, 1, 7, 5, 13, 5, 5, 13, 1, 1, 619)

arr = ActiveSheet.UsedRange

Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\data\Reninterface.txt", True)

For r = 1 To UBound(arr, 1)
TheLine = ""
For c = 1 To UBound(arr, 2)
TestStr = Left(Trim(CStr(arr(r, c))), Sizes(c - 1))
TheLine = TheLine & TestStr & String(Sizes(c - 1) - Len(TestStr), " ")
Next c
ts.Writeline TheLine
Next r

'Create Detail records

Sheets("Interface").Select

Sizes = Array(3, 8, 1, 3, 5, 8, 4, 8, 2, 1, 1, 3, 1, 1, 3, 6, 5, 5, 4, 5, 4, 4, 6, 2, 6, 2, 14, 4, 4, 6, 8, 10, 4, 10, 3, 1, 14, 8, 8, 8, 3, 8, 3, 8, 8, 9, 2, 10, 9, 1, 10, 13, 13, 30, 1, 13, 8, 2, 8, 2, 5, 13, 50, 50, 50, 50, 50, 20, 2, 9, 3, 13, 2, 3, 3, 4, 4, 53, 2)

arr = ActiveSheet.UsedRange

Set fso = CreateObject("Scripting.FileSystemObject")
'Set ts = fso.CreateTextFile("c:\data\Reninterface.txt", True)

For r = 1 To UBound(arr, 1)
TheLine = ""
For c = 1 To UBound(arr, 2)
TestStr = Left(Trim(CStr(arr(r, c))), Sizes(c - 1))
TheLine = TheLine & TestStr & String(Sizes(c - 1) - Len(TestStr), " ")
Next c
ts.Writeline TheLine
Next r
Application.SendKeys ("{BACKSPACE}")

ts.Close

'Testing to open in notepad and remove spaces at end
'path = "C:\WINDOWS\notepad.exe"
'file = "C:\data\Headerinterface.txt"
'dTaskID = Shell(path + " " + file, vbNormalFocus)
'SendKeys "{DOWN}", False
'SendKeys "{DOWN}", False
'SendKeys "{BACKSPACE}", False

Set ts = Nothing
Set fso = Nothing

Application.ScreenUpdating = True

Sheets("startup").Select
Range("D7").Select
ActiveCell.ClearContents

MsgBox "Done"

Application.ScreenUpdating = True

End Sub

 

[Ruddles]

Perhaps someone will spot it quicker than I can but in the meantime you might check:-

Has arr been converted into a 2D array at that point?

Do the values if its UBounds agree with the UsedRange?

 

[georgegone]

Thank You Ruddles.

The user had changed the data and caused only blank data to be passed. The values in UBounds were empty and caused the code to break. So this is user error not 2010 conversion error.