| Sommaire |
| Excel Perfectionnement |
| Excel Graphique |
| Divers Excel |
| Excel Vba début |
| Complément VBA |
| Utilitaires |
| Gestion (philosophie) |
| Gestion (techniques) |
| Liens |
| Utilitaires en Vrac |
| Gfc Windows |
|
Classeurs |
Afin d'éviter la mauvaise utilisation d'un classeur qui contient des macros par la
désactivation des macros au chargement du classeur (alerte macro), cette procédure fait
un contrôle l'état du classeur pendant les phases de l'enregistrement et de
l'ouverture.Si les macros ne sont pas activées, seule, la feuille Test avec le messsage
que le classeur est inactif, apparaît.
Pour cela, nous utilisons les évènements Workbook_BeforeClose (avant la fermeture) et
Workbook_Open (à l'ouverture) de ThisWorkBook.
A la fermeture :
| Macro | Commentaire |
| Private Sub Workbook_BeforeClose(Cancel As
Boolean) Application.ScreenUpdating = False On Error Resume Next Dim Feuille As Worksheet For Each Feuille In ThisWorkbook.Worksheets Feuille.Visible = xlSheetVeryHidden Next With Sheets("Test") .Visible = True .Activate End With With ActiveWindow .DisplayGridlines = False .DisplayHeadings = False .DisplayHorizontalScrollBar = False .DisplayVerticalScrollBar = False .DisplayWorkbookTabs = False End With ThisWorkbook.Save ThisWorkbook.Close End Sub |
'Désactivation du rafraichissement de l'écran 'Passe à la ligne suivante si erreur détectée 'Déclaration de variable comme sheet 'Test pour toutes les feuilles 'Met les feuilles en "Très" cachées 'Avec la feuille Test 'Mise en visible 'Activée 'Avec la fenêtre (ou classeur), ôte : 'quadrillage, barres de défilement, onglets...
'Sauvegarde le Classeur |
| Note : Seule, la feuille Test sera visible à l'ouverture - la sauvegarde est automatique | |
A l'ouverture :
| Macro | Commentaire |
| Private Sub Workbook_Open() On Error Resume Next Dim Feuille As Worksheet For Each Feuille In ThisWorkbook.Worksheets Feuille.Visible = True Next Sheets("Début").Activate Sheets("Test").Visible = xlSheetVeryHidden With ActiveWindow .DisplayGridlines = True .DisplayHeadings = True .DisplayHorizontalScrollBar = True .DisplayVerticalScrollBar = True .DisplayWorkbookTabs = True End With End Sub |
'Passe à la ligne suivante si erreur détectée 'Déclaration de variable comme sheet 'Test pour toutes les feuilles 'Rend visible les feuilles 'Active la feuille début 'Met la feuille Test en "Très" cachée Avec la fenêtre (ou classeur), met : 'quadrillage, barres de défilement, onglets...
|
| Note : Cache la feuille et remet le classeur en état. Il
convient également de protéger le projet VBA par un mot de passe : Dans L'éditeur VBA , clic droit sur le projet (petit fenêtre en haut à gauche) - Propriétés - Protection télécharger le classeur SansMacro.xls |
|
Problème : Une année est contenue dans une cellule du classeur actif et s'appelle "annee". Comment enregistrer ce classeur dans le répertoire de C\:Mes Documents sous le nom Rapport_AAAA; les AAAA correspondent au contenu de la cellule "annee" |
|
| Macro | Commentaires |
| Sub EnregCellule() On Error Resume Next If Range("annee") = Empty Or Err <> 0 Then MsgBox "Saisissez l'année" Else ActiveWorkbook.SaveAs Filename:= _ "C:\Mes Documents\Rapport_" & Range("annee") & ".xls" End If End Sub |
'Si erreur, on continue 'Contrôle du remplissage de la cellule année ou si la cellule nommée "annee" existe bien 'Message d'alerte 'Sinon 'Sauvegarde sous le nom voulu |
| Note : Le contrôle de cohérence On Error Resume Next et Err <> 0 et la concaténation de chaine de caractère avec le contenu de la cellule "annee" | |
Variante : Nous voulons enregistrer le classeur sous la forme Rapport_062001 (c. a. d. MMAAAA) cette date étant définie par la date du jour. |
|
| Macro | Commentaires |
| Sub EnregCellule2() On Error Resume Next ActiveWorkbook.SaveAs Filename:= _ "C:\Mes Documents\Rapport_" & _ Evaluate("=TEXT(TODAY(),""MMYYYY"")") & ".xls" End Sub |
'Si erreur, on continue pour éviter un bug si le fichier existe déjà. 'Sauvegarde sous le nom voulu |
| Note :
=TEXT(TODAY(),""MMYYYY"") correspond sous Excel à =TEXTE(AJOURDHUI();"MMAAAA") et la méthode Evaluate permet de récupérer le résultat. |
|
Problème : Ouvrir un fichier dont le nom sera saisi dans une boîte de dialogue |
|
| Macro | Commentaires |
| Sub OuvertureFichier() Dim Fichier As String On Error Resume Next Fichier = InputBox("Indiquez le le fichier de transfert") Workbooks.Open Filename:="C:\Mes Documents\" & _ Fichier & ".xls" If Err <> 0 Then MsgBox "fichier " & Fichier & " introuvable !" End End If 'Suite procédure.... End Sub |
'déclaration variable en chaine de caractères 'Si erreur, on continue 'Récupération du nom saisi dans la boîte de dialogue dans la variable année 'Ouverture du fichier 'Si erreur d'exécution, alors 'Message d'alerte 'Fin procédure |
| Note : L'intérêt est d'utiliser une variable texte pour le chemin complet du fichier. Le End n'est utile que s'il y a une suite à la procédure. Le gestionnaire d'erreur permet le contrôle de la saisie | |
Problème : Dans une Feuil4 masquée, nous voulons suivre les heures
d'ouvertures et de fermeture de notre classeur. En A1 : Jour( format jj/mm/aaaa sur la
colonne), B1 : Ouverture (format hh:mm:ss), C1 : Fermeture (format hh:mm:ss) D1 : Durée
(format [h]:mm:ss) |
|
| Macro | Commentaires |
| Private Sub Workbook_Open() Sheets("Feuil4").Rows("2:2").Insert Sheets("Feuil4").Cells(2, 1) = Now Sheets("Feuil4").Cells(2, 2) = Time End Sub |
'Utilisation de
l'évenement open (ouverture du classeur) 'Insertion d'une ligne ' en A2, le jour 'en B2, l'heure d'ouverture |
A la fermeture : dans le module ThisWorkbook |
|
| Macro | Commentaires |
| Private Sub Workbook_BeforeClose(Cancel As
Boolean) If Not IsEmpty(Sheets("Feuil4").Cells(2, 1)) Then Sheets("Feuil4").Cells(2, 3) = Time Sheets("Feuil4").Cells(2, 4) = Sheets("Feuil4")._ Cells(2, 3).Value - Sheets("Feuil4").Cells(2, 2).Value End If End Sub |
'Utilisation de
l'évenement BeforeClose (avant la fermeture du classeur) 'Test si A2 est non vide ' en C2, l'heure 'en D2, heure de fermeture-heure d'ouverture |
| Note : l'insertion de ligne 2
(Sheets("Feuil4").Rows("2:2").Insert). Le fait que la feuil4 soit
masquée est indifférente à la procédure et n'ayant pas de sélection de feuille et
classique application.screenupdating=false n'est pas nécessaire. classeur exemple : duree.xls |
|
Problème : Fermer un classeur sans sauvegarder les modifications |
|
| Macro | Commentaires |
| Private Sub Workbook_BeforeClose(Cancel As
Boolean) ThisWorkbook.Saved = True End Sub Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean) |
'Evenement avant la
fermeture 'Le classeur est dit saugardé
|
| Note : L'évement Fermeture est acceptée. Mais, au préalable, le classeur est considéré comme sauvegardé (saved) - Il convient si on ne veut pas d'enregistrement intempestifs de désactiver l'action d'enregistrer. Ces 2 procédures se trouvent dans le module ThisWorkbook | |
Problème : Sauvegarder un classeur sans message d'alerte : "voulez-vous enregistrer..." |
|
| Private Sub Workbook_BeforeClose(Cancel As
Boolean) ThisWorkbook.Save End Sub |
'Evenement avant la
fermeture 'Enregistrement du classeur |
| Note : L'évement Fermeture est acceptée. Mais, au préalable, soit le classeur est enregistré (save) donc le message d'alerte n'apparaîtra plus. | |
Problème : Déprotéger un classeur |
|
| Macro | Commentaires |
| Sub Deprotection() If ActiveWorkbook.ProtectStructure = True Or _ ActiveWorkbook.ProtectWindows = True Then _ ActiveWorkbook.Unprotect End Sub |
'Test si le classeur dispose du propriété de protection à Vrai, et déprotection
|
| Note : Unprotect (méthode) ProtectStructure (propriété ayant un état : vrai - faux) | |
Problème : Faire la bascule protection - déprotection |
|
| Sub ProtectDeprotect() On Error Resume Next ActiveWorkbook.Protect If Err <> 0 Then ActiveWorkbook.Unprotect End Sub |
'Gestionaire d'erreur enclenché 'Protection du classeur 'Si erreur, déprotection |
| Note : Si une méthode (ici Protect) déclenche, on fait l'autre | |
| Macro | Commentaires |
| Sub EntetePiedPageClasseur() Dim Text As String, Feuille As Worksheet texte = Application.StatusBar Application.StatusBar = "Macro en cours d'exécution...Patientez" Application.ScreenUpdating = False For Each Feuille In Worksheets Feuille.PageSetup.CenterFooter = _ ThisWorkbook.FullName Next Application.StatusBar = texte End Sub |
'Déclaration de variables 'Récupération du text du le barre d'état 'Nouveau Message dans la barre d'état 'Désactivation du rafraichissement de l'écran 'Boucle sur chaque feuille du classeur 'Mise en place du pied de page central avec le nom et ' chemin du classeur 'Remise en état de la barre d'état |
| Note : cette macro est assez lente,
nous verrons plus tard comment l'accélerer A retenir : Le nom et chemin du classeur : ThisWorkbook.FullName, le message dans la barre d'état : Application.StatusBar |
|
| Macro | Commentaires |
| Sub RetourLigne() Dim Info As String Info = ActiveWorkbook.FullName & _ Chr(13) & "imprimez le " & Date & Chr(13) & "à " & Time ActiveSheet.PageSetup.LeftFooter = _ "&""Tahoma,regular""&5" & Info End Sub |
'Déclaration de variables (chaîne de caractères) 'Création des informations du Pied de page : nom et chemin complet, retour ligne, date d'impression, retour ligne et heure 'affectation des informations au pied page gauche en modifiant la police |
| Note : Chr(13) ou Chr(10) sont les codes ASCII permettant le saut de ligne - la police peut également être modifiée par VBA | |