Audit d’un modèle: les temps de calcul

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.

Les premières étapes sont classiques:

  • 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:

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *