Minh_Tri
Messages postés6Date d'inscriptionlundi 8 janvier 2018StatutMembreDernière intervention10 janvier 2018
-
8 janv. 2018 à 10:47
Minh_Tri
Messages postés6Date d'inscriptionlundi 8 janvier 2018StatutMembreDernière intervention10 janvier 2018
-
10 janv. 2018 à 09:03
Bonjour à tous,
Je vais essayer d'être le plus claire possible.
L'outil que je dois mettre en place sous Excel, en VBA, doit récupérer, dans une base de données externe (pas dans le même classeur et répertoire), des coûts en fonction des études et des outils que l'on choisi :
- Sur une première feuille (Projet) on retrouve des cases à cocher qui représentent les études demandées. Voici le code lié à ces cases à cocher :
"demande" est la variable qui va contenir l'étude que l'on veut. Comme le nombre d'études est grand j'ai préféré faire un code global que va reprendre chaque case à cocher. Voici le code en question :
Private Sub MacroDemande()
'__________________________________'
'AJOUT D'UNE ETUDE' '__________________________________'
Dim ws As Worksheet
Application.ScreenUpdating = False
With Sheets("Projet") If .OLEObjects("CheckBox" & i).Object.Value = True Then For Each ws In ThisWorkbook.Worksheets If ws.Range("J15").Value <> "" Then ws.Range("P28").End(xlDown).Offset(1, 0).Value = demande End If Next ws Else For Each ws In ThisWorkbook.Worksheets If ws.Range("J15").Value <> "" Then Set Search = ws.Range("P28:P100").find(demande, LookIn:=xlValues, lookat:=xlWhole) If Not Search Is Nothing Then With ws .Range(Search.Address, Search.Offset(0, 1).Address).Delete .Range("P60:Q60").Copy .Range("P61:Q61").PasteSpecial Paste:=xlPasteFormats .Range("P60:Q60").Clear End With End If End If Next ws End If End With
End Sub
Ce code va permettre d'entrer l'étude cochée dans un tableau (Tableau des études) sur d'autres feuilles. Le 'copy/paste sert juste à garder la mise en forme du tableau car le 'Delete supprime la ligne et donc fait remonter la mise en forme. Le tableau est composé de 2 colonnes, en P les études et en Q le coût qui va être récupérer plutard.
- Sur la deuxième feuille (Chiffrage, qui contient le tableau décrit ci-dessus), on retrouve une barre de recherche pour saisir les outils à chiffrer, un tableau récapitulatif (Tableau des spécifiques), et une liste déroulante. On s'intéresse ici à la liste déroulante :
Private Sub ListBoxSpe_Click()
'_________________________________________'
'LISTE DES SPECIFIQUES' '_________________________________________'
nbOP = Range("J15").Value
Application.ScreenUpdating = False
For i = 0 To ListBoxSpe.ListCount - 1 If ListBoxSpe.Selected(i) = True Then spe = ListBoxSpe.Value 'Mémorisation de l'outil choisi dans la listbox If spe <> "" Then Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole) Set OP = Sheets(ws).Rows(202).find(nbOP, lookat:=xlWhole) Set list = Sheets(ws).Columns(1).find(spe, LookIn:=xlValues, lookat:=xlWhole) 'Recherche du spécifique dans le tableau If Not list Is Nothing Then Range("T30:T60").ClearContents Range("P30").Select Do While Not IsEmpty(ActiveCell) demande = ActiveCell.Value 'Mémorisation de l'option Set recherche = Range("A201:L260").find(demande, lookat:=xlPart) 'Recherche de l'option dans la bdd If Not recherche Is Nothing Then 'Implantation des options sélectionnées pour le spécifique recherché Range("S29").Value = spe Range("T28").End(xlDown).Offset(1, 0).FormulaLocal = "=INDEX([BaseSource.xlsx]Feuil1!A1:K260;" & list.Row & ";" & recherche.Column & ")" & "*" & Cells(list.Row, OP.Column).Address ActiveCell.Offset(1, 0).Select End If Loop
'Sélection de la ligne du spécifique dans le tableau Cells(list.Row, OP.Column).Select
End If Range(tableau.Address, tableau.Offset(0, 3).Address).Select Union(Range(tableau.Address, tableau.Offset(0, 3).Address), Range(Cells(list.Row, OP.Column).Address)).Select
ElseIf Range("S29") <> "" Then Range("S29").ClearContents Range("T30:T60").ClearContents End If End If Next i
End Sub
Elle permet dans un premier temps de visualiser les outils que l'on a saisie, en cliquant sur un outil on sélectionne la ligne où se trouve l'outil dans le tableau des spécifiques (pour ne supprimer que cet outil si on le souhaite), et enfin d'afficher le coût lié seulement à cet outil.
Pour récapituler, on choisit d'abord les études qui nous concerne (AVP, 3D, Elec... dans la première feuille "Projet"). En fonction des études qu'on à cocher, sur la feuille "Chiffrage" dans le tableau des études ne va apparaître seulement ces études. Sur cette feuille "Chiffrage" on va chercher des outils avec la barre de recherche que l'on va sélectionner (par double clique sur la cellule) pour faire apparaître les coûts d'études dans le tableau des études.
Mon problème c'est lorsque j'ai une sélection dans ma listbox (feuille "Chiffrage") et que je décoche une étude (feuille "Projet") ça m'affiche une erreur : "Erreur d'exécution '1004': Erreur définie par l'application ou par l'objet" et la ligne surlignée est la suivante :
Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole)
tableau est la variable qui me permet de trouver l'outil sélectionner dans le tableau des spécifiques afin de sélectionner toute la ligne.
Je ne comprend pas pourquoi lorsque l'on fait cette opération cela ne marche pas sachant que les 2 codes ne sont pas liés.
J'ai essayé de vous expliquer mon problème le plus clairement possible en me focalisant sur les contrôles active X concernés sans trop entrer dans les détails de mon outil Excel mais s'il reste des parties où vous êtes dans le flou n'hésitez pas à me le faire savoir ! En espérant que j'ai bien respecté les normes de demande d'aide du forum ^.^".
Il faut savoir que je suis débutant en VBA et que j'ai tout appris sur internet.
J'ai voulu illustré toute cette histoire avec des photos mais je n'ai pas réussi à insérer d'images "Request Error".
Pour les plus courageux je vous remercie d'avance et je suis à votre disposition pour toutes informations complémentaires :).
Patrice33740
Messages postés8556Date d'inscriptiondimanche 13 juin 2010StatutMembreDernière intervention 2 mars 202321 8 janv. 2018 à 11:20
Bonjour,
La programmation demande beaucoup de rigueur, je vois que tu ne déclares pas tes variables, c'est la première des règles à appliquer : pour cela, commences tous tes modules par Option Explicit.
Autre source potentielle d'erreur, dans le module ListBoxSpe_Click, tu utilises Range() sans préciser le feuille Tu le fais très bien dans le module MacroDemande avec With ws et .Range()
Minh_Tri
Messages postés6Date d'inscriptionlundi 8 janvier 2018StatutMembreDernière intervention10 janvier 2018 8 janv. 2018 à 11:50
Bonjour et merci pour ta réponse,
J'ai oublié de préciser que mes variables sont déclarées dans un module en public. Mais j'ai mis
Option Explicit
comme tu l'as indiqué et il a bien relevé des variables non déclarés donc je t'en remercie.
J'ai réécrit le module ListBoxSpe_Click plus correctement :
Private Sub ListBoxSpe_Click()
'_________________________________________'
'LISTE DES SPECIFIQUES' '_________________________________________'
nbOP = Range("J15").Value
Application.ScreenUpdating = False
For i = 0 To ListBoxSpe.ListCount - 1 If ListBoxSpe.Selected(i) = True Then spe = ListBoxSpe.Value 'Mémorisation de l'outil choisi dans la listbox If spe <> "" Then Set tableau = Sheets(ws).Range("H29:H60").find(spe, lookat:=xlWhole) Set OP = Sheets(ws).Rows(202).find(nbOP, lookat:=xlWhole) Set list = Sheets(ws).Columns(1).find(spe, LookIn:=xlValues, lookat:=xlWhole) 'Recherche du spécifique dans le tableau If Not list Is Nothing Then With Sheets("Chiffrage") .Range("T30:T60").ClearContents .Range("P30").Select Do While Not IsEmpty(ActiveCell) demande = ActiveCell.Value 'Mémorisation de l'option Set recherche = Range("A201:L260").find(demande, lookat:=xlPart) 'Recherche de l'option dans la bdd If Not recherche Is Nothing Then 'Implantation des options sélectionnées pour le spécifique recherché .Range("S29").Value = spe .Range("T28").End(xlDown).Offset(1, 0).FormulaLocal = "=INDEX([BaseSource.xlsx]Feuil1!A1:K260;" & list.Row & ";" & recherche.Column & ")" & "*" & Cells(list.Row, OP.Column).Address ActiveCell.Offset(1, 0).Select End If Loop
'Sélection de la ligne du spécifique dans le tableau .Cells(list.Row, OP.Column).Select End With End If With Sheets("Chiffrage") .Range(tableau.Address, tableau.Offset(0, 3).Address).Select Union(Range(tableau.Address, tableau.Offset(0, 3).Address), Range(Cells(list.Row, OP.Column).Address)).Select End With ElseIf Range("S29") <> "" Then With Sheets("Chiffrage") .Range("S29").ClearContents .Range("T30:T60").ClearContents End With End If End If Next i
End Sub
Tu as bien raison de relever mon manque de rigueur, je ferai plus attention la prochaine fois, mais pour le coup ici le problème persiste :/.
Patrice33740
Messages postés8556Date d'inscriptiondimanche 13 juin 2010StatutMembreDernière intervention 2 mars 202321 8 janv. 2018 à 12:37
Pourquoi déclares-tu toutes tes variables publiques alors qu'un portée privée dans le module suffirait, c'est aussi une source d'erreur.
Il faut déclarer les variables avec le type ad hoc (pas toutes en variant) et la bonne portée.
Minh_Tri
Messages postés6Date d'inscriptionlundi 8 janvier 2018StatutMembreDernière intervention10 janvier 2018 8 janv. 2018 à 13:32
J'utilise plusieurs même variables dans des modules différents. Serai-t-il plus préférable de les déclarer à chaque module en privé?
Oui mes variables sont pour la plupart en Range, Integer et String.
Patrice33740
Messages postés8556Date d'inscriptiondimanche 13 juin 2010StatutMembreDernière intervention 2 mars 202321 Modifié le 8 janv. 2018 à 14:21
Il est préférable de les déclarer dans chaque procédure (avec Dim).
Dans ton code il n'y a aucune variables qui dusse avoir la portée publique ni même la portée privée d'un module.
Les variables publiques ne sont utiles que lorsque les modifications apportées par une procédure sont utilisées par une procédure d'un module d'un autre classeur. Tu peux limiter la portée publique au seul fichier dans lequel elles sont déclarée en utilisant Option Private Module pour limiter la portée à une procédure d'un autre module du même classeur.
Tu peux utiliser des variables privées dont la portée est limitée au module en les déclarant en tête de module (sans Public et avec ou sans Private) lorsque les modifications apportées par une procédure doivent être utilisées par une autre procédure du même module.
Il y a peu de cas où l'utilisation des variables publiques ou privées est indispensable. C'est le cas des déclarations de procédures, des constantes universelles, ...
En général, il est préférable d'utiliser une variable dont la portée est limitée à la procédure et quand c'est nécessaire, de la transmettre dans les arguments de la procédure ou de la fonction appelée.
Patrice33740
Messages postés8556Date d'inscriptiondimanche 13 juin 2010StatutMembreDernière intervention 2 mars 202321 8 janv. 2018 à 14:33
Exemple :
Option Explicit
Private Sub CheckBox1_Click()
Dim MaDemande As String
Dim Numero As String
MaDemande = "AVP"
Numero = "1"
Call MacroDemande(MaDemande, Numero)
End Sub
Private Sub MacroDemande(demande As String, i As String)
Dim ws As Worksheet
'... même code qu'avant
End Sub
8 janv. 2018 à 11:50
J'ai oublié de préciser que mes variables sont déclarées dans un module en public. Mais j'ai mis comme tu l'as indiqué et il a bien relevé des variables non déclarés donc je t'en remercie.
J'ai réécrit le module ListBoxSpe_Click plus correctement :
Tu as bien raison de relever mon manque de rigueur, je ferai plus attention la prochaine fois, mais pour le coup ici le problème persiste :/.
8 janv. 2018 à 12:37
Il faut déclarer les variables avec le type ad hoc (pas toutes en variant) et la bonne portée.
8 janv. 2018 à 13:32
Oui mes variables sont pour la plupart en Range, Integer et String.
Modifié le 8 janv. 2018 à 14:21
Dans ton code il n'y a aucune variables qui dusse avoir la portée publique ni même la portée privée d'un module.
Les variables publiques ne sont utiles que lorsque les modifications apportées par une procédure sont utilisées par une procédure d'un module d'un autre classeur. Tu peux limiter la portée publique au seul fichier dans lequel elles sont déclarée en utilisant Option Private Module pour limiter la portée à une procédure d'un autre module du même classeur.
Tu peux utiliser des variables privées dont la portée est limitée au module en les déclarant en tête de module (sans Public et avec ou sans Private) lorsque les modifications apportées par une procédure doivent être utilisées par une autre procédure du même module.
Il y a peu de cas où l'utilisation des variables publiques ou privées est indispensable. C'est le cas des déclarations de procédures, des constantes universelles, ...
En général, il est préférable d'utiliser une variable dont la portée est limitée à la procédure et quand c'est nécessaire, de la transmettre dans les arguments de la procédure ou de la fonction appelée.
Plus de détails sur les variables ici :
http://www.mdf-xlpages.com/modules/publisher/item.php?itemid=51 et
http://silkyroad.developpez.com/VBA/LesVariables/
8 janv. 2018 à 14:33