Microsoft Office & VBA
Optimization
Microsoft Office & VBA
Results method
cprocProfiler.Results (rOutput as range) purpose Reports on this profiling session. arguments rOutput – This is a range representing the first cell (upper left) where the report for this profiler is to be written to and […]
Microsoft Office & VBA
Pause Method
Public Sub Pause(Section As String) purpose Pauses a timing section that has previously been started with the Start method. The main use of the Pause method would be to exclude certain timings, for example the time spent waiting for user […]
Microsoft Office & VBA
Start method
cProcProfiler. Start(Section As String, Optional ProcName As String = “Not given”, Optional pLevel As ptLevels = ptLevelLow) purpose Starts a timing section that will last until the Finish or Pause method is executed. The first time it is called for a given section it […]
Microsoft Office & VBA
FinishProfiler method
FinishProfiler purpose Marks the end of a profiling session and stops all timing activity. arguments none returns none For help and more information join our forum, follow the blog or follow me on Twitter
Microsoft Office & VBA
Finish method
cProcProfiler.Finish(Section As String) purpose Finishes a timing section that has previously been started with the Start method. arguments Section – the name of the Code section. This is the key through which you access this in all […]
Microsoft Office & VBA
destroyTimers method
cProcProfiler.destroyTimers purpose Clean up when a profiling session is complete and the results have been reported arguments none returns none Notes – Advanced If you are only using one profiler, you will […]
Microsoft Office & VBA
StartProfiler method
cProcProfiler.StartProfiler(sname As String, Optional pLevel As ptLevels = ptLevelAll) purpose Starts a profiling session that will last until the FinishProfiler method is executed. arguments sName –Purely informational and results will be reported against this name. In more […]
Microsoft Office & VBA
ptLevels enum
Public Enum ptLevels ptLevelNone ptLevelLow ptLevelMedium ptLevelHigh ptLevelAll purpose These are given when creating a cProcProfiler or starting a cProcTimer. They allow you to filter which sections to include in a profile session without having […]
Microsoft Office & VBA
procProfilerWrapper module
procProfilerWrapper Normally one cProcProfiler object is enough. A couple of wrapper procedures are provided so you can use the default profiler session without the need to create your own. Public Methods Usage Name Returns Argument(s) […]
Microsoft Office & VBA
cProcTimer
cProcTimer Class Normally you do not need to access this class. However, some of the properties can be useful for more advanced applications and rogue use case identification. Public Methods Usage Name Returns Argument(s) Summary […]
Microsoft Office & VBA
cProcProfiler
cProcProfiler Class Your main interaction with the profiler is through this class. The Usage column indicates the type of profiling session in which you are likely to need to access a particular property or method. […]
Microsoft Office & VBA
Advanced profiling: Rogue Use Cases
Identifying rogue cases – (by that I mean a combination of circumstances that cause a normally well behaved procedure to take forever), is very simple using these profiling tools. For the sake of this example, […]
Microsoft Office & VBA
Objects and the garbage collector
When I first started playing around with VBA, everything I read emphasized how important it was that when you initialized an object, you remembered to set it to nothing afterwards in order to ‘release the […]
Microsoft Office & VBA
Profiling user defined functions
Once you’ve analyzed your workbook for columns which are slow to calculate, it may be that the evidence is showing that some of your user defined functions need to be optimized. Or perhaps you have […]
Microsoft Office & VBA
Strings and garbage
VBA has a garbage collector that runs from time to time in order to free up memory that is no longer being used. in Objects and the garbage collector I showed how to teardown objects so they […]
Microsoft Office & VBA
Execution Time Logging
Sometimes recalculation seems to take a long time and you cant find the formula that is slowing it all down. The advice you find is usually generic, telling you that function X() is slow etc, […]
Microsoft Office & VBA
Automatic Profiling
What is this about? If you want to press a button and include all the code you need to analyze your VBA code, this is what you need, and you can be up and profiling […]
Microsoft Office & VBA
Introduction to Optimization
Does Excel need optimization ? Generally speaking no. The normal use of excel with a few sheets and straightforward formulas does not need any optimization. However, sometimes workbooks grow, especially if you try to have […]
Microsoft Office & VBA
Optimization
What can you do with this? Profile Workbooks Profile Code Automatic Code Profiler It’s all about optimisation This section looks at how to exercise a workbook to identify the calculation speed of every single column, and also […]