Forms to enter trading information

Question

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?

Answer 1

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.

Answer 2

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.