Jeu du pendu en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Le jeu du pendu en VBA Excel

Dans cette formation, nous proposons de monter le jeu du pendu avec Excel, en le créant de toutes pièces. L'objectif est de pouvoir exploiter au mieux les formules et fonctionnalités d'Excel avant de faire appel au code Visual Basic pour prendre le relai.



A chaque tour, une définition est proposée pour décrire un mot masqué. Le joueur doit proposer des lettres une à une. Lorsqu'elle est trouvée dans le mot, l'expression masquée se dévoile peu àpeu. Lorsque la lettre est absente, le pendu se dessine au fur et à mesure. Le joueur a le droit à quatre erreurs par mots. Dix mots sont proposés aléatoirement à chaque partie. A l'issue, le participant peu consulter sa note, inscrite en bas à droite dans le tableau de bord, comme l'illustre la capture ci-dessus.

Présentation du classeur de Jeu
Pour créer le jeu du pendu avec Excel, faut-il encore disposer d'une base de données des définitions avec les mots à trouver. C'est la raison pour laquelle nous proposons de récupérer un classeur existant sur lequel, la structure de base a déjà été conçue.

• Télécharger le classeur Excel jeu-du-pendu.xlsm en cliquant sur son lien,
• L'ouvrir dans Excel et cliquer sur le bouton Activer la modification si nécessaire,

Deux feuilles composent ce classeur. Sur la feuille Jeu, la structure du pendu a déjà été conçue, mais rien n'est encore fonctionnel. Une zone est prévue en B5 pour afficher la définition du mot ou de l'expression à trouver. La cellule B7 doit être utilisée par le code VBA pour reconstruire la chaîne de caractères, au fur et à mesure des propositions de lettres par le joueur. La cellule E11 doit accueillir les propositions de lettres du participant. La cellule fusionnée en B15 doit servir à consolider et afficher toutes les lettres qui ont déjà été proposées. Vous notez la présence de petites cases en bas à droite servant à fournir des données de synthèse au cours de la partie. Deux boutons sont prévus. Le bouton Nouveau doit servir à débuter une nouvelle partie du pendu. Le bouton Proposer doit servir à soumettre la lettre suggérée afin que le code Visual Basic Excel la contrôle. Sur la droite enfin, vous notez la présence d'un pendu, dessiné en couleurs pâles. Au fil des erreurs, il doit s'illuminer par la mise en forme conditionnelle, afin d'alerter visuellement le joueur.



La feuille Mots quant à elle, propose la base de données des expressions à retrouver, sur le thème des dessins animés anciens. Quarante définitions pour quarante expressions sont proposées. Vous notez la présence d'une colonne Compteur, vide à ce stade. Nous l'exploiterons par calculs Excel pour enregistrer la progression du jeu.



Il s'agira d'être en mesure de proposer les termes à trouver de façon aléatoire, à chaque partie. Comme une partie se déroule sur dix expressions et que la base en comporte quarante, chaque lancement sera ainsi différent.

Restreindre les saisies utilisateur
Bien sûr, une fois que le développement sera terminé, la feuille Mots sera masquée afin que le joueur n'ait pas accès à la base de données des bonnes réponses. De plus, il s'agira d'empêcher la saisie dans toutes les cellules portant des calculs ou dont le contenu est exclusivement modifié par le code VBA Excel. Mais comme nous devons réaliser des essais pendant de développement, nous ne pouvons pas encore protéger la feuille afin de verrouiller les cellules concernées. En revanche, nous pouvons d'ores et déjà appliquer une règle de saisie dans la cellule fusionnée E11 de la feuille Jeu. Il s'agit de la seule cellule dans laquelle le joueur aura le droit d'intervenir à l'issue. Et il doit l'utiliser pour soumettre une lettre et pas plus. Plutôt que de contrôler la saisie de l'utilisateur par le code VBA, nous pouvons exploiter les fonctionnalités de validation que propose Excel.

• Cliquer dans la cellule E11 de la feuille Jeu, pour la sélectionner,
• Puis, cliquer sur l'onglet Données en haut de la fenêtre Excel pour activer son ruban,
• Dans la section Outils de données, cliquer sur le bouton Validation de données,

Une boîte de dialogue apparaît, comme l'illustre la figure ci-dessous. Nous l'avons utilisée à maintes reprises afin de créer des listes déroulantes dans les cellules d'une feuille et ainsi limiter les autorisations de saisie.

• Dans la zone Autoriser, choisir Longueur du texte avec la liste déroulante,
• Dans la section Données, choisir l'opérateur égale à,
• Puis, dans la zone de saisie Longueur, taper le chiffre 1,
• Valider ce réglage en cliquant sur le bouton Ok de la boîte de dialogue.

Nous venons de créer une règle de validité qui empêche la saisie de plusieurs caractères d'affilée, dans la cellule E11. D'ailleurs, comme l'illustre la capture ci-dessus, si vous tentez d'enchaîner les caractères, une alerte s'affiche et vous empêche de valider la cellule. Le joueur doit en effet proposer une lettre, l'une après l'autre.

Générer les coups suivants
Lorsque le mot a été trouvé ou que le joueur a commis quatre erreurs, le terme suivant doit être proposé afin de dérouler la partie. Comme nous ne pouvons pas développer tout le code VBA en une seule fois, nous allons commencer par introduire les lignes qui permettent de générer les questions suivantes. C'est un clic sur le bouton Proposer qui doit déclencher cette action. Arbitrairement, nous choisissons d'exploiter la cellule B17 de la feuille Jeu, comme compteur de partie.

• Cliquer avec le bouton droit de la souris sur le bouton Proposer,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la boîte de dialogue qui suit, Saisir Proposer dans la zone Nom de la macro,
• Puis, cliquer sur le bouton Nouvelle,

Nous basculons ainsi dans l'éditeur de code Visual Basic Excel, entre les bornes de la procédure Sub Proposer(). Tout code VBA saisi entre ces bornes, se déclenchera au clic sur le bouton Proposer.

• Entre les bornes de la procédure, ajouter la ligne de code suivante :

Range('b17').Value = Range('b17').Value + 1



Range est un objet VBA Excel qui désigne une plage de cellules et à défaut une seule cellule, selon les références qui lui sont passées en paramètre. Ainsi Range('b17') permet de pointer sur la cellule B17 de la feuille active, soit la feuille Jeu. Value est la propriété qui permet d'accéder au contenu d'un objet de type Range, soit au contenu de la cellule ici. Donc, avec l'expression : Range('b17').Value = Range('b17').Value + 1, nous ajoutons la valeur 1 à la valeur déjà présente, à chaque clic sur le bouton. En termes de programmation, nous incrémentons la valeur de la cellule.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
• Cliquer dans n'importe quelle cellule afin de désactiver la sélection du bouton,
• Puis, cliquer à plusieurs reprises sur le bouton Proposer,

La valeur de la cellule B17 est effectivement incrémentée à chaque clic, comme l'illustre la capture ci-dessous. Nous devons utiliser cette donnée comme repère afin de dérouler les expressions du pendu. Si le chiffre 4 est inscrit en B17, cela signifie que quatre expressions ont déjà été proposées. Il s'agit donc de fournir la définition du mot suivant, soit la cinquième.



Cette valeur peut être utilisée comme indice de ligne par la fonction d'extraction Index, pour récupérer la définition et le mot à rechercher, dans la table de données de la feuille Mots. Nous choisissons arbitrairement la cellule J7 pour extraire l'expression à reconstruire. Bien sûr à l'issue, cette cellule sera masquée au joueur.

• Cliquer dans la cellule J7 de la feuille Jeu, pour la sélectionner,
• Taper le symbole = pour débuter le calcul,
• Saisir le nom de la fonction de gestion d'erreur suivi d'une parenthèse, soit SIERREUR(,
• Taper le nom de la fonction d'extraction suivi d'une parenthèse, soit INDEX(,
• Cliquer sur l'onglet Mots en bas de la fenêtre pour activer sa feuille,
• Sélectionner toute la base de données, soit la plage de cellules B3:D42,
• Taper un point-virgule pour passer dans l'argument de l'indice de ligne de recherche,
• Cliquer sur l'onglet Jeu en bas de la fenêtre pour revenir sur la première feuille,
• Sélectionner la cellule B17 du compteur incrémenté par le code VBA,
• Taper le symbole + suivi du chiffre 1, soit + 1 pour pointer sur la ligne suivante,
• Taper un point-virgule pour passer dans l'argument de l'indice de colonne d'extraction,
• Saisir le chiffre 3 et fermer la parenthèse de la fonction Index,
• Taper un point-virgule pour passer dans l'argument de la gestion d'erreur de la fonction SiErreur,
• Saisir deux guillemets pour laisser vide la cellule en cas d'erreur, soit '',
• Fermer la parenthèse de la fonction SiErreur et valider le calcul avec la touche Entrée,

Nous avons donc construit la formule suivante :

=SIERREUR( INDEX( Mots!B3:D42;B17+1;3); '')

Et vous remarquez que le mot correspondant à l'indice de ligne incrémenté en B17, est extrait de la source de données, depuis la feuille Mots. Nous avons supprimé le préfixe Jeu! devant la cellule B17 car il n'est pas nécessaire de désigner une feuille qui est considérée par défaut.

Nous avons exploité la fonction Excel SiErreur car une fonction d'extraction telle que Index, retourne un message d'erreur lorsqu'aucune donnée à rechercher ne lui est fournie. Dans le premier argument, nous imbriquons la fonction Index. Ainsi un résultat d'extraction est retourné, le mot à trouver, lorsqu'aucune erreur n'est rencontrée. Dans le cas contraire, nous laissons la cellule vide par l'inscription de deux guillemets dans le deuxième argument, qui correspond à la partie de la gestion d'erreur.

Quant à la fonction Index, elle retourne une valeur issue d'une source de données, en fonction d'un indice de ligne et d'un indice de colonne. Nous passons donc en premier argument de la fonction, la plage de cellules des expressions à trouver. En deuxième argument, nous fournissons le compteur incrémenté en guise d'indice de ligne. Puis nous terminons avec le chiffre 3 puisque le terme à trouver se situe en troisième colonne du tableau de recherche.

Si vous cliquez à plusieurs reprises sur le bouton Proposer, en même temps que le compteur est incrémenté, l'expression à chercher change.



Dans la cellule fusionnée B5, il s'agit de reproduire exactement la même formule, en adaptant le numéro de colonne d'extraction. La définition du mot ou de l'expression à trouver, se situe en deuxième colonne. Il s'agit donc de remplacer l'indice 3 par 2, dans le troisième argument de la fonction Index.

• Cliquer dans la cellule J7 pour la sélectionner,
• Sélectionner et copier l'intégralité du calcul depuis la barre de formule,
• Valider par Entrée ou Echap pour ne pas détériorer le calcul,
• Cliquer dans la cellule B5 pour la sélectionner,
• Dans sa barre de formule, coller le calcul (CTRL + V) précédemment copié,
• Changer le chiffre 3 par le chiffre 2 dans le troisième argument de la fonction Index,
• Valider le calcul avec la touche Entrée du clavier,

Le fait de copier une formule de barre de formule à barre de formule, permet de ne pas déplacer les références du calcul, afin de le reproduire à l'identique. Ainsi, il ne restait plus qu'à adapter l'indice de colonne de la fonction Index, pour désigner l'emplacement de l'extraction.

Désormais à chaque clic sur le bouton Proposer, un nouveau mot est importé en J7 avec sa définition correspondante, en B5. Sauf que, avant de proposer un nouveau mot, un clic sur le bouton Proposer doit suggérer une nouvelle lettre destinée à reconstruire l'expression cachée. Et les termes précédents ne doivent apparaître que sous deux conditions. Soit le mot a été intégralement découvert, soit quatre erreurs ont été commises, invalidant le coup. Cela signifie que le code du bouton Proposer doit continuer d'être développé. Mais avant cela, nous allons nous soucier du bouton Nouveau.

Réinitialiser les réglages du jeu
Au clic sur le bouton Nouveau, une nouvelle partie doit commencer. De nombreuses cellules doivent être réinitialisées, à commencer par la cellule B17 du compteur. Le simple fait de supprimer le contenu de cette cellule, fait réagir les fonctions Excel d'extraction que nous avons bâties dessus. Comme l'indice d'extraction dans la fonction Index est défini sur B17 + 1, nous repartons ainsi de la première question.

• Cliquer avec le bouton droit de la souris sur le bouton Nouveau,
• Dans le menu contextuel, choisir Affecter une macro,
• Dans la boîte de dialogue qui suit, saisir le nom de macro : Nouveau,
• Puis, cliquer sur le bouton Nouvelle,

Nous basculons ainsi de nouveau dans l'éditeur de code VBA Excel, entre les bornes de la nouvelle procédure Sub Nouveau(). Tout code VBA saisi entre ces bornes se déclenchera au clic sur le bouton Nouveau. Etonnamment et comme le prouve l'explorateur de projet, cette procédure a été créée dans un nouveau module, le module2. Par esprit de structure, nous souhaitons que cette procédure figure dans le même module que la précédente.

• Réaliser le raccourci clavier CTRL + A pour sélectionner toute la procédure,
• Couper le code par CTRL + X,
• Dans l'explorateur de projet, double cliquer sur Module1 dans l'arborescence,
• Et coller (CTRL + V) la procédure coupée, au-dessus de la précédente,

Désormais le module2 n'est plus utile, raison pour laquelle nous allons le supprimer.

• Dans l'explorateur de projet, cliquer avec le bouton droit de la souris sur Module2,
• Dans le menu contextuel, choisir Supprimer Module2,
• Au message qui apparaît, cliquer sur Non pour ne pas le sauvegarder,
• Entre les bornes de la procédure Nouveau(), ajouter l'instruction suivante :

Range('B17').Value= ''

L'objet VBA Range permet donc de désigner la cellule B17 dont les références lui sont passées en paramètre. Grâce à sa propriété Value nous réinitialisons son contenu en le vidant.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille (ALT + F11),
• Cliquer dans n'importe quelle cellule pour désactiver la sélection du bouton,
• Puis, cliquer sur le bouton Nouveau,

Instantanément, le contenu de la cellule B17 se vide. Et comme les formules d'extraction sont bâties sur cet indice de ligne, la question et sa définition sont réinitialisées sur la première ligne du tableau de données. En prévision et de la même façon, doivent être réinitialisées les cellules suivantes :

• La cellule fusionnée B15 des lettres déjà proposées sui seront consolidées par le code VBA,
• La cellule fusionnée E11 qui permet au joueur de suggérer une lettre à proposer,
• La cellule fusionnée B7 qui reconstruit le mot masqué au fur et à mesure,
• La cellule E18 qui permettra d'incrémenter les coups corrects pour chaque mot,
• La cellule F18 qui permettra de comptabiliser les erreurs pour construire le pendu,
• La cellule I18 qui permettra de consolider la note à chaque nouveau mot trouvé,

La cellule H18 quant à elle, se contentera d'afficher le nombre de mots restants, en partant de 20. Une fonction de calcul suffira. Donc elle se réinitialisera toute seule. En conséquence, nous devons ajouter les lignes de code permettant de réinitialiser chacune des cellules que nous venons de lister.

• Basculer dans l'éditeur de code à l'aide de la combinaison ALT + F11 par exemple,
• Sous le code précédent pour la cellule B17, ajouter les instructions suivantes :

Range('B15').Value = '':Range('E11').Value = '': Range('B7').Value = ''
Range('E18').Value = '': Range('F18').Value = '': Range('I18').Value = ''
Range('E11').Select

Notez l'emploi des deux points (:) dans la syntaxe VBA pour permettre l'énumération des instructions plutôt que de saisir le code sur plusieurs lignes. La méthode Select sur l'objet Range pour la cellule E11 permet d'activer la cellule de la lettre à proposer, afin que le joueur n'ait pas à la resélectionner.

Génération aléatoire par tri des données
Comme nous le disions au début de ce support, nous souhaitons que l'ordre des mots proposés, change d'une partie à une autre. La solution que nous suggérons est de réaliser un tri aléatoire sur le tableau de données. Tantôt nous trierons sur la colonne des définitions, tantôt sur la colonne des mots. Et tantôt nous trierons croissant et tantôt décroissant, ce qui nous offre quatre possibilités de tris différents.

La formation pour purger les tableaux Excel en VBA nous avait appris à réaliser des tris sur des plages de cellules par le code. Et c'est en enregistrant une macro automatique Excel, que nous avions appris les objets et méthodes nécessaires. Nous allons faire de même ici. Une fois le code prélevé et adapté, nous nous soucierons de générer ce tri aléatoirement.

• Enregistrer le code précédent (CTRL + S) et basculer sur la feuille (ALT + F11),
• Cliquer sur l'onglet Mots en bas de la fenêtre pour activer sa feuille,
• Cliquer sur l'onglet Développeur en haut de la fenêtre pour activer son ruban,

Si l'onglet Développeur n'est pas présent, la formation pour débuter la programmation en VBA, rappelle simplement comment l'afficher.

• Dans la section Code de l'onglet Développeur, cliquer sur le bouton Enregistrer une macro,
• Dans la boîte de dialogue qui suit, la nommer : Trier,

Le choix par défaut Ce classeur est celui que nous devons conserver afin de ne garder le code VBA de cette macro, que pour cette application, et non pour toutes les utilisations à venir. Dès lors que nous aurons cliqué sur le bouton Ok, la macro Excel enregistrera la moindre de nos actions, afin de les traduire en code Visual Basic. Il s'agit donc de simuler les actions strictement nécessaires.

• Cliquer sur le bouton Ok pour démarrer l'enregistrement,
• Sélectionner les colonnes C et D du tableau, soit la plage de cellules C2:D42,
• Cliquer sur l'onglet Accueil en haut de la fenêtre pour activer son ruban,
• Dans la section Edition sur la droite, cliquer sur la flèche du bouton Trier et filtrer,
• Dans la liste, choisir Tri personnalisé,
• Dans la boîte de dialogue qui suit, à l'aide des listes déroulantes, définir un tri croissant sur les valeurs de la colonne Expression, comme l'illustre la capture ci-dessous,
• Cliquer sur le bouton Ok pour valider ce tri,
• Puis cliquer tout de suite sur le bouton Arrêter l'enregistrement du ruban Développeur,

Le code du tri personnalisé est désormais transcrit en langage VBA. Nous allons nous en inspirer.

• Basculer dans l'éditeur de code à l'aide du raccourci ALT + F11 par exemple,

Vous constatez de nouveau la présence du Module2. Ce dernier a été créé automatiquement pour accueillir le code VBA de la macro Excel.

• Double cliquer sur le Module2 dans l'explorateur de projet,

Le code est encapsulé dans la procédure Trier, tel que nous avons nommé la macro. Certaines lignes ne sont pas nécessaires car elles retranscrivent des actions intermédiaires ou des réglages superflus.

• Supprimer les deux lignes de code préliminaires :

Range('C2').Select
ActiveWindow.SmallScroll Down:=27

Seule la sélection complète de la plage de cellules pour le tri nous intéresse. De plus, les actions à la souris sont inutiles pour nos traitements, comme le Scroll dans notre cas.

• Si elle est présente, supprimer la seconde ligne transcrivant l'action du Scroll de la souris :

ActiveWindow.SmallScrollDown :=-48

• Dans l'instruction With, supprimer l'instruction de la méthode SortMethod :

.SortMethod = xlPinYin

Il s'agit en effet d'une méthode de tri destinée aux langues chinoises, ce qui ne nous concerne pas. Quoiqu'il en soit, vous notez que le tableau a correctement été trié croissant grâce au paramètre Order:=xlAscending, sur la colonne des expressions à trouver, grâce à l'argument Key:=Range('D3:D42'), mais tout en gardant la colonne Définition liée dans le tri, grâce à la méthode .SetRangeRange('C2:D42'), dans l'instruction With. L'instruction With permet d'optimiser le code VBA, en énumérant toutes les méthodes et propriétés d'un objet afin d'éviter les répétitions.

Maintenant que le code restitué est nettoyé, nous allons le prélever pour l'exploiter dans une procédure indépendante que nous allons créer dans le module1. Cette procédure sera appelée par le code du bouton Nouveau.

• Sélectionner tout le code compris entre les bornes de la procédure, donc sans les lignes du Sub et du End Sub,
• Les copier (CTRL + C),
• Double cliquer sur le Module1 dans l'explorateur de projet pour afficher son code,
• Sous la procédure Nouveau, donc après son End Sub, créer la procédure tri_aleatoire, en ajoutant ces lignes :

Sub tri_aleatoire()

End Sub

• Entre les bornes de la procédure ainsi créée, coller le code (CTRL + V) précédemment copié,
• Puis à la fin de la procédure Nouveau, ajouter l'appel à ce code de tri, comme l'illustre la capture ci-dessous :

Ainsi, dès qu'une nouvelle partie est enclenchée par clic sur le bouton Nouveau, après réinitialisation de toutes les cellules mentionnées par le code, le tableau des mots à trouver est systématiquement trié. Mais pour l'instant, il est toujours trié de la même façon. Nous devons donc ajouter les instructions qui permettront de faire varier ce tri aléatoirement.

C'est la fonction Rnd qui permet de générer un nombre aléatoire en VBA. Et pour que ce nombre soit d'une part un entier et d'autre part compris entre une borne inférieure et une borne supérieure, l'aide en ligne nous enseigne cette syntaxe :

Int((upperbound - lowerbound + 1) * Rnd + lowerbound)

Int est la fonction VBA qui ne conserve que la partie entière du nombre qui lui est passé en paramètre, soit le nombre aléatoire. upperbound correspond à la borne supérieure tandis que lowerbound correspond à la borne inférieure. Comme nous envisageons quatre possibilités de tris, nous devons faire varier cette valeur aléatoire entre 1 et 4. En conséquence :

• Ajouter les instructions suivantes au tout début de la procédure tri_aleatoire, avant les instructions de tri :

Dim nb_alea As Byte
nb_alea = Int((4 * Rnd()) + 1)

Nous déclarons tout d'abord la variable nb_alea pour stocker la valeur générée comme un entier court (Byte), puisqu'il s'agit d'un petit nombre. Puis nous affectons à cette variable, la valeur générée par l'instruction de la fonction Rnd, telle que nous l'enseigne l'aide en ligne.

Selon la valeur de ce nombre, nous devons faire varier la colonne ou l'ordre de tri. L'instruction Select Case permet de gérer facilement de nombreuses conditions à énumérer.

• Avant l'instruction With, remplacer le tri statique par les instructions suivantes :

Select Case nb_alea
Case 1:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 2:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 3:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 4:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End Select

Si le nombre aléatoire vaut 1, un tri croissant est réalisé sur la première colonne (C3:C42) et décroissant (Order:=xlDescending) s'il vaut 2. Si le nombre aléatoire vaut 3, un tri croissant est réalisé sur la deuxième colonne (D3:D42) et décroissant s'il vaut 4.

Le code VBA complet pour le tri aléatoire de la base de données du pendu est donc le suivant :

Sub tri_aleatoire()
Dim nb_alea As Byte

nb_alea = Int((4 * Rnd()) + 1)

Range('C2:D42').Select
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Clear

Select Case nb_alea
Case 1:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 2:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('C3:C42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
Case 3:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
Case 4:
ActiveWorkbook.Worksheets('Mots').Sort.SortFields.Add Key:=Range('D3:D42'), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal
End Select

With ActiveWorkbook.Worksheets('Mots').Sort
.SetRange Range('C2:D42')
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With
End Sub

• Enregistrer les modifications et basculer sur Excel,
• Sélectionner la feuille Jeu et cliquer sur le bouton Nouveau,
• Cliquer à plusieurs reprises sur ce bouton,

Comme nous le constatons, à chaque clic, un mot et sa définition associée sont proposés aléatoirement. Dans la procédure Nouveau, il est opportun de placer la sélection de la cellule E11 après le tri, pour que la cellule reste effectivement sélectionnée.

• En conséquence, intervertir les lignes de code comme suit :

tri_aleatoire
Range('E11').Select

Pour parfaire l'initialisation de la partie, nous avons besoin d'une dernière procédure. Elle est destinée à afficher dans la zone du mot à trouver, autant de tirets qu'il y a de lettres dans le mot. Il s'agira donc d'un indice précieux pour le joueur. Cette procédure est volontairement indépendante, dans la mesure où elle devra être appelée pour chaque nouveau mot durant la partie.

• Entre les procédures Nouveau et tri_aleatoire, créer la procédure construction comme suit :

Sub construction()
End Sub

Pour remplacer chaque lettre de l'expression à trouver, nous pouvons parcourir toutes ses lettres grâce à une boucle For Next, sur la longueur de la chaîne de caractères. Souvenez-vous, c'est la fonction VBA Len qui retourne le nombre de caractères de la chaîne qui lui est passée en paramètre. Donc, nous avons premièrement besoin d'une variable pour parcourir cette boucle.

• Entre les bornes de la procédure construction, ajouter le code suivant :

Dim i As Byte

For i = 1 To Len(Range('j7').Value)
Range('b7').Value = Range('b7').Value & '-'
Next i

Nous partons du principe que l'expression à trouver ne dépassera jamais les 255 caractères. C'est pourquoi nous déclarons la variable de boucle (i), comme un entier court (Byte). Puis, du premier au dernier caractère (Len(Range('j7').Value)) du mot à trouver, nous inscrivons un tiret à la place, en reconstruisant la chaîne par concaténation (& '-').

Cette procédure doit être appelée, une fois le tri aléatoire généré pour qu'elle agisse sur le dernier mot extrait.

• Dans la procédure Nouveau, après l'appel du tri aléatoire, ajouter l'appel de la construction,

Le code complet associé au bouton Nouveau pour réinitialiser la partie est donc le suivant :

Sub Nouveau()
Range('B17').Value = ''
Range('B15').Value = '': Range('E11').Value = '': Range('B7').Value = ''
Range('E18').Value = '': Range('F18').Value = '': Range('I18').Value = ''

tri_aleatoire
construction
Range('E11').Select
End Sub

Il reste un dernier petit réglage à réaliser pour initialiser complètement une partie. La cellule H18 de la feuille Jeu, doit comptabiliser les mots restants en fonction du nombre qui a déjà été joué. Une simple soustraction Excel avec la cellule B17 suffit, puisque le compteur est déjà incrémenté dans cette cellule.

• Enregistrer les modifications et basculer sur la feuille Excel,
• Cliquer dans la cellule H18 pour la sélectionner,
• Saisir le calcul suivant :

=10-B17

Comme la cellule B17 est vide par défaut, la cellule affiche 10 mots restants à chaque début de partie.

• Cliquer sur le bouton Nouveau pour initialiser une nouvelle partie,

En fonction du tri aléatoire généré, un mot et sa définition sont importés. La cellule B7 de l'expression à reconstituer, indique bien autant de tirets qu'il y a de lettres dans le mot, encore visible à ce stade. Le nombre de coups restants est toujours calé sur 10. Si vous cliquez sur le bouton Proposer, en même temps que les mots défilent et que le compteur en B17 s'incrémente, la valeur des coups restants en H18 est bien décrémentée.



Protéger les cellules des formules
En réalité, la seule cellule dans laquelle le joueur a le droit d'intervenir est la cellule E11, pour proposer une lettre. Comme nous l'avons déjà appris, les cellules autorisées doivent d'abord être déverrouillées avant de protéger la feuille et donc, toutes les cellules qui sont restées verrouillées.

• Cliquer avec le bouton droit de la souris sur la cellule E11,
• Dans le menu contextuel, choisir Format de cellule,
• Dans la boîte de dialogue qui suit, activer l'onglet Protection,
• Puis, décocher la case Verrouillée et valider par Ok,

Si nous protégeons la feuille à l'aide du ruban Révision, le code VBA Excel n'aura pas plus le droit que l'utilisateur, d'intervenir dans les cellules verrouillées et protégées. Si bien que nous devons ajouter les instructions permettant en début de chaque procédure d'ôter la protection de la feuille. Ainsi le code VBA peut réaliser ses traitements. Puis, en fin de chaque procédure, il doit réactiver la protection de la feuille. Une macro Excel automatique nous livre facilement le code nécessaire.

• Basculer dans l'éditeur de code,
• En entête des procédures Nouveau et Proposer, ajouter l'instruction suivante :

ActiveSheet.Unprotect

ActiveSheet est l'objet VBA Excel qui désigne la feuille active, soit la feuille Jeu. Sa méthode Unprotect permet d'ôter la protection de la feuille si elle est en vigueur.

• A la fin des procédures Nouveau et Proposer, ajouter l'instruction suivante :

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

Ce code est livré par une macro automatique. Inversement au cas précédent donc, la méthode Protect de l'objet ActiveSheet permet de réactiver la protection de la feuille, à l'issue du traitement VBA.



Désormais, si vous cliquez sur l'un ou l'autre bouton de la feuille Jeu, certes la protection est d'abord ôtée, mais elle est réactivée à la fin du traitement. Si bien que si vous tentez de modifier le contenu d'une cellule autre que la cellule E11, un message d'alerte apparaît vous interdisant toute modification.

Contrôle de la saisie Utilisateur
Il est temps désormais de développer le jeu du pendu à proprement parlé. Pour l'instant le bouton Proposer n'a d'autre effet que de proposer le mot suivant à trouver. Or, cette action ne doit intervenir que dans deux cas. Soit l'expression a été intégralement découverte, soit le joueur a commis quatre erreurs. Ce traitement doit donc être branché dans une instruction conditionnelle If, destinée à vérifier ces deux critères.

• Dans la procédure Proposer, encadrer l'incrémentation de la cellule B17, de l'instruction conditionnelle suivante :

If (UCase(Range('b7').Value) = expression Or Range('F18').Value >= 4) Then
Range('b17').Value = Range('b17').Value + 1
End If

Pour que l'incrémentation se fasse et donc, que le mot suivant soit proposé, deux conditions doivent d'abord être remplies de façon non exclusive, ce qui signifie, soit l'une, soit l'autre. Le mot reconstruit (Range('b7').Value) doit être strictement identique à l'expression à trouver. Ou bien, le joueur doit avoir commis quatre erreurs, invalidant le mot. C'est la cellule F18 en effet, qui servira à enregistrer les fautes.

Il est à noter que la variable expression n'existe pas encore à ce stade. Elle est pourtant utilisée dans le premier critère de l'instruction If. Nous devons, sans plus attendre, déclarer toutes les variables nécessaires aux traitements VBA, notamment pour le contrôle de la saisie du joueur.

• Au tout début de la procédure Proposer, ajouter les déclarations suivantes :

Dim nbCar As Byte: Dim i As Byte
Dim nbLettres As Byte: Dim j As Byte
Dim trouve As Boolean
Dim Lettres As String: Dim la_lettre As String
Dim expression As String
Dim debut: Dim fin

nbCar et nbLettres seront utilisées pour stocker le nombre de caractères compris respectivement, dans le mot à rechercher et dans la chaîne mémorisant toutes les lettres déjà suggérées. C'est la raison pour laquelle nous les déclarons comme des entiers courts. Les variables i et j serviront de compteurs respectifs pour parcourir l'ensemble des caractères sur la longueur stockée dans les variables précédentes.

Le booléen trouve permettra de savoir si le caractère proposé par le joueur, a été trouvé ou non, dans les boucles de traitements. C'est en fonction de sa valeur que nous remplacerons le tiret par la lettre dans l'expression à reconstruire, ou au contraire, que nous le conserverons.

Ensuite nous avons besoin de variables String pour mémoriser les chaînes de caractères à comparer. Ainsi Lettres stockera la chaîne des lettres déjà proposées en B15. Comme son nom l'indique, la_lettre permettra de stocker le caractère proposé par le joueur en E11. Et enfin expression stockera le mot à retrouver, importé en J7. Et souvenez-vous, c'est lui qui est exploité dans la condition de l'instruction If, pour permettre ou non de proposer le mot suivant.

Pour terminer, debut et fin sont des variables non typées que nous utiliserons pour temporiser l'exécution du code, avant de passer au mot suivant. Ainsi le joueur pourra constater son succès, consulter le mot complet, ainsi que son score.

Maintenant que la variable expression est déclarée, si vous cliquez sur le bouton Proposer de la feuille Jeu, comme le critère n'est pas vérifié, l'expression suivante n'est pas proposée.

Avant de poursuivre avec l'affectation des variables, nous devons poser deux critères qui, s'ils sont vérifiés mettront fin à l'exécution de la procédure. En effet, si les 10 mots ont été proposés (inscrits en B17), alors la partie est terminée. Si l'utilisateur n'a suggéré aucune lettre (Cellule E11 vide), alors il doit recommencer.

• A la suite du code, après l'instruction ActiveSheet.Unprotect, ajouter les lignes suivantes :

If (Range('B17').Value >= 10) Then
MsgBox ('La partie est finie ! Cliquez sur le bouton Nouveau pour en commencer une nouvelle')
Exit Sub
End If
If (Range('E11').Value = '') Then
MsgBox ('Veuillez saisir une lettre !')
Exit Sub
End If

Après chaque message informant le joueur (MsgBox), l'instruction Exit Sub permet de mettre fin à l'exécution du code et donc, d'ignorer toutes les lignes qui suivent.

• Enregistrer les modifications et basculer sur la feuille Jeu,
• Vider le contenu de la cellule E11 (Suppr),
• Puis, cliquer sur le bouton Proposer,

Comme vous le constatez, le message apparaît bien à l'écran et tant que le joueur ne place pas un caractère dans la cellule, le code de la procédure Proposer est annihilé.

• Revenir dans l'éditeur de code VBA,
• Puis, ajouter les affectations suivantes, après les instructions If :

Lettres = UCase(Range('B15').Value)
la_lettre = UCase(Range('E11').Value)
expression = UCase(Range('J7').Value)

Nous mémorisons donc chacune des chaînes dans les variables texte prévues à cet effet. Pour ignorer les problèmes de casse lors de la comparaison, nous utilisons la fonction VBA Excel UCase. Cette dernière transcrit en majuscules, la chaîne qui lui est passée en paramètre.

La suite du contrôle de la saisie du joueur ne doit se faire que dans la mesure où la lettre proposée, ne figure pas déjà dans la liste des lettres qui ont été précédemment suggérées. C'est donc une fois de plus l'instruction conditionnelle If qui doit encadrer la suite du code. Elle doit vérifier que la lettre proposée est un nouveau caractère. La fonction VBA InStr renvoie la position d'une occurrence recherchée dans une chaîne de caractères, en l'occurrence la cellule B15 dont le contenu est mémorisé dans la variable Lettres. Si la valeur 0 est retournée, cela signifie que l'occurrence recherchée n'a pas été trouvée. Dans ce cas, le traitement par le code VBA peut se poursuivre.

• Après les affectations, ajouter les bornes de l'instruction If, comme suit :

If (InStr(1, Lettres, la_lettre, vbTextCompare) = 0) Then

End If

Nous recherchons le caractère proposé (la_lettre) dans la chaîne de toutes les lettres déjà suggérées (Lettres), en partant du début de la chaîne (1) avec une méthode de comparaison textuelle (vbTextCompare).

Si le critère est vérifié (=0), puisque la lettre est bien une nouvelle proposition, alors elle doit d'abord être ajoutée à la liste des lettres proposées, en B15.

• En conséquence, à la suite du code, dans le If, ajouter les instructions suivantes :

Range('B15').Value = Lettres & la_lettre
Lettres = Range('B15').Value

Par concaténation, nous commençons par ajouter la nouvelle lettre à la suite des autres, dans la cellule B15 (Range('B15').Value = Lettres & la_lettre). Du coup, pour le traitement à venir de la reconstruction de l'expression secrète à reconstruire, nous réaffectons la variable Lettres pour qu'elle considère bien le nouveau caractère (Lettres = Range('B15').Value).

Avant de commencer la reconstruction du terme en cours de découverte en B7, nous devons vérifier si cette lettre figure bien dans l'expression à trouver, ou non. L'objectif est de pouvoir incrémenter le compteur des frappes correctes ou fausses, selon le cas, respectivement en cellules E18 et F18. Pour ce faire :

• Ajouter les tests suivants, à la suite du code, toujours dans l'instruction If :

If (InStr(1, expression, la_lettre, vbTextCompare) <> 0) Then
Range('E18').Value = Range('E18').Value + 1
Else
Range('F18').Value = Range('F18').Value + 1
End If

Dans le critère de l'instruction If, la fonction VBA InStr est de nouveau à l'honneur. Cette fois, elle est chargée de vérifier, si la lettre proposée figure bien dans l'expression à dévoiler (<>0). Dans ce cas, le compteur des coups corrects est incrémenté en E18. Dans le cas contraire (Else), le compteur des fautes est incrémenté en F18.

Dans le cas où cette nouvelle lettre ne figure pas dans les caractères de l'expression recherchée (Else), maintenant que le compteur des fautes est incrémenté, rien d'autre ne reste à faire. En revanche, lorsque le caractère est trouvé, il s'agit de reconstruire le terme à dévoiler en B7 pour y inclure la nouvelle lettre. Nous devons commencer par supprimer son contenu.

• Dans la branche If de l'instruction conditionnelle, ajouter le code suivant :

Range('B7').Value= ''
nbCar = Len(expression)
nbLettres = Len(Lettres)

For i = 1 To nbCar
trouve = False
For j = 1 To nbLettres
If (Mid(Lettres, j, 1) = Mid(expression, i, 1)) Then
trouve = True
Range('b7').Value = Range('b7').Value & Mid(Lettres, j, 1)
End If
Next j
If trouve = False Then Range('b7').Value = Range('b7').Value & '-'
Next i

Nous vidons le contenu de la cellule qui reconstruit le mot à retrouver (Range('B7').Value = ''). Dans les variables nbCar et nbLettres nous mémorisons respectivement, le nombre de caractères du mot à trouver et des lettres déjà utilisées. Par le biais d'une double boucle, l'objectif est de pouvoir comparer chacune des lettres de l'expression (For i = 1 To nbCar) à trouver avec chacune des lettres proposées (For j = 1 To nbLettres). A chaque fois que le résultat est concordant, la lettre doit être replacée en B7 à la même position que dans l'expression d'origine. Comme les deux boucles sont imbriquées, pour chaque lettre de l'expression, la boucle repasse en revue toutes les lettres proposées pour la comparaison.

Nous exploitons la fonction VBA Mid, que nous avions présentée dans la formation pour importer des données en VBA Excel. Elle permet de prélever une partie d'une chaîne passée en premier paramètre, depuis une position passée en deuxième paramètre, sur une certaine longueur, passée en troisième paramètre. Comme cette longueur vaut 1, nous comparons les lettres entre elles.

Si le résultat finit par être concordant, nous affectons la variable booléenne à True, et nous remplaçons le caractère à sa place dans l'expression : Range('b7').Value = Range('b7').Value & Mid(Lettres, j, 1).

En dehors de la boucle des lettres proposées, si la variable booléenne vaut toujours False, cela signifie qu'aucune concordance n'a été trouvée pour la lettre de l'expression correcte. Dans ce cas, nous replaçons le tiret en lieu et place : If trouve = False Then Range('b7').Value = Range('b7').Value & '-'.

Le contrôle de la saisie est terminé. Lorsque la lettre est trouvée, elle est ajoutée en B7 dans l'expression recomposée au fur et à mesure. Mais le code lui, n'est pas complet. Dans la branche de l'instruction If qui permet de proposer le mot suivant, les compteurs des frappes correctes et incorrectes doivent être réinitialisés. La note doit être recalculée, en fonction des erreurs commises. Et les cellules du jeu, comme la lettre, l'expression à recomposer où les lettres proposées, doivent être vidées.

• Avant l'incrémentation de la cellule B17, ajouter les instructions suivantes, dans le If :

debut = Timer
Do While Timer < debut + 2
Loop

Range('I18').Value = Range('I18').Value + (1 - Range('F18').Value * 0.25)
Range('F18').Value = '': Range('E18').Value = ''
Range('B7').Value = '': Range('B15').Value = '': Range('E11').Value = ''

Tout d'abord, nous initialisons la variable debut sur le temps qu'il est, grâce à la fonction VBA Timer. Puis, comme nous l'avions vu dans la formation VBA Access pour archiver les données et construire un diaporama, nous réalisons une boucle vide pendant 2 secondes (Do While Timer < debut + 2). L'objectif est de laisser le temps au joueur de consulter les résultats, avant la nouvelle proposition. Nous n'utilisons pas l'instruction DoEvents cette fois. Cette dernière permet de rendre la main pendant le processus. Mais ici, nous ne souhaitons pas que le joueur ait l'opportunité de saisir pendant le laps de temps.

Ensuite, nous ajoutons à la note déjà consolidée en F18, les points obtenus pour ce mot. Nous comptons un quart de point en moins par faute commise (1 - Range('F18').Value * 0.25). Puis nous vidons toutes les cellules énoncées précédemment.

Bien sûr, une fois que le compteur est incrémenté en B17 afin de proposer un nouveau mot, par le biais des formules, les tirets doivent être reconstruits sur la longueur de l'expression. Il suffit d'appeler la procédure construction.

• Après l'incrémentation de B17 et avant le End If, ajouter l'appel de la procédure, comme suit :

Construction

Il reste à indiquer au joueur que la partie est terminée, lorsque le compteur de mots restants a atteint la valeur 0, et ce avant de réactiver la protection de la feuille.

• Après le End If et avant la protection de la feuille, ajouter les instructions suivantes :

If (Range('H18').Value = 0) Then
MsgBox ('La partie est terminée, vous avez obtenu la note de ' & Range('I18').Value)
End If

• Enregistrer les modifications et basculer sur la feuille de jeu,
• Débuter une nouvelle partie et tester l'application,

Les points, les fautes ainsi que les coups et les mots sont correctement comptabilisés et enchainés. Le code complet du bouton Proposer est le suivant :

Sub Proposer()
Dim nbCar As Byte: Dim i As Byte
Dim nbLettres As Byte: Dim j As Byte
Dim trouve As Boolean
Dim Lettres As String: Dim la_lettre As String
Dim expression As String
Dim debut: Dim fin

ActiveSheet.Unprotect

If (Range('B17').Value >= 10) Then
MsgBox ('La partie est finie ! Cliquez sur le bouton Nouveau pour en commencer une nouvelle')
Exit Sub
End If
If (Range('E11').Value = '') Then
MsgBox ('Veuillez saisir une lettre !')
Exit Sub
End If

Lettres = UCase(Range('B15').Value)
la_lettre = UCase(Range('E11').Value)
expression = UCase(Range('J7').Value)

If (InStr(1, Lettres, la_lettre, vbTextCompare) = 0) Then
Range('B15').Value = Lettres & la_lettre
Lettres = Range('B15').Value

If (InStr(1, expression, la_lettre, vbTextCompare) <> 0) Then
Range('E18').Value = Range('E18').Value + 1

Range('B7').Value = ''

nbCar = Len(expression)
nbLettres = Len(Lettres)

For i = 1 To nbCar
trouve = False
For j = 1 To nbLettres
If (Mid(Lettres, j, 1) = Mid(expression, i, 1)) Then
trouve = True
Range('b7').Value = Range('b7').Value & Mid(Lettres, j, 1)
End If
Next j
If trouve = False Then Range('b7').Value = Range('b7').Value & '-'
Next i

Else
Range('F18').Value = Range('F18').Value + 1
End If

End If

If (UCase(Range('b7').Value) = expression Or Range('F18').Value >= 4) Then
debut = Timer
Do While Timer < debut + 2
Loop

Range('I18').Value = Range('I18').Value + (1 - Range('F18').Value * 0.25)
Range('F18').Value = '': Range('E18').Value = ''
Range('B7').Value = '': Range('B15').Value = '': Range('E11').Value = ''
Range('b17').Value = Range('b17').Value + 1

construction
End If

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Dessiner le pendu en fonction des résultats
Le jeu est certes fonctionnel mais il reste certains réglages à effectuer. Ainsi, des cellules doivent êtres masquées comme celle du mot correct et celle du compteur en B17. Mais le pendu doit visuellement rendre compte des erreurs commises, comme dans le vrai jeu. Il est pré-dessiné. Il reste à faire réagir ses couleurs dynamiquement, en fonction du compteur de fautes en F18. Commençons par masquer les informations confidentielles. Pour ce faire, la protection de la feuille, activée par VBA, doit être temporairement désactivée.

• Cliquer sur l'onglet Révision en haut de la fenêtre Excel, pour activer son ruban,
• Dans la section Modifications, cliquer sur le bouton Oter la protection de la feuille,
• Cliquer avec le bouton droit sur l'étiquette de colonne J,
• Dans le menu contextuel, choisir Masquer,
• Cliquer sur la cellule B17 du compteur pour la sélectionner,
• Lui appliquer une couleur de police identique à la couleur du fond de la cellule,

Nous réactiverons la protection de la feuille seulement lorsque nous aurons réglé la mise en forme conditionnelle du pendu. La potence doit apparaître en rouge lorsque la première faute est commise.

• Avec la touche CTRL, sélectionner ensemble les plages et cellules suivantes : M13:S13, S1:S12, N1:R1 et O2,
• Cliquer ensuite sur le bouton Mise en forme conditionnelle du ruban Accueil,
• Dans la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, sélectionner la dernière rubrique : Utiliser une formule,
• Puis cliquer dans la zone de saisie de la formule pour l'activer,
• Ensuite, cliquer sur la cellule F18 de la feuille pour construire le critère du format dynamique,
• Taper le symbole supérieur suivi du symbole égal suivi du chiffre 1, soit >=1,

Pour l'instant nous avons simplement construit l'expression du critère à vérifier sur les cellules de la potence, soit : =$F$18>=1. Lorsqu'au moins une erreur a été commise en effet, la potence doit apparaître en rouge. Pour ce faire :

• Cliquer sur le bouton Format en bas de la boîte de dialogue,
• Dans la boîte suivante, choisir une couleur de remplissage rouge et valider,

Comme l'illustre la capture ci-dessus, lorsque le critère sera vérifié dans les cellules sélectionnées, elles seront remplies de rouge.

• Valider la boîte de dialogue du format dynamique en cliquant sur Ok,

Il reste à faire de même pour les parties du pendu mais pour un nombre d'erreurs plus important.

• Sélectionner les jambes et le bassin, soit les plages et cellules suivantes : N8:N11, O8 et P8:P11,
• Appliquer le même format conditionnel que précédemment, mais pour le critère : =$F$18>=2,
• Sélectionner ensuite le buste et les bras, soit les plages de cellules suivantes : O5:07, M6:N6 et P6:Q6,
• Appliquer le même format dynamique mais pour le critère : =$F$18>=3,
• Enfin, sélectionner la tête, soit la plage de cellules N3:P4,
• Appliquer la même mise en forme conditionnelle, mais pour le critère : =$F$18>=4,
• Puis, dans le ruban Révision cliquer sur le bouton Protéger la feuille,
• Valider la boîte de dialogue qui suit en cliquant sur le bouton Ok,

Il ne reste plus qu'à tester et à masquer la feuille Mots pour que les bonnes réponses ne soient pas accessibles par le joueur.



Les erreurs commises apparaissent désormais dynamiquement, grâce à la mise en forme conditionnelle. Le message est plus clair pour le joueur qui reçoit la pression. Cette version Bêta du pendu est terminée. Il serait intéressant de la faire évoluer en proposant des thèmes multiples, par le biais d'une liste déroulante par exemple.