Home Resume Excel Birds

F O R M U L A   T E S T E R

 
			* Section I contains code that executes the formula test.
			* Section II contains code for a timer class that the formula test uses to clock 
				the execution speed of the tested formula
			* The test worksheet must be designed such that there is data available for the formula 
				to perform the calculation that it is meant to perform. 
			* Obviously different data sets will yield different results. Use judgement when crafting the test data set. 
			* When the formula test executes, the tested formula enters into range D1:D5000.
			* A successful formula test outputs the result in the immediate window inside 
				visual basic for application's integrated development environment.
			* Result is measured in seconds.
			
				Option Explicit

				Sub testFormula()
					Dim ws As Worksheet
					Dim Timer As Timer
					Dim Index As Long
					Dim rngAddress As String
    
					With Application
						.Calculation = xlCalculationManual
						.ScreenUpdating = False
						.DisplayStatusBar = False
						.EnableEvents = False
					End With

					Set ws = Sheets("Test")
					Set Timer = New Timer
    
					Timer.TimerSet 
    
					For Index = 1 To 5000
						rngAddress = "D" & Index
						ws.Range(rngAddress).FormulaArray = _
							"=SUM(INDEX(G1:G4,N(IF(1,MATCH(A1:A5000,F1:F4,0))))*B1:B5000)"
					Next Index

					Timer.PrintTimeElapsed

					With Application
						.Calculation = xlCalculationAutomatic
						.ScreenUpdating = True
						.DisplayStatusBar = True
						.EnableEvents = True
					End With
				End Sub
			
				Option Explicit
    
				Private Declare PtrSafe Sub QueryPerformanceCounter Lib "kernel32" _
						(ByRef lpPerformanceCount As LARGE_INTEGER)
        
				Private Declare PtrSafe Sub QueryPerformanceFrequency Lib "kernel32" _
						(ByRef lpFrequency As LARGE_INTEGER)
    
				Private Type LARGE_INTEGER
					First32Bits As Long
					Second32Bits As Long
				End Type

				Private Type TIMER_ATTRIBUTES
					CounterStart As Double
					CounterNow As Double
					PerformanceFrequency As Double
				End Type

				Private Const MaxCombinations_32Bits = 4294967296#
				Private This As TIMER_ATTRIBUTES
    
				Private Sub Class_Initialize()
					PerformanceFrequencySet
				End Sub
    
					Private Sub PerformanceFrequencySet()
						Dim TempFrequency As LARGE_INTEGER
						QueryPerformanceFrequency TempFrequency
						This.PerformanceFrequency = ParseLargeInteger(TempFrequency)
					End Sub
    
				Public Sub Start()
					Dim TempCounterStart As LARGE_INTEGER
					QueryPerformanceCounter TempCounterStart
					This.CounterStart = ParseLargeInteger(TempCounterStart)
				End Sub

				Public Sub PrintTimeElapsed()
					Dim TimeElapsed As Double
					CounterNowSet
					TimeElapsed = (This.CounterNow - This.CounterStart) / This.PerformanceFrequency
					Debug.Print Format(TimeElapsed, "0.000000"); " Seconds Elapsed "
				End Sub

				Public Function CheckXSecondsPassed(ByVal SecondsPassed As Double) As Boolean
					CounterNowSet
					If ((This.CounterNow - This.CounterStart) / This.PerformanceFrequency) >= SecondsPassed Then
						CheckXSecondsPassed = True
					Else
						CheckXSecondsPassed = False
					End If
				End Function

					Private Sub CounterNowSet()
						Dim TempCounterNow As LARGE_INTEGER
						QueryPerformanceCounter TempCounterNow
						This.CounterNow = ParseLargeInteger(TempCounterNow)
					End Sub
    
				Private Function ParseLargeInteger(ByRef LargeInteger As LARGE_INTEGER) As Double
					Dim First32Bits As Double
					Dim Second32Bits As Double
    
					First32Bits = LargeInteger.First32Bits
					Second32Bits = LargeInteger.Second32Bits
					If First32Bits < 0 Then First32Bits = First32Bits + MaxCombinations_32Bits
					If Second32Bits < 0 Then Second32Bits = First32Bits + MaxCombinations_32Bits
					ParseLargeInteger = First32Bits + (MaxCombinations_32Bits * Second32Bits)
				End Function