VBA, déclaration de variables

[JJ1]

Bonsoir,

J'ai une macro qui fonctionnait avec moins de lignes (65000) sous Excel 2003.
J'ai voulu l'adapter à 2007 en augmentant les nombres formant les combinaisons (de 3 à 4 nombres), ce qui donne environ 200 000 lignes.
La macro deboggue sur "transpose", malgré la déclaration en Long.
Pouvez-vous me dire comment déclarer mes variables.
merci de votre aide.
(je joins la macro "toto" installée dans le fichier zippé.)

 

[eriiic]

Re : VBA, déclaration de variables

Bonjour,

Je ne vois pas où x évolue, ça gène...

eric

 

[job75]

Re : VBA, déclaration de variables

Bonjour JJ1, eriiiic,

Ne pas transposer et écrire :

Sub toto()
Dim tablo(48), i As Long, j As Long, k As Long, l As Long
Dim t1(900000, 0), t2(900000, 0), t3(900000, 0), t4(900000, 0), x As Long
Application.ScreenUpdating = False

For i = 0 To 48
tablo(i) = Cells(2, i + 1)
Next

For i = 0 To 45
  For j = i + 1 To 46
    For k = j + 1 To 47
      For l = k + 1 To 48
        t1(x, 0) = tablo(i)
          t2(x, 0) = tablo(j)
          t3(x, 0) = tablo(k)
          t4(x, 0) = tablo(l)
           
          Next
    Next
  Next
Next

Range("A4:A" & x + 3).Value = t1
Range("B4:B" & x + 3).Value = t2
Range("C4:C" & x + 3).Value = t3
Range("D4:D" & x + 3).Value = t4

Application.ScreenUpdating = True

End Sub

A+

 

[JJ1]

Re : VBA, déclaration de variables

Bonsoir Eric.....oui ça gêne effectivement !
J'ai apuré le code pour ne garder que l'essentiel (la formation de toutes les combinaisons de 4 nombres en fonction de la ligne 2) mais dans le code avant Next, il y avait x=x+1.
Mon problème est bien évidemment la déclaration des variables mais si tu as une idée pour le faire fonctionner, je suis preneur.
Merci et bonne soirée à tous.

 

[job75]

Re : VBA, déclaration de variables

Re,

Explication.

Application.Transpose renvoie une valeur d'erreur quand on cherche à transposer un vecteur de plus de 65535 éléments.

C'est comme ça 😎 Testez avec ces 2 macros :

Sub Test1()
x = Application.Transpose([A1:A65535])
MsgBox UBound(x) 'nombre d'éléments
End Sub

Sub Test2()
x = Application.Transpose([A1:A65536])
MsgBox UBound(x) 'renvoie une valeur d'erreur
End Sub

A+

 

[job75]

Re : VBA, déclaration de variables

Re,

Nos posts se sont croisés, voir mon post #3 (et #5)...

A+

 

[JJ1]

Re : VBA, déclaration de variables

Re, Bonsoir Job,

Merci pour cette explication mais..
J'ai mis le code fourni sans Transpose......................j'ai du faire fort car j'ai obtenu 208 000 fois la même combinaison???, autrement rien à dire, ça fonctionne vite! (sauf le résultat!)
Je pense que ma copie est à revoir ....
Sinon si vous pouvez me dire pourquoi j'ai ce résultat?
Merci (je joins mon excellente adaptation dans le fichier joint.)

 

[job75]

Re : VBA, déclaration de variables

Re,

JJ1, il faudrait utiliser exactement le code de mon post #3...

Mais x n'est calculé nulle part, alors il a toujours la valeur 0...

A+

 

[job75]

Re : VBA, déclaration de variables

Re,

Bon, il manquait juste la ligne x = x + 1....

Tester bien sûr sur Excel 2007/2010, pas sur 2003 :

Sub toto()
Dim tablo(48), i As Long, j As Long, k As Long, l As Long
Dim t1(900000, 0), t2(900000, 0), t3(900000, 0), t4(900000, 0), x As Long
Application.ScreenUpdating = False

For i = 0 To 48
tablo(i) = Cells(2, i + 1)
Next

For i = 0 To 45
  For j = i + 1 To 46
    For k = j + 1 To 47
      For l = k + 1 To 48
        t1(x, 0) = tablo(i)
          t2(x, 0) = tablo(j)
          t3(x, 0) = tablo(k)
          t4(x, 0) = tablo(l)
          x = x + 1
        Next
    Next
  Next
Next
Range("A4:A" & x + 3).Value = t1
Range("B4:B" & x + 3).Value = t2
Range("C4:C" & x + 3).Value = t3
Range("D4:D" & x + 3).Value = t4

Application.ScreenUpdating = True

End Sub

A+

 

[Fo_rum]

Re : VBA, déclaration de variables

Bonsoir,

et ainsi ?

Sub Combinaisons()
  Dim tps As Double, tablo(48), i As Long, j As Long, k As Long, l As Long
  Dim t1(900000, 3)
  Dim t
  Rows("4:" & Rows.Count).Delete
  t = Timer
  For i = 0 To 48
    tablo(i) = Cells(2, i + 1)
  Next

  For i = 0 To 45
    For j = i + 1 To 46
      For k = j + 1 To 47
        For l = k + 1 To 48
          t1(x, 0) = tablo(i)
          t1(x, 1) = tablo(j)
          t1(x, 2) = tablo(k)
          t1(x, 3) = tablo(l)
          x = x + 1
        Next
      Next
    Next
  Next
  Range("A4:D" & x + 4).Value = t1()

  MsgBox Timer - t
End Sub

 

[job75]

Re : VBA, déclaration de variables

Bonsoir Fo_rum,

Oui un seul tableau au lieu de 4 c'est quand même mieux 🙂

Et pour le fun une solution qui permet d'utiliser aussi Excel 2003 et ses 65536 lignes :

Option Explicit
Option Base 1 'les indices des tableaux commencent à 1

Sub toto()
Dim tablo, col%, i&, j&, k&, l&, x&, t(65532, 4)
Application.ScreenUpdating = False
tablo = Application.Transpose(Application.Transpose([A2:AW2]))
col = 1
For i = 1 To 46
  For j = i + 1 To 47
    For k = j + 1 To 48
      For l = k + 1 To 49
        x = x + 1
        t(x, 1) = tablo(i)
        t(x, 2) = tablo(j)
        t(x, 3) = tablo(k)
        t(x, 4) = tablo(l)
        If x = 65532 Then
          Cells(4, col).Resize(x, 4) = t
          x = 0
          col = col + 5
          Erase t 'vide le tableau
        End If
      Next
    Next
  Next
Next
Cells(4, col).Resize(x, 4) = t
End Sub

Fichier joint.

A+

 

[JJ1]

Re : VBA, déclaration de variables

Bonjour Job, Fo-rum,à tous, et merci pour vos codes.
je teste ce soir les macros.
Bon AM

 

[JJ1]

Re : VBA, déclaration de variables

Re,
Bravo, code excellent, 212 000 combinaisons en 3 secondes !
Bonne soirée

 

[JJ1]

Re : VBA, déclaration de variables

Bonjour à tous,
Je voudrais "sortir" du Next quand un nombre (par exemple le 31) est rencontré pour la première fois en colonne A, je ne sais pas trop la formule (GOTO quelque chose ou EXIT FOR ?)
Merci beaucoup.

 

[job75]

Re : VBA, déclaration de variables

Re,

A partir de mon dernier code (post #11), avec GoTo 1 en effet :

Option Explicit
Option Base 1 'les indices des tableaux commencent à 1

Sub toto()
Dim exclu, tablo, col%, i&, j&, k&, l&, x&, t(65532, 4)
Application.ScreenUpdating = False
exclu = 31 'à adapter
tablo = Application.Transpose(Application.Transpose([A2:AW2]))
col = 1
For i = 1 To 46
  If tablo(i) = exclu Then GoTo 1
  For j = i + 1 To 47
    For k = j + 1 To 48
      For l = k + 1 To 49
        x = x + 1
        t(x, 1) = tablo(i)
        t(x, 2) = tablo(j)
        t(x, 3) = tablo(k)
        t(x, 4) = tablo(l)
        If x = 65532 Then
          Cells(4, col).Resize(x, 4) = t
          x = 0
          col = col + 5
          Erase t 'vide le tableau
        End If
      Next
    Next
  Next
1 Next
If x Then Cells(4, col).Resize(x, 4) = t
End Sub

Nota : rajouté If x Then à la fin, par sécurité...

Edit : tout dépend aussi de ce que tu veux dire par rencontré pour la 1ère fois...

A+