Intermittent Error Method 'Apply' of object 'Sort' failed after multiple runs

J

jtcoleman

New member
Joined
May 23, 2016
Messages
4
Reaction score
0
Points
0
Hello,

I am new to vb and am having a problem with my macro. The problem is that I am getting different errors on varying lines after I run my macro some random number of times. I have tried multiple solutions and can see no real correlation between the symptoms and changing anything in my code. If I save and close my file and then try to rerun the macro it works just fine the first time but after that it may or may not error again. Additionally, after receiving an error and resetting and then waiting a few minutes, the macro will sometimes rerun with no problems. The problem is hard to diagnose because of its dynamic nature and therefore it is difficult to describe. I will attach my file that contains all pertinent code and example data and maybe someone running Excel 2010 on windows 7 can replicate my problem on their machine. A little more info about my file: I created a custom tab "Caesar" with custom buttons to run my macros using an custom UI editor. These buttons (primarily "Run All") execute the macros I am having trouble with. I realize that I may have written certain parts of the code in sub optimal fashion but I am open to any suggestions and any help I can get. As far as the functionality of the code goes, it does exactly what I want to to do every time until it hits an error and even then it performs properly and just does not finish. I apologize if I have left any necessary data out but like I said I am new to vb and the problem is difficult to describe.

Thanks in advance for any help
View attachment mytest - for upload.xlsm
 
No Caesar tab, no buttons…
 
Code: 
Sub Clear_Inputs_and_Outputs(control As IRibbonControl)
  If MsgBox("WARNING" & vbLf & "This will erase ALL input data and results" & vbLf & vbLf & "Are you sure?", vbYesNo) <> vbYes Then Exit Sub
      
  Application.ScreenUpdating = False

  For j = 0 To 3
    Sheets(Choose(j + 1, "Static", "Dynamic Displacements", "Restraints", "Reference").UsedRange.Columns(1).Resize, 11).ClearContents
  Next
  Sheets("Support Loads").UsedRange.Offset(4).ClearContents
   
  Application.ScreenUpdating = True
End Sub
 
snb said:
@p45cal
Tab= commandbar
Click to expand...
Ah.

This is a difficult one - it took some time before I got errors.
On one it was the .Apply which failed; on debugging it would repeatedly fail even if I set the next line back up to the .SortFields.Clear line and stepped through. However, was it luck? I don't know, but in the same debugging session, when I altered .Header = xlGuess to .Header = xlNo it no longer errored on the .Apply line. Worth trying?

Occasionally I got errors method '_default' of object 'range' failed on lines such as Range("D1").Offset(l + 1, 6) = goal.Value. This is an unqualified reference (it depends on which is the active sheet) - perhaps if it were qualified?? Maybe it's worth qualifying as much as you can in the code. For example, you have this snippet:
Code: 
Sub Restraints()
Worksheets("Restraints").Select

If Worksheets("Reference").Range("A1").Value = 0 Or Worksheets("Restraints").Range("C1").Value = 0 Then
    MsgBox ("Unexpected or no data entered. Please review input and try again.")
    'Worksheets("Restraints").Protect "process"
    Exit Sub
End If
Range("D:D").Replace What:="=", Replacement:="'", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
You can nearly always remove Select statements from the code and qualify references as follows:
Code: 
With Worksheets("Restraints")
If Worksheets("Reference").Range("A1").Value = 0 Or .Range("C1").Value = 0 Then
    MsgBox ("Unexpected or no data entered. Please review input and try again.")
    '.Protect "process"
    Exit Sub
End If

.Range("D:D").Replace What:="=", Replacement:="'", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
where each instance of Worksheets("Restraints"). has been replaced by a single dot and where the unqualified Range("D:D") has been preceded by a dot.
here's that whole macro converted (commented code too) and note the comments on 2 lines in the sorting code:
Code: 
Sub Restraints()
With Worksheets("Restraints")
  If Worksheets("Reference").Range("A1").Value = 0 Or .Range("C1").Value = 0 Then
    MsgBox ("Unexpected or no data entered. Please review input and try again.")
    '.Protect "process"
    Exit Sub
  End If

  'Application.Interactive = False
  'Application.ScreenUpdating = False

  '.Unprotect "process"

  '.UsedRange
  'Range(.Range("J3:Y3"), .Range("J3:Y3").End(xlDown)).ClearContents
  '.Range("J3:Y10000").ClearContents
  '.UsedRange

  .Range("D:D").Replace What:="=", Replacement:="'", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False

  i = 1
  ii = 1
  j = 0
  k = 1
  l = 1
  Do While l <= Worksheets("Reference").Range("A1").Value

    comp1 = Worksheets("Static").Range("M" & l + 2)
    val1 = .Range("C:C").Find(comp1, , xlValues, xlWhole, xlByRows).Row

    If .Range("B" & k).Value = .Range("B1").Offset(i - 1, 0).Value Then
      Dim goal As Range
      Set goal = .Range("C" & k)
      .Range("D1").Offset(l + 1, 6) = goal.Value
      Do While .Range("B" & k).Value = .Range("B1").Offset(i - 1, 0).Value
        Set goal = .Range("D" & i & ":" & "H" & i)
        .Range("D1").Offset(l + 1, 7 + j).Range("A1:E1") = goal.Value
        i = i + 1
        ii = ii + 1
        j = 5 * (ii - 1)
      Loop
    Else
      k = i
      l = l + 1
      ii = 1
      j = 0
    End If
  Loop
  With .Sort
    .SortFields.Clear
    .SortFields.Add Key:=Range("J3"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal  'note you don't qualify this range
    .SetRange Range("J3" & ":" & "Y" & Worksheets("Reference").Range("A1").Value + 2)  'note you don't qualify this range
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
  End With
  o = .Range("J:J").Cells.SpecialCells(xlCellTypeConstants).Count
  If o - 1 <> Worksheets("Reference").Range("A1").Value Then
    MsgBox "Restraint nodes do not match Static nodes. " & vbCrLf & "Verify input data is correct before using results."
  Else
    If restraintvar = 0 Then
      MsgBox "Restraints Complete"
    End If
  End If

  Application.Goto .Range("A1")


  '.Range("I:Y").Locked = True
  '.Protect "process"
  Application.Interactive = True
  Application.ScreenUpdating = True

  Exit Sub
Unlockrestraints1:
  .UsedRange
  Range(.Range("J3:Y3"), .Range("J3:Y3").End(xlDown)).ClearContents
  '.Range("J3:Y10000").ClearContents
  .UsedRange
End With
restrainterr = 1
'.Range("I:Y").Locked = True
'.Protect "process"
Application.ScreenUpdating = True
Application.Interactive = True
MsgBox "Restraint and Static nodes do not match.  Please review input data and try again."
End Sub
 
Last edited:
The most common failures:

- an endless do loop

- an endless worksheet_change loop

avoid both.
 
Thanks for the suggestions!
@p45cal - I used the fix you suggested for the .Sort issue and I'm still getting an error. And I tried replicating your experience exactly by getting it to error then changing the sort code and resetting the next line and continuing. Also, I changed all the modules to use the With method. This has not made a difference either, however I have noticed that the first error I get is the sort error. Then if I stop and run and different macro it may work or it will start giving me a different error (either Method 'ClearContents' of object 'Range' failed or Delete method Range failed or Application-defined or object-define error. I also revised the formatting that was done exclusive in the "Static" macro to be a separate function to be called when necessary. Another change I made was to turn calculations to manual at the begging of each macro. I did this because the general feeling Excel is giving me about my problem is that there is some internal memory cache that exceeds its limits which is causing it to crash. I have researched this as much as I can and can find no evidence to back up that theory. And yes, I do know that excel on my machine is limited to 2GB of ram however, when I run the same file on my personal machine (windows 10 pro, excel 2013) I could not get it to error out on me no matter what I tried.

@snb - thanks for the cleaner clear all code. would you mind extrapolating on this?
The most common failures:

- an endless do loop

- an endless worksheet_change loop

avoid both.
Click to expand...
i.e. how do you know that these are the problems causing my errors, and regardless of what may cause the errors at any individual time why would either of those endless loops only occur sometimes?
 
In the attached I've added a few more qualifiers.
What I said in the comments about not qualifying those ranges in the sort routines seems to be wrong - I've added them in.
Whether it's just luck or not I don't know but after Excel once refusing to close (without forcing it to), I can't get the workbook to error!
 

Attachments

  • excelGuru6102mytest - for upload-forum_updates03.xlsm
    294 KB · Views: 8
Thanks for the update but unfortunately I still get errors with the ClearContents method using your file. However some good news is I did find a workaround for the sort routine error. It was as simple as on error resume next right above the apply command. While this does not fix the cause of the error, it does seem to have some 'resetting' capability because if get and error and then rerun the same macro that errored out, it will run without hitting the error. I also found that I cannot get any errors to appear if I do not have any data on "Dynamic Displacements" and "Restraints" sheets. I have tested this MANY different times and in many different orders and all with no errors.
 
You must log in or register to reply here.
Back
Top