Générer des nombres aléatoires en VBA Excel
Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :
Produire des nombres aléatoires en VBA Excel
De nombreuses applications justifient la génération de nombres aléatoires. On peut les exploiter pour créer des codes ou des références uniques. Ils sont utiles pour trier des tableaux de données au hasard. Ainsi dans la création de jeux avec Excel, ils permettent des extractions différentes à chaque nouvelle partie. Associés à la fonction VBA Timer pour temporiser l'exécution, ils peuvent générer des animations de couleurs par exemple.
C'est la fonction Rnd() en Visual Basic qui permet par défaut de générer un nombre réel compris entre 0 et 1. Au cours de cette formation, nous proposons quelques démonstrations par étape, afin de dévoiler son intérêt.
Présentation du classeur Excel
Comme toujours, nous partons d'un classeur proposant déjà la structure et des données à manipuler, afin de concentrer nos travaux sur la génération aléatoire par le code VBA.
- Télécharger le classeur Excel combinaisons-aleatoires.xlsm en cliquant sur son lien,
- L'ouvrir dans Excel et cliquer sur le bouton Activer le contenu si nécessaire,
Produire un nombre aléatoire par défaut
Dans un premier temps, nous souhaitons présenter la fonction VBA Rnd() et constater le retour qu'elle produit, dans son utilisation la plus simple.
- Cliquer avec le bouton droit de la souris sur le premier bouton Générer, en G3,
- Dans le menu contextuel, choisir Affecter une macro,
- Dans la boîte de dialogue qui suit, la nommer : nb_alea, puis cliquer sur le bouton Nouvelle,
Nous basculons ainsi dans l'éditeur de code Visual Basic Excel, entre les bornes de la procédure nb_alea(). Notez l'emploi du caractère Underscore (_) dans le nom de la procédure, pour simuler un espace. Les espaces ne sont en effet pas autorisés dans les noms des objets et fonctions de programmation. Le code VBA saisi entre les bornes de cette procédure se déclenchera au clic sur le bouton Générer que nous avons ainsi affecté.
- Entre les bornes de la procédure, saisir la ligne de code suivante :
Avec l'objet VBA Range, bien connu désormais, nous désignons la cellule passée en paramètre, H3 ici, pour y écrire un nombre aléatoire. C'est pourquoi nous exploitons sa propriété Value qui permet d'accéder à son contenu. Et nous affectons ce contenu à la valeur retournée par la fonction Rnd(). Celle-ci ne requiert aucun paramètre.
- Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
- Cliquer dans n'importe quelle cellule pour désactiver la sélection du bouton,
- Puis cliquer sur ce premier bouton Générer afin de déclencher la ligne de code,
Une valeur numérique, avec un grand nombre de décimales, s'inscrit en effet en cellule H3. Si nous cliquons à répétition sur le bouton Générer, ce nombre varie mais ne dépasse jamais 1. La fonction VBA Rnd() produit en effet une valeur aléatoire réelle, comprise entre 0 et 1 par défaut. Ainsi pour choisir de générer un nombre au hasard entre 0 et 10, il suffit de multiplier le résultat retourné par la fonction Rnd().
- Basculer dans l'éditeur de code VBA Excel,
- Modifier la ligne de code, comme suit :
- Enregistrer les modifications et basculer sur la feuille Excel,
- Puis, cliquer de nouveau sur le bouton Générer à plusieurs reprises,
Générer une valeur aléatoire entière
C'est l'objectif du bouton Générer qui suit, situé en cellule G7. Le principe consiste à ne conserver que la partie entière du nombre retourné par la fonction VBA Rnd. De nombreuses fonctions de conversions et d'arrondis sont proposées en Visual Basic. Parmi elles, il y a la fonction Int(), très simple d'emploi. Elle ne requiert qu'un seul paramètre et retourne la partie entière du nombre qui lui est passé :
Nb_entier = Int(nb_reel)
Pour 9,483 par exemple, la fonction Int, retournera le chiffre 9, soit la partie entière du nombre décimal.
- Cliquer droit sur le bouton Générer situé en G7,
- Dans le menu contextuel, choisir Affecter une macro,
- Dans la boîte de dialogue qui suit, la nommer : nb_alea_entier,
- Puis, cliquer sur le bouton Nouvelle afin de créer sa procédure de code attachée,
- Entre les bornes de la procédure ainsi générée, coller le code précédent,
- Dans l'objet Range, modifier la cellule de réception H3 par H7,
- Puis, imbriquer le calcul du nombre aléatoire dans la fonction Int, ce qui donne :
Range('H7').Value = Int(10 * Rnd())
End Sub
- Enregistrer les modifications et revenir sur la feuille Excel,
- Cliquer à plusieurs reprises sur ce deuxième bouton Générer situé en G7,
Un nombre est généré aléatoirement à chaque clic sur le bouton en effet. Ce nombre est bien un entier, compris entre 0 et 10 d'ailleurs. Mais la valeur 10 n'est jamais atteinte puisque la fonction VBA Int en extrait la partie entière. Ainsi, pour 9,999, le retour est 9.
Nombre aléatoire entre deux bornes
Dans le souci de maîtriser quelque peu le hasard, nous souhaitons désormais être en mesure de produire un nombre entier aléatoire et borné, c'est-à -dire compris entre une valeur minimum et une valeur maximum. L'exemple précédent nous donne quasiment la réponse, puisque nous y générons un nombre aléatoire compris entre 0 et 10. Mais si nous souhaitions produire par le hasard, un nombre compris entre 5 et 10, il ne suffit pas d'ajouter 5 au résultat généré. D'ailleurs l'aide en ligne de VBA nous enseigne la syntaxe suivante, afin de produire cette valeur :
Nb_borne = Int((borne_sup - borne_inf + 1) * Rnd + borne_inf)
Dans notre cas donc : Int(6 * Rnd + 5), doit générer une valeur entière comprise entre 5 et 10. Notez l'absence des parenthèses dans la syntaxe de la fonction Rnd. VBA est un langage permissif, comme Rnd ne requiert pas de paramètre, ses parenthèses sont facultatives. Ce résultat doit être produit en cellule H11, par le troisième bouton Générer situé en G11.
- Sur la feuille Excel, cliquer droit sur le troisième bouton Générer,
- Dans le menu contextuel, choisir Affecter une macro,
- Dans la boîte de dialogue qui suit, la nommer nb_alea_bornes,
- Cliquer sur le bouton Nouvelle afin de créer la procédure de code attachée,
- Entre les bornes de la procédure, saisir la ligne suivante :
- Enregistrer les modifications et basculer sur la feuille Excel,
- Cliquer dans une cellule pour désélectionner le bouton,
- Puis, cliquer plusieurs fois consécutivement sur ce bouton Générer,
A force de persévérance, nous arrivons à générer tous les nombres entiers compris entre ces deux bornes, y compris le plus petit (5) et le plus grand (10).
Créer des combinaisons aléatoires de lettres
Voici une mise en pratique intéressante de la fonction VBA Rnd. Nous souhaitons générer des codes aléatoires bâtis sur une séquence de quatre lettres consécutives de l'alphabet. Comme la fonction Rnd ne sait produire que des nombres aléatoires, un petit tableau de correspondance est préconçu entre les colonnes L et M. Pour chaque valeur jusqu'à 26, la seconde colonne du tableau fournit son équivalent en lettre de l'alphabet. Fort logiquement, la lettre A est associée à la valeur 1 tandis que la lettre Z est associée à la valeur 26.
Il s'agit donc dans un premier temps de générer un nombre aléatoire entier compris entre 1 et 26. Puis, pour chacun de ces nombres, il faut extraire la lettre correspondante dans le tableau. C'est la fonction Excel RechercheV qui permet de récupérer la valeur d'un tableau en fonction d'une donnée cherchée. Mais ici, nous souhaitons enclencher cette extraction par le code VBA. Or, ce dernier permet d'exploiter les fonctions Excel des feuilles de calcul, mais avec leur nom anglais. Le nom anglais de la fonction RechercheV est VLookup. Elle requiert strictement les mêmes paramètres que son homologue sur la feuille de calcul. Une fois les quatre lettres rapatriées, elles devront être concaténées , pour produire le code textuel aléatoire, en cellule de réception I15.
Nous allons procéder par étape en commençant par déclarer les variables pour stocker les valeurs.
- Cliquer droit sur le quatrième bouton Générer placé en G15,
- Dans le menu contextuel, choisir Affecter une macro,
- La nommer : nb_alea_combi et cliquer sur le bouton Nouvelle,
- Entre les bornes de la procédure ainsi créée, déclarer les variables suivantes :
Dim compteur As Byte
Comme nous avons quatre nombres à générer et quatre lettres correspondantes à extraire, nous déclarons des tableaux de variables. Inscrits dans un traitement récurrent, ils permettront d'éviter de recoder strictement les mêmes instructions. Nous définissons leur taille à 3 pour pouvoir remplir leurs quatre rangées en partant de zéro. La variable compteur servira à faire tourner une boucle For entre ses deux extrémités (0 et 4). Avant d'initialiser la boucle pour générer les nombres aléatoires, nous devons commencer par vider les cellules H15 et I15 de leur potentiel précédent contenu.
- A la suite du code, ajouter les instructions suivantes :
For compteur = 0 To 3
nb_alea(compteur) = Int(26 * Rnd + 1)
Next compteur
Grâce à l'objet Range et sa propriété Value, nous commençons par vider le contenu des cellules désignées en paramètre. Puis, nous créons une boucle d'instruction, permettant un traitement récurrent sur quatre passages, en faisant varier la variable compteur de 0 à 3, soit les extrémités des tableaux de variables. Et c'est ainsi que nous affectons à chaque rangée du tableau nb_alea, un nombre aléatoire compris entre 1 et 26.
Nous allons concaténer ces valeurs générées au hasard en cellule H15. Nous réaliserons ainsi un petit point de contrôle, avant d'extraire les lettres correspondantes issues du tableau de recherche.
- A la suite du code dans la boucle, ajouter les instructions suivantes :
If (compteur < 3) Then
Range('H15').Value = Range('H15').Value & ','
L'instruction conditionnelle If, permet d'ajouter une virgule entre chaque nombre aléatoire, sauf s'il s'agit du dernier.
- Enregistrer les modifications et basculer sur la feuille Excel,
- Cliquer à plusieurs reprises sur le quatrième bouton Générer,
A chaque clic, une séquence de quatre valeurs aléatoires, séparées par des virgules, est en effet générée et inscrite en cellule H15. Pour chacune d'entre elles, il s'agit désormais d'extraire la lettre correspondante, grâce à la fonction VBA VLookup, dont la syntaxe est la suivante :
VLookup(valeur_cherchee, tableau_de_recherche, num_col_retour, False)
La valeur cherchée n'est autre que le nombre aléatoire généré dans la boucle et stocké dans le tableau de variables. Le tableau dans lequel cette valeur doit être cherchée n'est autre que la plage de cellules L3:M28 qui peut être désignée grâce à l'objet Range. Le numéro de colonne pour l'information à extraire en retour est la deuxième, celle des lettres. Donc nous indiquerons le chiffre 2. Et enfin nous terminerons par la valeur booléenne False pour indiquer à la fonction d'extraction de réaliser une correspondance exacte.
C'est la propriété WorksheetFunction de l'objet Application en VBA Excel qui permet d'accéder à liste de toutes les fonctions de calculs.
- Dans la boucle, sous l'affectation du tableau de valeurs nb_alea, ajouter l'instruction suivante :
A chaque rangée du tableau de variables txt_alea, nous affectons le résultat de l'extraction de sa valeur numérique correspondante (nb_alea(compteur)), issue du tableau de recherche (Range('L3:M28')). Il s'agit alors de concaténer ces valeurs textuelles extraites afin d'inscrire la combinaison résultante en I15 cette fois. Pour ce faire :
- Sous la ligne précédente, avant le If, ajouter le code de concaténation suivant :
Il ne reste plus qu'Ã tester.
- Enregistrer les modifications et basculer sur la feuille Excel,
- Cliquer à plusieurs reprises sur le quatrième bouton Générer,
Une séquence aléatoire de quatre caractères est effectivement générée. Chaque lettre ainsi retournée correspond bien à sa position dans le tableau de recherche. Dans notre cas par exemple, la lettre g est bien la septième tandis que la lettre q est bien la dix-septième. Le code complet de la procédure VBA est le suivant :
Sub nb_alea_combi()
Dim nb_alea(3) As Byte: Dim txt_alea(3) As String
Dim compteur As Byte
Range('H15').Value = '': Range('I15').Value = ''
For compteur = 0 To 3
nb_alea(compteur) = Int(26 * Rnd + 1)
txt_alea(compteur) = Application.WorksheetFunction.VLookup( nb_alea(compteur), Range('L3:M28'), 2, False)
Range('H15').Value = Range('H15').Value & nb_alea(compteur)
Range('I15').Value = Range('I15').Value & txt_alea(compteur)
If (compteur < 3) Then Range('H15').Value = Range('H15').Value & ','
Next compteur
End Sub
Créer des couleurs aléatoires
Les couleurs de cellules peuvent être générées de différentes façons en VBA Excel. L'une d'entre elles consiste à exploiter la fonction RGB() qui permet de produire une couleur en fonction de ses trois composantes : Rouge, Vert et Bleu. Chacune de ses composantes se définit selon une valeur numérique comprise entre 0 et 255, soit un Byte. A l'indice 0, la composante est absente tandis qu'à 255, sa densité est à son maximum. Dans un premier temps, nous souhaitons simplement affecter une couleur de remplissage, à la sélection active sur la feuille, au clic sur le bouton Fond Aléa.
- Cliquer droit sur le bouton Fond Aléa,
- Dans le menu contextuel, choisir Affecter une macro,
- Dans la boîte de dialogue, la nommer fond_aleatoire et cliquer sur le bouton Nouvelle,
- Entre les bornes de la procédure, saisir l'instruction suivante :
Comme nous l'avions appris dans la formation pour débuter la programmation en VBA Excel, l'objet Selection désigne la plage de cellules actives sur la feuille Excel. La propriété dérivée Color de sa propriété Interior permet de définir la couleur de fond de cet objet, soit de la plage de cellules sélectionnée. Nous affectons cette couleur à l'aide de la fonction VBA RGB pour laquelle nous faisons varier chacune de ses composantes, de façon aléatoire, entre 0 et 255.
- Enregistrer les modifications et basculer sur la feuille Excel,
- Sélectionner la ligne de titre du tableau sur la gauche, soit la plage de cellules B2:D2,
- Puis, cliquer sur le bouton Fond Aléa,
A chaque clic, une couleur de fond aléatoire est attribuée à la ligne de titre sélectionnée. Nous souhaitons désormais exploiter cette astuce pour générer une petite animation de couleurs aléatoires, sur le petit tableau situé sous le bouton Animer. Nous devons utiliser la fonction Timer que nous avions exploitée en VBA Access pour générer un diaporama. Cette fonction permet de temporiser le code selon des intervalles de temps et ainsi de créer des animations. Pour parcourir chacune des cellules du tableau, l'une après l'autre et incrément de temps après incrément de temps, nous devons cette fois exploiter l'objet VBA Cells. Ce dernier permet de pointer sur une cellule de la feuille en fonction de ses indices de ligne et de colonne : Cells(ligne, colonne). En outre, il permettra de faire varier facilement ces indices en l'imbriquant dans une boucle de traitement.
- Cliquer droit sur le bouton Animer et choisir Affecter une macro dans le menu contextuel,
- La nommer : animation_alea et cliquer sur le bouton Nouvelle,
- Ajouter les déclarations suivantes, entre les bornes de la procédure :
Dim rouge As Byte: Dim vert As Byte: Dim bleu As Byte
Dim debut: Dim compteur As Byte
compteur = 0
Les variables ligne et colonne serviront donc à faire varier les indices de ligne et de colonne de l'objet Cells, à l'intérieur d'une double boucle. Les trois variables rouge, vert et bleu permettront, grâce à la fonction Rnd, de faire varier les composantes de couleur de la cellule en cours de lecture, grâce à la fonction VBA RGB. La variable debut, non typée, sera utilisée pour initialiser le compteur de temps avec la fonction Timer. La variable compteur que nous affectons à zéro dans la foulée, permettra de diriger une boucle de traitements récurrents, pour poursuivre les animations. Pour cela :
- A la suite du code, ajouter les bornes de la boucle, comme suit :
compteur = compteur + 1
Loop Until compteur = 50
Arbitrairement, nous initialisons un traitement récurrent sur 50 passages, sans oublier d'incrémenter la variable (compteur = compteur + 1). C'est à l'intérieur de cette boucle que nous devons intervenir sur chacune des cellules et temporiser avec la fonction Timer, pour donner l'illusion d'une animation.
A l'intérieur de ce traitement récurrent, nous devons parcourir chacune des cellules en faisant varier, tous les indices de colonnes, pour chaque indice de ligne. Les cellules sont situées entre les lignes 20 à 25 et entre les colonnes 6 à 9. Puisque nous connaissons les bornes, la boucle For est la plus adaptée.
- Dans la boucle Do, ajouter l'imbrication suivante :
For colonne = 6 To 9
Next colonne
Next ligne
Pour chaque cellule ainsi pointée dans la double boucle, nous devons faire varier les trois composantes de sa couleur de remplissage. Pour ce faire :
- Ajouter les instructions suivantes, dans la double boucle For :
Cells(ligne, colonne).Interior.Color = RGB(rouge, vert, bleu)
La fonction Rnd permet de générer une composante de couleur comprise entre 0 et 255. Chacune d'elle est mémorisée dans sa variable. Nous désignons ensuite la cellule par son indice de ligne et de colonne en cours, dans la double boucle : Cells(ligne,colonne). L'objet Cells est un objet de type Range, donc il propose les mêmes propriétés que précédemment pour affecter la couleur de fond. Nous l'affectons ainsi à une couleur aléatoire en lui passant les trois composantes stockées dans leur variable respective.
A ce stade, si nous exécutons le code en cliquant sur le bouton, nous apercevons quelques variations de couleurs, selon la vitesse de traitement par le processeur. S'il est très rapide, nous ne voyons que le résultat final et aucune variation intermédiaire. Il est donc essentiel, après avoir défini les premières couleurs de toutes les cellules, de temporiser. C'est ici qu'intervient la fonction Timer.
- En dehors de la double boucle, après le Next ligne, mais avant l'incrémentation du compteur de la boucle Do Loop, ajouter les instructions suivantes :
Do While Timer < debut + 0.1
Loop
Grâce à la fonction Timer, nous prélevons le temps qu'il est et le stockons dans la variable debut. Nous utilisons cette variable comme test afin de boucler dans le vide, sur un dixième de seconde (Do While Timer < debut + 0.1). Pendant ce laps de temps défini, cette astuce empêche le code de poursuivre son exécution et donc de passer à l'incrément suivant de la boucle Do Loop qui contient toutes les autres boucles.
- Enregistrer les modifications et basculer sur la feuille Excel,
- Cliquer sur une cellule vide pour désélectionner le bouton,
- Puis, cliquer sur le bouton Animer,
Tout fonctionne parfaitement. Sur 50 passages, tous les dixièmes de secondes, les cellules changent de couleurs aléatoirement donnant l'illusion d'une animation ou d'un clignotement. Le code complet, pour produire ces effets visuels est le suivant :
Sub animation_alea()
Dim ligne As Byte: Dim colonne As Byte
Dim rouge As Byte: Dim vert As Byte: Dim bleu As Byte
Dim debut: Dim compteur As Byte
compteur = 0
Do
For ligne = 20 To 25
For colonne = 6 To 9
rouge = Int(255 * Rnd): vert = Int(255 * Rnd): bleu = Int(255 * Rnd)
Cells(ligne, colonne).Interior.Color = RGB(rouge, vert, bleu)
Next colonne
Next ligne
debut = Timer
Do While Timer < debut + 0.1
Loop
compteur = compteur + 1
Loop Until compteur = 50
End Sub
Sur Facebook
Sur Youtube
Les livres
Contact
Mentions légales
