Faire varier le nom d'une variable
Bonjour à tous et désolé pour le titre un peu flou.
J'ai le code suivant, répété de nombreuse fois avec d'autres termes à la place de "Ctrl". Je souhaiterais faire une boucle sur les différents noms pour alléger un peu le code.
TC_Ctrl_a = 0
C_Ctrl_a = 0
Moy_Ctrl_a = 0
L_Ctrl_a = 0
TL_Ctrl_a = 0
[...]
If .Range("B" & i).Value = "CONTROLE" Then
If (DateDiff("d", Date_Fin, Date_Debut) + 1) < 4 And (DateDiff("d", Date_Fin, Date_Debut) + 1) > 0 Then
TC_Ctrl_a = TC_Ctrl_a + 1
End If
If (DateDiff("d", Date_Fin, Date_Debut) + 1) < 15 And (DateDiff("d", Date_Fin, Date_Debut) + 1) > 3 Then
C_Ctrl_a = C_Ctrl_a + 1
End If
If (DateDiff("d", Date_Fin, Date_Debut) + 1) < 31 And (DateDiff("d", Date_Fin, Date_Debut) + 1) > 14 Then
Moy_Ctrl_a = Moy_Ctrl_a + 1
End If
If (DateDiff("d", Date_Fin, Date_Debut) + 1) < 91 And (DateDiff("d", Date_Fin, Date_Debut) + 1) > 30 Then
L_Ctrl_a = L_Ctrl_a + 1
End If
If (DateDiff("d", Date_Fin, Date_Debut) + 1) > 90 Then
TL_Ctrl_a = TL_Ctrl_a + 1
End If
End ifJ'ai tenté de faire une boucle sur un range (colonne AZ) avec
For i = 1 to 99
cellsec = .Cells(i, 52).Value
TC_ & cellsec & _a = 0
etc...
next iMais ça part en erreur.
Bonjour,
et on trouve quoi dans tes cellules : " .Cells(i, 52).Value"
je ne vois pas rien qui ressemble à ton
TC_ & cellsec & _a = 0 dans ton premier code.
Cdt.
Bonsoir,
pierre.jy a écrit :Je ne vois pas rien qui ressemble à ton
TC_ & cellsec & _a = 0dans ton premier code.
Normal, c'est à prendre au second degré...
Laisse béton ! Sans vouloir offenser notre ami, c'est une ineptie : Tu ne peux pas remplacer une variable par un "machin" non déclaré...
Du coup ton TC_ ne ressemble à rien et ton _a non plus.
En fait le problème vient du fait que tu emploies des noms de variables (sans vouloir t'offenser...) ALAKON !
Vire donc tous ces "_" qui rendent ton code incompréhensible et utilise des noms du genre TC TL MC ou Libellule, Papillon... De toute façon les noms de variables ne se saucissonnent pas.
Il y a surement quelque chose a faire avec cette variable vu que effectivement tu veux lui faire parcourir toute une plage, mais sans le classeur KIVABIEN avec, je crains fort que tu ne trouves guère de solution.
A+
Hi,
C'est vrai que c'est pas clair vu de l’extérieur.
Les TC_,TL_ etc... signifie (dans ma topologie) TrèsCourt, TrèsLong, etc... De même, _m et _a indiquent si je traite les données pour le mois sélectionné ou l'année complète.
Le fichier répertorie des arrêts de travail de personnes bossant dans différent services (que j'avais listé en colonne AZ, soit la 52eme) et je cherche à sortir le nombre d'arrêt de telle ou telle longueur (très court, court, etc...).
Je cherche donc à obtenir des chiffres avec des indices sous la forme Temps d'arrêt & Service en question & Durée visée (mois ou année). Je fais partir tous mes indices à 0 et je fais parcourir ma colonne en incrémentant de 1 l'indice correspondant.
Voici un exemple du tableau en question (expurgé car contenant des données d'entreprise). Le bouton ne sert qu'à changer de mois mais je l'ai cassé en dégageant rapidement des infos, sinon il marche bien).
Dans les faits, je fini par obtenir deux tableaux (avec leurs graph), répertoriant pour chaque année le nombre d'absence de chaque type. idem pour le mois sélectionné (qui me donnera par exemple les absences des mois de Mars pour l'année 2016, 2017, etc...), et en tenant compte de la durée des arrêts (n Très courts, x Courts, etc...)
Là où ça se corse, c'est que je voudrait un truc efficace pour avoir ces tableaux... pour chaque service (8 ou 10 au total).
Ajouter bêtement les services à la suite ne marche pas, la procédure se révélant trop longue. L'idée est donc de boucler sur un range plutôt que de copier n fois le même bloc en changeant juste le nom du service.
J'espère avoir été clair. enfin à peu près :$
Bonjour,
Tes explications et surtout le document joint n'éclairent en rien le sujet.
J'intègre tes explications sur la signification des noms de variable mais les "_" n'apportent rien de plus.
Pour le reste on a une rondelle de macro et un fichier complètement extrait du contexte :
A supposer qu'on arrive à y retrouver l'intégralité de la macro...
On ne va pas s'amuser à parcourir l'historique de tous tes posts pour comprendre la question.
A+
Les "_" servent juste de séparateur. J'aurais pu mettre des "-" ou autre chose à la place (voir rien du tout). Mais du coup c'est peut-être (surement) un caractère réservé, donc autant partir sur quelque chose de plus neutre.
Le fichier Excel sert juste à montrer sa structure. Quant à l'absence de macro, vu que je me suis manifestement vautré autant repartir sur un truc neuf plutôt que d'essayer de patcher quelque chose de bancal.
Mais anyway si tu tiens à te faire mal aux yeux, voilà le fichier avec la macro (qui se trouve dans le bouton "OK" de l'userform qui pop en changeant de mois). Le code est tellement dégueulasse qu'à mon avis je ferais mieux de tout reprendre depuis le début.
Ha ! Voilà qui me semble plus clair.
Ce qui l'est moins c'est la macro en question...
Plus exactement le problème n'est pas la clarté mais le manque d'optimisation.
Une bonne macro devrait se voir dans un écran (sans avoir besoin de scroller...) disait-on il y a quelques années !
Si la capacité des mémoires ont pas mal évolués depuis et permettent de s'affranchir un peu de cette indication, une macro de quelques 1000 lignes est quand même un peu... déraisonnable !
Bon je vais me pencher un peu et essayer de voir ce qu'on peut en tirer.
Avis de concours : Je ne crains pas la concurrence et tous les fous furieux VBA ou autre masos du codage peuvent se mettre en action !
Nota : Les premiers arrivés ne seront pas forcément les gagnants !
A+
Bonsoir,
Je donne ici l'état actuel de ma réflexion.
Je précise que je n'ai pas essayer de reprogrammer ce que tu as fait car en fait je n'ai pas trop compris la logique...
Je me suis contenté de convertir tes variables en variables Array et de leur donner des noms avec une sémantique compréhensible.
J'ai toutefois relevé ce qui me semble une erreur qui affecte tes résultats sur la plage T2:T3 et V2:V3 et par voie de conséquence le graphique 2
Dans ton classeur l'erreur se situe dans ces lignes :
For i = 2 To derligne
If .Range("J" & i) = LeType Then
Debut = Month(.Range("H" & i))
Fin = Month(.Range("I" & i))
Date_Debut = .Range("H" & i).Value 'Date _Debut ???
Date_Debut = .Range("I" & i).Value 'Date _Debut ? encore ?
If Year(.Range("H" & i)) = k Or Year(.Range("I" & i)) = k Then ...Je n'ai pas su corriger. On pourrait croire qu'il suffit de corriger avec Date_Fin sur la seconde ligne, mais le résultat n'est pas plus convaincant. Par suite mon adaptation pour cette section est tout aussi fausse...
En dehors de ce problème mon code donne un correctif plutôt pas mal optimisé -je trouve- pour simplifier ta production.
Par ailleurs selon mes premiers essais les données et les graphiques 1 et 3 semblent cohérents (en comparaison avec les tiens)
J'espère que tu as une bonne provision de Doliprane !
Bon j'ai pas trop commenté hein ! Difficile de commenter un code d'un millier de ligne concentré en une centaine :
Tu devras faire un petit effort d'abstraction avec les boucles et les conditions pour comprendre la correspondance des indices...
J'ai supprimé toutes les variables inutiles
Demain j'essaierai de corriger ce que tu as voulu faire dans le deuxième graphique : Pas sur que j'y parvienne... Tu t'es noyé dans un fatras de variables et de DateDiff assez obscur.
Nota :
% = As Integer
& = As Long
$ = As String
A+
Plus exactement le problème n'est pas la clarté mais le manque d'optimisation.
Une bonne macro devrait se voir dans un écran (sans avoir besoin de scroller...) disait-on il y a quelques années !
Si la capacité des mémoires ont pas mal évolués depuis et permettent de s'affranchir un peu de cette indication, une macro de quelques 1000 lignes est quand même un peu... déraisonnable !
Ça je m'en rends bien compte, c'est pour ça que je me suis dit qu'il valait mieux tout remettre à plat
D'où la volonté de caser des boucles (la façon qui me semble la plus simple pour raccourcir un code récurent).
Bien vu pour la faute de frappe (c'est aussi un problème qui tend à apparaitre avec les macros longues comme le mois de mai), la colonne I étant bien celle de la fin de période.
Je vais me pencher sur ton code et faire des test dans la journée.
Bon, j'ai lu le code, ce qui est bien ici c'est qu'on apprend plein de choses fortuitement
- Pour les déclarations Dim... je note l'emploi des $, % et &. Par contre je vois que tu as ajouté des arguments dans la déclaration (0 to 9). Pour quelle raison ? Est-ce obligatoire ?
- A un moment tu fais un Erase de deux choses ( n_Serv et m_Serv). Est-ce équivalent à un Set n_Serv = Nothing ? Leur donner la valeur 0 n'est-il pas mieux, ou les effaces-tu pour libérer de la mémoire ?
- J'ai noté l'emploi de Range("").Value2. A quoi sert ce 2 ?
- A un moment tu ouvres des boucles For ii = 0 to 9... Next. Pourquoi mettre Next et non pas Next ii ?
- En fin de sub je note l'emploi de Unload Me, je présume que c'est pour libérer de la mémoire comparé au Userform1.Hide que j'avais mis ?
- Enfin concernant la boucle sur les services, quid si leur nom n'est pas Serv + numéro mais plutôt des noms en toutes lettres (genre Ressources Humaines, Mécanique 2, Cuisine...) ? Peut-on boucler dessus quitte à faire une liste des services sur une colonne ?
Merci !
Ajouter des arguments dans la déclaration (0 to 9)
Ce sont des variables tableau (Array). Sur ce sujet je te renvoie à cette documentation.
J'ai utilisé cette forme car c'est la plus explicite : Le premier venu comprend que la variable comprend 10 éléments de 0 à 9...
J'aurais pu écrire aussi (notation équivalente) :
n_Serv%(10)
Mais avec cette notation les gens oublient facilement que s'il y a bien 10 éléments le premier est toujours 0...
Erase :
C'est un moyen commode de réinitialiser la variable (Avec ou sans Set, nothing ne peut être affectéé a un Array.
Value2 :
j'ai noté que tu utilisait des variable "Long" pour stocker les Dates. C'est une excellente idée... à condition de ne pas chercher à les convertir en Date après !
Value2 est une excellent habitude pour le stockage des dates dans des variables de type Long. Je vais pas te faire une longue théorie sur ce sujet. Sache simplement que ça évite en particulier bien des confusions de dates entre des données de type 12/07/2015 et 07/12/2015 Avec Value2 VBA comprend qu'il faut pas chercher midi à 14 heures et que 12/07 en France c'est 12 Juillet et pas 7 décembre...
Autre avantage des Dates sous forme Long : pas besoin de DateDiff pour obtenir une différence (nombre de jours) DateFin - DateDeb est très intuitif...
Pourquoi mettre Next et non pas Next ii
Je le fais parfois... Quand je n'oublie pas ! Bien que ce ne soit pas obligatoire, c'est vrai que sur de grand pensum comme cette macro, c'est bien utile !
Unload Me :
Tu présumes bien !
La boucle sur les services, quid si leur nom n'est pas Serv + numéro
Tu te débrouilles de la manière la plus simple pour toi ! Mais la clef du truc étant un indice il faut qu'a chaque indice corresponde... le bon service !
Moi j'utilise beaucoup Split mébon chacun fait comme il aime !
Sub test()
Var = "Compta"
a = Split("RH Compta Accueil Secrétariat")
For i = 0 To UBound(a)
If Var = a(i) Then MsgBox Var & " est service " & i
Next
End SubNota :
Faire une liste des services sur une colonne offre une complication supplémentaire : tu dois alors gérer un tableau à 2 dimensions.
Bon j'ai pas encore trouvé la source de mon erreur pour T3 et V3 : Je suppose que j'ai zappé quelque chose...
Bon courage.
Merci pour es éclaircissement.
Je crois que Array rejoint ce que j'avais en tête. J'essaie de réécrire un truc propre et je reviens vers vous.
Hello !
J'ai retravaillé le code à partir de zéro en tentant d'utiliser les Array, mais j'ai encore quelques lignes qui bloquent, à savoir :
- La fonction pour vérifier si une feuille existe déjà (honteusement repompée ailleurs).
- La ligne suivante (vers la fin) qui me sort une erreur 1004 :
ThisWorkbook.Sheets(LesServices(i)).Range(.Cells(k - 2014, j + 8)).Value = MkMOISRésolu, faut juste virer le Range
Edit : Et en plus ça ne marche pas, j'ai Zéro partout :/
Edit quelques jours plus tard : Trouvé pour ça, c'était une bête histoire de parenthèse.
Ci-dessous le code en question (dont j'ai modifié le nom et le nombre de services pour raison de confidentialité)
Option Explicit
Function FeuilleExiste(wk As Workbook, stFeuille) As Boolean
On Error Resume Next
FeuilleExiste = Not (wk.Sheets(stFeuille) Is Nothing)
End Function
Private Sub CommandButton1_Click()
' % = As Integer
' & = As Long
' $ = As String
Dim k%, i%, j%, h%, derligne%, derligneSERV%, MkMOIS%, MkANNEE%, TC%, C%,M%,L%,tL%,TCm%,Cm%,Mm%,Lm%,TLm%
Dim DateDeb&, DateFin&, DSt&, DEnd&
Dim LeMois$
Dim LesServices as Variant, LesTypes as Variant
LesServices = VBA.Array("Serv1", "Serv2", "Ours_Blanc", "Serv6")
LeMois = Month(CDate("01/" & ComboBox1.Value))
LesTypes = VBA.Array("AT", "MALA", "MALM", "MANP")
With ThisWorkbook.Sheets("Listing")
derligne = .Range("A" & Rows.Count).End(xlUp).Row
For k = 2016 To Year(Now)
.Range("R" & k - 2014).Value = ComboBox1.Value & " " & k 'Marquage du mois et de l'année dans la cellule
.Range("AF" & k - 2014).Value = ComboBox1.Value & " " & k
.Range("L" & k - 2014).Value = k 'Marquage de l'année dans la cellule
.Range("Y" & k - 2014).Value = k
For i = LBound(LesServices) To UBound(LesServices)
' If FeuilleExiste(ThisWorkbook, i) Then 'Verification si la feuille du service existe
' derligneSERV = ThisWorkbook.Sheets(i).Range("A" & Rows.Count).End(xlUp).Row
' ThisWorkbook.Sheets(i).Range("A2:Y" & derligneSERV).ClearContents 'On efface les anciennes données
'' Else
' Sheets.Add After:=Sheets(Sheets.Count)
' Sheets(Sheets.Count).Name = LesServices(i) 'Si non : création d'une feuille correspondant au service avec les entetes
ThisWorkbook.Sheets(LesServices(i)).Range("B1, H1").Value = "AT"
ThisWorkbook.Sheets(LesServices(i)).Range("C1, I1").Value = "MALA"
ThisWorkbook.Sheets(LesServices(i)).Range("D1, J1").Value = "MALM"
ThisWorkbook.Sheets(LesServices(i)).Range("E1, K1").Value = "MANP"
ThisWorkbook.Sheets(LesServices(i)).Range("N1, U1").Value = "1-3"
ThisWorkbook.Sheets(LesServices(i)).Range("O1, V1").Value = "4-14"
ThisWorkbook.Sheets(LesServices(i)).Range("P1, W1").Value = "15-30"
ThisWorkbook.Sheets(LesServices(i)).Range("Q1, X1").Value = "30-90"
ThisWorkbook.Sheets(LesServices(i)).Range("R1, Y1").Value = "91+"
' End If
ThisWorkbook.Sheets(LesServices(i)).Range("G" & k - 2014).Value = ComboBox1.Value & " " & k
ThisWorkbook.Sheets(LesServices(i)).Range("T" & k - 2014).Value = ComboBox1.Value & " " & k
ThisWorkbook.Sheets(LesServices(i)).Range("A" & k - 2014).Value = k
ThisWorkbook.Sheets(LesServices(i)).Range("M" & k - 2014).Value = k
' Mise à zéro des compteurs annuel et mensuels (Xm)
TC = 0
C = 0
M = 0
L = 0
TL = 0
TCm = 0
Cm = 0
Mm = 0
Lm = 0
TLm = 0
For j = LBound(LesTypes) To UBound(LesTypes)
MkANNEE = 0 'Mise à zéro du compteur pour le service en cours, le type d'arrêt en cours et l'année en cours
MkMOIS = 0 'Idem, mais en plus pour le mois spécifié
For h = 2 To derligne
DateDeb = .Range("H" & h).Value2
DateFin = .Range("I" & h).Value2
DSt = Month(.Range("H" & h).Value2)
DEnd = Month(.Range("I" & h).Value2)
If ((.Range("B" & h).Value = LesServices(i)) And (.Range("J" & h).Value = LesTypes(j)) And ((Year(.Range("H" & h).Value2) = k) Or (Year(.Range("I" & h).Value2) = k))) Then
MkANNEE = MkANNEE + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
MkMOIS = MkMOIS + 1
End If
If .Range("J" & h).Value <> "AT" Then
If ((DateFin - DateDeb) <= 3) Then
TC = TC + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
TCm = TCm + 1
End If
End If
If ((DateFin - DateDeb) <= 14) And ((DateFin - DateDeb) > 3) Then
C = C + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
Cm = Cm + 1
End If
End If
If ((DateFin - DateDeb) <= 30) And ((DateFin - DateDeb) > 14) Then
M = M + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
Mm = Mm + 1
End If
End If
If ((DateFin - DateDeb) <= 90) And ((DateFin - DateDeb) > 30) Then
L = L + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
Lm = Lm + 1
End If
End If
If ((DateFin - DateDeb) > 90) Then
TL = TL + 1
If ((LeMois >= DSt) And (LeMois <= DEnd)) Then
TLm = TLm + 1
End If
End If
End If
End If
Next h
ThisWorkbook.Sheets(LesServices(i)).Cells(k - 2014, j + 8).Value = MkMOIS
ThisWorkbook.Sheets(LesServices(i)).Cells(k - 2014, j + 2).Value = MkANNEE
ThisWorkbook.Sheets(LesServices(i)).Range("N" & k - 2014).Value = TC
ThisWorkbook.Sheets(LesServices(i)).Range("O" & k - 2014).Value = C
ThisWorkbook.Sheets(LesServices(i)).Range("P" & k - 2014).Value = M
ThisWorkbook.Sheets(LesServices(i)).Range("Q" & k - 2014).Value = L
ThisWorkbook.Sheets(LesServices(i)).Range("R" & k - 2014).Value = TL
ThisWorkbook.Sheets(LesServices(i)).Range("U" & k - 2014).Value = TCm
ThisWorkbook.Sheets(LesServices(i)).Range("V" & k - 2014).Value = Cm
ThisWorkbook.Sheets(LesServices(i)).Range("W" & k - 2014).Value = Mm
ThisWorkbook.Sheets(LesServices(i)).Range("X" & k - 2014).Value = Lm
ThisWorkbook.Sheets(LesServices(i)).Range("Y" & k - 2014).Value = TLm
Next j
Next i
Next k
End With
Calculate
Unload Me
End Sub