La société de Jeffrey a un certain nombre de rapports qui utilisent un grand nombre de formules matricielles CSE (Ctrl + Maj + Entrée). Lorsque quelqu’un oublie de maintenir Ctrl et Maj en appuyant sur Entrée, les formules résultantes ne correspondent pas à la bonne réponse. Auditer chaque cellule, rechercher les crochets \ {} est à la fois fastidieux et prend du temps. Jeffrey se demande s’il existe un moyen rapide de trouver les « crochets manquants » ou de lever un indicateur d’erreur si Ctrl + Maj + Entrée n’est pas pressé alors qu’il devrait l’être?

Il n’y a pas de méthode intrinsèque ou formule pour faire cela dans Excel. Cela signifie que vous devez vous tourner vers une solution basée sur une macro.

Heureusement, VBA propose plusieurs manières d’aborder ce problème. Une approche consiste simplement à utiliser une formule pour s’assurer que chaque formule dans une sélection est en fait une formule matricielle.

Sub MakeCSE1()

Dim rCell As Range

For Each rCell In Selection         rCell.FormulaArray = rCell.Formula     Next rCell End Sub

Cette macro suppose que vous sélectionnerez les cellules à «convertir» avant d’exécuter réellement la macro. Si vous préférez, vous pouvez définir une plage de cellules (donner un nom à la plage), puis exécuter une macro similaire qui fait toujours son travail sur cette plage.

Sub MakeCSE2()

Dim rng As Range     Dim rCell As Range     Dim rArea As Range

Set rng = Range("CSERange")

For Each rArea In rng.Areas         For Each rCell In rArea.Cells             If rCell.HasArray = False Then                 rCell.FormulaArray = rCell.Formula             End If         Next rCell     Next rArea End Sub

Cette macro recherche une plage nommée CSERange, puis vérifie chaque cellule de la plage. S’il ne contient pas de formule matricielle, la formule est convertie en formule matricielle.

Notez l’utilisation de la propriété HasArray pour vérifier si une cellule contient une formule matricielle. Cette propriété peut en fait être utile d’autres manières. Par exemple, vous pouvez créer une fonction simple définie par l’utilisateur, telle que celle-ci:

Function NoCellArray1(rng As Range) As Boolean     NoCellArray1 = Not rng.HasArray End Function

Cette fonction renvoie True si la cellule pointée ne contient pas de formule matricielle. S’il en contient un, False est renvoyé. Vous pouvez ensuite utiliser cette fonction comme base pour un format conditionnel. Tout ce que vous avez à faire est de créer un format qui l’utilise de cette manière:

=NoCellArray1(A5)

Étant donné que NoCellArray renvoie True si la cellule ne contient pas de formule matricielle, votre format conditionnel peut définir la couleur de la cellule sur rouge ou définir un autre signe visible indiquant que la cellule n’a pas la formule matricielle requise. Vous pouvez également utiliser la fonction suivante pour accomplir la même tâche:

Function NoCellArray2(rng As Range) As Boolean     NoCellArray2 = (Evaluate(rng.FormulaArray) <> rng.Value)

End Function

Une approche totalement différente consiste à ajouter quelque chose à vos formules qui leur permette d’être facilement reconnues en tant que formules matricielles. Par exemple, vous pouvez ajouter ce qui suit à la fin de l’une de vos formules matricielles:

+N("{")

Cela n’affecte en aucun cas le calcul, mais peut être facilement vérifié pour voir s’il existe. La vérification peut être effectuée par un gestionnaire d’événements, comme le suivant:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Right(Selection.FormulaArray, 5) = "(""{"")" Then         ActiveCell.Select         Selection.FormulaArray = ActiveCell.Formula     End If End Sub

Notez que le gestionnaire vérifie si la formule se termine par (« \ {« ) et, si c’est le cas, force la formule à être traitée comme une formule matricielle. L’avantage de cette approche est que vous n’aurez plus jamais à appuyer sur Ctrl + Maj + Entrée sur la feuille de calcul – le gestionnaire d’événements s’en charge pour vous. Si, à un moment donné, vous souhaitez reconvertir la formule en une version standard (non-tableau), modifiez simplement la formule pour qu’elle n’inclue pas + N (« \ {« ).

_Note: _

Si vous souhaitez savoir comment utiliser les macros décrites sur cette page (ou sur toute autre page des sites ExcelTips), j’ai préparé une page spéciale qui comprend des informations utiles.

lien: / excelribbon-ExcelTipsMacros [Cliquez ici pour ouvrir cette page spéciale dans un nouvel onglet de navigateur].

ExcelTips est votre source pour une formation Microsoft Excel rentable.

Cette astuce (473) s’applique à Microsoft Excel 97, 2000, 2002 et 2003. Vous pouvez trouver une version de cette astuce pour l’interface ruban d’Excel (Excel 2007 et versions ultérieures) ici:

link: / excelribbon-Checking_for_Proper_Entry_of_Array_Formulas [Vérification de l’entrée correcte des formules de tableau].