Durée d'exécution d'une formule

[job75]

Bonjour le forum,

Suite à ce fil :

https://www.excel-downloads.com/threads/nombre-doctets-dune-formule-calcule-par-macro-vba.148601/

voici une macro permettant d'évaluer la durée d'exécution d'une formule :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Target.HasFormula Then Exit Sub 'si ce n'est pas une formule
Dim q As Byte, F$, n&, i&, t1#, t2#, t3#
Cancel = True
q = MsgBox("Formule validée matriciellement ?", 4) 'choix de la validation
F = Target.Formula
n = 100000 'nombre de boucles, à diminuer pour les formules lourdes (10000, 1000...)
Application.ScreenUpdating = False
Application.Calculation = xlManual 'pour éviter le recalcul éventuel d'autres formules
If q = 7 Then 'formule normale
  t1 = Timer
  For i = 1 To n
    Target.Formula = "=1"
  Next
  t2 = Timer
  For i = 1 To n
    Target.Formula = F
  Next
  t3 = Timer
Else 'formule matricielle
  t1 = Timer
  For i = 1 To n
    Target.FormulaArray = "=1"
  Next
  t2 = Timer
  For i = 1 To n
    Target.FormulaArray = F
  Next
  t3 = Timer
End If
Application.Calculation = xlAutomatic
Application.ScreenUpdating = True
MsgBox "Durée d'exécution de la formule (en µs) : " & Format(1000000 / n * IIf(t3 - t2 > t2 - t1, t3 - t2 - t2 + t1, 0), "0.0")
End Sub

On a le choix entre formule "normale" et formule validée matriciellement.

Fichier joint.

A+

 

[Staple1600]

Re : Durée d'exécution d'une formule

Bonjour job75

Merci pour cet outil de test

Comment se fait-il que le temps d'exécution de la macro soit plus long
que le calcul des formules ?

PS: résultat avec Excel 2000 sous Vista: 34,9 (double-clic sur D6, formule normale)

 

[job75]

Re : Durée d'exécution d'une formule

Bonjour le forum, bonjour Staple

J'ai édité la macro du post #1 car j'avais oublié de déclarer la variable i...

De plus j'ai ajouté la variable n (=100000) qu'il suffit de diminuer si la formule à étudier est trop lourde.

@ Staple : le calcul de la durée d'exécution de la formule est la différence des durée d'exécution de 2 boucles, d'autant plus longues que n est grand.

@ david84 : suite à ton MP, il semble que cette macro pose problème sur Excel 2007. Je ne vois pas pourquoi car c'est une macro très élémentaire (et je n'ai pas cette version d'Excel).

A+

 

[david84]

Re : Durée d'exécution d'une formule

Re,
Job, Staple,
C'est bizarre effectivement...peut-être que d'autres tests effectués par des personnes possédant XL 2007 permettraient de vérifier si les bugs (quand validation matricielle où il faut forcer la sortie de la macro par la touche echap) et temps d'exécution long (25 à 30 secondes quand validation classique) constatés sur mon ordinateur perdurent.
A+

 

[job75]

Re : Durée d'exécution d'une formule

Bonsoir david84, le forum,

Ton ordi est peut-être tout simplement trop lent sur Excel 2007.

De toute façon avec n = 100.000 boucles ça prend un peu de temps, il suffit d'être un peu patient.

On peut réduire le nombre n, mais attention, le résultat risque d'être imprécis (à cause de Timer dont la précision est seulement de 1/100ème de seconde).

Si l'on arrête la macro par <Echap>, normal qu'il y ait bug

A+

 

[BOISGONTIER]

Re : Durée d'exécution d'une formule

Bonjour,

Salaire =Feuil1!$D$2:$D$40000
Service =Feuil1!$B$2:$B$40000
Statut =Feuil1!$C$2:$C$40000

0,03 sec Excel 2002
0,07 sec Excel 2007

Sub Essai()
  t = Timer()
  total = [SumProduct((Service="compta")*(statut="cadre")*Salaire)]
  MsgBox Timer() - t
  MsgBox total
End Sub

JB

 

[david84]

Re : Durée d'exécution d'une formule

Re

Job, Staple, JB,
peut-être qu'effectivement mon ordi est trop lent sur 2007, c'est pour cela que je demandais à d'autres personnes possédant 2007 de tester ton fichier pour voir.

Si l'on arrête la macro par <Echap>, normal qu'il y ait bug

Je suis obligé de faire cette manip au bout de 60 sec pour pouvoir arrêter la macro.

Sur ton ordi, quel est le temps d'attente entre le moment où tu valides par double clic et le moment où la boîte de dialogue affichant les résultats apparaît chez toi (validations classique et matricielle) ?
Staple, même question qu'à Job : peux-tu faire le test (34,9 : c'est des secondes ?) ?
Le test de JB est intéressant et montre ce qu'il faudrait obtenir avec un affichage instantané du résultat mais oblige à rentrer la formule à tester.
Ne pourrait-on pas inclure dans la macro le fait que celle-ci fonctionne quelle que soit la formule à tester ?
Désolé, mais sur ce coup-là, vu mes connaissances en VBA, je ne peux vous être d'aucune utilité, sauf à vous donner mon avis (et encore, je ne sais pas s'il vous est utile ou si je suis à côté de la plaque).
A+

 

[job75]

Re : Durée d'exécution d'une formule

Bonjour,

Sur ordi portable Windows XP avec Excel 2003 :

- double-clic sur D6 (formule normale) => total 14 s et résultat formule 54 µs

- double-clic sur D8 (formule matricielle) => total 32 s et résultat formule 85 µs

A+

 

[job75]

Re : Durée d'exécution d'une formule

Re,

Concernant la macro de JB (que je salue).

Il n'y a pas besoin de boucle, car la durée d'exécution de la formule est très longue (30000 µs).

Elle montre simplement que le calcul sur Excel 2007 est plus de 2 fois plus long que sur 2002...

A+