Looking for a Code Cleaner for Excel 64 bit RRS feed

  • Question

  • My excel files keep crashing randomly now for 2 years with little to no suggestions from MS how to fix.Is there a code cleaner available for Excel 64 bit machines?

    Monday, November 16, 2015 1:09 PM

All replies

  • I'm not aware of an x64 version but if the objective is to remove any corruption that may have crept into the project it's easy to do it manually (obviously with a backup)

    1. Head all modules Option Explicit (if not already) and do a Debug compile, make any corrections if/as required.
    2. Drag all normal & class modules and any forms into a new project
    3. Copy and save all code in the workbook and worksheet object modules (eg onto sheets in the workbook you dragged the modules to).
    4. Save the original workbook as an xlsx (if necessary first change the IsAddin property if it's an addin). This will remove the VBProject.
    5. Drag all the modules & and forms back, and copy back any code to object modules.
    6. Resave as xlsm or xlam.

    It's probably worth doing Debug compile again, the file size will increase which is normal, and test.

    Monday, November 16, 2015 1:33 PM
  • Thanks Peter,

    I am trying to fugure out how to snip the module but all headers sat General.  How do I change that?

    This is the code which has worked fine for weeks and now it gives me an error below and just locks up.  I have done anything to the files

    Name:  run time error.jpg
Views: 29
Size:  22.6 KB

    Sub ResetBackToGame()
      Dim d As Double:
      d = Application.WorksheetFunction.CountIf([i2:i101], "?")
      Dim i As Integer:
      'make sure user entered positive number 1 to 50 into Q5
      If IsEmpty(Range("Q5")) _
       Or Range("Q5") < 1 _
       Or Range("Q5") > 50 Then
        'do nothing if is empty or value not between 1 and 50, inclusive
        Exit Sub
      End If
      i = 50 - [q5]
      Application.ScreenUpdating = False
      [I2:O101].Copy Cells(3 + i - d, 9)    'copies I2:O101 to position (3+i-d) rows down.
      Range("i2", Cells(2 + i, 15)) = "?"   'puts ? in I2 over & down to cleared row in O.
      [i102:o152].CLEAR: [q5].ClearContents
      Application.ScreenUpdating = True     'not required, auto-resets to True at End Sub or Exit Sub
    End Sub

    Thanks for your help

    Monday, November 16, 2015 1:49 PM
  • How do I run the debug, when I clicked on it , it grays out and does nothing.  It is saved in a xls file.  Do I need to resave in a xslm file?  This is my back up
    Monday, November 16, 2015 2:02 PM
  • If debug/compile in your original is grayed out it means it is already fully compiled and no errors with variable declarations (assuming you've got Option Explicit throughout)

    If your file with the code is an xls, after dragging all modules and copying code in object modules elsewhere it's best to save as an xlsx if you have access to 2007 or later.  This will guarantee there's no trace of any corrupt code in the project, because an xlsx doesn't have a VBProject. After bringing all the modules and code back into the code-empty xlsx you can save as an xls like your original.

    Monday, November 16, 2015 2:26 PM
  • I have Office 2010, 2013 and 2016.  Ive done as instructed but still have the issue  hmmm

    I do have an older laptop with Vista and XP 32 bit

    Would you know what else I could check for this error?  Lie I said, the code ran for over a month and then all the sudden locks up

    Name:  run time error.jpg
Views: 29
Size:  22.6 KB

    Monday, November 16, 2015 2:31 PM
  • There's no link for me to your image file, but I doubt it would help identify the problem without seeing your workbook.

    Whatever the error is it's unlikely to be related to your OS unless you're doing something related to windows with APIs, though I didn't know 2013/2016 would even work in those versions!

    Monday, November 16, 2015 2:40 PM