I have wanted a way to do code profiling in VBA for quite some time.It becomes very complicated to figure out what methods are actually being executed for how long and how often in complex Access applications. A key reason this is complicated is that many form events or function calculations happen very often and not only as the result of code. Form events fire based on other form events or user input, etc.This is a basic class I am calling Profiler:Option Compare DatabaseOption ExplicitPrivate initTime As DoublePrivate mProfiledMethod As StringPublic Property Let ProfiledMethod(pValue As String)    mProfiledMethod = pValueEnd PropertyPrivate Sub Class_Initialize()    initTime = GetTickCountEnd SubPrivate Sub Class_Terminate()    GetProfileManager.addMethodCall mProfiledMethod, GetTickCount() - initTimeEnd SubHere is what I am calling a ProfileManager class:Option Compare DatabaseOption ExplicitPrivate m_MethodTotalTimes As Scripting.DictionaryPrivate m_MethodTotalCalls As Scripting.DictionaryPublic Sub addMethodCall(p_method As String, p_time As Double)    If m_MethodTotalTimes.exists(p_method) Then        m_MethodTotalTimes(p_method) = m_MethodTotalTimes(p_method) + p_time        m_MethodTotalCalls(p_method) = m_MethodTotalCalls(p_method) + 1    Else        m_MethodTotalTimes.Add p_method, p_time        m_MethodTotalCalls.Add p_method, 1    End IfEnd SubPublic Sub PrintTimes()    Dim mKey    For Each mKey In m_MethodTotalTimes.Keys        Debug.Print mKey &  was called  & m_MethodTotalCalls(mKey) &  times for a total time of  & m_MethodTotalTimes(mKey)    Next mKeyEnd SubPrivate Sub Class_Initialize()    Set m_MethodTotalTimes = New Scripting.Dictionary    Set m_MethodTotalCalls = New Scripting.DictionaryEnd SubHere is my main module example. I have several nested methods.Public Declare Function GetTickCount Lib kernel32.dll () As LongPrivate mProfileManager As profileManagerPublic Function GetProfileManager() As profileManager    If mProfileManager Is Nothing Then        Set mProfileManager = New profileManager    End If    Set GetProfileManager = mProfileManagerEnd FunctionPublic Sub resetProfileManager()    Set mProfileManager = NothingEnd SubSub mainProfilerTest()    'reinit profile manager    resetProfileManager    'run some time/tests    test1    'print results    GetProfileManager.PrintTimesEnd SubSub test1()    Dim mProfiler As New Profiler    mProfiler.ProfiledMethod = test1    Dim i As Long    For i = 0 To 100        test2    Next iEnd SubSub test2()    Dim mProfiler As New Profiler    mProfiler.ProfiledMethod = test2    test3    test4End SubSub test3()    Dim mProfiler As New Profiler    mProfiler.ProfiledMethod = test3    Dim i As Long, j As Long    For i = 0 To 1000000        j = 1 + 5    Next iEnd SubSub test4()    Dim mProfiler As New Profiler    mProfiler.ProfiledMethod = test4    Dim i As Long, j As Long    For i = 0 To 500000        j = 1 + 5    Next iEnd SubThis works alright. I will have to add the two lines of code to create/init each Profiler at the beginning of any method I want to profile, which is not ideal but not terribly awful.On my machine the raw output is:test3 was called 101 times for a total time of 640test4 was called 101 times for a total time of 390test2 was called 101 times for a total time of 1030test1 was called 1 times for a total time of 1030For actual runtime, I am intending on wrapping a ProfileManager over a simple sub which triggers form events. For example I might make a simple sub to Open a form, but do something like:resetProfileManagerDoCmd.OpenForm Form NameGetProfileManager.PrintTimesWhich will print out all the profiled methods for all the tracked methods.Alternatively, I can reset the profile manager in the background and mimic user behavior and retrieve the profile at any time in the intermediate window with:?GetProfileManager.PrintTimesI am basically looking for feedback on how to make this better. It is pretty rough currently because I don't want to go through all the methods I'd want to profile and start adding this code without having more eyes on this.
VBA Code Profiling
optimization;vba
This is pretty good and optimal already. Just a few hints here and thereI would take the Private Declare Function GetTickCount Lib kernel32.dll () As Longand throw that into the Profiler class due to the class being the only one needing it. Trust me it's easy to forget to declare that on a new project in standard module and annoy the sh** out of yourself. You seem inconsistent about naming your members. Some subs are camelCase, others are PascalCase. Decide which one you're going to use and apply throughout the code.Generally, your project encapsulation is very loose. I mean, everything depends on everything if that makes any sense; (see interesting post here)I would think that ProfileManager should include members like Reset and GetProfileManager. BUT since your Profiler needs to access the manager in its Class_Terminate() event and you can't pass parameters nor can you raise an event to notify the manager that the object is about to get destroyed there isn't any other way to achieve this as far as I know due to your current design...I played with the code for about 2 hours but my conclusion is based on an assumption that you want to minimize it down to just creating an instance of Profiler and calling one Let property without having to explicitly destroy the instance (2 lines of code currently) ie. relying on the Class_Terminate event - you've already got the best approach and there is not much room for further optimizations.I am still not quite sure what the purpose of your ResetProfileManager() sub is so currently I find it a bit redundant since you could just do this once: Private manager As ProfileManagerSub MainProfilerTest()    Set manager = New ProfileManager...Oh btw. I would have changed the mProfileManager to just manager.. makes things simpler.And further to the above if you explicitly assign a new ProfilerManager to your manager you could improve the performance a bit by modifying the GetProfileManager by removing unnecessary check:Public Function GetProfileManager() As ProfileManager    Set GetProfileManager = managerEnd FunctionAlso, in ProfileManager consider using simpler names like:Private times As DictionaryPrivate calls As DictionaryAnd in the Profiler:Private initTime As DoublePrivate method As StringI would also add the Class_Terminate event to the ProfileManger and free refs to the DictionariesPrivate Sub Class_Terminate()    Set times = Nothing    Set calls = NothingEnd SubI think it's matter of preference but generally I don't prefix Dictionary with Scripting. Any decent person who's even a bit familiar with VBA will know that Dictionary is Scripting.Dictionary.I also considered shortening the declaration to one line using imitation of a static class in combination with parametarised constructors but I've seen your chat message from last night saying that you wouldn't want to do that.I guess that's about all I can say about your code. Like I said if you didn't mind to explicitly destroy your Profile instances that would completely change the story :)