FAQ VBA
FAQ VBAConsultez toutes les FAQ
Nombre d'auteurs : 10, nombre de questions : 133, dernière mise à jour : 15 juin 2021
Sommaire→Excel→Classeurs- Comment ajouter un classeur contenant juste une feuille ?
- Comment ouvrir un classeur ?
- Comment travailler sur deux classeurs ?
- Comment fermer un classeur sans enregistrer les changements ?
- Je ne comprends pas la protection du classeur !
- Comment dupliquer une plage sur plusieurs feuilles du classeur ?
- Quelle différence entre ActiveWorkbook et ThisWorkbook ?
- Comment écrit-on une fonction de feuille de calcul ?
- Peut-on supprimer les liaisons d'un classeur ?
- Comment empêcher la fermeture de mon classeur ?
Il y a deux méthodes. On peut restreindre le nombre de feuilles de calcul créées dans un nouveau classeur
Application.SheetsInNewWorkbook = 1
Application.Workbooks.AddSeulement cela est un paramétrage de l'application et il faudrait restaurer l'ancienne valeur. On peut aussi créer un classeur contenant une feuille de type spécifique
Application.Workbooks.Add xlWBATWorksheetcontient une feuille de calcul
Application.Workbooks.Add xlWBATChartContient une feuille graphique
On utilise la méthode Open de la collection WorkBooks Elle accepte plusieurs paramètres dont quelques-uns sont utiles à connaître, FileName est le seul paramètre obligatoire. Il doit contenir le chemin complet
Application.Workbooks.Open "d:\tuto.xls"On peut aussi passer par la méthode GetOpenFilename pour laisser l'utilisateur sélectionner le fichier
Application.Workbooks.Open Application.GetOpenFilename()Les paramètres intéressants supplémentaires sont : UpdateLinks Une référence externe appartient à un autre classeur, une référence distante appartient à un autre programme. ReadOnly Booléen qui définit si le classeur doit être ouvert en lecture seule Password Représente le mot de passe pour un classeur à ouverture protégée
- 0--> Ne met à jour aucune référence
- 1--> Met à jour des références externes mais pas des références distantes
- 2--> Met à jour des références distantes mais pas des références externes
- 3--> Met à jour des références distantes et des références externes
Dans ce cas on utilises des variables
Dim objWorkbookSource As Workbook, objWorkbookCible As Workbook
set objWorkbookSource = Application.Workbooks.Open(Application.GetOpenFilename)
set objWorkbookCible = Application.Workbooks.AddIl ne reste plus qu'à basculer entre les deux variables
Il suffit de faire
ActiveWorkbook.Close FalseLa méthode Close permet de gérer un grand nombre de cas. Sa syntaxe générale est :
Close(SaveChanges, FileName, RouteWorkbook)Si SaveChanges est vrai et que le classeur n'a pas encore été enregistré, il utilisera la valeur de filename. Si celle-ci n'est pas précisée, il y aura appel de la boîte de dialogue SaveAs.
Dans Excel, la protection d'un objet ne joue que sur les descendants directs. Je m'explique : la protection d'un classeur ne protège que ses fenêtres et sa structure de feuille. Si je fais :
ActiveWorkbook.Protect Password:="monpasse", Structure:=True, Windows:=FalseJe ne protège que la structure. Cela veut dire qu'il n'est pas possible d'ajouter, de supprimer ou de déplacer des feuilles. En aucun cas cela ne protège les cellules. Pour cela il faut protéger la ou les feuilles.
On utilise la méthode FillAcrossSheet de l'objet WorkBook
ThisWorkbook.Worksheets.FillAcrossSheets Range("A1:C10"), xlFillWithAllNotez que le deuxième paramètre permet de préciser si on veut dupliquer le contenu, le format ou les deux.
ThisWorkbook représente le classeur qui contient le code faisant appel à thisWorkbook. Active Workbook représente le classeur actif de l'application. De manière générale, il vaut mieux éviter les objets actifs et privilégier des variables, car la programmation des objets actifs est assez piègeuse : ceux-ci tendent à ne plus l'être (actif) quand on en a besoin. Prenons un exemple.
Set objSheet = ThisWorkbook.Sheets("recup")
Application.Workbooks.Open "d:\user\tuto1.xls"
ActiveWorkbook.OpenLinks Name:=ActiveWorkbook.LinkSources(xlExcelLinks)Après l'appel de OpenLinks plus de possibilité de savoir aisément quel classeur est désigné par ActiveWorkbooks
Il s'agit d'une fonction normale.Par exemple
Function InvChaine(Cellule As Range) As Variant
Dim MaChaine As String, cmpt As Long
If Len(Cellule.Value) = 0 Then
InvChaine = CVErr(xlErrValue)
Else
MaChaine = CStr(Cellule.Value)
For cmpt = Len(MaChaine) To 1 Step -1
InvChaine = InvChaine & Mid(MaChaine, cmpt, 1)
Next cmpt
End If
End FunctionSi dans votre feuille vous écrivez la formule =invchaine(E2) La cellule contiendra la chaine retournée ou #VALEUR
Ce n'est pas toujours évident. Pour supprimer les liaisons il y a globalement deux méthodes. Une recherche complète permettant la récupération de ce qui est récupérable
Sub ChercheLiaison()
Dim NomFichier As String, MonClasseur As Workbook, Liaisons As Variant
Dim compteur As Long, comptCar As Long, Cible As Range
Dim FirstAddress As String, PlageLiee As Range, comptFeuille As Long, Reponse As Integer
Dim MaFeuille As Worksheet, MonGraphe As Chart, MonGraphe1 As ChartObject, MaSerie As Series
NomFichier = Application.GetOpenFilename
Workbooks.Open NomFichier, False
Set MonClasseur = ActiveWorkbook
Liaisons = MonClasseur.LinkSources
If IsEmpty(Liaisons) Then Exit Sub
'parcours les feuilles
For Each MaFeuille In MonClasseur.Worksheets
MaFeuille.Activate
MaFeuille.Cells.Select
For compteur = 1 To UBound(Liaisons)
For comptCar = Len(Liaisons(compteur)) To 1 Step -1
If Mid(Liaisons(compteur), comptCar, 1) = "\" Then
Liaisons(compteur) = Mid(Liaisons(compteur), comptCar + 1)
Exit For
End If
Next comptCar
Set Cible = Selection.Find(What:=Liaisons(compteur), After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False)
If Not Cible Is Nothing Then
FirstAddress = Cible.Address
Do
If PlageLiee Is Nothing Then Set PlageLiee = Cible Else Set PlageLiee = Union(PlageLiee, Cible)
Set Cible = Selection.FindNext(After:=Cible)
Loop While Not Cible Is Nothing And Cible.Address <> FirstAddress
End If
Next compteur
If Not PlageLiee Is Nothing Then
Reponse = MsgBox("La feuille " & MaFeuille.Name & " contient " & PlageLiee.Cells.Count & _
" cellules avec des liaisons" & vbCrLf & _
"voulez-vous les supprimer ?", vbYesNo + vbQuestion, "Liaisons trouvées")
If Reponse = 6 Then
For Each Cible In PlageLiee.Cells
Cible.Formula = Cible.Value
Next
End If
Set PlageLiee = Nothing
End If
For Each MonGraphe1 In MaFeuille.ChartObjects
For Each MaSerie In MonGraphe1.SeriesCollection
For compteur = 1 To UBound(Liaisons)
If InStr(1, MaSerie.Formula, Liaisons(compteur), vbTextCompare) > 0 Then
Reponse = MsgBox("le graphe de la feuille " & MonGraphe1.Name & _
" contient une série " & MaSerie.Name & " avec des liaisons" & vbCrLf & _
"Voulez-vous les supprimer ?", vbYesNo + vbQuestion, "Liaisons trouvées")
If Reponse = 6 Then
MaSerie.Delete
Exit For
End If
End If
Next compteur
Next
Next
Next
For Each MonGraphe In MonClasseur.Charts
For Each MaSerie In MonGraphe.SeriesCollection
For compteur = 1 To UBound(Liaisons)
If InStr(1, MaSerie.Formula, Liaisons(compteur), vbTextCompare) > 0 Then
Reponse = MsgBox("le graphe de la feuille " & MonGraphe.Name & _
" contient une série " & MaSerie.Name & " avec des liaisons" & vbCrLf & _
"voulez-vous les supprimer ?", vbYesNo + vbQuestion, "Liaisons trouvées")
If Reponse = 6 Then
MaSerie.Delete
Exit For
End If
End If
Next compteur
Next
Next
End SubUne méthode beaucoup plus violente qui consiste à modifier les liaisons par le nom du classeur source. Cela va créer des références circulaires qu'il faudra alors supprimer. Les données anciennement liées seront perdues.
Dim Nom As String, objWorkbook As Workbook, TabLiaison As Variant, cmpt As Long
Dim objWorksheet As Worksheet, objRange As Range
Set objWorkbook = Application.Workbooks.Open(Application.GetOpenFilename, UpdateLinks:=0)
TabLiaison = objWorkbook.LinkSources(xlExcelLinks)
If IsEmpty(TabLiaison) Then Exit Sub
For cmpt = LBound(TabLiaison) To UBound(TabLiaison)
objWorkbook.ChangeLink TabLiaison(cmpt), objWorkbook.FullName, xlLinkTypeExcelLinks
Next cmpt
For Each objWorksheet In objWorkbook.Worksheets
Do
Set objRange = objWorksheet.CircularReference
If Not objRange Is Nothing Then objRange.Value = objRange.Value
Loop While Not objRange Is NothingDans le module ThisWorkbook on met
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel=true
End SubComme à un moment il faudra fermer le classeur on désactivera la gestion des événements
Application.EnableEvents = False
ThisWorkbook.Close True