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 formula test.
		* 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.
		* Different test 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 
		* Result is measured in seconds.

		Private Sub ExectuteFormulaTest_Click()
			FormulaTest.runTest Me
		End Sub


		Option Explicit

		Public Sub runTest(ByRef worksheetTests As Worksheet)
			Dim formulaTimer As timer
			Dim index As Long
			Dim rngAddress As String

			With Application
				.Calculation = xlCalculationManual
				.ScreenUpdating = False
				.DisplayStatusBar = False
				.EnableEvents = False
			End With

			Set worksheetTests = ActiveWorkbook.Sheets("Test")
			Set formulaTimer = New timer


			For index = 1 To 5000
				rngAddress = "D" & index
			worksheetTests.Range(rngAddress).FormulaArray = _
			Next index


			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 LargeInteger)
		Private Declare PtrSafe Sub QueryPerformanceFrequency Lib "kernel32" _
				(ByRef lpFrequency As LargeInteger)
		Private Type LargeInteger
			first32Bits As Long
			second32Bits As Long
		End Type

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

		Private Const MaxCombinations32Bits = 4294967296#
		Private this As TimerAttributes
		Private Sub Class_Initialize()
		End Sub
			Private Sub PerformanceFrequencySet()
				Dim tempFrequency As LargeInteger
				QueryPerformanceFrequency tempFrequency
				this.PerformanceFrequency = parseLargeInteger(tempFrequency)
			End Sub
		Public Sub start()
			Dim TempCounterStart As LargeInteger
			QueryPerformanceCounter TempCounterStart
			this.CounterStart = parseLargeInteger(TempCounterStart)
		End Sub

		Public Sub printTimeElapsed()
			Dim timeElapsed As Double
			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
			If ((this.CounterNow - this.CounterStart) / this.PerformanceFrequency) >= SecondsPassed Then
				checkXSecondsPassed = True
				checkXSecondsPassed = False
			End If
		End Function

			Private Sub counterNowSet()
				Dim TempCounterNow As LargeInteger
				QueryPerformanceCounter TempCounterNow
				this.CounterNow = parseLargeInteger(TempCounterNow)
			End Sub
		Private Function parseLargeInteger(ByRef largeInt As LargeInteger) As Double
			Dim first32Bits As Double
			Dim second32Bits As Double
			first32Bits = largeInt.first32Bits
			second32Bits = largeInt.second32Bits
			If first32Bits < 0 Then first32Bits = first32Bits + MaxCombinations32Bits
			If second32Bits < 0 Then second32Bits = first32Bits + MaxCombinations32Bits
			parseLargeInteger = first32Bits + (MaxCombinations32Bits * second32Bits)
		End Function