Un de mes clients avait d’énormes problèmes sur un de ces modèles. Le copier / coller fonctionnait très mal et régulièrement Excel était figé.
A chaque fois que l’utilisateur cliquait sur « Annuler Saisie », Excel était figé et plus rien ne fonctionnait: il fallait tuer le processus Excel.
Après avoir pris le temps d’échanger avec l’utilisateur pour comprendre depuis quand venait le problème, si celui-ci était systématique, sur tous les ordinateurs, etc…
J’ai très rapidement compris que nous étions face à un problème de temps de calculs.
- vérification que toute la feuille n’est pas utilisée avec CTRL + FIN pour trouver la dernière cellule utilisée.
- Contrôle des mises en forme conditionnelles (si le nombre est trop important, cela peut ralentir le calcul).
- Recherche de fonctions volatiles (ALEA, MAINTENANT, AUJOURDHUI, DECALER, CELLULE, INDIRECT, INFORMATION, …)
- Recherche de tables de données
- Vérification si l’appareil photo Excel est utilisé.
Il s’agissait bien d’un problème de mises en forme conditionnelles mais le problème n’était pas totalement résolu. Le nombre de feuilles et de cellules étant important, il a fallu travailler en resserrant l’étau: calculer le temps de calcul du classeur, puis de chaque feuille, de certaines plages de cellules.
Voici comment j’ai procédé:
J’ai tout d’abord ajouté le code VBA suivant dans mon classeur EXCEL, dans un module dédié.
Private Declare Function getFrequency Lib « kernel32 » _
Alias « QueryPerformanceFrequency » (cyFrequency As Currency) As Long
Private Declare Function getTickCount Lib « kernel32 » _
Alias « QueryPerformanceCounter » (cyTickCount As Currency) As Long
Function MicroTimer() As Double
‘
‘ Returns seconds.
Dim cyTicks1 As Currency
Static cyFrequency As Currency
‘
MicroTimer = 0
‘ Get frequency.
If cyFrequency = 0 Then getFrequency cyFrequency
‘ Get ticks.
getTickCount cyTicks1
‘ Seconds
If cyFrequency Then MicroTimer = cyTicks1 / cyFrequency
End Function
Sub RangeTimer()
DoCalcTimer 1
End Sub
Sub SheetTimer()
DoCalcTimer 2
End Sub
Sub RecalcTimer()
DoCalcTimer 3
End Sub
Sub FullcalcTimer()
DoCalcTimer 4
End Sub
Sub DoCalcTimer(jMethod As Long)
Dim dTime As Double
Dim dOvhd As Double
Dim oRng As Range
Dim oCell As Range
Dim oArrRange As Range
Dim sCalcType As String
Dim lCalcSave As Long
Dim bIterSave As Boolean
‘
On Error GoTo Errhandl
‘ Initialize
dTime = MicroTimer
‘ Save calculation settings.
lCalcSave = Application.Calculation
bIterSave = Application.Iteration
If Application.Calculation <> xlCalculationManual Then
Application.Calculation = xlCalculationManual
End If
Select Case jMethod
Case 1
‘ Switch off iteration.
If Application.Iteration <> False Then
Application.Iteration = False
End if
‘ Max is used range.
If Selection.Count > 1000 Then
Set oRng = Intersect(Selection, Selection.Parent.UsedRange)
Else
Set oRng = Selection
End If
‘ Include array cells outside selection.
For Each oCell In oRng
If oCell.HasArray Then
If oArrRange Is Nothing Then
Set oArrRange = oCell.CurrentArray
End If
If Intersect(oCell, oArrRange) Is Nothing Then
Set oArrRange = oCell.CurrentArray
Set oRng = Union(oRng, oArrRange)
End If
End If
Next oCell
sCalcType = « Calculate » & CStr(oRng.Count) & _
» Cell(s) in Selected Range: «
Case 2
sCalcType = « Recalculate Sheet » & ActiveSheet.Name & « : «
Case 3
sCalcType = « Recalculate open workbooks: «
Case 4
sCalcType = « Full Calculate open workbooks: «
End Select
‘ Get start time.
dTime = MicroTimer
Select Case jMethod
Case 1
If Val(Application.Version) >= 12 Then
oRng.CalculateRowMajorOrder
Else
oRng.Calculate
End If
Case 2
ActiveSheet.Calculate
Case 3
Application.Calculate
Case 4
Application.CalculateFull
End Select
‘ Calculate duration.
dTime = MicroTimer – dTime
On Error GoTo 0
dTime = Round(dTime, 5)
MsgBox sCalcType & » » & CStr(dTime) & » Seconds », _
vbOKOnly + vbInformation, « CalcTimer »
Finish:
‘ Restore calculation settings.
If Application.Calculation <> lCalcSave Then
Application.Calculation = lCalcSave
End If
If Application.Iteration <> bIterSave Then
Application.Calculation = bIterSave
End If
Exit Sub
Errhandl:
On Error GoTo 0
MsgBox « Unable to Calculate » & sCalcType, _
vbOKOnly + vbCritical, « CalcTimer »
GoTo Finish
End Sub
En résumé, j’ai ajouté 4 procédures. En tapant sur ALT+F8, vous pouvez les exécuter:
Tout d’abord, il faut fermer toutes les applications et ne garder ouvert que le classeur qui pose problème de façon à ne pas avoir de pollution lors de la mesure du temps de calcul.
Ensuite, j’ai vérifié que j’étais bien en calcul manuel (par défaut mes classeurs sont en manuel).
A ce moment, j’ai pu commencer l’analyse, en commençant par la mesure du calcul global puis du recalcul du classeur juste après. Ainsi si le temps de recalcul est long, cela signifie que nous avons encore des fonctions volatiles qui peuvent poser problème.
Ensuite, j’ai fait l’analyse pour chaque feuille de calcul et j’ai pu identifier celle qui posait problème (qui était assez facilement identifiable sans la macro, mais nous pouvons toujours avoir des surprises).
J’ai ensuite divisé ma feuille en plusieurs parties et ai lancer l’analyse sur les plages sélectionnées. Ainsi je suis arrivé aux cellules qui posaient problème.
En fait, pour chaque cellule identifiée, une fonction VBA était appelée pour calculer un nombre de cellule d’une plage ayant une couleur particulière. Etant donné le nombre de cellules, le temps de calcul était très long. Pour tester, j’ai supprimer ces cellules et le calcul était instantané.
J’ai donc ouvert VBA et ai analysé le code. Il m’a suffit d’ajouter au début du code:
Application.Calculation = xlCalculationManual
puis à la fin du code:
Application.Calculation = xlCalculationAutomatic
Et le problème était résolu!
Pour information, le temps de calcul du classeur avant:
Le temps de calcul du classeur après:
