VBA Amélioration macro fichier association

[david84]

Bonjour à tous,
J'ai fait il y a quelques temps un fichier permettant l'enregistrement et le suivi d'associations sportives : enregistrer les nouvelles associations, mise à jour de leur coordonnées (adresse, tel , mail,...) possibilité de les contacter par mail,...
Ce fichier a été fait à l'aide de formules et d'enregistrement de macros, auxquelles s'étaient ajoutées une ou deux macros faites par des personnes de ce forum (notamment JNP que je remercie au passage).
Il fonctionne correctement mais j'aimerai maintenant optimiser les codes afin de le rendre plus rapide et de nettoyer les codes des "sélect" et autres informations parasites dues à l'enregistreur de macro, et au besoin reformuler certaines parties de la macro afin de gagner en efficacité.
Pour ce faire et pour simplifier le travail, je voudrais traiter feuille par feuille et avancer au fur et à mesure.
Le but est que je comprenne la manière de faire pour pouvoir ensuite le faire moi-même (je vais le prendre comme des travaux pratiques afin de comprendre un peu plus le VBA).
Dans le fichier ci-joint, ne sont présentes que 3 feuilles :
- une feuille "création" permettant de créer une nouvelle fiche
- une feuille "BD" dans lesquelles les différentes associations sont regroupées
- une feuille "listes" contenant l'ensemble des listes mises à jour.

La macro que je vous demande de m'aider à traiter se trouve dans le module et permet d'enregistrer une fois tous les champs remplis la nouvelle fiche dans la feuille "BD" et de mettre à jour la feuille "listes" afin que les menus déroulants soient à jour.
J'ai bien sûr essayé de le faire moi-même mais il y a toujours quelque chose qui cloche. Je veux donc voir comment vous procéderiez et ne manquerai pas de vous questionner pour comprendre les procédures utilisées.

Je me tiens à votre disposition pour d'éventuelles précisions et vous remercie de votre collaboration.
Cordialement

 

[david84]

Re : VBA Amélioration macro fichier association

Re

Range("A2", Range("A65536").End(xlUp)).Copy Range("AA2")

cela provoque un bug.
j'ai modifié comme suit et cela semble fonctionner :

Range("A2").CurrentRegion.Copy Range("AA2")

Je teste ton dernier message (#15) et te tiens au courant.
Merci
Edit :

essaye peut être ainsi :
Code:

With Sheets("NomFeuille")
.Range("A2:A3").AutoFill Destination:=.Range("Tableau4[N° ENREG]")
End With

Je remplace cette partie par quelle partie du code ?

 

[Pierrot93]

Re : VBA Amélioration macro fichier association

Re,

celle ci :

    Range("A2:A3").AutoFill Destination:=Range("Tableau4[N° ENREG]")

petit rappel, comme des "select" ont été supprimés, toutes les instructions s'appliquent de ce fait à la feuille active, sauf bien sur à préciser le nom de la feuille en question, si la feuille active n'est pas concernée... remplacer donc des :
range("A1")......
par
Sheets("NomFeuille").range("A1")...

 

[david84]

Re : VBA Amélioration macro fichier association

Re

j'ai modifié et cela ne bogue plus. Cependant :

petit rappel, comme des "select" ont été supprimés, toutes les instructions s'appliquent de ce fait à la feuille active

Tous les select n'ont pas été supprimés en amont de la partie modifiée de la macro.
Pour plus de clarté je te joins le fichier contenant les dernières modifications pour que l'on ne s'embrouille pas. Tu verras que si tu remplaces les select, cela provoques un bug (ou alors c'est de ma faute !).
Merci pour ton aide précieuse.

Edit : merci à Yann pour son mot d'encouragement

 

[skoobi]

Re : VBA Amélioration macro fichier association

Re,
Salut pierrot ,

Pour ce qui est des Select (très bon réflexe de vouloir les supprimer ), voici d'autres modifications (en marge se trouve le N° de ligne pour retrouver plus vite ):

20       Sheets("BD").Select
       Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

devient:

20       Sheets("BD").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

27       Range("A2").Select

Cette ligne est inutile à mon avis (ah cet enregistreur de macro...)

60        Sheets("listes").Select
       Range("A2:D2").Select
       Range(Selection, Selection.End(xlDown)).ClearContents

devient:

60       With Sheets("listes")
.Range("A2", .Range("A2").End(xlDown)).ClearContents
End With

64       Sheets("BD").Select
       Range("B2:E2").Select
       Range(Selection, Selection.End(xlDown)).Copy
       Sheets("listes").Select
       Range("A2").Select
       ActiveSheet.Paste

devient:

64       With Sheets("BD")
         .Range("B2", .Range("B2").End(xlDown)).Copy Sheets("listes").Range("A2")
       End With

3 lignes en moins .

87       Range("AA2").Select
       Range(Selection, Selection.End(xlToRight)).Select
       Range(Selection, Selection.End(xlDown)).ClearContents

Là on va utiliser une variable Range:

Dim Cell as Range 'à mettre au début du code

87       Set Cell = Range("AA2", Range("AA2").End(xlToRight))
       Range(Cell, Cell.End(xlDown)).ClearContents

Tous ces exemples devraient t'aider pour faire le reste.

 

[david84]

Re : VBA Amélioration macro fichier association

Re

Skoobi, as-tu testé les modifications proposées sur le dernier fichier (message #18) ?

Code:

20 Sheets("BD").Select
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

devient:
Code:

20 Sheets("BD").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

cela provoque un bug à

Range("Tableau4[N° DE FICHES]").Range("Tableau4[[#Headers],[DISCIPLINES]:[ASSOCIATIONS ]]").Range(Selection, Selection.End(xlDown)).Copy

Code:

27 Range("A2").Select

Cette ligne est inutile à mon avis (ah cet enregistreur de macro...)

Ligne enlevée et pas de problème constaté.

Code:

60 Sheets("listes").Select
Range("A22").Select
Range(Selection, Selection.End(xlDown)).ClearContents

devient:
Code:

60 With Sheets("listes")
.Range("A2", .Range("A2").End(xlDown)).ClearContents
End With

Modification effectuée et pas de problème constaté

Code:

64 Sheets("BD").Select
Range("B2:E2").Select
Range(Selection, Selection.End(xlDown)).Copy
Sheets("listes").Select
Range("A2").Select
ActiveSheet.Paste

devient:
Code:

64 With Sheets("BD")
.Range("B2", .Range("B2").End(xlDown)).Copy Sheets("listes").Range("A2")
End With

Cette modification provoque un bug.

Là on va utiliser une variable Range:

Code:

Dim Cell as Range 'à mettre au début du code

Code:

87 Set Cell = Range("AA2", Range("AA2").End(xlToRight))
Range(Cell, Cell.End(xlDown)).ClearContents

La modification ne provoque pas de bug.

Je vais essayer de mon côté de comprendre pourquoi mais je voulais auparavant savoir si tu avais testé préalablement de ton côté ou pas.
Merci de ton implication.
A+

 

[JNP]

Re : VBA Amélioration macro fichier association

Bonjour le fil ,

notamment JNP que je remercie au passage

Un peu surpris, je n'ai pas souvenir d'avoir fait des miracles pour toi , mais merci quand même .
J'ai voulu répondre hier, mais, David devait être en train de modifier le forum et je ne récupérais que des erreurs PHP .
Je vois que les petits camarades de jeu ont bien avancé.
Mes remarques pour faire avancer le schmimblick :
Pierrot propose de passer en XLS, mais il me semble que les .Sort.SortFields.Add ne fonctionne pas sous 2003, il faudrait réécrire les tris à condition qu'il n'y ait pas plus de 3 critères. D'autre part, la gestion des tableaux 2007 fait que les lignes supplémentaires sont automatiquement jointes au tableau, ce qui n'est pas le cas de 2003. Donc l'enregistrement XLS ne pose pas de problème, mais j'ai des doutes que le VBA fonctionne .
Une des remarques principale, c'est qu'il est toujours dangereux d'avoir un Range sans sa Sheet devant (comme l'a fait remarquer Pierrot). La première des choses serait donc de les rajouter partout, avec des With quand nécessaire. Pour éviter les soucis, je changerais les Sheets("Création") par le ConeName, soit directement Feuil4.Range(...), ou mieux, je modifierais le CodeName pour le faire correspondre au nom de l'onglet. 2 avantages à cela, l'aide à la saisie fonctionne avec le CodeName, et même si un petit farfelu modifie le nom de l'onglet, les macros restent opérationnelles .
Dans les améliorations qui n'ont pas encore été proposées

With ActiveWorkbook.Worksheets("BD").ListObjects("Tableau4").Sort
.SortFields.Clear
.SortFields.Add Key:=Range("Tableau4[DISCIPLINES]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("Tableau4[COMMUNES]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.SortFields.Add Key:=Range("Tableau4[[ASSOCIATIONS ]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Bonne journée

 

[Pierrot93]

Re : VBA Amélioration macro fichier association

Bonjour Davie, Skoobi, JNP

regarde le code ci-dessous, fonctionne chez moi sous 2003 dans ton dernier fichier, sans aucun "select"... A noter je n'ai pas exécuté de tri, la gestion dans 2007, vba ou pas d'ailleurs est complètement différente, 2007 gère de nouveaux objets...

Sub nouvelle_fiche()
Application.ScreenUpdating = False
If Application.WorksheetFunction.CountIf(Range("E6:E17"), "") > 0 Then
    MsgBox ("Merci de renseigner tous les champs ou d'entrer un tiret (-) pour pouvoir enregistrer les données.")
    Exit Sub
End If
Sheets("BD").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
Sheets("Création").Range("A2:O2").Copy
With Sheets("BD")
    With .Range("A2")
    .PasteSpecial Paste:=xlPasteValues
    .PasteSpecial Paste:=xlPasteFormats
    .Value = "1"
    End With
    .Range("A3").Value = "2"
    .Range("A2:A3").AutoFill Destination:=.Range("A2", .Range("A65536").End(xlUp)(0))
    With Sheets("listes")
        .Range("A2:D" & .Range("B65536").End(xlUp).Row).ClearContents
    End With
    .Range("B2:E" & .Range("B65536").End(xlUp).Row).Copy Sheets("listes").Range("A2")
End With
End Sub

bonne journée
@+

 

[david84]

Re : VBA Amélioration macro fichier association

Re
JNP

Pierrot propose de passer en XLS, mais il me semble que les .Sort.SortFields.Add ne fonctionne pas sous 2003, il faudrait réécrire les tris à condition qu'il n'y ait pas plus de 3 critères. D'autre part, la gestion des tableaux 2007 fait que les lignes supplémentaires sont automatiquement jointes au tableau, ce qui n'est pas le cas de 2003. Donc l'enregistrement XLS ne pose pas de problème, mais j'ai des doutes que le VBA fonctionne .

Est-il donc préférable que je fasse les essais sur un fichier enregistré au format .xlm pour voir si cela fonctionne ?

Pour éviter les soucis, je changerais les Sheets("Création") par le ConeName, soit directement Feuil4.Range(...), ou mieux, je modifierais le CodeName pour le faire correspondre au nom de l'onglet. 2 avantages à cela, l'aide à la saisie fonctionne avec le CodeName

Pour être sûr de bien comprendre ton conseil, peux-tu STP de placer un exemple concret en prenant un morceau du code actuel et le même morceau ré-écrit comme tu me le conseilles ?

Pierrot

Je pourrai tester ton code cet AM et te ferai un retour.

Merci à vous deux, sans oublier Skoobi.
A+

 

[JNP]

Re : VBA Amélioration macro fichier association

Re ,

Est-il donc préférable que je fasse les essais sur un fichier enregistré au format .xlm pour voir si cela fonctionne ?
Pour être sûr de bien comprendre ton conseil, peux-tu STP de placer un exemple concret en prenant un morceau du code actuel et le même morceau ré-écrit comme tu me le conseilles ?

Pas spécialement. Si tu ouvres un fichier XLS sous 2007, il s'ouvre en "Compatibilité". Les seuls cas (en tout cas, les seuls que j'ai pu constaté ) ou VBA plante, c'est si ta macro dépasse le nombre de colonnes ou de lignes 2003, ou si tu essaie de copier des onglets de XLSM vers XLS. C'est simplement que celui qui l'ouvrira en XLS sous 2003 risque d'être déçu car les macros ne tourneront pas chez lui (Pierrot, que je salue, ainsi que Skoobi, te l'a confirmé tout à l'heure).
Je te met ton fichier de départ transformé en PJ pour les CodeName.
Bon courage

 

[david84]

Re : VBA Amélioration macro fichier association

Re JNP,

Je te met ton fichier de départ transformé en PJ pour les CodeName

Le seul changement constaté est le fait que aies renommé le nom des feuilles ("BD" devient "BD(BD)" par exemple). Est-ce cela que tu voulais dire lorsque tu disais

ou mieux, je modifierais le CodeName pour le faire correspondre au nom de l'onglet.

?

Le fichier comporte-t-il d'autres modifications ?

Excuse-moi d'être un peu "lourd" mais cela est nouveau pour moi et je veux être sûr de bien te comprendre afin de ne pas te faire perdre ton temps et ne pas tourner en rond.
A+

 

[Pierrot93]

Re : VBA Amélioration macro fichier association

Re,

je viens de découvrir que nous pouvons également travailler directement sur l'objet liste, je vous livre ci-dessous le résultat de mes tests... A noter, un cas ou il est obligatoire d'effecter une sélection de feuille...

Sub test()
Dim l As ListObject, ir As Range
Set l = Worksheets("BD").ListObjects(1)
Sheets("BD").Activate
l.Range.Activate
Set ir = l.InsertRowRange
ir.Value = Sheets("Création").Range("A2:O2").Value
End Sub

Merci David pour ce fil qui m'a permi de découvrir encore de nouvelles possibilités...

@+

 

[JNP]

Re : VBA Amélioration macro fichier association

Re ,

Le fichier comporte-t-il d'autres modifications ?

Oui. L'intérêt de la manip est de ne plus écrire

Sheets("BD").Range("A1")

mais directement

BD.Range("A1")

qui ne tiens pas compte du nom de l'onglet, mais de ce fameux CodeName qu'il suffit de changer dans la propriété (Name), je sais ça parrait pas logique , de la feuille .
L'autre avantage, c'est que lorsque tu saisis BD.r, une simple tab affiche BD.Range .
Bon courage

 

[skoobi]

Re : VBA Amélioration macro fichier association

Re

Skoobi, as-tu testé les modifications proposées sur le dernier fichier (message #18) ?

cela provoque un bug à



Ligne enlevée et pas de problème constaté.

Modification effectuée et pas de problème constaté


Cette modification provoque un bug.


La modification ne provoque pas de bug.

Je vais essayer de mon côté de comprendre pourquoi mais je voulais auparavant savoir si tu avais testé préalablement de ton côté ou pas.
Merci de ton implication.
A+

Salut David, JNP, Pierrot ,

Non David, je n'ai pas testé étant donné que j'ai excel 2003 et que certaines syntaxes ne sont pas reconnu...

 

[david84]

Re : VBA Amélioration macro fichier association

Re

Pierrot
J'ai essayé de mon côté mais cela provoque un bug.
J'ai même essayer de faire plus simple pour voir :
Lorsque je veux par exemple modifier

BD.Select
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
création.Range("A2:O2").Copy

par

BD.Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
création.Range("A2:O2").Copy

cela provoque un bug à la ligne

Range("Tableau4[N° DE FICHES]").Range("Tableau4[[#Headers],[DISCIPLINES]:[ASSOCIATIONS ]]").Range(Selection, Selection.End(xlDown)).Copy

soit une 20taine de lignes plus bas

Skoobi
merci pour la précision. Comme je ne connais pas les versions antérieures à 2007, je ne me doutais pas que les macros créées sous 2007 avaient quelques problèmes sur les versions antérieures.

JNP

J'ai fait les modifications comme suggéré et cela fonctionne
J'ai donc continué à "nettoyer" le code lorsque ce type de configuration se produisait. J'ai également modifié le code name comme tu me l'avais suggéré.

Pour plus de clarté je vous joins la dernière mise à jour pour que l'on sache où nous en sommes.

Merci à tous
David

 

[Pierrot93]

Re : VBA Amélioration macro fichier association

Re,

as tu testé le code de mon post 26 en l'état... fonctionne très bien chez moi sous 2003...

Attention dans le nom des variables, procédures ou nom de module évite l'accentuation, vba aime pas trop...