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?