2
\$\begingroup\$

My project has grown for a year and now comprises of 19 standard modules, 7 class modules 11 forms. Some of the forms appear to freeze when called but, in fact, they are just slow to respond, taking a couple of minutes, regardless of what is clicked, e.g. just a change of focus or triggering an event procedure. The forms act normally again when reloaded (not to be confused with restarting Excel; I mean closing the form, waiting for it to respond, and then opening the same form again).

The problem first cropped up with a form that displays a PDF in a separate window. Then the malaise spread to other forms, even very simple ones, like the one that enters a date, not every time but too often. And recently - after I added 4 more classes (2 collections of collections) - simple reloading shortens the response time of the form calling Acrobat significantly but doesn't cure it completely. The response time is reduced from minutes to seconds.

I have come to the conclusion that the problem is caused by memory management. VBA, I argue, is unable to hold the entire code at its fingertips and delay is caused by moving parts of it from the front to the back burner or the opposite direction. The larger the project the more code there is to move around memory. The form calling Acrobat Reader is leading the way because it is calling another app (Acrobat).

It's a Win10 64-bit machine with Inspiron 3670 processor with 8GB installed RAM (7.83 GB usable). I'm running Excel 365 and it's normal that an Internet browser should be open at the same time (currently Edge) plus a stock trading web based app which is probably large. However, I haven't experienced any difference in the behaviour of my Excel VBA forms due to other applications being open at the same time. The problem appears contained within Excel, probably within this one workbook.

There aren't a lot of data in my workbook. All sheets add up to less than 1000 rows * 15 columns on average. My program has a large number (perhaps 120) public enumerations. All public procedures are in standard or class modules. Forms are unloaded and destroyed when they go out of use. Classes are intentionally preserved but I judge their demand upon memory to be the tip of the iceberg.

Below is the the procedure that calls the form that calls Acrobat.

Sub FileTradeNotes()

    Dim Form    As mKSKFiling

    With ActiveWindow
        .Top = 50
        .Left = 200
        .Width = 990
        .Height = 630
    End With

    SetApplication False, True
    Set Form = New mKSKFiling
    With Form
        If .Tag > 0 Then
            .Show vbModal
        End If
    End With
    
    Unload Form
    Set Form = Nothing
    SetApplication True
End Sub

SetApplication disables events, screen updating and calculations. The Initialize event procedure runs fast. The slowness of response starts only after the form is loaded and fully displayed. Nevertheless, it might be the GetActiveWindow API that is causing the problem.

Private Sub UserForm_Initialize()
    ' NIC 003 09 Jan 2021

    Dim Arr         As Variant
    Dim R           As Long
    
    WinHnd = GetActiveWindow
    If GetKSKmail(Mail) Then Me.Tag = UBound(Mail) + 1
    ReDim NewFn(NfnTop - 1)
    
    Arr = Lists.Range("OrderValidations").Value
    With CbxAct
        For R = 2 To UBound(Arr)
            .AddItem Arr(R, 1)
        Next R
        .ListIndex = 0
        NewFn(NfnAct) = .List(0)
    End With
    
    Arr = Split("Sell - Buy +")                 ' NtrShort & NtrLong
    With CbxTrade
        For R = 0 To UBound(Arr) Step 2
            .AddItem Arr(R)
            .List(.ListCount - 1, 2) = Arr(R + 1)
        Next R
        .ListIndex = NtrLong
    End With
    
    CtlEvents = True
    Arr = Split("OrderAcknowledgement,OA,Debit Note,DR,Credit Note,CR,Statement,ST", ",")
    With CbxType
        For R = 0 To UBound(Arr) Step 2
            .AddItem Arr(R)
            .List(.ListCount - 1, 2) = Arr(R + 1)
        Next R
        .ListIndex = 0
        NewFn(NfnType) = .List(0, 2)
    End With
End Sub

I'm not familiar with APIs. This is, in fact, my first attempt to work with them. Therefore I also append below the procedure where they feature big. It's called when the OK button is clicked or another button on the form that controls the document displayed in the Acrobat window.

Private Function CloseReaderDC(Optional ByVal MailIdx As Integer) As Boolean
    ' NIC 003 11 Feb 2021

    Dim WinCap      As String
    Dim Wnd         As LongPtr
    
    If MailIdx Then
        WinCap = AcrobatWindowID(Mail(MailIdx))
        Wnd = FindWindow(vbNullString, WinCap)
        ' this command quits the app instead of closing the document
        If Wnd Then SendMessage Wnd, WM_CloseClick, 6038, ByVal 0&
    Else
        WinCap = AcrobatWindowID
        Wnd = FindWindow(WinCap, vbNullString)
        If Wnd Then SendMessage Wnd, WM_CLOSE, 0, ByVal 0&
    End If
    If Wnd Then
        CloseReaderDC = True
    Else
        Msg.InfoBox "CantClose", 0, vbCritical, WinCap
    End If
End Function

My question is where to look for a solution.

Should I perhaps not make procedures private whenever possible? Is it wrong to let forms refer to public procedures in standard code modules? Should I remove comments? Or should I look for ways to split my workbook into several self-contained, interlinked units? Or, perhaps, is VBA just out of its depth on a project of this size?

Logically, if a process that takes a fraction of a second is executed 1000 times slower (within seconds) VBA may be shifting code execution within memory. But the time that takes can only be extended another 1000-fold (to take minutes) if the processor churns in useless loops. Something must cause such loops and something else must end them. I may be able to discourage the former and prompt the latter. Any suggestions?

\$\endgroup\$
11
  • \$\begingroup\$ Do you make your Excel file available? \$\endgroup\$
    – PChemGuy
    Sep 16, 2021 at 4:06
  • \$\begingroup\$ I wouldn't make it public but yes, I would make it available to individuals. Any suggestion how to do that? \$\endgroup\$
    – Variatus
    Sep 16, 2021 at 6:45
  • \$\begingroup\$ One of my projects has over 150 modules, classes and forms combined with over 70000 lines of code in VBA and it works perfectly fine. It can perform billions of calculations effortlessly. Some of the sheets have tens of thousands of rows and hundreds of columns. At some point everything was super slow until I found that a specific VBA bug was causing the issue and then I came up with a workaround for that bug. You need to isolate the part that is slow. Start by commenting out all the code in the form and then gradually turn code back on and test each time to see how long it takes to run. \$\endgroup\$ Sep 16, 2021 at 8:15
  • 1
    \$\begingroup\$ @Variatus I do not get notified unless you use the correct username. I happened to check the comments section and found your comment. You can start by printing timestamps to the Immediate window. For each section of code add Debug.Print "[SectionName] started at " & Now before the section and Debug.Print "[SectionName] ended at " & Now after the section. Then run the code and check the Immediate window. This will at least give you a good start by seeing what section is slow. And then of course you split the section as many times as you need to find the issue. Hope this helps \$\endgroup\$ Sep 17, 2021 at 9:51
  • 1
    \$\begingroup\$ @Variatus I had to remove the .Unload even from the standard modules. It does not matter though, I was just trying to say that it might be a bug (any other) and it needs to be isolated. When you click the control that does nothing for 3 minutes, you must have some event that fires for that control. What happens if you put a breakpoint on the very first line of that event? Does it get reached immediately, or do you have to wait the 3 minutes for it to get reached? If it gets reached immediately, then what other line of code takes 3 minutes? Instead of a breakpoint, you can use Stop. \$\endgroup\$ Sep 19, 2021 at 6:13

2 Answers 2

1
\$\begingroup\$

The problem I encountered turned out to be unrelated to the code. Instead, interruption was caused by Excel's AutoSave function, possibly in tandem with a weak Internet connection.

At this time I still don't know if simply turning off AutoSave would cure the problem. What I have done successfully is to remove my workbook from OneDrive, which disables AutoSave automatically. After removing the file from the cloud the problem has disappeared.

I still don't know why the program was interrupted only on clicks in a user form but apparently not on clicks in a worksheet. However the picture I get is that code execution was interrupted on clicking any control in any of my user forms and would resume after conditions on the cloud had been met. I assume that the interruptions were of variable length - from 0 seconds to several minutes - depending upon how quickly the cloud would respond. Having a chronically weak Internet connection leads me to believe that the broadband quality may also affect my experience.

\$\endgroup\$
2
  • \$\begingroup\$ Interesting. I personally don't use the cloud. If you ask questions in the future it might be important to describe the environment the code is running in as well. \$\endgroup\$
    – pacmaninbw
    Nov 13, 2021 at 13:09
  • \$\begingroup\$ I don't know this stuff at all (well outside my usual languages/enviroments), but is there code you could add to suspend auto-save for the duration of the function? That would be an interesting addition to this answer, and useful to others, too. \$\endgroup\$ Nov 25, 2021 at 16:39
1
\$\begingroup\$

I managed to solve a similar performance problem when I had a Workbook with 5 userforms and a maximum of 3000 rows in one of the 23 spreadsheets. The average in the rest of the spreadsheets was below 1000 rows and a maximum of 10000 lines of VBA code.

I was using OneDrive like you, but the problem seems to be the synchronization with the cloud that OneDrive makes for every little change that is produced in the Workbook file while you are working on it by manual interaction or through VBA code. The problem isn't only with Macro Enabled Workbooks: with Excel files of a certain size the problem arise as well.

The solution I found is simply stopping the OneDrive synchronization Option while working on the file and enabling it again after that. This option is found in the contextual menu of the OneDrive icon in the notification area of the taskbar on Windows.

The Excel AutoSave feature also can generate a lag in the execution, but at specified time intervals configured in the Excel Options (every 5, 10, 15 minutes) and proportional to the file size. From my experience, the problem is not the AutoSave feature, but the OneDrive automatic synchronization option as I explain above.

\$\endgroup\$
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.