Nombre d'octets d'une formule calculé par macro VBA

[job75]

Bonjour le forum,

Suite à ce fil :

https://www.excel-downloads.com/threads/nombre-doctets-memoire-dune-expression.129833/

et à celui-ci :

https://www.excel-downloads.com/threads/formule-somme-de-2-colonnes-sur-4.148446/

où david84 souhaitait une macro pour calculer le nombre d'octets utilisés par une formule,

j'ai donc réalisé cette macro (clic droit sur l'onglet et Visualiser le code) :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Target.HasFormula Then Exit Sub 's'il n'y a pas de formule
Dim fich$, n1&, n2&
Cancel = True
fich = ThisWorkbook.Path & "\" & ThisWorkbook.Name

[IV1:IV2048].Formula = "=COUNTA(1,)"
[IV1:IV2048].Replace 1, "A1", LookAt:=xlPart 'pour que les formules soient toutes différentes (en notation R1C1)
ThisWorkbook.Save
n1 = FileLen(fich)

[IV1:IV2048].Replace ",", "," & Mid(Target.Formula, 2, 9 ^ 9)
ThisWorkbook.Save
n2 = FileLen(fich)

[IV1:IV2048].ClearContents
ThisWorkbook.Save
MsgBox "Nombre d'octets de la formule : " & Application.Round((n2 - n1) / 2048, 0)
End Sub

Fichier joint.

A+

 

[mth]

Re : Nombre d'octets d'une formule calculé par macro VBA

Bonjour job75

Un grand merci pour ton fichier, rudement bien !! (je l'ai mis bien au chaud pour m'en servir souvent )

Merci à toi et @ +

m

 

[david84]

Re : Nombre d'octets d'une formule calculé par macro VBA

Bonjour Job, mth,
j'en ai rêvé et Job l'a fait !
Merci à toi.
Si je comprends bien, il suffit de copier le code dans le fichier que l'on veut tester.
Y-a-t-il quelque chose à adapter ou cela fonctionnera tel quel ?
Autre question pendant que j'y suis : sais-tu si l'on peut se servir du timer pour calculer la vitesse d'exécution d'une formule Excel "native",

=SOMMEPROD((MOD(COLONNE(A1:Z1)-1;4)<2)*A1:Z1)

par exemple ?
Si oui, connais-tu la démarche ?
A+

 

[job75]

Re : Nombre d'octets d'une formule calculé par macro VBA

Bonjour Mireille David le forum,

Autre question pendant que j'y suis : sais-tu si l'on peut se servir du timer pour calculer la vitesse d'exécution d'une formule Excel "native"

A priori avec la macro que j'ai proposée on peut comparer les durées d'exécution de la macro sur 2 formules (utiliser Timer bien sûr).

Il faut un nombre de lignes suffisant en colonne IV (multiple de 512).

La durée d'exécution de la formule elle-même, pas évident...

Deux remarques sur la macro :

1) Sur mon ordi la valeur de (n2 - n1)/2048 est 41,25 : n2 présente un excédent de 512 octets.

D'où viennent ces octets, je ne sais pas, peut-être de la macro dont le nombre d'octets varierait en cours d'exécution...

2) J'utilise Application.Round plutôt que Round car cette dernière fonction n'existe pas sur Excel 97.

A+

 

[job75]

Re : Nombre d'octets d'une formule calculé par macro VBA

Re,

Une tentative pour cerner la durée de calcul d'une formule :

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Not Target.HasFormula Then Exit Sub 's'il n'y a pas de formule
Dim fich$, n1&, n2&, t1#, t2#, t3#, t4#
Cancel = True
fich = ThisWorkbook.Path & "\" & ThisWorkbook.Name

[IV1:IV8192].Formula = "=COUNTA(1,)"
[COLOR="Red"]t1 = Timer[/COLOR]
[IV1:IV8192].Replace 1, "A1", LookAt:=xlPart 'pour que les formules soient toutes différentes (en notation R1C1)
[COLOR="Red"]t2 = Timer[/COLOR]
ThisWorkbook.Save
n1 = FileLen(fich)

[COLOR="Red"]t3 = Timer[/COLOR]
[IV1:IV8192].Replace ",", "," & Mid(Target.Formula, 2, 9 ^ 9)
[COLOR="Red"]t4 = Timer[/COLOR]
ThisWorkbook.Save
n2 = FileLen(fich)

[IV1:IV8192].ClearContents
ThisWorkbook.Save
MsgBox "Nombre d'octets de la formule : " & Application.Round((n2 - n1) / 8192, 0)
[COLOR="Red"]MsgBox "Durée d'exécution de la formule (en µs) : " & Format(1000000 * IIf(t4 - t3 > t2 - t1, t4 - t3 - t2 + t1, 0) / 8192, "0.000")[/COLOR]
End Sub

Ce n'est sûrement pas très juste.

A+

 

[david84]

Re : Nombre d'octets d'une formule calculé par macro VBA

Re
Bonjour Job,
j'ai fais quelques tests sur ton nouveau fichier et chez moi cela semble fonctionner.
Le problème, c'est le temps de traitement qui risque d'en décourager plus d'un.
J'ai tout de même remarqué que l'augmentation du nombre d'octets allait de pair avec une vitesse d'exécution ralenti. Bon, là bien sûr, on parle de temps de traitement très court mais ce qui serait intéressant, c'est de pouvoir mesurer ce temps de traitement sur des formules matricielles employées sur des plages de données bien plus importantes, ce qui permettrait d'optimiser les formules proposées (enfin, c'est l'idée).
J'ai regardé ta macro pour essayer de la comprendre, mais sans trop de succès.
Ce que je voulais voir, c'était de quelle manière ne garder que le timer permettant d'évaluer la vitesse de traitement de la formule, comme cela est possible en VBA, avec un temps de traitement très court.
Si cela permet effectivement de raccourcir considérablement le temps de traitement, cela vaudrait peut-être la peine d'avoir 2 macros :
- une permettant de calculer le nombre d'octets (ton 1er fichier)
- l'autre permettant de mesurer la vitesse de traitement de la formule utilisée (utilisation du timer).
Maintenant je ne sais pas ce que tu en penses (peut-être suis-je à côté de la plaque).
A+

 

[job75]

Re : Nombre d'octets d'une formule calculé par macro VBA

Bonjour David, le forum,

En effet il vaut mieux une nouvelle macro pour évaluer la durée d'exécution d'une formule.

Il faut d'ailleurs distinguer entre formule "normale" et formule matricielle.

J'ai donc ouvert une nouvelle discussion :

https://www.excel-downloads.com/threads/duree-dexecution-dune-formule.148727/

A+