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?
Debug.Print "[SectionName] started at " & Nowbefore the section and
Debug.Print "[SectionName] ended at " & Nowafter 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\$
.Unloadeven 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