Le débogage de code VBA

DEBUTONS LE DEBOGAGE : COMPILONS ! 

Pourquoi compiler ? 

Quand compiler ?

Comment compiler ?

DEBOGUONS LES ERREURS D’EXECUTION ET DE LOGIQUE

Ajoutons la barre de menu du débogueur

Exécutons le code en mode pas à pas

Examinons les techniques et outils à notre disposition

Affichage de la valeur d’une variable

Mise en place de point d’arrêt

Mise en place d’espions

La fenêtre des « Variables locales »

La fenêtre d’exécution

Utilisation de l’instruction « Debug.Print »

Utilisation de l’instruction « Msgbox »

APRES DEBOGAGE, N’OUBLIONS PAS !

 

 

DEBUTONS LE DEBOGAGE : COMPILONS !

Le langage VBA est du type « interprété » alors que d’autre langages sont de type « compilé » (tels C++, C#, Pascal…)

Cela signifie qu’il n’y a pas nécessité de compiler les codes VBA pour pouvoir les exécuter.

L’interpréteur VBA se charge de transformer « à la volée », ligne à ligne, le code VBA compréhensible par l’humain en code compréhensible par la machine (exécutable).

Toutefois, VBA possède une fonction de compilation produisant à partir du code source VBA un autre objet exécutable par Windows.

Il semble que cette opération n’ait pas d’impact significatif sur les performances d’exécution des codes VBA, contrairement à ce qu’on pourrait attendre.

 

Pourquoi compiler ?

En compilant notre code, nous pourrons éliminer les erreurs de syntaxe non corrigées ou non détectées lors de la saisie du code dans l’éditeur VBE.

Ici, une liste non exhaustive des erreurs commises couramment détectées lors de la compilation :

  • Instruction IF sans END IF
  • Directive WITH sans END WITH
  • Instruction SELECT sans END SELECT
  • Instruction FOR ou FOR EACH sans NEXT
  • Appel de procédure ou fonction inexistantes ou non atteignables
  • Passage de paramètres non conformes ou omis lors d’appel aux procédures et fonctions.
  • Variable non déclarée en cas d’utilisation de la clause « Option Explicit ».

VBA ne nous contraint pas à déclarer toutes les variables utilisées dans le code (d’ailleurs, par exemple, l’enregistreur de macro ne le fait pas).

Cependant, beaucoup d’auteurs de référence recommandent l’utilisation de la clause « Option explicit »

Concernant l’utilisation des variables en VBA, nous nous reporterons utilement aux chapitres de la formation VBA du site « EXCEL-PRATIQUE » :

https://www.excel-pratique.com/fr/vba/variables

https://www.excel-pratique.com/fr/vba/variables_suite

 

Quand compiler ?

Le compilateur VBA n’indique pas les erreurs en bloc mais s’arrête à chaque fois qu’il en rencontre une pour la signaler.

Nous avons tout intérêt, pour corriger nos erreurs de syntaxe, à lancer régulièrement une compilation au fur et à mesure de l’écriture d’une séquence de code conséquente.

Comment compiler ?

Nous exécutons la compilation de notre projet VBA par l’activation des choix dans le menu VBE : Débogage/Compiler [nom de projet] ou le raccourci clavier : ‘Alt+D+l’ :

Menu_Debogage_Compiler

 

Lorsqu’un projet est compilé sans erreur, le choix « Compiler [nom de projet] » apparaît en grisé dans le sous- menu « Débogage ».

 

DEBOGUONS LES ERREURS D’EXECUTION ET DE LOGIQUE

Une fois notre code compilé sans erreur, nous nous attaquons au débogage des erreurs se produisant à l’exécution ainsi que des erreurs de logique.

 

Ajoutons la barre de menu du débogueur

Pour nous faciliter la tâche, nous pouvons ajouter à l’éditeur VBA la barre de menu spécifique au débogage : En cliquant droit sur la barre de menu et en sélectionnant « Débogage » :

La barre suivante est ajoutée :

Cette barre reprend les fonctions accessibles dans le sous-menu « Débogage » :

Exécutons le code en mode pas à pas

Dans l’éditeur VBE, nous avons la possibilité d’exécuter le code suivant diverses modalités :

En positionnant le curseur sur le début d’une procédure ou fonction et en cliquant sur le bouton dans la barre de débogage   (ou F8) nous exécutons le code en mode pas à pas, sur chaque ligne d’instructions de notre code :

De cette façon, nous suivrons le cheminement de notre code, en vérifiant la logique et corrigeant les erreurs.

Nous ne pouvons exécuter de cette façon que les procédures (Sub) ou fonctions (Function) qui n’attendent aucun paramètre d’entrée. Pour déboguer une procédure ou fonction attendant des paramètres, nous débuterons le débogage dans une procédure ou fonction appelante transmettant les paramètres attendus.

Après avoir activé le mode pas à pas, la ligne de la procédure ou fonction à exécuter est surlignée en jaune :

Lorsque nous cliquons une nouvelle fois sur le bouton « Pas à pas » (ou F8), le débogueur se positionne sur la première instruction à exécuter, en négligeant le lignes de déclarations de constantes ou de variables :

Ainsi de suite jusqu’à la fin de la procédure ou fonction (End Sub/End Function)

En mode d’exécution pas à pas, en plaçant le curseur sur la flèche jaune dans la marge tout en maintenant le clic gauche de la souris, nous avons la possibilité de déplacer la prochaine exécution en aval ou en amont des instructions du code.

Examinons les techniques et outils à notre disposition

 

Affichage de la valeur d’une variable

En suivant l’exécution de notre code en mode « pas à pas », nous avons la possibilité de connaître la valeur de variables impliquée dans l’instruction à exécuter, en plaçant simplement le curseur sur cette variable : un « tip » nous indique sa valeur :

Mise en place de point d’arrêt

Nous positionnons un point d’arrêt en cliquant dans la marge devant une instruction (ou en cliquant sur le bouton  ou F9).

La ligne d’instruction apparaît alors surlignée en rouge foncé avec un repère de même couleur dans la marge :

Une fois un point d’arrêt positionné, lorsque nous exécutons le code en mode « continu » (F5), l’exécution s’arrêtera sur la ligne avant de l’exécuter :

Pour effacer tous les points d’arrêts positionnés : (Ctrl+Maj+F9).

 

Mise en place d’espions

 

Avec la mise en place d’espions, nous avons la possibilité de définir des expressions permettant soit de visualiser leurs valeurs tout au long de l’exécution, soit de stopper l’exécution lorsque l’expression devient vraie, soit de stopper l’exécution lorsque leurs valeurs changent.

Nous positionnons un espion en cliquant le bouton  (ou Alt+D+u).

Dans l’exemple suivant nous positionnons un espion permettant de suivre l’évolution de la valeur de la variable objet « oRange » :

La fenêtre « Espions » que nous faisons apparaître en cliquant sur le bouton (ou Alt+A+s), liste tous les espions positionnés avec leurs valeurs :

La fenêtre des « Variables locales »

La fenêtre « Variables locales » que nous faisons apparaître en cliquant sur le bouton   (ou Alt+A+v), liste toutes les variables de la procédure en cours :

Et pour les tableaux mémoire (array), la liste de toutes leurs dimensions et valeurs :

De plus, nous avons la possibilité dans cette fenêtre d’ouvrir, pour les variables objets, la liste de toutes les propriétés de l’objet avec leurs valeurs :

La fenêtre d’exécution

La fenêtre « d’exécution » que nous faisons apparaître en cliquant sur le bouton  (ou Ctrl+G), nous offre une multitude d’utilisations :

 

  • En y formulant une question, nous pouvons obtenir immédiatement la réponse.
    Par exemple si nous voulons connaitre le dossier du classeur actif, il suffit d’enter dans la fenêtre le signe « ? » suivi de la propriété recherchée. La valeur est alors restituée dans la ligne suivante de la fenêtre :

 

  • Nous pouvons également y exécuter une instruction en dehors du code de la procédure exécutée. Par exemple avec l’instruction suivante, on modifie la couleur du fond d’une plage définie au préalable :

Résultat obtenu :

  • Nous pouvons exécuter une procédure ou une fonction en lui passant des paramètres et obtenir la valeur retournée :
  • Nous pouvons affecter une valeur à une variable :
  • Nous pouvons y voir figurer les valeurs affectées par l’instruction « Print» (Cf ci-dessous).

Utilisation de l’instruction « Debug.Print »

L’instruction « Debug.Print » suivi d’une valeur introduite dans le code, nous permet de visualiser cette valeur dans la « Fenêtre exécution » (Ctrl+G)

Notons que cette instruction a l’avantage de pouvoir être laissée dans le code après mise au point du fait que son résultat n’apparaîtra pas à l’utilisateur dans un usage ordinaire.

Utilisation de l’instruction « Msgbox »

L’instruction « Msgbox » suivi d’une variable, permet d’obtenir l’affichage d’un message contenant la valeur de cette variable :

Le déroulement du code est interrompu tant que l’on ne clique pas sur le bouton « OK » du message.

Nous pouvons noter que le désavantage de cette instruction réside dans le fait qu’elle ne doit pas subsister dans le code une fois la mise au point finalisée pour ne pas faire apparaître aux utilisateurs un message obscur interrompant le déroulement du programme.

 

 

APRES DEBOGAGE, N’OUBLIONS PAS !

Après notre phase de mise au point, nous n’oublions pas :

  • De supprimer tous les points d’arrêts positionnés.
  • De supprimer tous les espions interrompant le code.
  • De supprimer toutes les instructions « Msgbox » introduites dans le code pour afficher des valeurs.

 

Allons vers d’autres articles « Bonnes Pratiques VBA ».

Version PDF de cet article : Outils de débogage VBA

Révéler les lignes et colonnes cachées

 

Cette macro révèle les lignes et colonnes cachées dans toutes les feuilles d’un classeur.

L’utilisateur doit indiquer le classeur à traiter.

Sub MontrerTout()
'------------------------------------------------------------------------------------------------
'Procédure pour révéler toutes les lignes et colonnes cachées dans toutes les feuilles d'un classeur
'-l'utilisateur doit choisir le classeur à traiter
'-En fin de traitement le classeur traité reste ouvert
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à traiter
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à traiter", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
        'On révèle toutes les lignes de la feuille
        oSheet.Rows.EntireRow.Hidden = False
        'On rend visible le contenu de toutes les lignes
        oSheet.Rows.EntireRow.AutoFit
        'On révèle toutes les colonnes de la feuille
        oSheet.Columns.EntireColumn.Hidden = False
        'On rend visible le contenu de toutes les colonnes
        oSheet.Columns.EntireColumn.AutoFit
   Next
    
    'Séquence de fin de traitement
    MsgBox "Les lignes et colonnes du classeur '" & sWBName & "' ont toutes été rélévées.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "La révélation de toutes les lignes et colonnes du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE POURSUIVRE LE TRAITEMENT"
    End Select
    
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Protéger/déprotéger les feuilles

Toutes feuilles d’un classeur sont protégées avec la première macro en leur affectant un mot de passe donné.

La seconde macro déprotège toutes les feuilles d’un classeur précédemment protégées par un mot de passe donné.

Les mots de passe peuvent être modifiés.

Macro de protection des feuilles d'un classeur

Sub ProtegeFeuilles()
'------------------------------------------------------------------------------------------------
'Procédure de protection des feuilles d'un classeur avec le mot de passe générique cPWD
'-l'utilisateur doit choisir le classeur à protéger
'-Le classeur est fermé a l'issue du traitement
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cPWD = "0987654"      'Password à adapter
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à protéger
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à protéger", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
        'On protège la feuille avec le mot de passe
        oSheet.Protect cPWD
    Next
    
    'Séquence de fin de traitement
    oWB.Close True   'On ferme le classeur protégé
    MsgBox "Les feuilles du classeur '" & sWBName & "' sont protégées avec le mot de passe générique.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "L'opération de protection du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE PROTEGER LE CLASSEUR"
    End Select
    oWB.Close False
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Macro de déprotection des feuilles d'un classeur

Sub DeprotegeFeuilles()
'------------------------------------------------------------------------------------------------
'Procédure de déprotection des feuilles d'un classeur protégé avec le mot de passe générique
'-l'utilisateur doit choisir le classeur à déprotéger
'-Le classeur est fermé a l'issue du traitement
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cPWD = "0987654"      'Password à adapter
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"
    
    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à déprotéger
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à déprotéger", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
    
    'On boucle sur toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
         'On déprotège la feuille
        oSheet.Unprotect cPWD
    Next
    
    'Séquence de fin de traitement
    oWB.Close True   'On ferme le classeur
    MsgBox "Les feuilles du classeur '" & sWBName & "' sont déprotégées du mot de passe générique.", vbExclamation, "FIN D'OPERATION"
    
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub
'Séquence de traitement des erreurs
Gestion_Err:
    Select Case Err
        Case Else
            MsgBox "L'opération de déprotection du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
                    & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE DEPROTEGER LE CLASSEUR"
    End Select
    
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Coloriser les cellules suivant leur contenu

La colorisation du texte des cellules suivant leur contenu peut améliorer la lisibilité des tableurs comportant un grand nombre de cellules.

La macro proposée réalise la colorisation de toutes les cellules d’un tableur suivant la charte suivante :

  • le texte des cellules contenant des calculs EXCEL est colorisé en vert
  • le texte des cellules contenant une référence à une autre cellule de la même feuille est colorisé en bleu marine
  • le texte des cellules contenant une référence à une autre cellule dans une autre feuille est colorisé en violet
  • Dans tous ces cas, le corps du texte est mis en gras
  • Le texte de la cellule est colorisé en rouge dans le cas où son contenu est #REF!

Les couleurs peuvent être modifiées pour correspondre à une autre charte de couleurs.

L’utilisateur indique le tableur à coloriser, la macro s’occupe du reste…

 

Sub ColorisationDesCellules()
'------------------------------------------------------------------------------------------------
'Procédure de colorisation des cellules d'un classeur contenant des formules
'-l'utilisateur doit choisir le classeur à traiter
'-Le traitement met évidence par la couleur de certaines cellules du classeur suivant la charte suivante :
'- le texte des cellules contenant des calculs EXCEL est colorisé en vert
'- le texte des cellules contenant une référence à une autre cellule de la même feuille est colorisé en bleu marine
'- le texte des cellules contenant une référence à une autre cellule dans une autre feuille est colorisé en violet
'- Dans tous ces cas, le corps du texte est mis en gras
'- Le texte de la cellule est colorisé en rouge dans le cas où son contenu est #REF!
'Le classeur reste ouvert après le traitement
'Vous pouvez adapter les couleurs à votre propre charte en changeant les valeurs des constantes déclarées en tête:
'  cCouleurCalcul | cCouleurReferenceInterne | cCouleurReferenceExterne | cCouleurErreur.
'Auteur : Excellons.org
'Date : juin 2019
'------------------------------------------------------------------------------------------------
Const cCouleurCalcul = 3506772              'Vert  : A adapter
Const cCouleurReferenceInterne = 9851952    'Bleu  : A adapter
Const cCouleurReferenceExterne = 10498160   'Violet: A adapter
Const cCouleurErreur = vbRed                'Rouge : A adapter
Const cFilter = "Classeur EXCEL(*.xls*), *.xls*"

    Dim oWB As Workbook
    Dim oSheet As Worksheet
    Dim oRangeToScan As Range
    Dim oCell As Range
    Dim sWBName As Variant
    Dim sExtension As String
    Dim sDecoupe() As String
    Dim lErr As Long
    Dim i As Integer
    
    'On demande à l'utilisateur d'indiquer le nom du classeur à traiter
    sWBName = Application.GetOpenFilename(cFilter, 1, "Choisissez le classeur à traiter", , False)
      
    'On s'assure que l'utilisateur n'a pas quitté la boite de dialogue sans indiquer de fichier. Sinon on met fin à l'opération.
    If sWBName = False Then
        MsgBox "Aucun classeur indiqué !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On s'assure que l'utilisateur a indiqué un classeur EXCEL
    sDecoupe() = Split(sWBName, ".") 'On découpe le nom en 2 pour récupérer son extension
    'On s'assure que le fichier choisi possède une extension de classeur EXCEL. Sinon on met fin à l'opération.
    If Left(sDecoupe(1), 3) <> "xls" Then
        MsgBox "Le fichier indiqué ne semble pas être un classeur EXCEL !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
        
    'On active la séquence de gestion d'erreurs
    On Error GoTo Gestion_Err
    
    'On affecte l'objet local classeur
    Set oWB = Application.Workbooks.Open(sWBName)
   
    'On inspecte toutes les feuilles du classeur
    For Each oSheet In oWB.Worksheets
        'On s'assure que la feuille n'est pas protégée
        If Not oSheet.ProtectContents Then
            'On affecte la plage de cellules à inspecter -> seulement les cellules contenant une formule
            On Error Resume Next
            Set oRangeToScan = oSheet.Cells.SpecialCells(xlCellTypeFormulas)
            On Error GoTo Gestion_Err 'On rétablit la gestion des erreurs
            
            'On s'assure que la feuille possède des cellules avec formule
            If Not oRangeToScan Is Nothing Then
                'On inspecte les toutes cellules de la plage
                For Each oCell In oRangeToScan.Cells
                'On s'assure que la cellule ne contient pas d'erreur
                    If Not IsError(oCell.Value) Then
                        'On met en gras le corps du texte de la cellule
                        oCell.Font.Bold = True
                        On Error GoTo 0                     'On doit annuler la séquence de débranchement d'erreur
                        On Error Resume Next                'On poursuit le traitement même en cas d'erreur
                        Application.GoTo oCell.FormulaR1C1  'On tente d'atteindre la cellule référencée dans la formule
                        
                        'On s'assure du numéro de l'erreur générée par l'instruction précédente
                        Select Case Err.Number
                            Case Is = 0                     'La référence a été atteinte -> la cellule contient une référence à une cellule
                                'On teste pour savoir si la cellule contient une référence à une feuille
                                If InStr(1, oCell.Formula, "!") > 0 Then
                                    'Si oui, on colorise le texte avec la couleur affectée aux références externes
                                    oCell.Font.Color = cCouleurReferenceExterne
                                Else
                                    'Sinon on colorise le texte de la cellule avec la couleur affectée aux références internes
                                    oCell.Font.Color = cCouleurReferenceInterne
                                End If
                                On Error GoTo Gestion_Err   'On rétablit la gestion des erreurs
                            Case Is = 1004                  'La référence est incorrecte -> la cellule contient un calcul
                                'On colorise le texte de la cellule avec la couleur affectée aux calculs
                                oCell.Font.Color = cCouleurCalcul
                                Err.Clear
                                On Error GoTo Gestion_Err   'On rétablit la gestion des erreurs
                            Case Else
                                'Si une autre erreur est survenue, on se débranche vers la séquence de gestion des erreurs
                                lErr = Err.Number
                                On Error GoTo Gestion_Err 'On rétablit la gestion des erreurs
                                Err.Raise lErr            'On déclenche l'erreur
                        End Select
                    Else
                        oCell.Font.Color = cCouleurErreur
                    End If
                Next
            End If
            'on fait le ménage
            Set oRangeToScan = Nothing
        End If
    Next
    
    'Séquence de fin de traitement
    MsgBox "Les cellules du classeur :" & vbCrLf & "'" & sWBName & "'" & vbCrLf & "contenant des formules ont été colorisées avec succès.", vbInformation, "FIN D'OPERATION"

    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    
    'On fait le ménage
    Set oRangeToScan = Nothing
    Set oSheet = Nothing
    Set oWB = Nothing
    Exit Sub


'Séquence de traitement des erreurs
Gestion_Err:
    'On affiche l'erreur et on sort...
    MsgBox "La colorisation des cellules du classeur '" & sWBName & "' a rencontré l'erreur suivante :" & vbCrLf _
            & Err & "-" & Err.Description, vbCritical, "IMPOSSIBLE DE POURSUIVRE LE TRAITEMENT"
    
    'On fait le ménage
    On Error GoTo 0 'On annule le branchement vers de gestion d'erreur
    Set oRangeToScan = Nothing
    Set oSheet = Nothing
    Set oWB = Nothing
End Sub

Reproduire les sauts de page

 

  • L’utilisateur indique le nom de la feuille ‘modèle’
  • L’utilisateur indique le nom de la feuille ‘cible’
  • La macro fait le reste…
Sub DuplicatePageBreaks()
'------------------------------------------------------------------------------------------------
'Macro de reproduction des sauts de page positionnés sur une feuille source
'-l'utilisateur indique le nom de la feuille source et de la feuille cible
'Auteur : Excellons.org
'Date : mai 2019
'------------------------------------------------------------------------------------------------
    Dim aSheetsNames As New Collection
    Dim oFromSheet As Worksheet, oToSheet As Worksheet
    Dim sFromSheetName As String, sToSheetName As String
    Dim i As Integer, iNb As Integer
    Dim oPB As HPageBreak
    Dim booOK As Boolean
    
    'On récupère les noms de feuilles du classeur dans une collection
    For Each oToSheet In ThisWorkbook.Worksheets
        aSheetsNames.Add oToSheet.Name
    Next
    
    'On vérifie qu'il existe plus d'une feuille dans le classeur, sinon on sort
    If aSheetsNames.Count = 1 Then
        MsgBox "Ce classeur ne contient qu'une seule feuille !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On demande à l'utilisateur d'indiquer la feuille source
    sFromSheetName = InputBox("Nom de la feuille source ?", "Feuille source des sauts de page", aSheetsNames.Item(1), 2000, 1000)
    'On vérifie que l'utilisateur a indiqué un nom de feuille valide, sinon sort
    booOK = False
    For i = 1 To aSheetsNames.Count
        If sFromSheetName = aSheetsNames.Item(i) Then
            booOK = True
        End If
    Next
    If Not booOK Then
        MsgBox "Nom de feuille source non indiqué ou invalide !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On référe la feuille source
    Set oFromSheet = ThisWorkbook.Worksheets(sFromSheetName)
    'On récupère le nombre de sauts de page
    iNb = oFromSheet.HPageBreaks.Count
    'S'il n'y a aucun saut de page, on sort
    If iNb = 0 Then
        MsgBox "La feuille source indiquée ne contient aucun saut de page !" & vbCrLf & vbCrLf & "Opération sans objet.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If

    'On demande à l'utilisateur d'indiquer la feuille cible
    sToSheetName = InputBox("Nom de la feuille cible ?", "Feuille cible sur laquelle reproduire les sauts de page", aSheetsNames.Item(2), 3000, 5000)
    'On vérifie que l'utilisateur a indiqué un nom de feuille valide, sinon sort
    booOK = False
    For i = 1 To aSheetsNames.Count
        If sToSheetName = aSheetsNames.Item(i) Then
            booOK = True
        End If
    Next
    If Not booOK Then
        MsgBox "Nom de la feuille cible non indiqué ou invalide !" & vbCrLf & vbCrLf & "Opération impossible.", vbCritical, "FIN D'OPERATION"
        Exit Sub
    End If
    
    'On réfère la feuille devant recevoir les sauts de page
    Set oToSheet = ThisWorkbook.Worksheets(sToSheetName)
    'On efface les sauts de page de la feuille cible
    oToSheet.ResetAllPageBreaks
    'On boucle sur le nombre de sauts de page
    For i = 1 To oFromSheet.HPageBreaks.Count
        'On réfère un saut de page
        Set oPB = oFromSheet.HPageBreaks(i)
        'On réplique ce saut de page dans la feuille destinataire
        oToSheet.HPageBreaks.Add oPB.Location
    Next
    MsgBox "Opération terminée avec succès !", vbExclamation, "FIN D'OPERATION"
    
    'On fait le ménage
    Set oPB = Nothing
    Set oFromSheet = Nothing
    Set oToSheet = Nothing
End Sub

Mise en page 1/1

Pour ne plus à avoir à répéter les mêmes clics, une macro d’automatisation de la mise en page de l’impression de la feuille active avec les caractéristiques suivantes :

  • Format paysage
  • Marge étroite
  • Une seule page
  • Contenu centré horizontalement et verticalement 
Sub ImprimerFeuilleSurPage()
'----------------------------------------------------------------------------------
'Macro d'automatisation de l'impression de la feuille active sur une seule page :
'-Format portrait
'-Marge étroite
'-Une seule page
'-Contenu centré horizontalement et verticalement
'Auteur : Excellons.org
'Date : mai 2015
'----------------------------------------------------------------------------------
    With ActiveSheet.PageSetup              'On pointe sur la mise en page de la feuille active
        'On définit les marges étroites
        .LeftMargin = Application.InchesToPoints(0.236220472440945)
        .RightMargin = Application.InchesToPoints(0.236220472440945)
        .TopMargin = Application.InchesToPoints(0.748031496062992)
        .BottomMargin = Application.InchesToPoints(0.748031496062992)
        .HeaderMargin = Application.InchesToPoints(0.31496062992126)
        .FooterMargin = Application.InchesToPoints(0.31496062992126)
        'On positionne la feuille dans la page
        .CenterHorizontally = True          'On centre horizontalement
        .CenterVertically = True            'On centre verticalement
        .Orientation = xlLandscape          'On passe au format portrait
        'On spécifie l'impression sur une page unique
        .FitToPagesWide = 1
        .FitToPagesTall = 1
    End With
End Sub