Comment utiliser Select Case en VBA dans Excel

Remplacer une cascade de If imbriqués par un Select Case

La cascade de If imbriqués se remplace en recopiant chaque seuil sur une ligne Case, sans rien changer au résultat. Le classeur d’exemple porte huit commandes, avec leur montant et le nombre de jours écoulés depuis la commande, et c’est sur ces deux colonnes que tout l’article travaille.

Le barème de remise compte quatre tranches, ce qui donne trois seuils à franchir. Écrit avec des If imbriqués, chaque tranche s’ouvre dans le Else de la précédente, si bien que le code descend en escalier et que les quatre résultats se retrouvent à quatre niveaux d’indentation différents.

Le résultat est juste, mais il coûte cher à relire. Ajouter une cinquième tranche oblige à trouver le bon Else, à décaler tout ce qui suit et à vérifier que chaque End If ferme toujours le bon bloc, alors que la décision, elle, reste d’une simplicité enfantine. La suite de ElseIf aplatit déjà l’escalier, et elle reste la bonne réponse quand les conditions portent sur des variables différentes, comme l’explique notre article sur la condition If en VBA.

Ici, pourtant, les quatre lignes posent la même question à la même variable, et c’est exactement le cas que Select Case est fait pour prendre. La structure nomme le montant une fois en tête, puis aligne les quatre réponses au même niveau.

Le nom de la variable n’apparaît plus qu’une fois, les quatre tranches se lisent l’une sous l’autre dans l’ordre du barème, et la cinquième s’insère en ajoutant une ligne. Sur les huit commandes du classeur, les deux écritures rendent la même remise à l’euro près, ce qui est le minimum qu’on attende d’une réécriture.

Écrire un Select Case, de l’expression évaluée au End Select

Un Select Case tient en trois pièces, l’expression évaluée après les deux mots d’ouverture, une ligne Case par possibilité, et End Select pour refermer. Tout ce qui est écrit sous un Case lui appartient, jusqu’au Case suivant, et l’indentation de ces lignes n’est qu’un confort de lecture.

Choisir l’expression que Select Case évalue

L’expression placée après Select Case est tout ce qui rend une valeur, et pas seulement une variable simple. Un calcul comme Len(reference), un appel de fonction, une comparaison qui rend Vrai ou Faux, ou la valeur d’une cellule lue par Range("C2").Value y tiennent aussi bien, et chacune de ces formes se comporte comme attendu.

Cette expression n’est évaluée qu’une fois, au moment où la macro entre dans le bloc, puis sa valeur sert à tous les cas. La différence se mesure avec une fonction qui compte ses propres appels, puisqu’un Select Case à quatre cas l’appelle une seule fois là où la suite de ElseIf équivalente l’appelle trois fois. Sur une fonction lente ou qui écrit quelque part, l’écart cesse d’être théorique.

Lister plusieurs valeurs dans le même Case

Plusieurs valeurs séparées par des virgules sur la même ligne Case déclenchent le même bloc, ce qui évite d’écrire une condition à rallonge avec des Or. La virgule accepte aussi des plages, et les deux se mélangent librement dans la même liste.

Prévoir un Case Else pour tout ce qui n’est pas listé

Case Else se place en dernier et récupère tout ce qu’aucun cas n’a pris, ce qui en fait le filet de sécurité de la structure. Sans lui, une valeur imprévue ne déclenche rien du tout, et c’est là que la structure devient sournoise.

Aucune erreur n’est levée, aucune boîte ne s’ouvre, la variable garde simplement ce qu’elle contenait avant, et la ligne qui suit End Select s’exécute comme si de rien n’était. Une remise restée à zéro parce que le montant n’était dans aucune tranche ressemble alors à une remise volontairement nulle, et rien dans le classeur ne distingue les deux.

Découper une valeur en tranches avec Case To et Case Is

Deux écritures couvrent un intervalle plutôt qu’une valeur précise, et elles ne se valent pas selon ce qu’on mesure. To ferme la tranche des deux côtés, alors que Is ne l’ouvre que d’un seul. Le choix entre les deux décide du sort des valeurs qui tombent entre deux bornes.

Borner une tranche des deux côtés avec To

Le mot To attend la borne basse, puis la borne haute, et les deux font partie de la tranche. Sur le délai de livraison, qui se compte en jours entiers, Case 4 To 7 attrape aussi bien le quatrième jour que le septième. La commande livrée en trois jours et celle livrée en huit passent donc ailleurs, chacune dans une tranche voisine.

L’ordre des deux bornes n’est pas négociable, la basse d’abord et la haute ensuite. Une tranche écrite Case 10 To 1 ne se déclenche jamais, pour aucune valeur. VBA ne signale rien, puisqu’aucun nombre n’est à la fois supérieur à dix et inférieur à un.

To fonctionne aussi sur du texte, où il compare les chaînes dans l’ordre alphabétique. Case "a" To "m" retient « bonjour » et laisse « zebre » de côté. C’est ce qui permet de répartir une liste de noms sur deux traitements, sans écrire vingt-six cas.

Ouvrir une tranche d’un seul côté avec Is

Le mot Is remplace la valeur attendue par une comparaison, et il accepte les opérateurs de comparaison habituels. Les écritures Case Is < 500 et Case Is >= 5000 se lisent comme la condition d’un If, à ceci près que la variable n’est pas répétée d’une ligne à l’autre.

C’est l’écriture à choisir dès que la valeur peut tomber entre deux bornes, et un montant en euros est précisément dans ce cas. Un barème écrit Case 500 To 1999 puis Case 2000 To 4999 laisse un trou entre les deux tranches. Une commande de 1 999,50 € n’y entre nulle part et se retrouve dans le Case Else, donc à 15 % de remise au lieu de 5 %.

Comprendre pourquoi seul le premier Case vrai s’exécute

VBA descend la liste des cas dans l’ordre où ils sont écrits, s’arrête au premier qui correspond, exécute son bloc et saute directement après End Select. Les cas suivants ne sont même pas examinés, et c’est ce qui permet au barème par Case Is < de tenir debout.

Dans ce barème, une commande de 1 250 € rend vrais deux cas sur quatre, celui des moins de 2 000 € et celui des moins de 5 000 €. Elle reçoit bien 5 %, parce que le cas des moins de 2 000 € est écrit au-dessus, et intervertir les deux lignes donnerait 10 % à la même commande sans qu’aucun message ne prévienne.

La règle se voit mieux sur une valeur de 200 confrontée à deux cas qui se recouvrent largement. Avec Case Is > 50 écrit avant Case Is > 100, c’est le premier qui l’emporte, et l’ordre inversé fait gagner le second, alors que la valeur comparée n’a pas bougé.

Comparer du texte sans se faire piéger par les majuscules

Sur du texte, Select Case compare caractère par caractère et tient compte de la casse, parce qu’un module VBA démarre en Option Compare Binary sans que rien ne l’écrive. Un Case "Livree" reconnaît donc « Livree » et rien d’autre, ni « livree », ni « LIVREE », alors que les trois désignent la même chose dans une colonne remplie à la main.

Deux parades existent, et elles n’ont pas la même portée. La première tient en une ligne posée tout en haut du module, avant la première procédure, et elle change la comparaison de texte de tout le module d’un coup.

La seconde parade ne change rien au module et voyage avec le code, ce qui la rend préférable dès qu’une procédure sera recopiée ailleurs. Elle consiste à mettre l’expression à la même casse que les cas, avec UCase ou LCase, et à écrire les cas dans cette casse.

La casse se rappelle aussi à toi dans les intervalles de texte, où elle produit un résultat vraiment déroutant. En comparaison binaire, les majuscules se rangent avant les minuscules, donc Case "a" To "m" retient « bonjour » mais laisse tomber « Bonjour », qui se retrouve dans le Case Else avec les noms commençant par un z.

Un dernier détail vaut d’être connu, parce qu’il ne se voit pas à la lecture. Une tranche de nombres posée sur une variable déclarée As String ne reconnaît pas les nombres, et Case 1 To 10 laisse passer « 5 » sans broncher, la comparaison se faisant alors lettre par lettre. Déclare la variable au type de ce qu’elle contient, As Double pour un montant et As Long pour un compte de jours, et le problème disparaît de lui-même.

Croiser deux variables dans un Select Case True

Select Case compare tout à une seule expression, donc un test qui croise deux variables ne s’y met pas directement. C’est sa limite naturelle, et c’est aussi la question qui revient le plus vite dès qu’on a pris goût à la structure.

La parade tient en un mot. On teste True, et chaque Case porte alors une condition entière, exactement comme la condition d’un If, que VBA compare à Vrai. Le premier cas qui rend Vrai gagne, et le fonctionnement redevient celui qu’on connaît.

Sur les huit commandes du classeur, une seule remplit les deux conditions, celle de 5 600 € arrivée à douze jours. Deux autres sont en retard sans être grosses, et reçoivent la relance simple. La commande de 4 990 € livrée en sept jours ne déclenche rien, puisque sept n’est pas strictement supérieur à sept.

Un piège attend dans ces conditions composées, parce que VBA calcule les deux membres d’un And même quand le premier suffirait à trancher. Un cas écrit Case delai <> 0 And montant / delai > 100 lève donc l’erreur 11, celle d’une division par zéro, sur les lignes où le délai est nul. Or se comporte pareil, et la parade consiste à sortir la condition protectrice dans un Select Case extérieur, qui écarte ces lignes avant d’en arriver au calcul.

Cette écriture surprend la première fois qu’on la croise, et elle mérite un commentaire au-dessus quand une autre personne doit relire la macro. Elle reste pourtant le seul moyen de garder la forme d’un Select Case quand les conditions ne portent plus sur la même variable. C’est aussi ce qui évite de repasser à l’escalier de ElseIf.

Classer toute une colonne de commandes avec une seule macro

Tout ce qui précède se réunit dans une macro qui parcourt la feuille et remplit les deux colonnes vides du classeur. Une boucle For descend de la ligne 2 jusqu’à la dernière ligne remplie, et chaque tour range le montant dans le barème puis le délai dans son statut.

La ligne qui calcule derniere part du bas de la colonne A et remonte jusqu’à la première cellule remplie, ce qui laisse la macro s’adapter au nombre de commandes. Les deux structures restent indépendantes l’une de l’autre, et rien n’empêche d’en ajouter une troisième pour une autre colonne, sur le modèle des deux premières. Notre article sur la boucle For en VBA détaille la boucle qui les enveloppe.

Les colonnes Remise et Statut se remplissent d’un coup pour les huit lignes. La commande de 500 € reçoit 5 %, celle de 1 999 € aussi, et celle de 2 000 € passe à 10 %, ce qui montre à l’œil nu où se posent les seuils du barème.

Une remise de ce genre s’obtient aussi par une formule de feuille, avec SI.CONDITIONS ou une recherche dans un tableau de barème. Sur une colonne isolée, c’est même souvent le plus rapide des deux. La macro reprend l’avantage dès que le classement s’enchaîne avec d’autres gestes, une couleur de ligne, un export ou un courrier. Tout tient alors dans la même procédure, qui se relance d’un raccourci quand les commandes changent.