VBA Amélioration macro fichier association

david84

XLDnaute Barbatruc
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
 

Fichiers joints

Dernière édition:

skoobi

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour David :),

Sache que, si tu ne le sais pas encore, que les fonctions standards d'excel existe en VBA.
Pour ce qui est de vérifier que les champs soient remplis, je ferais la vérification avec NB.SI:

Code:
=NB.SI(E6:E17;"")
En VBA ça donne:

Code:
Application.WorksheetFunction.CountIf(Range("E6:E17"), "")
Petite précision: j'aurais aussi pu écrire
Code:
Application.CountIf....
plus court.

Ce qui donne:

Code:
' Dim i, nberreur As Integer
'   For i = 6 To 17
'    If Range("E" & i).Text = "" Then
'        nberreur = nberreur + 1
'        If nberreur = 1 Then
'            Range("E" & i).Select
'        End If
'    End If
'   Next
'   If nberreur > 0 Then
  If Application.WorksheetFunction.CountIf(Range("E6:E17"), "") = 12 Then
    MsgBox ("Merci de renseigner tous les champs ou d'entrer un tiret (-) pour pouvoir enregistrer les données.")
    Exit Sub
   End If
Bon dimanche.
 

YANN-56

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour david, skoobi, et à ceux qui passeront par ici,

David; puisque tu as parlé de mieux comprendre le VBA.

Je t'adresse un exemple de son utilisation un peu dans l'idée de ton Classeur.
(Les deux Classeurs devant être dans un même répertoire!)

Il s'agit d'un extrait d'une de mes vielles applis relative à la gestion des membres d'un Club,
mais le principe serait facilement adaptable à ce que tu cherches à faire.

J'ai tenté de nettoyer un peu, mais il reste plein de trucs mal dits et inutiles.

Regarde, et dis-moi si cela t'intéresse... Je pourrais t'aiguiller sur son adaptation.
(A noter qu'il n'y a strictement aucunes formules écrites sur les pages Excel)

A te lire,

Amicalement.

Yann
 

Fichiers joints

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re
Merci Skoobi et Yann de vous être penché sur mon fichier.
Skoobi : je vais tester le bout de code et et tiens au courant;).
Yann :
Code:
J'ai tenté de nettoyer un peu, mais il reste plein de trucs mal dits et inutiles.
De quel fichier parles-tu ?
Code:
(A noter qu'il n'y a strictement aucunes formules écrites sur les pages Excel)
Sur le fichier exemple il y en a une dans la feuille liste et d'autres dans la feuille création (ligne 2 et menus déroulants).
Je regarde également tes fichiers mais je préfère partir de celui que j'ai placé en exemple afin de ne pas me disperser:eek:.

Pour une meilleure compréhension de ma demande j'ai remplacé le fichier initial par une version modifiée (plus d'explications dans la feuille "création" et disparition du menu déroulant en E7 qui ne sert à rien dans ce contexte présent).
Merci à vous deux.
A+
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Bonjour à tous

David, si tu veux toucher plus de monde, enregistre peut être ton fichier en xls...

bon après midi
@+
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re Skoobi
j'ai apporté une légère modification à ton code pour qu'il fonctionne :
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
Cela fonctionne bien et je trouve que c'est une bonne idée.
Merci

Pierrot:), bonne idée bien sûr ! Je viens de modifier l'enregistrement du fichier (message 1) en y intégrant la modification suggérée par Skoobi.
Merci à vous tous!
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

ma petite contribution, tu pourrais remplacer ceci :
Code:
    Sheets("BD").Select
    Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Sheets("Création").Select
    Range("A2:O2").Copy
    Sheets("BD").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Range("A2").Select
par cela :
Code:
    Sheets("BD").Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow
    Sheets("Création").Range("A2:O2").Copy
    With Sheets("BD").Range("A2")
        .PasteSpecial Paste:=xlPasteValues
        .PasteSpecial Paste:=xlPasteFormats
    End With
en effet en vba, les "select" et "activate" sont rarement utiles...

A noter égalemeent, j'ai enlevé les arguments facultatifs de la méthode "PasteSpecial" car de toute façon c'est cette même valeur par défaut qui sera appliquée, puisque non modifiée...
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

une autre modif si j'ai bien compris le résultat à obtenir, remplacer :
Code:
        Range("A2").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
    Range("AA2").Select
    ActiveSheet.Paste
par :
Code:
Range("A2", Range("A65536").End(xlUp)).Copy Range("AA2")
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re
merci Pierrot mais après ta modif la macro bogue plus loin à
Range("A2:A3").AutoFill Destination:=Range("Tableau4[N° ENREG]")
.

Apparition d'une fenêtre contenant ce message : "erreur d'exécution 1004. La méthode autofill de la classe range a échoué".
J'avais d'ailleurs essayé par moi-même d'ôter les différents select mais cela provoquait des bogs...

Je vais essayer de comprendre pourquoi de mon côté.

A+
 
Dernière édition:

YANN-56

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

David,

Je ne parlais que de mon exemple...

De ne pas vouloir se disperser est respectable...
D'autant que tu es entre de bonnes mains.

Bonne suite.

Amicalement

Yann
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

il y a sans doute lieu de préciser le nom de la feuille :
Code:
Sheets("NomFeuille").Range("A2:A3").AutoFill Destination:=Range("Tableau4[N° ENREG]")
par contre dans le fichier joint, je ne trouve pas ta plage nommée "Tableau4[N° ENREG]"....
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re
par contre dans le fichier joint, je ne trouve pas ta plage nommée "Tableau4[N° ENREG]"....
cette plage correspond à la 1ère colonne du tableau de l'onglet "BD".
Sur Excel 2007 je ne connais pas les autres versions donc je ne sais si c'est la même chose), à partir du moment où tu insères un tableau (tableau4), excel utilise le nom des colonnes lorsque tu enregistres la macro.
 

david84

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re
Pierrot, lorsque je modifie de la sorte, cela ne bogue pas :
Sheets("BD").Select
Rows("2:2").Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrBelow

Sheets("Création").Range("A2:O2").Copy
With Sheets("BD").Range("A2")
.PasteSpecial Paste:=xlPasteValues
.PasteSpecial Paste:=xlPasteFormats
End With

Range("A2").Select
C'est mieux mais tous les "select" ne sont pas enlevés...
A+
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

oui à priori chez moi sous 2003 ce n'est pas le cas... mais je ne suis pas un très grand spécialiste de ce genre de tableau...
 

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

essaye peut être ainsi :
Code:
With Sheets("NomFeuille")
[COLOR="red"][B].[/B][/COLOR]Range("A2:A3").AutoFill Destination:=[B][COLOR="Red"].[/COLOR][/B]Range("Tableau4[N° ENREG]")
End With
mais sans pouvoir tester, difficile à dire, mais jamais vu, sous 2003 ce genre de synthaxe pour un objet "range" :
Code:
Range("Tableau4[N° ENREG]")
 

david84

XLDnaute Barbatruc
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 ?
 
Dernière édition:

Pierrot93

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re,

celle ci :
Code:
    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

XLDnaute Barbatruc
Re : VBA Amélioration macro fichier association

Re

j'ai modifié et cela ne bogue plus. Cependant :
Code:
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 !:eek:).
Merci pour ton aide précieuse.

Edit : merci à Yann pour son mot d'encouragement
 

Fichiers joints

skoobi

XLDnaute Barbatruc
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 ;)):

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
Code:
27       Range("A2").Select
Cette ligne est inutile à mon avis (ah cet enregistreur de macro...)

Code:
60        Sheets("listes").Select
       Range("A2:D2").Select
       Range(Selection, Selection.End(xlDown)).ClearContents
devient:
Code:
60       With Sheets("listes")
.Range("A2", .Range("A2").End(xlDown)).ClearContents
End With
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
3 lignes en moins ;).

Code:
87       Range("AA2").Select
       Range(Selection, Selection.End(xlToRight)).Select
       Range(Selection, Selection.End(xlDown)).ClearContents
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
Tous ces exemples devraient t'aider pour faire le reste.
 

david84

XLDnaute Barbatruc
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("A2:D2").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+
 

Discussions similaires


Haut Bas