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

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 […]

Optimization

Countdown timer

Implementing an asynchronous timer for Excel Get it now This article will show you how to implement a progress bar or countdown timer in VBA, and give you a downloadable example and reusable classes to get you started. […]