PDA

Vollständige Version anzeigen : Zeitmessung mit Excel


jinx
12.06.2001, 13:38
<font size="2" face="Century Gothic">Moin,

wer in Excel Zeitmessungen vornehmen will, muss dazu entweder die VBA-Funktion Now() (Auflösung 1 Sekunde) oder Timer (liefert die Zahl der Millisekunden seit Mitternacht mit einer Auflösung von etwa 10 Millisekunden) verwenden. Für längere Laufzeiten ist also ein Vorgehen wie das folgende ausreichend:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span>
&nbsp;
<span class="TOKEN">Sub</span> TimeTest()
<span class="REM">' aus: John Walkenbach, MS Excel 2000 Power Programmning with VBA,</span>
<span class="REM">' Hungy Minds, Inc., New York, NY - Cleveland, OH - Indianapolis, IN, 1999</span>
<span class="REM">' ISBN: 0-7645-3263-4</span>
<span class="REM">' Seite 171f</span>
<span class="REM">'</span>
<span class="REM">' dient eigentlich als Nachweis, das man mit Variablendeklaration &quot;schneller&quot; arbeiten kann</span>
<span class="REM">' daher sei hier nur am Rande der Hinweis auf die Verwendung von Pr&auml;fixen hingewiesen</span>
<span class="REM">'</span>
<span class="TOKEN">Dim</span> x <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> y <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> A <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> B <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> C <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> i <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> j <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> StartTime <span class="TOKEN">As</span> Date
<span class="TOKEN">Dim</span> EndTime <span class="TOKEN">As</span> Date
<span class="REM">'</span>
<span class="REM">' Speichert die Startzeit</span>
StartTime = Timer
<span class="REM">'</span>
<span class="REM">' f&uuml;hrt einige Berechnungen durch</span>
<span class="REM">' genau das ;-)</span>
x = 0
y = 0
<span class="TOKEN">For</span> i = 1 <span class="TOKEN">To</span> 1000
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> 1000
A = x + y + i
B = y - x - i
C = x - y - i
<span class="TOKEN">Next</span> j
<span class="TOKEN">Next</span> i
<span class="REM">'</span>
<span class="REM">' speichert die Endzeit</span>
EndTime = Timer
<span class="REM">'</span>
<span class="REM">' zeigt die Gesamtzeit in Sekunden an</span>
<span class="REM">' Mitteilungsbox ein wenig ver&auml;ndert</span>
MsgBox &quot;Die Berechnung dauerte &quot; &amp; Format(EndTime - StartTime, &quot;0.0&quot;) &amp; &quot; Sekunden.&quot;, _
vbInformation, &quot;Info an &quot; &amp; Application.UserName
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)</font>

jinx
15.06.2001, 11:35
<font size="2" face="Century Gothic">Moin,

wer genauere Angaben im Millisekundenbereich benötigt, sollte auf API zurückgreifen. In der vorgestellten Lösung wird ein Klassenmodul names <b>CHighResTimer</b> angelegt, in welches der folgende Code eingefügt wird:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="REM">'******************************************************************************* ********</span>
<span class="REM">'PUT FOLLOWING CODE IN A CLASS MODULE CHighResTimer</span>
<span class="REM">'******************************************************************************* ********</span>
<span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span>
&nbsp;
<span class="REM">'How many times per second is the counter updated?</span>
<span class="TOKEN">Private</span> <span class="TOKEN">Declare</span> <span class="TOKEN">Function</span> QueryFrequency <span class="TOKEN">Lib</span> &quot;kernel32&quot; _
<span class="TOKEN">Alias</span> &quot;QueryPerformanceFrequency&quot; ( _
lpFrequency <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>) <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
&nbsp;
<span class="REM">'What is the counter's value</span>
<span class="TOKEN">Private</span> <span class="TOKEN">Declare</span> <span class="TOKEN">Function</span> QueryCounter <span class="TOKEN">Lib</span> &quot;kernel32&quot; _
<span class="TOKEN">Alias</span> &quot;QueryPerformanceCounter&quot; ( _
lpPerformanceCount <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>) <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
&nbsp;
<span class="REM">'Variables to store the counter information</span>
<span class="TOKEN">Dim</span> cyFrequency <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
<span class="TOKEN">Dim</span> cyOverhead <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
<span class="TOKEN">Dim</span> cyStarted <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
<span class="TOKEN">Dim</span> cyStopped <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
&nbsp;
<span class="TOKEN">Private Sub</span> Class_Initialize()
<span class="TOKEN">Dim</span> cyCount1 <span class="TOKEN">As</span> Currency, cyCount2 <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
&nbsp;
<span class="REM"> 'Get the counter frequency</span>
QueryFrequency cyFrequency
&nbsp;
<span class="REM"> 'Call the hi-res counter twice, to check how long it takes</span>
QueryCounter cyCount1
QueryCounter cyCount2
&nbsp;
<span class="REM"> 'Store the call overhead</span>
cyOverhead = cyCount2 - cyCount1
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span><hr>&nbsp;
<span class="TOKEN">Public Sub</span> StartTimer()
<span class="REM"> 'Get the time that we started</span>
QueryCounter cyStarted
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span><hr>&nbsp;
<span class="TOKEN">Public Sub</span> StopTimer()
<span class="REM"> 'Get the time that we stopped</span>
QueryCounter cyStopped
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span><hr>&nbsp;
<span class="TOKEN">Public</span> <span class="TOKEN">Property Get</span> Elapsed() <span class="TOKEN">As</span> <span class="TOKEN">Double</span>
<span class="TOKEN">Dim</span> cyTimer <span class="TOKEN">As</span> <span class="TOKEN">Currency</span>
&nbsp;
<span class="REM"> 'Have we stopped or not?</span>
<span class="TOKEN">If</span> cyStopped = 0 <span class="TOKEN">Then</span>
QueryCounter cyTimer
<span class="TOKEN">Else</span>
cyTimer = cyStopped
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="REM"> 'If we have a frequency, return the duration, in seconds</span>
<span class="TOKEN">If</span> cyFrequency &gt; 0 <span class="TOKEN">Then</span>
Elapsed = (cyTimer - cyStarted - cyOverhead) / cyFrequency
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">Property</span></pre></div>

Aufgerufen wird dann folgendes Makro aus einem allgemeinen Modul; für die Berechnung dient wieder das Beispiel von Walkenbach:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span>
&nbsp;
<span class="TOKEN">Sub</span> TimeTestAPI()
<span class="REM">' aus: John Green / Stephen Bullen / Felipe Martins: Excel 2000 VBA Programmer´s Reference</span>
<span class="REM">' Wrox Press Ltd., Birmingham, 1999, 3. Reprint Novemder 2000</span>
<span class="REM">' Kapitel 18: Programming with Windows API, Seite 336f</span>
<span class="TOKEN">Dim</span> obTimer <span class="TOKEN">As</span> <span class="TOKEN">New</span> CHighResTimer
<span class="TOKEN">Dim</span> x <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> y <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> A <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> B <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> C <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> i <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> j <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="REM">'</span>
<span class="REM">' Speichert die Startzeit</span>
obTimer.StartTimer
<span class="REM">'</span>
<span class="REM">' f&uuml;hrt einige Berechnungen durch</span>
<span class="REM">' genau das ;-)</span>
x = 0
y = 0
<span class="TOKEN">For</span> i = 1 <span class="TOKEN">To</span> 1000
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> 1000
A = x + y + i
B = y - x - i
C = x - y - i
<span class="TOKEN">Next</span> j
<span class="TOKEN">Next</span> i
<span class="REM">'</span>
<span class="REM">' speichert die Endzeit</span>
obTimer.StopTimer
&nbsp;
<span class="REM">' zeigt die Gesamtzeit in Sekunden an</span>
<span class="REM">' Mitteilungsbox ein wenig ver&auml;ndert</span>
MsgBox &quot;Die Berechnung dauerte &quot; &amp; obTimer.Elapsed &amp; &quot; Sekunden.&quot;, _
vbInformation, &quot;Info an &quot; &amp; Application.UserName
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)</font>

jinx
10.10.2001, 16:11
<font size="2" face="Century Gothic">Moin @ all,

eine weitere Alternative wäre ein Vorgehen durch die Verwendung der Methode <b>GetTickCount()</b> aus der Dynamic Link Library <i>kernel32.dll</i> zu erreichen. Damit wird ein Wert vom Datentyp Long zurückgegeben, der die Anzahl der Millisekunden ausweist, die seit dem Start von Windows vergangen sind. Um das Ergebnis in Millisekunden zu sehen, muss das erhaltene Ergebnis durch 1000 geteilt werden, womit wir bei der Anzeige in etwa ein Resultat wie mit der Timer-Funktion erhalten:

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Explicit</span>
&nbsp;
<span class="REM">'Deklaration im Deklarationsbereich des Moduls</span>
<span class="TOKEN">Private</span> <span class="TOKEN">Declare</span> <span class="TOKEN">Function</span> GetTickCount <span class="TOKEN">Lib</span> &quot;kernel32.dll&quot; () <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
&nbsp;
<span class="TOKEN">Sub</span> TimeTestTickCount()
<span class="REM">' aus: Christian Friedrich, VBA mit Excel - Das umfassende Handbuch</span>
<span class="REM">' Galileo Press GmbH, Bonn, 2004</span>
<span class="REM">' Kapitel 22: Datum und Zeit / Laufzeit ermitteln , Seite 372f</span>
<span class="TOKEN">Dim</span> lngStartTime <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> x <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> y <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> A <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> B <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> C <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> i <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> j <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="REM">'</span>
<span class="REM">' Speichert die Startzeit</span>
lngStartTime = GetTickCount
<span class="REM">'</span>
<span class="REM">' f&uuml;hrt einige Berechnungen durch</span>
<span class="REM">' genau das ;-)</span>
x = 0
y = 0
<span class="TOKEN">For</span> i = 1 <span class="TOKEN">To</span> 1000
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> 1000
A = x + y + i
B = y - x - i
C = x - y - i
<span class="TOKEN">Next</span> j
<span class="TOKEN">Next</span> i
&nbsp;
<span class="REM">' zeigt die Gesamtzeit in Sekunden an</span>
MsgBox &quot;Die Berechnung dauerte &quot; &amp; (GetTickCount - lngStartTime) / 1000 &amp; &quot; Sekunden.&quot;, _
vbInformation, &quot;Info an &quot; &amp; Application.UserName
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)</font>