Sommaire > 3. Exemples de macros fonctions

3. Exemples de macros fonctions

Une macro fonction est une nouvelle fonction ajoutée à l'application hôte. Elle reçoit un ou plusieurs paramètres ou arguments et renvoie un résultat.

3.1. Fonction qui permet de contrôler un numéro de compte bancaire

3.1.1. Création de la fonction dans Excel

Pour créer une macro fonction, on doit introduire les instructions dans un module Visual Basic. Un même module Visual Basic peut contenir des macros commandes et des macros fonctions.

On active Visual Basic Editor et on ajoute un module de la façon suivante :

  • 1 Outils / Macro/ Visual Basic Editor
  • 2 Module / Insertion et on modifie la propriété « (Name)  » qui devient : « CompteBancaire »

Les instructions Visual Basic qui permettent de définir une fonction sont entourées des mots clés :

Function et End Function.

Exemple :

Création d'une fonction qui contrôle la cohérence d'un numéro de compte bancaire. Ce numéro a été encodé sous la forme XXXYYYYYYYZZ (sans les tirets) et la cellule a été formatée à l'aide du format 000-0000000-00[3].

  • 1

Un numéro de compte bancaire est correct si :

  • le nombre de contrôle formé par les deux derniers chiffres (ZZ) est égal au reste de la division par 97 du nombre formé par les dix premiers chiffres (XXXYYYYYYY),
  • cependant, si ce reste est 0, le nombre de contrôle sera 97.

Cette fonction renvoie la valeur «  OK » si le compte est correct, sinon elle renvoie la valeur «  COMPTE BANCAIRE INCORRECT ».

Voici le texte de la macro :

'
' Contrôle du numéro de compte bancaire
'
Function CtrlNoCompte(Compte) As String
    If IsEmpty(Compte) Then Exit Function
    If IsNumeric(Compte) Then
        I = Int(Compte / 100)     ' 10 premiers chiffres
        digit = Compte - I * 100  ' 2 derniers chiffres
                                  ' reste de la division
        reste = I - Int(I / 97) * 97
        If reste = 0 Then reste = 97
        If reste <> digit Then
            CtrlNoCompte = "Compte bancaire incorrect"
        Else
            CtrlNoCompte = "Ok"
        End If
    Else
        CtrlNoCompte = "Compte bancaire incorrect"
    End If
End Function

Appel de la macro fonction :

Ces fonctions sont classées dans les fonctions personnalisées. On peut utiliser « L'assistant fonction » ou la commande « Fonction... / Insertion ».

Dans la feuille de calcul, il suffit de l'utiliser comme une fonction d'EXCEL. Par exemple, si dans la cellule B2, on écrit la formule :

  • 1 =CtrlNoCompte(A2)



    on reçoit la valeur « Ok » ou « Compte bancaire incorrect » suivant que le compte bancaire est correct ou non.

3.1.2. Utilisation de la fonction dans Excel, Word et Access

On peut exporter cette fonction dans un fichier texte pour pouvoir l'utiliser dans une autre application hôte.

  • 1 Outils / Macro/ Visual Basic Editor
  • 2 Exportation du module :
    Sélectionner le module « CompteBancaire »
    Exporter un fichier... / Fichier et valider
    (nom du fichier : « CompteBancaire.bas »
  • 3 Importation du module :
    Importer un fichier... / Fichier et valider
    (nom du fichier : « CompteBancaire.bas »

On peut également utiliser cette fonction dans une macro commande. Par exemple :

  • Dans Excel, on veut vérifier que la cellule active contient bien un numéro de compte correct. L'instruction « MsgBox » va afficher à l'écran la valeur « Ok » ou « Compte bancaire incorrect » suivant que le compte bancaire (qui se trouve dans la cellule active) est correct ou non. Voici cette macro :
Sub TestNoCompte()
    MsgBox CtrlNoCompte(ActiveCell)
End Sub
  • Dans Word, on veut vérifier que la sélection (ou le mot qui entoure le curseur) contient bien un numéro de compte correct (introduit sans les « tirets »). Cette macro va remplacer la sélection par le numéro de compte formaté ou va afficher à l'écran la valeur « Compte bancaire incorrect » suivant que le compte bancaire est correct ou non. Ainsi, « 6060 » devient « 000-0000060-60 ». Voici cette macro :
Sub TestNoCompte()
    Selection.MoveStart Unit:=wdWord, Count:=1
    Selection.StartOf Unit:=wdWord, Extend:=wdExtend
    test = CtrlNoCompte(Selection)
    If test = "Ok" Then
        Selection = Format(Selection, "000-0000000-00")
        Selection.MoveRight Unit:=wdCharacter, Count:=1
        Selection.TypeText Text:=" "
    Else
        MsgBox (test)
    End If
End Sub

Dans Access, lors de la saisie du numéro de compte bancaire « [Numéro compte] », on vérifie que ce compte est correct. La fonction de validation CtrlNoCompte permet cette vérification. Voici la procédure à suivre :

  • 1 Modifier le formulaire « frmClients »
    sélectionner l'onglet « Formulaires », « frmClients » et le bouton « Modifier »
  • 2 Sélectionner d'un simple clic la zone « NuméroCompte »,
    Propriétés / Affichage et l'onglet « Données »
  • 3 Remplir les zones
    Masque de saisie : 000-0000000-00
    Valide si :[NuméroCompte] Est Null Ou CtrlNoCompte[NuméroCompte]="Ok"
    Message si erreur :Compte bancaire incorrect.


Soundex

L'algorithme utilisé pour transformer des noms, de telle sorte que deux noms phonétiquement voisins aient même valeur, est SOUNDEX, dont une description précise est donnée dans l'ouvrage de D.Knuth, The Art of Computer Programming, Vol.3, Sorting and Searching. – Addison Wesley, 1973, page 392.

Cette fonction va calculer un code en quatre caractères (maximum) de façon à regrouper des mots de « même » consonance. Elle sera utilisée pour rechercher des valeurs dans une liste ou base de données. C'est ainsi que les mots «mètre», «mettre», «maître», ... auront le même code. Cet algorithme est d'ailleurs utilisé en généalogie pour retrouver les noms qui auraient subi une transformation due entre autres à une faute de recopie.

Voici les idées sur lesquelles s'appuie l'algorithme :

  • les voyelles et Y contribuent moins pour la consonance d'un mot que les consonnes. Elles seront donc supprimées sauf celle en position initiale;
  • les lettres H, W ont aussi une contribution minimale et seront donc supprimées sauf celle en position initiale;
  • les consonnes redoublées comme NN, SS et MM ou les lettres qui ont la même prononciation peuvent être réduites à une seule occurrence;

Pour savoir si des lettres ont la même consonance, on s'appuie sur la table suivante (en français) :

  1. B, P
  2. C, K, Q
  3. D, T
  4. L
  5. M, N
  6. R
  7. G, J
  8. X, Z, S
  9. F, V

Voici un résumé des différentes étapes de l'algorithme :

  • supprimer les éventuels « espaces » initiaux
  • mettre le mot en majuscules
  • garder la première lettre
  • supprimer les lettres A, E, I, O, U, Y, H et W
  • remplacer les lettres restantes par le chiffre associé dans la table
  • supprimer les chiffres répétés (garder une occurrence)

Cette fonction est grossière : ce n'est pas une vraie fonction de transcription en phonèmes. La recherche avec orthographe approximative donne souvent des résultats trop nombreux, et parfois déconcertants; inversement elle peut omettre un nom pourtant phonétiquement proche.

Texte de cette fonction

Function Soundex(s) As String
    Dim i As Integer, s1 As String
'   Suppression des espaces et
'       transformation du mot en majuscule
    s = UCase(Trim(s))
    s1 = Left(s, 1)
    Select Case True
        Case s1 Like "[ÀÂÄ]": s1 = "A"
        Case s1 Like "[ÉÈÊË]": s1 = "E"
        Case s1 Like "[ÎÏ]": s1 = "I"
        Case s1 Like "[ÔÖ]": s1 = "O"
        Case s1 Like "[ÙÛÜ]": s1 = "U"
        Case s1 = "Ç": s1 = "C"
    End Select
    s = s1 & Mid(s, 2)
'   Calcul du soundex
'   Premier caractère
    Soundex = Left(s, 1)
'   Autres caractères
    For i = 2 To Len(s)
        If Len(Soundex) = 4 Then
            Exit Function
        Else
            s1 = Mid(s, i, 1)
            Select Case True
                Case s1 Like "[BP]": s1 = "1"
                Case s1 Like "[CKQ]": s1 = "2"
                Case s1 Like "[DT]": s1 = "3"
                Case s1 = "L": s1 = "4"
                Case s1 Like "[MN]": s1 = "5"
                Case s1 = "R": s1 = "6"
                Case s1 Like "[GJ]": s1 = "7"
                Case s1 Like "[XZS]": s1 = "8"
                Case s1 Like "[FV]": s1 = "9"
                Case Else
                    s1 = ""
            End Select
'           Elimination des doubles
            If s1 <> "" Then
                If s1 <> Right(Soundex, 1) Then
                    Soundex = Soundex & s1
                End If
            End If
        End If
    Next i
End Function

[3] CELLULES... / FORMAT onglet NOMBRE catégorie PERSONNALISé type : 000-0000000-00.

Sommaire - Début de cette page
Page suivante: 4. Création d'une feuille VBA

Imprimer cet article