All posts in VBAExcel

Excel est très pratique pour réaliser des documents sous la forme de tableaux : des bulletins de paie, des devis et des factures, par exemple. Il permet également de créer des études prévisionnelles, d’analyser et de synthétiser des données.

Parfois, il n’existe aucune fonctionnalité prédéfinie dans Excel pour répondre à un besoin particulier. Vous devez alors « programmer Excel » pour créer cette fonctionnalité. Pour cela, vous pouvez passer par l’enregistreur de macros ou par le langage VBA. Cette formation va vous apprendre à utiliser l’un comme l’autre. Aucune connaissance en programmation n’est nécessaire. Par contre, vous devez avoir déjà pratiqué Excel. Si les termes classeur, feuille de calcul, cellule et formule vous sont étrangers, commencez par vous former sur Excel. Consultez :

• Cette formation vidéo : https://www.mediaforma.com/formation-video-a-excel-2016/
• Cette page pour avoir quelques exemples de sujets abordés dans la formation vidéo : https://www.mediaforma.com/excel-2016/

Cet article va vous montrer comment tester la validité d’une adresse e-mail saisie par l’utilisateur dans une zone de texte. La couleur d’arrière-plan de la zone de texte sera verte si l’adresse est correcte, rouge sinon.

Pour faciliter la comparaison entre la saisie et le modèle attendu, nous allons utiliser une expression régulière. Pour cela, nous allons piocher dans les caractères de remplacement du tableau suivant :

Pour savoir si une adresse e-mail est valide, vous pouvez utiliser l’expression régulière suivante :

^[a-z0-9._-]+@[a-z0-9._-]{2,}\.[a-z]{2,4}$

Examinons cette séquence :

• L’adresse e-mail commence (^) par un nombre quelconque de lettres minuscules, de chiffres, de points décimaux et de caractères de soulignement : ^[a-z0-9._-]+
• Elle est suivie du caractère @
• D’une séquence de deux ou plus de deux lettres minuscules, chiffres, points décimaux ou caractères de soulignement : [a-z0-9._-]{2,}
• D’un point décimal : \.
• Et enfin d’une séquence de 2 à 4 lettres qui termine ($) l’adresse e-mail: [a-z]{2,4}

Cette courte introduction aux expressions régulières étant terminée, nous allons maintenant nous intéresser à son utilisation en VBA.

Dans un premier temps, vous devez valider l’utilisation de la bibliothèque Microsoft VBScript Regular Expression 5.5. Rendez-vous dans la fenêtre Microsoft Visual Basic pour Applications. Lancez la commande Références dans le menu Outils. La boîte de dialogue Références s’affiche. Déplacez-vous dans la zone de liste pour atteindre l’entrée Microsoft VBScript Regular Expression 5.5 et cochez la case qui la précède :

Validez en cliquant sur OK. Ça y est, vous pouvez utiliser des expressions régulières dans le code VBA du classeur.

Insérez une zone de texte dans la feuille. Pour cela, basculez sur l’onglet Développeur du classeur, cliquez sur l’icône Insérer du groupe Contrôles et cliquez sur Zone de texte, sous Contrôles ActiveX :

Assurez-vous que l’icône Mode Création est enfoncée (onglet Développeur, groupe Contrôles), puis double-cliquez sur la zone de texte. La procédure TextBox1_Change() est créée. Complétez-la comme ceci :

Private Sub TextBox1_Change()

    Dim reg As New VBScript_RegExp_55.RegExp

    reg.Pattern = "^[a-z0-9._-]+@[a-z0-9._-]{2,}\.[a-z]{2,4}$"

    If reg.Test(TextBox1.Text) = True Then

      TextBox1.BackColor = RGB(0, 255, 0)

    Else

      TextBox1.BackColor = RGB(255, 0, 0)

    End If

End Sub

Examinons ce code.

La première ligne définit l’objet regexp reg :

Dim reg As New VBScript_RegExp_55.RegExp

La deuxième instruction définit l’expression régulière et l’affecte à la propriété Pattern de l’objet reg :

reg.Pattern = "^[a-z0-9._-]+@[a-z0-9._-]{2,}\.[a-z]{2,4}$"

Le bloc If suivant applique la méthode Test() de l’objet reg à la zone de texte. Si la valeur retournée est True, la zone de texte correspond au modèle de l’expression régulière. Dans ce cas, l’arrière-plan de la zone de texte est coloré en vert :

If reg.Test(TextBox1.Text) = True Then

  TextBox1.BackColor = RGB(0, 255, 0)

Si la valeur retournée est False, la zone de texte ne correspond pas au modèle de l’expression régulière. Dans ce cas, l’arrière-plan de la zone de texte est coloré en rouge :

Else

  TextBox1.BackColor = RGB(255, 0, 0)

End If

Cette section va vous présenter un code utile qui pourra certainement vous servir un jour ou l’autre. Le but du jeu est de trouver les cellules qui apparaissent en double (ou plus) dans la colonne où se trouve la cellule active. Il n’y a rien de bien compliqué : il suffit de comparer tour à tour toutes les cellules de la colonne à la première, puis à la deuxième, puis à la troisième, ainsi de suite jusqu’à l’avant-dernière cellule de la colonne. Ici, la couleur d’arrière-plan de la cellule qui apparait en double sera modifiée. Mais rien ne vous empêche de modifier le code pour faire tout autre chose si vous le souhaitez. Voici le code utilisé :

Sub RechercherDoublons()

  Dim col, nbCells, i, j

  col = ActiveCell.Column

  nbCells = Application.WorksheetFunction.CountA(Range(Columns(col), Columns(col)))

  For i = 1 To nbCells - 1

    For j = i + 1 To nbCells

      If Cells(i, col) = Cells(j, col) Then

        Cells(j, col).Interior.Color = RGB(255, 0, 0)

      End If

    Next j

  Next i

End Sub

Examinons ce code.

La première ligne définit les variables qui seront utilisées dans le code :

Dim col, nbCells, i, j

La deuxième ligne stocke dans la variable col la colonne dans laquelle se trouve la cellule active. C’est dans cette colonne que les doublons seront recherchés :

col = ActiveCell.Column

La troisième ligne compte le nombre de cellules non vides de la colonne col. Le résultat est stocké dans la variable nbCells :

nbCells = Application.WorksheetFunction.CountA(Range(Columns(col), Columns(col)))

Le bloc de code suivant utilise deux boucles For Next imbriquées. La première parcourt les cellules de la première (1) à l’avant-dernière (nbCells-1) :

For i = 1 To nbCells - 1

La seconde parcourt les cellules d’indices compris entre i+1 et nbCells :

For j = i + 1 To nbCells

Si les cellules d’indice i, col et j, col sont identiques, un doublon a été trouvé :

If Cells(i, col) = Cells(j, col) Then

Dans ce cas, la cellule d’indice j, col est colorée en rouge :

Cells(j, col).Interior.Color = RGB(255, 0, 0)

Il est très simple de connaitre le chemin du dossier d’installation de Windows, du dossier système et du dossier des fichiers temporaire. Commencez par créer un objet Scripting.FileSystemObject :

Dim fs As New Scripting.FileSystemObject

Les dossiers spéciaux sont accessibles avec la fonction GetSpecialFolder() de l’objet Scripting.FileSystemObject.

Pour connaitre le dossier d’installation de Windows, passez la constante WindowsFolder ou la valeur 0 à cette fonction :

MsgBox fs.GetSpecialFolder(WindowsFolder)

MsgBox fs.GetSpecialFolder(0)

Pour connaitre le dossier système, passez la constante SystemFolder ou la valeur 1 à cette fonction :

MsgBox fs.GetSpecialFolder(SystemFolder)

MsgBox fs.GetSpecialFolder(1)

Pour connaitre le dossier des fichiers temporaires, passez la constante TemporaryFolder ou la valeur 2 à cette fonction :

MsgBox fs.GetSpecialFolder(TemporaryFolder)

MsgBox fs.GetSpecialFolder(2)

Une fois l’objet Scripting.FileSystemObject utilisé, supprimez-le de la mémoire en lui affectant la valeur Nothing :

Set fs = Nothing

Voici un exemple de code complet :

Dim fs As New Scripting.FileSystemObject

MsgBox fs.GetSpecialFolder(WindowsFolder)

MsgBox fs.GetSpecialFolder(SystemFolder)

MsgBox fs.GetSpecialFolder(TemporaryFolder)

Set fs = Nothing

Et voici le résultat :

      

Dans Windows, les fichiers possèdent des attributs qui peuvent changer leur comportement dans l’explorateur de fichiers. Par exemple, les fichiers cachés n’apparaitront pas dans l’explorateur de fichiers, ou encore, les fichiers à lecture seule ne pourront pas être modifiés.

Modifier les attributs d’un fichier

En utilisant l’instruction VBA SetAttr, vous pouvez modifier l’attribut d’un fichier quelconque. Voici sa syntaxe :

SetAttr "chemin", attribut

Où chemin est le chemin complet du fichier dont vous voulez modifier l’attribut et attribut est l’attribut que vous voulez lui donner.

Les différents attributs utilisables sont résumés dans ce tableau :

Par exemple, pour affecter l’attribut Lecture seule au fichier c:\data\a.pdf, vous utiliserez cette instruction :

SetAttr "c:\data\a.pdf", vbReadOnly

Vous pouvez également passer la valeur numérique correspondante à SetAttr :

SetAttr "c:\data\a.pdf", 1

Si nécessaire, vous pouvez cumuler plusieurs attributs en les additionnant. Par exemple, pour affecter les attributs Lecture seule et Fichier caché au fichier c:\data\a.pdf, vous utiliserez cette instruction :

SetAttr "c:\data\a.pdf", vbReadOnly + vbHidden

Ou encore :

SetAttr "c:\data\a.pdf", 3

Lire les attributs d’un fichier

Vous voulez connaitre l’attribut d’un fichier ? Utilisez la fonction GetAttr() :

GetAttr("chemin")

Où chemin est le chemin complet du fichier dont vous voulez connaitre les attributs.

Cette fonction retourne l’attribut du fichier spécifié sous une valeur numérique.

Par exemple, pour connaitre l’attribut de l’hypothétique fichier c:\data\a.pdf et l’afficher dans une boîte de dialogue, vous utiliserez cette instruction :

MsgBox "Le fichier c:\data\a.pdf a pour attribut : " & GetAttr("c:\data\a.pdf")

Ici, le fichier est en lecture seule :

Cet article va vous montrer comment manipuler des dossiers en utilisant des instructions VBA dans Excel. Vous verrez comment créer un dossier, supprimer un dossier, tester si un dossier existe et copier ou déplacer un dossier dans un autre.

Les instructions utilisées s’adresseront à un objet Scripting.FileSystemObject. Pour pouvoir créer un tel objet, vous devez définir une référence à la bibliothèque Microsoft Scripting Runtime. Lancez la commande Références dans le menu Outils. La boîte de dialogue Références s’affiche. Déplacez-vous dans la zone de liste des références disponibles et cochez la référence Microsoft Scripting Runtime :

Validez en cliquant sur OK. Vous êtes désormais prêt à manipuler des dossiers via un objet Scripting.FileSystemObject.

Créer un dossier

Pour créer un dossier, commencez par créer un objet Scripting.FileSystemObject :

Dim fs As New Scripting.FileSystemObject

Utilisez alors la méthode CreateFolder de l’objet Scripting.FileSystemObject pour créer un dossier en indiquant son chemin en argument :

fs.CreateFolder "chemin du dossier à créer"

Attention

Si le chemin comporte plusieurs niveaux, le niveau N ne pourra être créé que si le niveau N-1 existe.

Une fois le dossier créé, libérez la mémoire de l’objet fs :

Set fs = Nothing

Voici un exemple de code complet. Ici on suppose que le dossier c:\data existe et on crée le dossier c:\data\excel :

Dim fs As New Scripting.FileSystemObject

fs.CreateFolder "c:\data\excel"

Set fs = Nothing

Si le dossier spécifié en argument de la méthode CreateFolder existe, une erreur sera levée et une boîte de dialogue peu engageante s’affichera :

Pour améliorer les choses, vous pouvez mettre en place un gestionnaire d’erreurs :

On Error GoTo gestionErreurs

Dim fs As New Scripting.FileSystemObject

fs.CreateFolder "c:\data\excel"

Set fs = Nothing

End

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

End Sub

Si vous essayez de créer un dossier qui existe, la boîte de dialogue suivante s’affichera :

Si vous le souhaitez, vous pouvez créer un dossier sans passer par un objet Scripting.FileSystemObject :

MkDir "c:\data\excel"

Ou encore, en intégrant la gestion d’erreurs :

On Error GoTo gestionErreurs

MkDir "c:\data\excel"

End

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

Supprimer un dossier

Pour supprimer un dossier, commencez par créer un objet Scripting.FileSystemObject :

Dim fs As New Scripting.FileSystemObject

Indiquez alors le nom du dossier à supprimer à la méthode DeleteFolder de l’objet Scripting.FileSystemObject :

fs.DeleteFolder "F:\Atelier\Armoire"

Puis supprimez l’objet fs de la mémoire :

Set fs = Nothing

Voici le code complet, sans gestionnaire d’erreurs :

Dim fs As New Scripting.FileSystemObject

fs.DeleteFolder "F:\Atelier\Armoire"

Set GestionFichier = Nothing

Si vous essayez de supprimer un dossier inexistant, une erreur VBA est levée :

Pour améliorer les choses, définissez un gestionnaire d’erreurs :

On Error GoTo gestionErreurs

Dim fs As New Scripting.FileSystemObject

fs.DeleteFolder "c:\data\excel"

Set GestionFichier = Nothing

End

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

End Sub

Voici la boîte de dialogue affichée si vous tentez de supprimer un dossier inexistant :

Vous voulez un code plus compact ? Essayez cette instruction :

RmDir "c:\data\excel"

Ou encore, en intégrant la gestion d’erreurs :

On Error GoTo gestionErreurs

RmDir "c:\data\excel"

End

gestionErreurs:

  MsgBox "Erreur n° " & Err.Number & vbLf & Err.Description

Tester si un dossier existe

Pour tester si un dossier existe, commencez par créer un objet Scripting.FileSystemObject :

Dim fs As New Scripting.FileSystemObject

Vous pouvez alors utiliser la méthode FolderExists de cet objet en passant le nom du fichier en argument. Si le fichier existe, la méthode FolderExists retournera la valeur True. Dans le cas contraire, elle retournera la valeur False.

Pour terminer le code, supprimez l’objet Scripting.FileSystemObject de la mémoire en lui affectant la valeur Nothing :

Set fs = Nothing

Voici un exemple de code complet. Ici, si le dossier c:\data\excel n’existe pas, il est créé :

Dim fs As New Scripting.FileSystemObject

If fs.FolderExists("c:\data\excel") = False Then

  fs.CreateFolder "c:\data\excel"

End If

Set fs = Nothing

Copier un dossier dans un autre

Pour copier un dossier dans un autre, commencez par créer un objet Scripting.FileSystemObject :

Dim fs As New Scripting.FileSystemObject

Utilisez alors la méthode CopyFolder de cet objet en lui passant deux arguments : le chemin du dossier à copier et le chemin du dossier destination :

fs.CopyFolder "c:\data\excel", "c:\data\excel2"

Une fois la copie effectuée supprimer l’objet Scripting.FileSystemObject de la mémoire en lui affectant la valeur Nothing :

Set fs = Nothing

Voici un exemple de code complet. Ici, le dossier c:\data\excel est dupliqué dans le dossier c:\data\excel2 :

Dim fs As New Scripting.FileSystemObject

fs.CopyFolder "c:\data\excel", "c:\data\excel2"

Set fs = Nothing

Remarque

Si un fichier de même nom est trouvé dans le dossier destination, il n’est pas écrasé. Y compris si sa taille est différente.

Déplacer un dossier dans un autre

Pour déplacer un dossier dans une autre dossier, trois étapes sont nécessaires :

• Création du dossier destination
• Copie du dossier source dans le dossier destination
• Suppression du dossier source

Ici par exemple, le dossier c:\data\excel est déplacé dans le dossier e:\data\excel :

Dim fs As New Scripting.FileSystemObject

fs.CreateFolder "e:\data\excel"

fs.CopyFolder "c:\data\excel", "e:\data\excel"

fs.DeleteFolder "c:\data\excel"

Set fs = Nothing

Attention

Pour que ce code fonctionne, les dossiers c:\data\excel et e:\data doivent exister. Après son exécution, les fichiers et dossiers contenus dans le dossier c:\data\excel se retrouvent dans le dossier e:\data\excel.

Dans cet article, vous allez apprendre à accéder à un fichier texte en lecture. Ici, nous allons lire le contenu du fichier texte sauvegarde.txt, qui a été défini dans l’article « Sauvegarde dans un fichier texte ». Voici le contenu du fichier sauvegarde.txt :

es cinq lignes du fichier sauvegarde.txt seront copiées dans les cellules B1 à B5 pour obtenir ce résultat :

Voici le code utilisé :

Sub Macro1()

    On Error GoTo Erreur

    Dim Chaine As String

    Dim Fichier As String

    Dim UneLigne As String

    Dim i As Integer

    Fichier = "c:\data\sauvegarde.txt"

    Dim f As Integer

    f = FreeFile

    Open Fichier For Input As #f

    i = 0

    While Not EOF(f)

      i = i + 1

      Line Input #f, UneLigne

      UneLigne = UneLigne & " dans la cellule B" & i

      Cells(i, 2) = UneLigne

    Wend

    Close #f

    Exit Sub

Erreur:

    MsgBox "Le fichier de sortie est inaccessible"

End Sub

Il n’y a rien de compliqué dans ce code.

Une boucle While Wend parcourt le fichier sauvegarde.txt. La boucle prend fin lorsque tout le fichier a été parcouru :

While Not EOF(f)

  ...

Wend

Les lignes du fichier texte sont lues une par une avec une instruction Line Input :

Line Input #f, UneLigne

Le texte lu est complété :

UneLigne = UneLigne & " dans la cellule B" & i

Puis stocké dans la cellule i,2 :

Cells(i, 2) = UneLigne