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 " & Now
before the section andDebug.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\$.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 useStop
. \$\endgroup\$