Comment créer une fonction de recherche à deux critères pour récupérer la valeur et le format d’une cellule Excel ?

Dans ce tutoriel, nous allons répondre à la question posée par Clayrack dans les commentaires de la chaîne YouTube qui souhaite savoir comment utiliser une fonction Excel pour extraire une cellule en fonction de deux critères. Nous verrons comment faire en sorte de récupérer la valeur de la cellule, mais également son format.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour illustrer ce tutoriel, nous allons partir du tableau de suivi des CA mensuels d’une entreprise au cours des années 2019, 2020 et 2021 :

Dans un tutoriel précédent, nous avons déjà eu l’occasion de découvrir qu’effectuer une recherche 3D, c’est-à-dire en fonction d’un critère vertical et d’un autre horizontal n’est pas bien compliqué, il suffit en effet d’utiliser la fonction INDEX et EQUIV de la manière suivante :

=INDEX(B10:D21;EQUIV("Mai";A10:A21;0);EQUIV(2020;B9:D9;0)) 

Ici nous ne reviendrons par sur le détail de cette formule, vous retrouverez toutes les informations nécessaires dans l’article dédié.

En revanche, les choses vont se compliquer lorsque nous allons souhaiter récupérer également le format de la cellule !

En effet, aucune fonction d’Excel ne permet de réaliser cette opération, et nous allons devoir passer par le développement d’une petite macro-commande en VBA.

Si vous souhaitez en savoir davantage sur le VBA et aller plus loin dans la découverte de ce langage, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.

2. Créer la fonction de recherche

Pour commencer, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et nous allons commencer par créer un nouveau module.

Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous allons créer une fonction qui va permettre d’effectuer la recherche, et nous retourner dans un premier temps la cellule dans laquelle se trouve le résultat de la recherche.

Pour créer une fonction dans Excel, nous utilisons le mot-clé Function, suivi du nom que nous souhaitons lui donner, à savoir recherche3D :

Function recherche3D()
 
End Function

Lorsque nous appuyons sur la touche [Entrée], VBA insère automatique la ligne End Function, et tout ce que nous allons saisir entre ces deux lignes sera ensuite lancé automatiquement.

Maintenant que notre fonction est déclarée, nous allons pouvoir définir les arguments de celle-ci :

• Un premier argument va permettre à l’utilisateur de saisir les coordonnées du tableau, en-têtes inclus. Nous appelons cet argument table et nous le typons en tant que Range,
• Ensuite, le second argument va permettre à l’utilisateur de saisir le critère de recherche horizontale. Nous ne savons quel type d’information l’utilisateur va venir saisir, nous typons donc cette variable en tant que Variant,
• Et enfin, le troisième et dernier argument va permettre à l’utilisateur de renseigner l’argument vertical :

Function recherche3D(table As Range,  critereHorizontal As Variant, critereVertical As Variant)
 
End Function

Enfin, cette fonction va nous retourner une cellule, nous la typons donc comme un Range :

Function recherche3D(table As Range,  critereHorizontal As Variant, critereVertical As Variant) As Range
 
End Function

Pour effectuer la recherche, nous allons utiliser la fonction de recherche de VBA.

Celle-ci est à lancer sur une plage de cellule :

• Le critère horizontal va permettre d’identifier la colonne au sein du tableau, la recherche est donc à réaliser sur la première ligne,
• Le critère vertical va permettre d’identifier la ligne au sein du tableau, la recherche est donc à réaliser sur la première colonne

    Dim colonne As Range
    Set colonne = table.Rows(1).Find(critereHorizontal).EntireColumn 

Nous déclarons une variable colonne en tant que Range, dans laquelle nous venons stocker les coordonnées de la colonne dans laquelle se trouve la cellule à rechercher sur la première ligne.

La recherche étant réalisée avec la fonction Find() dans laquelle nous venons passer en paramètre le critère de recherche.

Ensuite, nous réalisons la même opération pour le critère de recherche vertical, sur la première colonne du tableau :

    Dim ligne As Range
    Set ligne = table.Columns(1).Find(critereVertical).EntireRow 

Cela étant fait, il ne reste plus qu’à retourner en tant que résultat de la fonction la cellule qui se trouve à l’intersection de ces deux plages de cellules :

    Set recherche3D = Intersect(colonne, ligne)

Voilà, nous pouvons maintenant tester la fonction depuis la feuille de calcul :

=recherche3D(A9:D21;2020;"Mai")

Il reste un dernier détail à mettre en place afin de s’assurer que la fonction va bien nous retourner le résultat attendu lorsque l’utilisateur va utiliser des références à des cellules plutôt qu’une valeur.

Pour cela, nous allons tester s’il est possible d’obtenir des coordonnées à partir de ces arguments et si c’est effectivement possible, alors nous modifierons les valeurs de ces arguments pour venir y stocker la valeur des cellules.

Et pour éviter tout message d’erreur, nous désactivons provisoirement la gestion des erreurs de VBA :

    On Error Resume Next
        If Not Range(critereHorizontal) Is Nothing  Then critereHorizontal = Range(critereHorizontal).Value
        If Not Range(critereVertical) Is Nothing Then  critereVertical = Range(critereVertical).Value
    On Error GoTo 0

3. Extraire le format

Maintenant que nous savons comment extraire le résultat de la cellule en fonction des critères définis, nous allons pouvoir nous atteler à extraire le format de la cellule.

Mais malheureusement, VBA ne nous permet pas de modifier le format de la cellule appelant une fonction…

Du moins pas directement, et nous allons devoir ruser.

Pour cela, nous allons en effet pouvoir utiliser l’un des évènements de VBA, qui permet de réaliser une opération lorsque quelque chose se passe sur une feuille ou le classeur.

Ainsi, nous allons pouvoir lancer une macro lorsque la cellule de récupération du résultat est modifiée, nous utiliserons donc l’évènement Worksheet_Change.

Pour cela, nous nous rendons dans la feuille de code de la feuille en double cliquant dessus :

Puis dans les menus déroulants situés au-dessus de la feuille nous sélectionnons :

• Worksheet pour afficher les évènements de feuille de calcul,
• Change pour insérer l’évènement qui nous intéresse

Cela étant fait, nous allons commencer par contrôler si la cellule modifiée est bien une cellule contenant la fonction recherche3D.

Pour cela, nous allons utiliser la propriété Formula de la cellule renvoyée par la variable Target, et vérifier que celle-ci commence bien par « =recherche3D »

Private Sub Worksheet_Change(ByVal Target  As Range)
    If Target.Formula Like "=recherche3D(*"  Then
    
    End If
End Sub

Nous utilisons ici l’opérateur Like, avec un critère partiel comme nous l’avons vu dans le tutoriel dédié.

Maintenant que nous savons que la cellule Target contient bien une fonction recherche3D, nous allons pouvoir extraire les arguments utilisés.

Nous commençons par stocker la valeur retournée par la propriété formula dans une variable nommée arguments :

        Dim arguments As Variant
        arguments = Target.Formula 

Puis, nous supprimons ce qui ne correspond pas aux arguments, c’est-à-dire le début du texte avec le nom de la fonction et la parenthèse ouvrante, et également la parenthèse fermante à la fin :

        arguments = Replace(arguments, "=recherche3D(",  "")
        arguments = Replace(arguments, ")",  "")

Ensuite, nous supprimons les éventuels guillemets qui se seraient récupérés et qui ne correspondraient pas aux valeurs des arguments :

        arguments = Replace(arguments,  """", "")

Et pour finir, nous allons transformer la variable arguments en un tableau :

        arguments = Split(arguments, ",")

Cela étant fait, nous allons pouvoir retrouver la cellule en utilisant la fonction recherche3D, afin de la copier :

        recherche3D(Range(arguments(0)),  arguments(1), arguments(2)).Copy

Et d’effectuer un collage spécial du format dans la cellule Target :

        Target.PasteSpecial xlPasteFormats

Avant de pouvoir tester, il va falloir stopper la détection des évènements pour ces deux lignes, afin de s’assurer qu’Excel ne lancer pas une boucle infinie, en utilisant l’instruction Application.EnableEvents :

            Application.EnableEvents = False
                recherche3D(Range(arguments(0)),  arguments(1), arguments(2)).Copy
                Target.PasteSpecial xlPasteFormats
            Application.EnableEvents = True

Cela fonctionne correctement, à condition de relancer la fonction :

Il reste donc une dernière modification à mettre en place.

Plutôt que d’utiliser l’évènement Worksheet_Change, nous allons utiliser l’évènement Worksheet_Calculate, qui se lance lorsqu’un calcul a lieu sur la feuille de calcul.

Ensuite, il restera à passer en revue toutes les cellules de la plage utilisée et obtenue avec la propriété UsedRange afin de détecter si celle-ci contient bien la fonction recherche3D, comme nous l’avons vu plus tôt :

Private Sub Worksheet_Calculate()
 
    Dim target As Range

Étant donné que l’évènement Worksheet_Calculate ne retourne aucune plage de cellule et pour ne pas avoir à modifier notre code, nous déclarons une nouvelle variable que nous appelons target.

Ensuite, nous intégrons notre code dans une boucle For Each qui va passer en revue toutes les cellules de la plage utilisée :

Private Sub Worksheet_Calculate()
 
    Dim target As Range
    
    For Each target In ActiveSheet.UsedRange
    
        If target.Formula Like "=recherche3D(*"  Then
        
            Dim arguments As Variant
            arguments = target.Formula
            
            arguments = Replace(arguments, "=recherche3D(",  "")
            arguments = Replace(arguments, ")",  "")
            
            arguments = Replace(arguments,  """", "")
            
            arguments = Split(arguments, ",")
            
            Application.EnableEvents = False
                recherche3D(Range(arguments(0)),  arguments(1), arguments(2)).Copy
                target.PasteSpecial xlPasteFormats
            Application.EnableEvents = True
    
        End If
    Next
End Sub

Pour finir, si nous souhaitons que la cellule ne soit plus identifiée comme étant copiée, il suffit de terminer notre macro par la ligne suivante :

Application.CutCopyMode = False

Et voilà, notre macro est maintenant terminée !