VBA Excel Macro freezes after SUB

[cjw29209]

I have an Excel file with extrinsic Macro’s written in VBA. It worked great up until the 2210 or 2211 Excel update. The last time it worked was early December 2022. Now it only works on an older laptop where I have turned off updates. I have tried the file on multiple other computers with updated versions of excel, but it only works on the copy of excel that is not updating. I discovered this issue in early January 2023, and it has not been resolved with the several updates that have been released in 2023.

One of the Macro's seems to work except that at the end of the Sub Excel freezes for several minutes before I can use it again.

Sub checktrans()
' This macro checks transactions for proper code
    Application.ScreenUpdating = False
    'Application.Calculation = xlCalculationManual
    Dim transcode As String
    Dim r1 As Range
    Sheets("TRANS").Select
   Range("a6").Select
 ' Range("a1050").Select
 ' use above lines when you want to skip ahead.  A6is normal start point, but chenge when you don;t want to recheck.
    Do While ActiveCell.Value > 0
        ActiveCell.Offset(0, 1).Range("A1").Select
        transcode = ActiveCell.Value
        Sheets("Balance Sheet").Select
        Range("b1:b500").Select
        Set r1 = Selection.FIND(What:=transcode, LookAt:=xlWhole)
        If r1 Is Nothing Then
            Sheets("TRANS").Select
            Application.ScreenUpdating = True
            MsgBox transcode & " was not found.  Fix code and then try again!"
            Exit Sub
        End If
        Sheets("TRANS").Select
        ActiveCell.Offset(1, -1).Range("A1").Select
     Loop
    Application.ScreenUpdating = True
    'Application.Calculation = xlCalculationAutomatic
    MsgBox "All tranaction codes found.  Ready to process!"
    End Sub

 

[cjw29209]

I have an Excel file with extrinsic Macro’s written in VBA.

I meant to write "I have an Excel file with extensive (Not extrinsic) macro’s written in VBA. "

 

[Skyybot]

What line does the code stop on?

 

[cjw29209]

it makes its to the End. The message from the line before END SUB pops up, but then after that Excel freezes for maybe 10-20 minutes before I can use it again.

 

[Skyybot]

Try adding this before the last MsgBox:

Application.CutCopyMode = False

 

[cjw29209]

Unfortunately, that did not help.

 

[mehidy1437]

Some tips from ChatGPT

It sounds like there may be compatibility issues with the VBA macros in your Excel file and the latest versions of Excel. This can happen when updates are made to Excel that affect the way VBA code is interpreted and executed.

To troubleshoot the issue, you could try the following steps:

1. Check for errors: Open the VBA editor in Excel and check for any errors or warnings that may be causing the macros to malfunction. Look for any issues with references to objects, libraries, or functions that may have been affected by the Excel update.
2. Update VBA references: Check if any of the references used by the VBA macros need to be updated. In the VBA editor, go to the Tools menu, then click on References. Look for any references that may have been changed or removed due to the Excel update.
3. Re-compile the code: Try re-compiling the VBA code to see if there are any syntax or logic errors that need to be fixed. In the VBA editor, go to Debug > Compile VBA Project. This will check the code for any errors and provide feedback on how to fix them.
4. Disable add-ins: Some Excel add-ins may interfere with the execution of VBA macros. Try disabling any add-ins that may be causing issues and see if the macros work properly.
5. Test on another computer: If the above steps don't work, try running the Excel file on another computer with a different version of Excel to see if the issue is specific to your computer or if it's a compatibility issue with the latest version of Excel.

If the macro is still causing Excel to freeze at the end, there may be an issue with the way the macro is written. It's possible that the macro is using too many system resources or is not optimized for the latest version of Excel. You could try optimizing the macro code by removing any unnecessary steps or optimizing loops and calculations.

------------------------------

From a quick look, the code appears to be a simple loop that checks each transaction code in the "TRANS" sheet against a list of codes in the "Balance Sheet" sheet. If a code is not found in the "Balance Sheet" sheet, a message box is displayed and the macro exits.

it sounds like the macro is working as intended except that Excel freezes for several minutes at the end of the macro. This could be due to a large number of transactions being processed or other factors such as the complexity of the "Balance Sheet" sheet or other open workbooks.

One thing you could try to optimize the code is to replace the use of .Select and .Activate with direct cell references. Using .Select and .Activate can slow down the execution of the code and lead to performance issues, especially with larger data sets. Here's an example of how you can replace the .Select method with direct cell references:

Sheets("TRANS").Range("A6").Activate
Do While ActiveCell.Value > 0
    transcode = ActiveCell.Offset(0, 1).Value
    Set r1 = Sheets("Balance Sheet").Range("B1:B500").Find(What:=transcode, LookAt:=xlWhole)
    If r1 Is Nothing Then
        MsgBox transcode & " was not found.  Fix code and then try again!"
        Exit Sub
    End If
    ActiveCell.Offset(1, -1).Activate
Loop

Also, to further optimize the code, you could limit the range of cells that are being searched in the "Balance Sheet" sheet to the actual range of codes rather than searching the entire column. This will reduce the search time and speed up the execution of the code. Here's an example of how to do this:

Dim lastrow As Long
lastrow = Sheets("Balance Sheet").Cells(Rows.Count, "B").End(xlUp).Row
Set r1 = Sheets("Balance Sheet").Range("B1:B" & lastrow).Find(What:=transcode, LookAt:=xlWhole)

These changes may not necessarily fix the issue with Excel freezing, but they should improve the performance of the macro and make it run faster. If you're still having issues with Excel freezing, you may need to consider optimizing other parts of your VBA code

 

[Fluff]

@mehidy1437 please note Rule17 regarding the use of ChatGPT

 

[mehidy1437]

@mehidy1437 please note Rule17 regarding the use of ChatGPT

@Fluff thanks for the info.