Astuces VBA

Dates et heures :

• Calculer le nombre de jours dans un mois

  Pour calculer le nombre de jours dans un mois ou définir le dernier jour du mois, vous pouvez utiliser la formule suivante :
  

  Sub nb_jours_mois()
   
    date_test = CDate("6/2/2012") 'Une date quelconque pour cet exemple

    nb_jours = Day(DateSerial(Year(date_test), Month(date_test) + 1, 1) - 1)
   
End Sub
  
  Pour mieux comprendre cette formule, la voici dans une version décortiquée et commentée :
  
  Sub nb_jours_mois()
   
    'Une date quelconque pour cet exemple
    date_test = CDate("6/2/2012")
   
    'Mois / année de la date
    mois = Month(date_test)
    annee = Year(date_test)
   
    'Calcul du premier jour du mois suivant
    date_mois_suivant = DateSerial(annee, mois + 1, 1)
   
    'Date du dernier jour
    dernier_jour_mois = date_mois_suivant - 1
   
    'Nombre de jour dans le mois (= dernier jour)
    nb_jours = Day(dernier_jour_mois)
   
End Sub

  Fonction

  Vous pouvez également l'utiliser en tant que fonction en ajoutant le code suivant dans un module :
  
  Function NB_JOURS(date_test As Date)
    NB_JOURS = Day(DateSerial(Year(date_test), Month(date_test) + 1, 1) - 1)
End Function
  Exemple d'utilisation de la fonction en VBA :
  
  Sub exemple()
    test = NB_JOURS(Range("A1"))
    MsgBox test
End Sub
  Exemple d'utilisation de cette même fonction sur une feuille de calcul :
  
•  

• Formats d'affichage des dates et heures

  Voici les principaux caractères utilisables pour définir le format des dates et heures :
  
  

  Caractères	Exemple	Description
  d	7	Jour (numérique sans zéro)
  dd	07	Jour (numérique avec zéro)
  ddd	mar.	Jour (texte raccourci)
  dddd	mardi	Jour (texte entier)
  m	2	Mois (numérique sans zéro)
  mm	02	Mois (numérique avec zéro)
  mmm	févr	Mois (texte raccourci)
  mmmm	février	Mois (texte entier)
  yy	12	Année (2 derniers chiffres)
  yyyy	2012	Année (4 chiffres)
  h	8	Heures sans zéro (0-23)
  hh	08	Heures avec zéro (00-23)
  n	3	Minutes sans zéro (0-59)
  nn	03	Minutes avec zéro (00-59)
  s	8	Secondes sans zéro (0-59)
  ss	08	Secondes avec zéro (00-59)

  Ainsi que quelques exemples de formats de dates et heures :
  
  Sub dates_et_heures()

    'Now renvoie la date et l'heure en cours (07.02.2012 09:09:02)
    date_test = Now()
   
    'Renvoie : 07.02.12
    Range("A1") = Format(date_test, "dd.mm.yy")

    'Renvoie : mardi 7 février 2012
    Range("A2") = Format(date_test, "dddd d mmmm yyyy")

    'Renvoie : Mardi 7 Février 2012
    Range("A3") = WorksheetFunction.Proper(Format(date_test, "dddd d mmmm yyyy"))

    'Renvoie : mar. 07
    Range("A4") = Format(date_test, "ddd dd")

    'Renvoie : MAR 07
    Range("A5") = "'" & Replace(UCase(Format(date_test, "ddd dd")), ".", "")

    'Renvoie : FÉVRIER 2012
    Range("A6") = UCase(Format(date_test, "mmmm yyyy"))
   
    'Renvoie : 07.02.2012 09:09
    Range("A7") = Format(date_test, "dd.mm.yyyy hh:mm")

    'Renvoie : Le 7 février à 9h09'02''
    Range("A8") = Format(date_test, "Le d mmmm à h\hmm'ss''")

    'Renvoie : 9H09
    Range("A9") = Format(date_test, "h\Hmm")

End Sub
   

Divers :

• Diminuer le temps d'exécution d'une macro

  Lorsque vous utilisez une macro qui effectue un grand nombre de modifications sur votre feuille, Excel actualise l'affichage à chaque modification ce qui peut considérablement ralentir la macro ...
  Pour demander à Excel de ne pas actualiser l'affichage, ajoutez ceci :
  

  Sub exemple()

    Application.ScreenUpdating = False
   
    'Code ici ...
 
    Application.ScreenUpdating = True 'Facultatif
 
End Sub

  Exemple concret de gain de temps

  Le test suivant a été effectué avec la macro qui gènère le calendrier annuel de l'application Calendrier-Pratique.
  Sans ScreenUpdating, le temps d'exécution était de 31.97 secondes :
  Avec ScreenUpdating, le temps d'exécution a été réduit à seulement 0.44 secondes :
  Si votre macro est un peu lente, pensez-y !
   

• Comment activer les macros ?

  Dans la plupart des cas, lorsque vous ouvrez pour la première fois un classeur qui contient des macros, Excel les désactive par mesure de sécurité et vous demande si vous souhaitez activer les macros (exemple avec Excel 2010) :
  Mais il peut arriver que les macros soient désactivées et qu'Excel ne vous propose pas de les activer ...
  Pour commencer (si ce n'est pas déjà fait), enregistrez le fichier sur votre PC (si vous ouvrez un fichier Excel depuis un site Web directement, sans l'enregistrer, les macros sont parfois désactivées sans notification).
  L'activation/désativation des macros diffère selon la version d'Excel utilisée. Vous trouverez ci-dessous des informations pour activer les macros pour la plupart des versions d'Excel (2013/2010/2007/2002).
  

  Activer les macros (EXCEL 2013)

  Cliquez sur "FICHIER" > "Options" :
  Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
  Et enfin sur "Paramètres des macros" :
  Le choix par défaut (et recommandé) est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
  

  Activer les macros (EXCEL 2010)

  Cliquez sur "Fichier" > "Options" :
  Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
  Et enfin sur "Paramètres des macros" :
  Le choix par défaut (et recommandé) est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
  

  Activer les macros (EXCEL 2007)

  Cliquez sur "Options Excel" :
  Puis sur "Centre de gestion de la confidentialité" > "Paramètres du Centre de gestion de la confidentialité" :
  Et enfin sur "Paramètres des macros" :
  Le choix recommandé est "Désactiver toutes les macros avec notification" (c'est celui qui vous invite à activer les macros à l'ouverture).
  

  Activer les macros (EXCEL 2002)

  Cliquez sur "Outils" > "Macro" > "Sécurité" :
  Le choix recommandé est "Niveau de sécurité moyen" (c'est celui qui vous invite à activer les macros à l'ouverture).
  

• Créer une animation

  Pour apprendre à réaliser une animation (par exemple : déplacer un objet de manière fluide), un tutoriel vidéo a été créé.
  
  

  Sub DeplacerUnObjet()
      
      If Sheets("Feuil1").TextBox_recherche.Value = "" Then 'Si champ vide
          
          Sheets("Feuil1").Shapes("Fleche").Top = 7.5
   
          Sheets("Feuil1").Shapes("Fleche").Left = 306
   
          Range("A1").Select
   
          secondes = 0.01
   
          For ii = 1 To 3
   
              For i = 3 To 117 Step 3
   
                  timer_avant = Timer
   
                  Do While Timer < timer_avant + secondes
                      DoEvents
                  Loop
   
                  Sheets("Feuil1").Shapes("Fleche").Left = 306 - i
   
                  Range("A1").Select
   
              Next
   
              For i = 3 To 117 Step 3
   
                  timer_avant = Timer
   
                  Do While Timer < timer_avant + secondes
                      DoEvents
                  Loop
   
                  Sheets("Feuil1").Shapes("Fleche").Left = 189 + i
   
                  Range("A1").Select
   
              Next
   
          Next
   
          Sheets("Feuil1").Shapes("Fleche").Left = 2000
   
      Else 'Si champ rempli
      
          'Instructions pour la recherche ici ...
          
      End If
   
  End Sub

  
  
  

• Désactiver les événements

  Si vous avez besoin d'exécuter un bout de code sans déclencher d'événements, placez le code entre ces 2 lignes :
  
  Sub exemple()

    Application.EnableEvents = False ' => désactive les événements
 
    'Le code ...
 
    Application.EnableEvents = True ' => réactive les événements
 
End Sub
   

• Effectuer une action selon la version d'Excel

  Pour déterminer la version d'Excel de l'utilisateur et effectuer une action différente selon la version en cours d'utilisation, vous pouvez utiliser Val(Application.Version) qui vous renvoie (le numéro de) la version d'Excel.
  
  Voici les numéros des versions d'Excel à utiliser pour effectuer des tests :
  

  8	Excel 97 (Mac : 98)
  9	Excel 2000 (Mac : 2001)
  10	Excel 2002
  11	Excel 2003 (Mac : 2004)
  12	Excel 2007 (Mac : 2008)
  14	Excel 2010 (Mac : 2011)
  15	Excel 2013

  Par exemple pour tester si la version d'Excel est inférieure à 2007, vous pouvez utiliser ce code :
  If Val(Application.Version) < 12 Then
    'Si version d'Excel inférieure à 2007
Else
    'Si version d'Excel égale à 2007 ou supérieure
End If

• Importer des données d'un site Web

  Pour apprendre à créer une macro pour importer des données à partir d'un site Web, un tutoriel vidéo a été créé.

• Stopper une macro en cours d'exécution

  Il peut arriver qu'on lance une macro bien trop lente ou pire encore, une boucle infinie ...
  Heureusement, avec Ctrl + Pause (Break) il est possible de stopper une macro en cours d'exécution !
  

Feuilles et classeurs :

• Empêcher la modification du nom d'une feuille

  Si la modification du nom d'une feuille Excel peut provoquer des erreurs, il peut être intéressant de l'interdire.
  Une solution simple consiste à entrer le code suivant dans la feuille qui vous intéresse et remplacer Exemple par le nom de la feuille :
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If ActiveSheet.Name <> "Exemple" Then
        ActiveSheet.Name = "Exemple"
    End If
End Sub
  A chaque changement de sélection, le nom de la feuille sera vérifié et remplacé si nécessaire.
   

• Empêcher l'enregistrement d'un fichier Excel

  Si vous avez créé un fichier qui nécessite l'activation des macros pour fonctionner et que vous souhaitez interdire l'enregistrement de modifications, c'est possible et ça ne prend qu'un instant.
  
  Ouvrez votre fichier sans activer les macros (ou en activant le "Mode Création") et collez le code suivant dans ThisWorkbook :
  
  Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Cancel = True 'Annule les demandes d'enregistrement
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    Application.ThisWorkbook.Saved = True 'Informe Excel que le fichier a déjà été enregistré (cela évite d'avoir une demande d'enregistrement à la fermeture)
End Sub
  Il ne vous reste plus qu'à enregistrer votre fichier (vous pouvez toujours l'enregistrer tant que les macros sont désactivées).
  

• Interdire la sélection de plusieurs cellules

  Une solution pour interdire à l'utilisateur de sélectionner plusieurs cellules à la fois est d'ajouter cette simple ligne de code dans l'événement Worksheet_SelectionChange de la feuille où cette interdiction doit être appliquée :
  
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ActiveCell.Select
End Sub
  A chaque changement de sélection, la cellule active sera sélectionnée (ActiveCell = 1 seule cellule) ...
  
  

  Forcer l'activation des macros

  Une solution consiste à masquer préalablement toutes les feuilles sauf une puis, lorsque l'utilisateur active les macros, afficher toutes les feuilles ...
  
  Pour mettre en place cette solution, commencez par créer la feuille de démarrage (celle que l'utilisateur verra tant que les macros ne seront pas activées) :
  Masquez ensuite toutes les feuilles sauf celle de démarrage (propriété : Visible) :
  Et entrez le code suivant dans ThisWorkbook :
  
  Private Sub Workbook_Open()

    'Affichage de la feuille EXEMPLE
    Sheets("EXEMPLE").Visible = -1
   
    'Feuille de démarrage cachée
    Sheets("MACROS").Visible = 2

End Sub
  Enregistrez, fermez et testez !
  Le fichier de cet exemple : exemple.xls
  
  

• Masquer les onglets

  Pour masquer les onglets d'un classeur et ainsi empêcher la navigation entre les feuilles, utilisez le code suivant :
  
  Sub masquer()
    ActiveWindow.DisplayWorkbookTabs = False
End Sub
  Et pour réafficher les onglets :
  
  Sub afficher()
    ActiveWindow.DisplayWorkbookTabs = True
End Sub

Mise en forme :

• Mise en forme de caractères dans une cellule

  Pour modifier la mise en forme de certains caractères, utilisez Characters :
  

  Range("A1").Characters(NO_DEPART, NB_DE_CARACTERES)
  
  

  Exemple pratique

  Dans cet exemple, la macro passera les 2-3 premiers caractères en italique et le nom de la ville en gras :
  La macro en détails :
  
  Sub test()

    For ligne = 1 To 12
   
        'Contenu de la cellule
        contenu = Cells(ligne, 1)
       
        'Contenu divisé en un tableau de 3 parties
        tab_contenu = Split(contenu, " ")
       
        'Longueur de la partie 1
        longueur_partie_1 = Len(tab_contenu(0))
       
        'Longueur de la partie 2
        longueur_partie_2 = Len(tab_contenu(1))
       
        'Partie 1 en ITALIQUE
        Cells(ligne, 1).Characters(1, longueur_partie_1).Font.Italic = True
       
        'Partie 2 en GRAS
        Cells(ligne, 1).Characters(longueur_partie_1 + 2, longueur_partie_2).Font.Bold = True

    Next

End Sub

Nombres et calculs :

• Générer des nombres aléatoires

  
  Voici le code à utiliser pour générer une valeur aléatoire entre 1 et 50 :
  
  Sub aleatoire()

    'Initialiser le générateur de nombres aléatoires
    '=> Randomize : à ajouter avant d'utiliser Rnd pour obtenir des valeurs complètement aléatoires
    Randomize
   
    'Nombre aléatoire entier entre 1 et 50 :
    nombre_aleatoire = Int(50 * Rnd) + 1
   
    MsgBox nombre_aleatoire
   
End Sub
  Pour obtenir par exemple une valeur entre 1 et 22, remplacez simplement 50 (dans le code ci-dessus) par 22.

• Générer des codes aléatoires

  Sub code_alea()
      'www.blog-excel.com/generer-code-aleatoire
      Randomize
   
      carac = "ABCD1234"
      lettre_aleatoire = ""
   
      For i = 1 To 10
          nombre_aleatoire = Int(Len(carac) * Rnd) + 1
          lettre_aleatoire = lettre_aleatoire & Mid(carac, nombre_aleatoire, 1)
          If i = 5 Then lettre_aleatoire = lettre_aleatoire & "-"
      Next
   
      MsgBox lettre_aleatoire
  End Sub

• Racine carrée / carré

  Racine carrée

  Pour obtenir la racine carrée d'une valeur en VBA, vous avez besoin de la fonction Sqr :
  

  Sub exemple()

    nombre = 9
   
    MsgBox Sqr(nombre) 'Affiche la racine carrée : 3

End Sub
  
  

  Carré

  Pour obtenir le carré d'un nombre, il vous suffit d'utiliser "^" (comme pour une formule dans une cellule) :
  
  Sub exemple()

    nombre = 9
   
    MsgBox nombre ^ 2 'Affiche le carré : 81
    MsgBox nombre ^ 3 'Affiche le cube : 729

End Sub

UserForm et contrôles :

• Créer un champ de recherche en VBA

  Dans ce tutoriel, vous verrez comment mettre en place un champ de recherche (TextBox) et afficher les résultats de différentes manières (dans cet exemple, affichage des résultats : sur la feuille + dans une ListBox) :

• Option Compare Text
   
  Private Sub TextBox1_Change()
      
      Application.ScreenUpdating = False
   
      Range("A2:A24").Interior.ColorIndex = 2 'Exemple 1 (feuille)
      ListBox1.Clear 'Exemple 2 (ListBox)
      
      If TextBox1 <> "" Then
          For ligne = 2 To 24
              If Cells(ligne, 1) Like "*" & TextBox1 & "*" Then
                  Cells(ligne, 1).Interior.ColorIndex = 43 'Exemple 1 (feuille)
                  ListBox1.AddItem Cells(ligne, 1) 'Exemple 2 (ListBox)
              End If
          Next
      End If
   
  End Sub

  Créer une barre de progression

  
  Aperçu de la barre :
  

• Liste déroulante personnalisée

  
  Le contrôle ComboBox ne permet pas de mettre en forme les lignes de sa liste.
  Pourtant, la liste déroulante visible sur cette image fonctionne très bien :
  

• UserForm modal

  
  Pour comprendre comment travailler sur une feuille Excel tout en ayant un UserForm ouvert, consultez le tutoriel vidéo disponible sur le blog Excel-Pratique :

• Utiliser des variables dans le nom d'un contrôle

  Pouvoir agir sur un contrôle en fonction d'une variable peut s'avérer très utile pour développer plus efficacement.
  
  Par exemple, pour supprimer le contenu de 20 TextBox, il est possible d'écrire :
  
  Private Sub CommandButton_vider_Click()
   
    TextBox1.Value = ""
    TextBox2.Value = ""
    TextBox3.Value = ""
    TextBox4.Value = ""
    TextBox5.Value = ""
    TextBox6.Value = ""
    TextBox7.Value = ""
    TextBox8.Value = ""
    TextBox9.Value = ""
    TextBox10.Value = ""
    TextBox11.Value = ""
    TextBox12.Value = ""
    TextBox13.Value = ""
    TextBox14.Value = ""
    TextBox15.Value = ""
    TextBox16.Value = ""
    TextBox17.Value = ""
    TextBox18.Value = ""
    TextBox19.Value = ""
    TextBox20.Value = ""
   
End Sub
  Mais grâce à Controls, il est possible de réduire le code à 3 lignes :
  
  Private Sub CommandButton_vider_Click()
   
    For i = 1 To 20
        Controls("TextBox" & i).Value = ""
    Next
   
End Sub

Variables :

• Supprimer une variable tableau

  Si vous souhaitez réinitialiser un tableau pour pouvoir modifier sa taille et son contenu, utilisez l'instruction Erase.
  
  

  Exemple avec un tableau dynamique :

  Sub exemple_simple()
   
    nombre_exemple = 11
   
    'Déclaration du tableau dynamique
    Dim tab_exemple()
   
    'Redimensionnement
    ReDim tab_exemple(nombre_exemple - 1)
   
    'EXEMPLE
    MsgBox UBound(tab_exemple) ' => renvoie : 10
 
    'Suppression du tableau
    Erase tab_exemple
   
    'Nouveau redimensionnement possible après suppression
    ReDim tab_exemple(15)
   
    'EXEMPLE
    MsgBox UBound(tab_exemple) ' => renvoie : 15
 
End Sub
  L'instruction Erase peut s'avérer très utile lorsque votre tableau dynamique est déclaré en tant que variable Global ...
  

  Exemple avec un tableau de taille fixe :

  Sub exemple_simple()
   
    'Déclaration du tableau
    Dim tab_exemple(10)
   
    'EXEMPLE : attribution de valeurs
    For i = 0 To 10
        tab_exemple(i) = i
    Next
   
    'EXEMPLE
    MsgBox tab_exemple(5) ' => renvoie : 5
 
    'Suppression du contenu du tableau
    Erase tab_exemple
   
    'EXEMPLE
    MsgBox tab_exemple(5) ' => ne renvoie rien (vide)
 
End Sub
  Avec un tableau de taille fixe, les éléments sont réinitialisés (vidés).