Outil VBA Excel de réservations planifiées

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


Sujets que vous pourriez aussi aimer :


Outil VBA de réservation

Dans une précédente formation Excel, nous avons construit l'outil capable de générer dynamiquement un calendrier hebdomadaire, au choix d'un numéro de semaine. Grâce au code VBA, nous devons transformer ce calendrier en planning de réservation.



L'idée consiste à importer dynamiquement dans le planning, les réservations archivées, en fonction de la semaine et de la salle demandées. Mais il s'agit de même d'enregistrer de nouvelles réservations, par inscription directe dans les cases du calendrier.

Source et présentation du concept
Les travaux précédents sur la construction automatique des calendriers hebdomadaires sont nécessaires. Nous proposons donc de réceptionner le classeur les hébergeant.

• Télécharger le classeur reserver-salles-reunion.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

La feuille Planning est active par défaut. Trois listes déroulantes sont proposées en cellules C5, E5 et G5. Elles permettent respectivement de définir l'année, la salle à réserver et la semaine à construire.

• En G5, choisir la semaine 37 à la place de la semaine 36,

Comme vous pouvez le voir, les jours de la semaine se reconstruisent en en-tête du calendrier hebdomadaire, sur la ligne 7.

Au réglage de ces valeurs par les listes déroulantes, les réservations stockées doivent être importées dans le calendrier, dans les cases des horaires et jours correspondants. Un code VBA doit donc réagir au changement de valeur dans ces trois cellules associées. Les réservations enregistrées sont stockées dans la feuille Archives.



Le code à bâtir doit être en mesure d'établir la correspondance sur le nom de la salle, la date et la tranche horaire. Dès lors, il saura restituer les données dans le planning.

De même, vous notez la présence d'un bouton nommé Archiver en haut de la feuille Planning. Un clic sur ce dernier doit permettre d'archiver les potentielles réservations ajoutées manuellement dans le planning, pour la semaine désignée. Mais il doit aussi permettre d'éliminer celles qui ont potentiellement été effacées.

Coordonnées des cellules en VBA
Nous avons appris la technique permettant d'intercepter les coordonnées des cellules modifiées notamment au travers du jeu de réflexe en VBA. Il s'agit d'une procédure évènementielle à générer. Elle transmet un paramètre qui n'est autre que la cellule incriminée. Grâce à lui, nous pouvons analyser les coordonnées impliquées. L'idée est de n'enclencher le traitement que lorsqu'il s'agit bien de l'une des trois cellules des listes déroulantes.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, double cliquer sur l'élément Feuil1(Planning),

Nous affichons ainsi sa feuille de code associée au centre de l'écran.

• En haut de cette dernière, déployer la liste déroulante de gauche,
• Parmi les propositions, choisir l'objet Worksheet,

Cette action a pour effet de créer la procédure évènementielle Worksheet_SelectionChange. Mais ce n'est pas celle qui nous intéresse. Nous souhaitons contrôler la modification d'une cellule (Change) sur la feuille (Worksheet) et non le changement de sélection.

• A l'aide de la seconde liste déroulante sur la droite, choisir l'évènement Change,

Grâce à ce choix, nous générons ainsi la procédure évènementielle qui nous intéresse :

Worksheet_Change(ByVal Target As Range)



Vous remarquez la présence de la variable Target passée en paramètre. Elle est typée comme un objet Range. Cela signifie qu'il s'agit de la cellule ou de la plage de cellules dont le contenu a été modifié. Grâce à lui et ses propriétés Row et Column, nous allons pouvoir identifier les coordonnées de la cellule à traiter.

• Supprimer la procédure Worksheet_SelectionChange,
• Entre les bornes de la procédure Worksheet_Change, ajouter le code VBA suivant :

Private Sub Worksheet_Change(ByValTarget As Range)
Dim ligne As Byte: Dim colonne As Byte

ligne = Target.Row: colonne = Target.Column

If (ligne = 5 And (colonne = 3 Or colonne = 5 Or colonne = 7)) Then
recup_salles
End If
End Sub

Nous déclarons les variables ligne et colonne pour réceptionner les coordonnées de la cellule, grâce aux propriétés précédemment évoquées. Les listes déroulantes sont toutes situées sur la ligne 5, respectivement en colonnes 3, 5 et 7. C'est pourquoi, nous enclenchons un test de correspondance afin de ne pas poursuivre le traitement lorsque les coordonnées ne correspondent pas. Lorsqu'elles correspondent en revanche, nous appelons la procédure recup_salles. Cette dernière n'existe pas encore. Nous devons donc la créer. Son rôle consiste à importer toutes les réservations archivées concordantes, dans le planning en cours.

• En dessous de la procédure évènementielle, créer la procédure recup_salles, comme suit :

Private Sub recup_salles()

End Sub

Des variables sont nécessaires au traitement.

• Entre les bornes de la procédure, ajouter les déclarations et affectations suivantes :

...
Dim semaine As Integer: Dim salle As String
Dim annee As Integer
Dim ligne As Byte: Dim colonne As Byte
Dim feuille As Worksheet: Dim ligne_ext As Integer
Dim trouve As Boolean

Set feuille = Sheets('Archives')
semaine = Range('G5').Value
salle = Range('E5').Value
annee = Range('C5').Value
...

Pour établir les correspondances, nous devons prélever les informations sur la semaine, la salle et l'année désignées. C'est pourquoi nous déclarons et typons les trois variables respectives en conséquence.

Les variables ligne et colonne doivent permettre de parcourir toutes les cases du calendrier. Comme leur nombre est limité, nous les déclarons comme des entiers courts (Byte). De même, nous exploiterons la variable ligne_ext pour analyser l'ensemble des lignes du tableau des archives. Ces dernières peuvent être denses. C'est la raison pour laquelle nous typons cette variable comme un entier.

Nous déclarons une variable en tant qu'objet feuille (Worksheet). L'objectif est de simplifier la syntaxe de correspondance en pointant sur la feuille Archives. Et c'est pourquoi, nous l'affectons sur cette dernière (Set feuille = Sheets('Archives')). La variable trouve est un booléen qui permettra de confirmer la correspondance établie afin d'optimiser le traitement en interrompant les boucles.

Nous prélevons enfin les informations de réglage du planning (semaine, salle, annee). Ce sont ces trois critères à recouper que nous devons chercher dans le tableau de la feuille Archives. Nous devons tout d'abord nous assurer qu'elles sont toutes trois renseignées. Si la condition combinée est satisfaite, l'analyse du tableau des réservations stockées peut débuter. Et ces informations sont stockées à partir de la ligne 3 de la feuille Archives, sur une hauteur indéterminée. C'est pourquoi nous devons enclencher une boucle While. Son rôle est de parcourir les archives tant que la fin du tableau n'est pas atteinte.

• A la suite du code de la procédure, ajouter les instructions VBA suivantes :

...
If (Cells(5, 3).Value <> '' And Cells(5, 5).Value <> '' And Cells(5, 7).Value <> '') Then

Range('C8:G18').Value = ''

ligne_ext = 3

Do While feuille.Cells(ligne_ext, 6).Value <> ''
trouve = False

ligne_ext = ligne_ext + 1
If (ligne_ext > 1000) Then Exit Do
Loop
End If
...

Nous engageons donc la vérification d'un triple critère pour les cellules situées en ligne 5 et en colonnes respectives 3, 5 et 7. Il s'agit de l'indication sur l'année, la salle et la semaine. Si ces cellules ne sont pas vides, nous poursuivons le traitement.

Tout d'abord, grâce à l'objet Range, nous désignons l'intégralité des cases du calendrier. Nous réinitialisons sa propriété Value afin de vider le planning. Nous initialisons la variable ligne_ext sur le premier indice de ligne du tableau de la feuille Archives. Puis, nous enclenchons une boucle de traitement. Celle-ci doit poursuivre son analyse tant que son critère est vrai, soit tant qu'une information est détectée en colonne 6, pour la ligne en cours (feuille.Cells(ligne_ext,6).Value).

A chaque passage dans cette boucle, nous initialisons le booléen à False. Sa valeur sera basculée dans un autre traitement itératif consistant à remplir le planning. Et c'est ce test qui permettra de ne pas parcourir inutilement toutes les cases lorsque la correspondance est trouvée. Nous n'oublions pas non plus d'incrémenter la variable de boucle à chaque passage (ligne_ext = ligne_ext + 1). Et nous ajoutons un garde-fou consistant à interrompre le programme lorsque le traitement est jugé trop long.

A chaque passage dans cette boucle, nous devons vérifier la correspondance recoupée sur l'année, la salle et la semaine. Un triple critère dans une instruction conditionnelle est donc de nouveau nécessaire. S'il est vérifié, nous devons parcourir l'ensemble des cases du calendrier hebdomadaire à la recherche d'une corrélation sur l'heure et la date exacte.

• Dans la boucle, juste après le booléen, ajouter les instructions VBA suivantes :

...
If (semaine = feuille.Cells(ligne_ext, 2).Value And salle = feuille.Cells(ligne_ext, 3).Value And annee = Right(feuille.Cells(ligne_ext,4).Value, 4)) Then

For ligne = 8 To 18
For colonne = 3 To 7

Next colonne
Next ligne

End If
...

Pour la vérification de la triple condition, vous notez l'emploi de la fonction Right. Avec le chiffre 4 passé en second paramètre, elle permet de prélever les quatre derniers caractères de la date archivée. Il en résulte l'année que nous comparons avec celle désignée sur la feuille Planning. Lorsque le triple critère est satisfait, nous engageons une double boucle destinée à analyser toutes les cases du calendrier hebdomadaire. Nous parcourons les lignes de la huitième à la dix-huitième. Et pour chacune d'elles, nous parcourons les colonnes de la troisième à la septième.

Désormais, si l'heure et la date coïncident précisément, nous devons prélever l'information de la réservation en cours d'analyse, pour l'inscrire dans la case correspondante du planning. En conséquence, un double critère doit tout d'abord est satisfait.

• A l'intérieur de la double boucle, ajouter les traitements suivants :

...
If (Cells(ligne, 2).Value = feuille.Cells(ligne_ext, 5).Value And Cells(7,colonne).Value = feuille.Cells(ligne_ext, 4).Value) Then

Cells(ligne, colonne).Value = feuille.Cells(ligne_ext, 6).Value
trouve = True
Exit For
End If
...

Si la concordance sur l'heure et le jour est confirmée, nous procédons à l'inscription dans la case correspondante, en cours d'étude dans la double boucle. Dans le planning, l'heure est située en colonne B pour la ligne en cours (Cells(ligne, 2).Value). La date quant à elle est placée en ligne 7 pour la colonne en cours (Cells(7,colonne).Value). Après avoir procédé à l'inscription, il n'est pas nécessaire de poursuivre le traitement pour cette même réservation. Il ne peut y avoir qu'une seule réservation pour un même horaire et un même jour. C'est pourquoi nous basculons l'état du booléen et exploitons l'instruction Exit For pour sortir de la seconde boucle, imbriquée dans la première.

Si le booléen confirme le succès de l'inscription, nous devons aussi sortir de la première boucle au risque sinon de repartir pour des tours inutiles.

• Entre la fermeture des deux boucles, ajouter l'instruction suivante :

...
Next colonne
If trouve = True Then Exit For
Next ligne
...

Il est temps de passer à la phase de test :

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Planning,
• Avec la troisième liste déroulante, remplacer la semaine 37 par la semaine 36,

Aussitôt, tous les rendez-vous de la semaine pour la salle désignée sont importés dans les cases concordantes sur l'horaire et la date.

Si vous changez la salle Multimédia pour la salle Oméga avec la seconde liste déroulante, vous obtenez son planning de réservation pour la semaine 36. Il s'agit donc déjà d'un outil fort précieux en termes d'organisation.

Le code complet de la procédure recup_salles est le suivant :

Private Sub recup_salles()
Dim semaine As Integer: Dim salle As String
Dim annee As Integer
Dim ligne As Byte: Dim colonne As Byte
Dim feuille As Worksheet: Dim ligne_ext As Integer
Dim trouve As Boolean

Set feuille = Sheets('Archives')
semaine = Range('G5').Value
salle = Range('E5').Value
annee = Range('C5').Value

'indices lignes : 8 à 18 - indices colonnes : 3 à 7
If (Cells(5, 3).Value <> '' And Cells(5, 5).Value <> '' And Cells(5, 7).Value <> '') Then

Range('C8:G18').Value = ''
ligne_ext = 3

Do While feuille.Cells(ligne_ext, 6).Value <> ''
trouve = False 'optimisation

If (semaine = feuille.Cells(ligne_ext, 2).Value And salle = feuille.Cells(ligne_ext, 3).Value And annee = Right(feuille.Cells(ligne_ext,4).Value, 4)) Then

For ligne = 8 To 18
For colonne = 3 To 7

If (Cells(ligne, 2).Value = feuille.Cells(ligne_ext, 5).Value And Cells(7,colonne).Value = feuille.Cells(ligne_ext, 4).Value) Then

Cells(ligne, colonne).Value = feuille.Cells(ligne_ext, 6).Value
trouve = True
Exit For
End If

Next colonne
If trouve = True Then Exit For
Next ligne

End If

ligne_ext = ligne_ext + 1
If (ligne_ext > 1000) Then Exit Do
Loop
End If
End Sub

Enregistrer les nouvelles réservations
Nous devons désormais finaliser l'outil en permettant l'enregistrement de nouvelles réservations. Et pour cela, il doit être permis à l'utilisateur d'inscrire directement l'indication dans la case concordante du planning. Dès lors, un clic sur le bouton Archiver, en haut de la feuille Planning, doit procéder à l'insertion de cette réservation, à la suite des enregistrements de la feuille Archives. Mais rien n'indique que d'autres modifications n'ont pas été entreprises dans l'intervalle de temps. De fait, nous proposons de purger d'abord les archives de la salle désignée pour la semaine mentionnée. Puis, nous procèderons à la réinscription intégrale de toutes les informations inscrites sur le planning hebdomadaire.

• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,
• Dès lors, double cliquer sur le bouton Archiver placé sur la ligne 2 de la feuille Excel,

Grâce à cette action, nous sommes de retour dans l'éditeur de code Visual Basic Excel, mais cette fois entre les bornes de la procédure Archiver_Click. Son code se déclenchera donc au clic sur le bouton. Comme il est de coutume désormais, nous devons commencer par la déclaration et l'initialisation des variables nécessaires.

• Entre les bornes de la procédure Archiver_Click, ajouter les instructions VBA suivantes :

...
Dim semaine As Integer: Dim salle As String
Dim annee As Integer
Dim feuille As Worksheet: Dim ligne_ext As Integer
Dim ligne As Byte: Dim colonne As Byte

ligne_ext = 3
Set feuille = Sheets('Archives')
semaine = Range('G5').Value
salle = Range('E5').Value
annee = Range('C5').Value
...

A l'exception du booléen, nous déclarons sensiblement les mêmes variables que précédemment. Nous avons en effet besoin de parcourir les cases du planning et les lignes du tableau d'archives. Il est de plus nécessaire de stocker les informations de réglage (semaine, salle, annee) pour les stocker. Nous initialisons ensuite les variables en conséquence, comme nous l'avons fait dans la procédure recup_salles.

Dès lors et dans un premier temps, nous proposons de parcourir l'ensemble des archives pour supprimer toutes les lignes correspondant aux cases inscrites dans le planning hebdomadaire. C'est ensuite que nous procèderons à la réinscription globale. Comme nous le disions, cette technique permet d'envisager toutes les modifications potentielles ayant eu lieu, avant le clic sur le bouton Archiver.

• A la suite du code VBA, ajouter les instructions suivantes :

...
Do While feuille.Cells(ligne_ext, 2).Value <> ''

If (semaine = feuille.Cells(ligne_ext, 2).Value And salle = feuille.Cells(ligne_ext, 3).Value And annee = Right(feuille.Cells(ligne_ext,4).Value, 4)) Then
feuille.Cells(ligne_ext, 2).EntireRow.Delete
Else
ligne_ext = ligne_ext + 1
End If

Loop
...

Nous engageons donc une boucle classique sur la feuille Archives. Son traitement récursif doit se poursuivre tant que son critère est vérifié. Et son critère consiste à déceler la présence d'une information. En d'autres termes, tant que la fin du tableau n'est pas atteinte, nous continuons.

A l'intérieur de la boucle, nous vérifions la triple condition sur la semaine, la salle et la semaine. Si elle est satisfaite, nous exploitons la propriété EntireRow sur une cellule de la ligne en cours d'analyse (feuille.Cells(ligne_ext,2)). Cette dernière permet d'accéder à la méthode Delete pour supprimer l'intégralité de la ligne spécifiée. Donc, nous procédons bien à la suppression d'une réservation présente dans le planning hebdomadaire. Et comme nous sommes dans une boucle, tous les enregistrements concordants seront effacés.

Dans le cas où cette triple condition n'est pas satisfaite, nous n'omettons pas d'incrémenter la variable de boucle, pour poursuivre l'analyse. En cas de suppression en effet, le pointeur se déplace naturellement sur l'enregistrement suivant.

Nous devons maintenant nous assurer de repartir du bon indice de ligne pour procéder à l'inscription. Une sécurité supplémentaire n'est pas de trop. C'est pourquoi, nous suggérons de parcourir de nouveau les archives, désormais purgées, à la recherche de la dernière ligne.

• A la suite du code de la procédure Archiver_Click, ajouter le traitement suivant :

...
ligne_ext = 3
Do While feuille.Cells(ligne_ext, 2).Value <> ''
ligne_ext = ligne_ext + 1
Loop
...

Nous partons bien de la première ligne (ligne_ext = 3). Puis, nous parcourons l'ensemble des archives jusqu'à atteindre la dernière ligne. Il en résulte le nouvel indice de départ, mémorisé dans la variable ligne_ext, à chaque passage dans la boucle.

Nous devons exploiter l'indice de ligne réceptionné pour procéder à l'inscription de chaque réservation inscrite dans le planning hebdomadaire. Comme il s'agit de parcourir l'ensemble des lignes et des colonnes, nous proposons d'exploiter la même astuce que précédemment. Il s'agit d'une double boucle.

• A la suite du code VBA de la procédure Archiver_Click, ajouter les instructions suivantes :

...
For ligne = 8 To 18
For colonne = 3 To 7
If (Cells(ligne, colonne).Value <> '') Then
feuille.Cells(ligne_ext, 2).Value = semaine
feuille.Cells(ligne_ext, 3).Value = salle
feuille.Cells(ligne_ext, 4).Value = Cells(7, colonne).Value
feuille.Cells(ligne_ext, 5).Value = Cells(ligne, 2).Value
feuille.Cells(ligne_ext, 6).Value = Cells(ligne, colonne).Value
ligne_ext = ligne_ext + 1
End If
Next colonne
Next ligne
...

Nous parcourons tout d'abord toutes les lignes comprises entre les indices 8 et 18 sur la feuille Planning. Et pour chacune d'elles, nous parcourons toutes les colonnes du planning, comprises entre les indices 3 et 7. De cette manière, nous nous assurons de passer toutes les cases du calendrier en revue.

Dès lors, nous procédons à l'inscription des informations dans les colonnes correspondantes de la feuille Archives.

Le développement VBA de l'outil de réservation est terminé. Il est nécessaire de le tester.

• Enregistrer les modifications et basculer sur la feuille Planning,
• Dans le ruban Développeur, cliquer sur le bouton Mode création,

Nous désactivons ainsi la conception.

• A l'aide des trois listes déroulantes, choisir l'année 2019, la salle Multimédia et la semaine 36,
• Sélectionner alors la cellule E10,
• Saisir l'indication suivante : Audit qualité, puis valider par la touche Entrée,

Nous venons de réserver la salle Multimédia pour le mercredi 04 Septembre 2019 à 11h. A ce titre, vous notez l'apparition d'une légère mise en forme dynamique. Le fond de la cellule est hachuré subtilement. Mais nous n'avons pas encore enregistré cette réservation.

• Cliquer maintenant sur le bouton Archiver, en haut de la feuille Planning,

En apparence, rien ne se produit. Mais le traitement est rapide en raison de l'optimisation du code VBA. Si vous affichez la feuille Archives, vous notez la présence de la nouvelle réservation au milieu des autres.

• Sur la feuille Planning, choisir la semaine 37,

Nous affichons ainsi le planning des réservations pour une nouvelle semaine.

• Choisir de nouveau la semaine 36,

Nous retournons sur la précédente vue confirmant l'enregistrement entériné de la nouvelle réservation, puisqu'elle est parfaitement restituée.

Nous avons donc réussi à bâtir un outil de réservation en VBA Excel. Ici, il est destiné aux salles de réunion. Mais bien entendu, il est facilement adaptable à tout type de réservation.

Le code VBA complet de la procédure Archiver_Click est le suivant :

Private Sub Archiver_Click()
Dim semaine As Integer: Dim salle As String
Dim annee As Integer
Dim feuille As Worksheet: Dim ligne_ext As Integer
Dim ligne As Byte: Dim colonne As Byte

ligne_ext = 3
Set feuille = Sheets('Archives')
semaine = Range('G5').Value
salle = Range('E5').Value
annee = Range('C5').Value

Do While feuille.Cells(ligne_ext, 2).Value <> ''

If (semaine = feuille.Cells(ligne_ext, 2).Value And salle = feuille.Cells(ligne_ext, 3).Value And annee = Right(feuille.Cells(ligne_ext,4).Value, 4)) Then
feuille.Cells(ligne_ext, 2).EntireRow.Delete
Else
ligne_ext = ligne_ext + 1
End If

Loop

ligne_ext = 3
Do While feuille.Cells(ligne_ext, 2).Value <> ''
ligne_ext = ligne_ext + 1
Loop

For ligne = 8 To 18
For colonne = 3 To 7
If (Cells(ligne, colonne).Value <> '') Then
feuille.Cells(ligne_ext, 2).Value = semaine
feuille.Cells(ligne_ext, 3).Value = salle
feuille.Cells(ligne_ext, 4).Value = Cells(7, colonne).Value
feuille.Cells(ligne_ext, 5).Value = Cells(ligne, 2).Value
feuille.Cells(ligne_ext, 6).Value = Cells(ligne, colonne).Value
ligne_ext = ligne_ext + 1
End If
Next colonne
Next ligne

End Sub