VBA : nom d'une variable obtenu à l'aide d'une concaténation

[CISCO]

Bonjour à tous

J'ai une série de variables ayant des noms semblables, du style lig4, lig5, lig6... Je les utilise dans une série de boucles FOR ligx = 0 to ...

Est-ce qu'il est possible en VBA de construire leur nom avec un code du style "lig" & i, un peu comme on le ferait avec INDIRECT("lig"& i) dans une formule ? Si on utilise directement

FOR i= 4 to 16
AAA(i)= 2* "lig" & i
Next i

cela ne fonctionne pas car Excel voit "lig" & i comme le texte "lig4", "lig5", "lig 6" et ainsi de suite et ne met pas les valeurs à la place de ces variables.

@ plus

 

[pierrejean]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour CISCO

Peut etre avec quelque chose du genre:

Sub test()
MesVariables = Array(10, 11, 12, 13, 14, 15, 16)
For n = LBound(MesVariables) To UBound(MesVariables)
   MsgBox ("MesVariables(" & n & ")= " & MesVariables(n))
Next
End Sub

 

[Paf]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour CISCO,

Est-ce qu'il est possible en VBA de construire leur nom (de variable) avec un code du style "lig" & i

ça dépend de ce que représente la variable lig, mais a priori la réponse est non.
On pourrait utiliser un tableau lig(x) avec quelque chose comme ceci:

dim lig(1 to 13)
lig(1)=12
lig(2)=45
...
...
lig(12)=56
lig(13)=91
For i= 4 to 16
   AAA(i)= 2* lig( i-3)
Next i

A+

Edit : bonjour pierrejean

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour à tous

Merci Pierrejean et Paf. J'essaye vos propositions dès que possible.

@ plus

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour

La solution de Pierrjean fonctionne pour une partie de mon besoin.
J'avais pensé à la solution de Paf mais elle ne passe pas correctement, car en réalité, j'en ai besoin sous la forme

For lig4 = 0 To Cells(4, 8 + col) 
For lig5 = 0 To Cells(5, 8 + col)
For lig6 = 0 To Cells(6, 8 + col)
For lig7 = 0 To Cells(7, 8 + col)
For lig8 = 0 To Cells(8, 8 + col)
....
For lig16 = 0 To Cells(16,8+col)

Blablabla

Next lig16
...
Next lig8
Next lig7
Next lig6
Next lig5
Next lig4

et il y a un bug dès la ligne For lig(5)= 0 to ... si je remplace tous les ligx par des lig(x) (en ayant défini un tableau lig() bien sûr au début de la macro).

Je peux contourner le problème en mettant le code sous la forme

For a = 0 To Cells(4, 8 + col) 
lig(4) = a
For b = 0 To Cells(5, 8 + col)
lig(5) = b
For c = 0 To Cells(6, 8 + col)
lig(6) = c
For d = 0 To Cells(7, 8 + col)
lig(7) = d
For e = 0 To Cells(8, 8 + col)
lig(8) = e

etc

mais cela ne me permet pas de simplifier mon code, le but ici étant d'éliminer des boucles imbriquées les unes dans les autres.

Encore merci à vous deux.

@ plus

P.S : Le code de la macro nbredetestconvenanttableau que je cherche à simplifier est dans le post #99 là
https://www.excel-downloads.com/threads/calcul-de-charge-planification.20005213/

 

[pierrejean]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Re

Peux-tu nous préciser ton besoin complet ???
Avec de préférence un petit fichier exemple

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Rebonjour Pierrejean.

Cf. le lien dans mon dernier post.
Et Merci.

@ plus

 

[pierrejean]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Re

Arf !!
Désolé : trop complexe pour moi

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour

Re
Arf !!
Désolé : trop complexe pour moi

Trop complexe, non, je ne pense pas, mais pas facile... J'essayerai plus tard, dans quelques semaines, dans quelques mois, quand je "maitriserai" mieux le VBA...

Avec ta proposition, j'ai réussi à réduire toute la partie marquée sous la forme blablaba dans mon post #5. C'est toujours ça.

Merci. Au plaisir.

@plus

 

[Paf]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Re,

Pas simple de comprendre le besoin sans lire les 100 posts et donc d'essayer de proposer quelque chose !

Désolé également.

A+

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonsoir

Re,

Pas simple de comprendre le besoin sans lire les 100 posts et donc d'essayer de proposer quelque chose !

Désolé également.

A+

Je m'en doute...

Si j'en ai le courage, je posterai un exemple plus simple comportant moins de lignes et moins de colonnes. Si j'en ai le courage...

Merci.

@ plus

 

[CISCO]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour

En pièce jointe, un fichier simplifié présentant le problème à résoudre. Dans cet exemple simplifié, il y a trois types de tests à réaliser, X, Y et Z, et 4 employés, A, B, C, et D. Le problème :

On a la liste d'employés habilités pour faire certains tests (plage C4:E7).

Chaque semaine, on a un cahier des charges fixant le nombre de tests à faire (ligne 10) et les temps nécessaires pour les faire (ligne 9).

La macro doit voir si c'est faisable avec les employés précités tout en faisant faire au max 35 h hebdomadaires à chacun d'eux.

J'ai fait une macro (dans le module 1) listant toutes les répartitions possibles. Si elle n'en trouve pas c'est que cela n'est pas faisable.

Le principe :
Les formules dans le tableau I4:K7 donne le nombre de tests max que peut faire l'employé de la ligne concernée.
La première partie de la macro met en mémoire toutes les répartitions verticales possibles permettant d'avoir le nombre de tests demandés cette semaine là.
La seconde partie de la macro combine ces diverses répartitions et affichent les combinaisons ne faisant pas travailler les employés plus de 35 h par semaine chacun.

Autrement dit, on travaille :
* d'abord dans chaque cellule, sans tenir compte des autres cellules du tableau I4:K7
* puis dans chaque colonne, sans tenir compte des autres colonnes du tableau I4:K7
* puis sur l'ensemble du tableau I4:K7

Pour traiter un autre exemple, il suffit de changer les parties en rouge sur la feuille.

Dans les véritables cas à traiter (cf. le lien dans le #5), il y a 6 types de tests et 13 employés. Cela fait donc beaucoup de cas à traiter et beaucoup de boucles For... to... ou de Select Case...

En créant le présent post, je cherchai à minimiser le nombre de boucles. C'est possible avec la proposition de Pierrejean.

Ceci dit, si vous avez d'autres idées, d'autres méthodes, ou au niveau mathématique, ou au niveau du code, je suis preneur.

@ plus

 

[Paf]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Re,

Après maints essais en utilisant des tableaux à trois dimensions ou des variables de type défini par l'utilisateur, et une luxation du neurone, j'ai abandonné toute velléité de tenter une simplification des boucles et du nombre de tableaux.

Désolé encore

A+

 

[Dranreb]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

Bonjour.
Quand c'est comme ça il faut décomposer le problème en plusieurs procédures et travailler avec des tableaux.
La procédure IncCol ci-dessous ne travaille pour l'instant que sur une colonne. Mais elle renvoie un Boolean qui indique qu'elle a fait le tour, et qu'il y aurait à l'appliquer aussi sur la colonne qui la précède.
La procédure de test montre 10 tableaux successifs obtenus. Mais le but ce n'est pas de les reprendre de la feuille mais de travailler chaque fois directement avec le tableau en mémoire incrémenté.

Sub test1()
Dim TabInc(), TabDép(), TNbTest(), Fin As Boolean, N As Long
TabDép = [I4:K7].Value
TNbTest = [I10:K10].Value
ReDim TabInc(1 To UBound(TabDép, 1), 1 To UBound(TabDép, 2))
For N = 0 To 10
   IncCol TabInc, TabDép, TNbTest, 3, Fin
   Cells(13, "I").Resize(4, 3).Offset(5 * N).Value = TabInc
   Next N
End Sub

Sub IncCol(TabInc(), TabDép(), TNbTest(), ByVal C As Long, Fin As Boolean)
Dim L As Long, Somme As Long
Fin = False
Do
   For L = UBound(TabDép, 1) To 1 Step -1
      If TabInc(L, C) > 0 Then TabInc(L, C) = TabInc(L, C) - 1: Exit For
      TabInc(L, C) = TabDép(L, C): Next L
   If L = 0 Then Fin = True
   Somme = 0
   For L = 1 To UBound(TabInc, 1): Somme = Somme + TabInc(L, C): Next L
   Loop Until Somme = TNbTest(1, C)
End Sub

 

[Dranreb]

Re : VBA : nom d'une variable obtenu à l'aide d'une concaténation

La version qui fait tout le tableau :

Sub test2()
Dim TabInc(), TabDép(), TNbTest(), Fin As Boolean, N As Long
TabDép = [I4:K7].Value
TNbTest = [I10:K10].Value
ReDim TabInc(1 To UBound(TabDép, 1), 1 To UBound(TabDép, 2))
For N = 0 To 2000
   IncCol TabInc, TabDép, TNbTest, Fin
   If Fin And N > 0 Then Exit Sub
   Cells(13, "I").Resize(4, 3).Offset(5 * N).Value = TabInc
   Next N
End Sub

Sub IncCol(TabInc(), TabDép(), TNbTest(), Fin As Boolean)
Dim C As Long, L As Long, Somme As Long, ResteSurLaColonne As Boolean
Fin = False
For C = UBound(TabDép, 2) To 1 Step -1
   ResteSurLaColonne = True
   Do
      For L = UBound(TabDép, 1) To 1 Step -1
         If TabInc(L, C) > 0 Then TabInc(L, C) = TabInc(L, C) - 1: Exit For
         TabInc(L, C) = TabDép(L, C): Next L
      If L = 0 Then ResteSurLaColonne = False
      Somme = 0
      For L = 1 To UBound(TabInc, 1): Somme = Somme + TabInc(L, C): Next L
      Loop Until Somme = TNbTest(1, C)
   If ResteSurLaColonne Then Exit Sub
   Next C
Fin = True
End Sub