Discussion :

[pijaku]

Bonjour,

Comme chacun sait, Excel et VBA ont des spécifications, limites et lacunes.

En ce qui concerne Excel, je ne vais pas les lister dans le sens ou c'est très bien fait ICI, du moins pour ce qui concerne les versions postérieures à 2007.

Vous trouverez ICI les limites de chaque type de données (Byte, Integer, Long, Currency, String,...).

Nous allons, dans ce sujet (re)découvrir et tenter de lister et résoudre un maximum de celles de VBA.

Etant persuadé en avoir oublié, n'hésitez pas à me les indiquer en réponse pour que ce sujet soit le plus exhaustif possible.

________________________________________________________________________________________________________________

1. Le nombre maximum d'Areas dans une feuille
   
   • Limite
     Ce nombre maximum est fixé à 8 192.
     Ce nombre correspond à la moitié de la limite de la méthode SpecialCells (cf plus bas) qui, vous en conviendrez, est fortement liée à la notion d'Areas.
     A noter toutefois que ce nombre semble s'augmenter au fur et à mesure des tests, Excel "réagissant" en effet sur ce sujet afin de s'adapter à la demande de l'utilisateur.
     Ainsi, un même utilisateur, sur un même pc, sans toucher à l'installation d'Office, peut voir ce maximum considérablement s'agrandir...
     Cela reste, pour moi, un des mystères de MS Excel.
   • Comment y pallier
     cf paragraphe ci-dessous, à propos de la méthode SpecialCells.
   
   
   
2. Limite de la méthode SpecialCells
   
   • Limite
     Elle ne peut recenser d'un seul coup plus d'un certain nombre d'aires (Areas) résultant de son application.
     Ce nombre est égal à la moitié de la limite d'un type integer soit 16 385.
   • Comment y pallier
     Lire la contribution d'unparia à ce sujet
   
   
   
3. Le nombre maximum d'éléments dans un Array
   Ce nombre est limité par la quantité de mémoire disponible.
   Il varie donc selon le type de données contenues dans cet Array.
   C'est pourquoi il est toujours vivement recommandé de bien typer toutes vos variables.
   
   
   
4. La division entière : \
   
   • Limite
     Le plus grand nombre que l'on puisse diviser grâce à l'opérateur \ est 2 147 483 647.
     Il correspond à la limite du type Long.
   • Test :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     Sub Demo_Division_Entiere()
     Dim d As Double, i As Double
        d = 2147483647
        MsgBox d \ 83647
        d = 2147483648#
        On Error Resume Next
        MsgBox d \ 2
        If Err.Number > 0 Then MsgBox Err.Description
        On Error GoTo 0
     End Sub

   • Comment y pallier
     Il suffit, pour cela, de prendre la partie entière de la division de deux Doubles.
     Voici une fonction qui fait très bien l'affaire, à titre d'exemple :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     Function Division_Entiere(Nb1 As Double, Nb2 As Double) As Double
        Division_Entiere = Int(Nb1 / Nb2)
     End Function

     Code appelant :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     MsgBox Division_Entiere(21474836489#, 12542)

   • A noter :
     VBA n'étant pas très bien armé pour les opérations sur de grands nombres, la division de deux Double peut générer des erreurs...
   
   
   
5. Le Modulo : Mod
   
   • Limite
     L'opérateur Mod est limité à 2 147 483 647.
     Il correspond à la limite du type Long, tout comme pour la division entière.
   • Test :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     Sub Demo_Mod()
     Dim d As Double
        d = 2147483647
        MsgBox d Mod 83647
        d = 2147483648#
        On Error Resume Next
        MsgBox d Mod 2
        If Err.Number > 0 Then MsgBox Err.Description
        On Error GoTo 0
     End Sub

   • Comment y pallier
     Tout comme pour la division entière, avec une petite fonction perso. Exemple :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     Function ModSansLimite(Nb1 As Double, Nb2 As Double) As Double
        ModSansLimite = Nb1 - (Int(Nb1 / Nb2) * Nb2)
     End Function

     Code appelant :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     MsgBox ModSansLimite(2147483648, 83647)

   
   
   
6. La méthode WorksheetFunction.Match
   
   • Limite
     La limite de cette méthode est de 65 536.
     Ce nombre correspond à la limites de lignes dans Excel 2003.
   • Test :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15
     16
     Sub Demo_Match()
     Dim t() As String, v, var As String, i As Long
        var = "liste21"
        ReDim t(65535)
        For i = LBound(t) To UBound(t)
           t(i) = "liste" & i
        Next
        v = Application.Match(var, t, 0)
        MsgBox v
        ReDim Preserve t(65536)
        t(65536) = "liste65536"
        On Error Resume Next
        v = Application.Match(var, t, 0)
        If Err.Number > 0 Then MsgBox Err.Description
        On Error GoTo 0
     End Sub

   • Comment y pallier
     Par une fonction personnelle.
     
     Cette fonction perso va utiliser une fonction qui détermine le nombre de dimensions d'un Array (Function Nb_Dimensions).
     Fonction Nb_Dimensions (utile également dans le paragraphe sur Transpose (ci-dessous)) :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     Function Nb_Dimensions(ByRef Tableau As Variant) As Integer
     'Retourne le nombre de dimensions d'une variable tableau
     Dim d As Integer, t As Long
        On Error GoTo Fin
        Do
           d = d + 1
           t = UBound(Tableau, d)
        Loop
     Fin:
        On Error GoTo 0
        Nb_Dimensions = d - 1
     End Function

     La fonction de "remplacement" de Match :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     26
     27
     28
     29
     Function Get_Row(ByRef Tableau As Variant, ByRef Texto As Variant, Optional ByRef Colonne As Long) As Long
     'Détermine l'index d'un élément à partir de son contenu
     'Retourne un Long représentant l'index de cet élément
     'Retourne -1 si l'élément n'existe pas dans le tableau ou erreur (de colonne, de Tableau, etc).
     Dim i As Long, strTemp As String
        Get_Row = -1
        Select Case Nb_Dimensions(Tableau)
            Case 1
     '===== le présent code, déposé par ucfoutu sur VBFrance, est la seule propriété de VBFrance
     '=====VBFrance en autorise les libres copie et utilisation à la seule condition d'y laisser
     '=====insérées les trois présentes lignes commentées --- ucfoutu ---
              strTemp = Chr(0) & Join(Tableau, Chr(0)) & Chr(0)
              i = InStr(strTemp, Chr(0) & Texto & Chr(0))
              If i = 0 Then
                 Get_Row = -1
              Else
                 strTemp = Mid(strTemp, 1, i)
                 Get_Row = UBound(Split(strTemp, Chr(0))) - 1
                 If Get_Row < 0 Then Get_Row = -1
              End If
     '======================
           Case 2
              'si paramètre colonne omis, on prends la première
              If Colonne = 0 Then Colonne = LBound(Tableau, 2)
              For i = LBound(Tableau, 1) To UBound(Tableau, 1)
                 If Tableau(i, Colonne) = Texto Then Get_Row = i: Exit For
              Next i
        End Select
     End Function

   • A noter :
     La même limite est fixée en ce qui concerne la fonction Index (WorksheetFunction.Index).
     Il est cependant beaucoup plus simple d'y remédier...
   
   
   
7. La méthode Transpose
   
   • Limite
     La limite de cette méthode est de 65 536.
     Tout comme Match et Index, ce nombre correspond à la limite de lignes dans Excel 2003.
   • A noter :
     Transpose, étant à la base une fonction de feuille de calcul, retourne un Array en Base 1 même si l'Array qui lui est injecté est en Base 0.
     Notre fonction pour pallier à la limite de Transpose palliera également à cela.
   • Test :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     Sub EssaiTranspose()
     Dim t() As String, v, i As Long
      
        ReDim t(2, 65535)
        For i = LBound(t) To UBound(t)
           t(1, i) = "liste" & i
        Next
        v = Application.Transpose(t)
        MsgBox "Avec TRANSPOSE, cas < 65 536" & vbCrLf & vbCrLf & _
        "t : " & LBound(t, 1) & " To " & UBound(t, 1) & "  <=>  " & LBound(t, 2) & " To " & UBound(t, 2) & vbCrLf & _
         "v : " & LBound(v, 1) & " To " & UBound(v, 1) & "  <=>  " & LBound(v, 2) & " To " & UBound(v, 2)
        Erase v
        ReDim Preserve t(2, 66000)
        For i = 65536 To 66000
           t(1, i) = "liste" & i
        Next i
        On Error Resume Next
        v = Application.Transpose(t)
        If Err.Number > 0 Then MsgBox "Avec TRANSPOSE, cas > 65 536" & vbCrLf & vbCrLf & Err.Description
        On Error GoTo 0
     End Sub

   • Comment y pallier
     Il nous faut une fonction personnalisée qui :
     > transpose l'array
     > le restitue dans la même base
     Exemple (Attention : utilise la fonction Nb_Dimensions cf paragraphe précédent) :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15
     16
     17
     18
     19
     20
     21
     22
     23
     24
     25
     Function Transposition(ByRef Tableau As Variant) As Variant
     'Transpose une variable tableau à 2 dimensions
     Dim tabl, i As Long, j As Long, c As Long
        Select Case Nb_Dimensions(Tableau)
           Case 1
              c = LBound(Tableau)
              c = IIf(c = 0, 1, 0)
              ReDim tabl(1 To UBound(Tableau) + c, 1 To 1)
              For i = 1 To UBound(tabl)
                 tabl(i, 1) = Tableau(i - c)
              Next
           Case 2
              ReDim tabl(LBound(Tableau, 2) To UBound(Tableau, 2), LBound(Tableau, 1) To UBound(Tableau, 1))
              For i = LBound(Tableau, 1) To UBound(Tableau, 1)
                 For j = LBound(Tableau, 2) To UBound(Tableau, 2)
                    tabl(j, i) = Tableau(i, j)
                 Next j
              Next i
           Case Else
              MsgBox "Le tableau ne comporte pas une ou deux dimensions"
              Exit Function
        End Select
        Transposition = tabl
        Erase tabl
     End Function

     Code appelant :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     Dim t() As String, v, i As Long
        ReDim t(2, 66666)
        For i = LBound(t) To UBound(t)
           t(1, i) = "liste" & i
        Next
        v = Transposition(t)
        MsgBox "Avec Transposition, cas > 65 536" & vbCrLf & vbCrLf & _
        "t : " & LBound(t, 1) & " To " & UBound(t, 1) & "  <=>  " & LBound(t, 2) & " To " & UBound(t, 2) & vbCrLf & _
         "v : " & LBound(v, 1) & " To " & UBound(v, 1) & "  <=>  " & LBound(v, 2) & " To " & UBound(v, 2)

   
   
8. Limite de la méthode Union
   
   • Lacunes
     Deux lacunes sont à déplorer au sujet de cette Méthode :
     > Si un des arguments = Nothing elle engendre une erreur Argument ou appel de procédure incorrect
     > Si deux Range s'intersectent, leurs cellules communes sont comptabilisées 2 fois.
   • Test :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     Sub Demo_Union()
     Dim Rng As Range, R As Range
        On Error Resume Next
        Set Rng = Application.Union(R, Range("A8:C10"), Range("B9:D11"))
        If Err.Number > 0 Then MsgBox Err.Description
        On Error GoTo 0
        Set Rng = Application.Union(Range("A8:C10"), Range("B9:D11"))
        MsgBox Rng.Address & "   " & Rng.Cells.Count
     End Sub

   • Comment y pallier
     Avec cette fonction :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     4
     5
     6
     7
     8
     9
     10
     11
     12
     13
     14
     15
     16
     17
     Function UnionSansDoublons(ParamArray Rngs() As Variant) As Range
     Dim RngTemp As Range, R As Range, i As Long
        For i = LBound(Rngs) To UBound(Rngs)
           If Not Rngs(i) Is Nothing Then
              If RngTemp Is Nothing Then
                 Set RngTemp = Rngs(i)
              Else
                 For Each R In Rngs(i).Cells
                    If Application.Intersect(RngTemp, R) Is Nothing Then
                       Set RngTemp = Application.Union(RngTemp, R)
                    End If
                 Next R
              End If
           End If
        Next i
        Set UnionSansDoublons = RngTemp
     End Function

     Code appelant :
     

     Code :

     Sélectionner tout - Visualiser dans une fenêtre à part

     1
     2
     3
     Dim Rng As Range, R As Range
        Set Rng = UnionSansDoublons(R, Range("A8:C10"), Range("B9:D11"))
        MsgBox Rng.Address & "   " & Rng.Cells.Count

________________________________________________________________________________________________________________

Il existe encore moultes autres limites et ou lacunes.
Par exemple : la limite de lignes dans un module est fixée à 65 531 (en tout cas chez moi)...

Je compte sur vous...

Merci

[clementmarcotte]

Un travail de moine. Bravo et merci.

[Qwazerty]

Salut

Super boulot.

Je me demande
La méthode Transpose
Limite
La limite de cette méthode est de 65 536.

Sur les dernières versions d'excel, le nombre de colonne correspond à un demi entier, soit 16 384. Transpose passant des lignes en colonnes ou des colonnes en lignes, je ne vois pas l'intérêt d'aller au delà de 65K puisque à un moment donné, soit on ne pourra pas sélectionner autant de colonnes, soit on ne pourra pas inscrire les données sélectionnées en ligne faute d'avoir assez de colonne disponible.
J'ai manqué quelques choses?

++
Qwaz

[pijaku]

Bonjour,
Merci clément, merci qwaz.
@qwaz.
L'intérêt, pour la méthode transpose, de dépasser cette limite, n'est intéressante que pour les variables tableaux, en mémoire.
Il reste exceptionnel de conserver des tableaux, sous Excel, comportant un nombre de lignes et de colonnes risquant de dépasser cette limite.
Si tel est le cas, il convient forcément (cf signature de clementmarcotte) d'utiliser un autre système.
Voilà voilà.

Merci du retour en tout cas.

Je profite de mon passage pour signaler d'autres lacunes, manquements concernant les méthodes Join, Split, Filter et fonction CONCATENER.... Mais là, j'ai du boulot...

A suivre donc.

[unparia]

Bonjour Franck
Je salue personnellement ce magnifique travail
Je n'ai qu'un regret : qu'il figure dans une rubrique "contributions" devenue quasiment inexploitable du fait d'un "déversement" (oserais-je dire "inondation") incontrôlé d'un peu tout et n'importe quoi.
Mon espoir : que ton travail soit transféré plus utilement (rubrique "tutos" ? , FAQ ?) par ceux qui le peuvent.
Amitiés
Jacques

[Pierre Fauconnier]

Salut Franck,

Il faudrait préciser que la limite de Match s'applique à un tableau, mais pas à une plage. Avec une plage (ce qui est, en Excel, l'utilisation la plus courante de Match, à mon avis), Application.Match permet d'aller au delà de 65536 sur les nouvelles versions…



Dans les faits, il y a certaines limites qui ne me semblent que théoriques. Manipuler un tableau de plus de 16384 colonnes en VBA, c'est loin d'être fréquent, me semble-t-il…

[EDIT]
Union
Pour moi, le fait que ça plante avec une des deux plages en Nothing ne me gêne pas. Il me semble normal de tester les plages avant de vouloir les unir.
Par contre, pour ce qui est du comptage, c'est bien observé, et je constate que VBA se comporte différemment selon que l'une des plages est inclue dans l'autre ou pas… C'est pour le moins dérangeant…

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Sub Test2()
  Dim a As Range, b As Range, c As Range
 
  ' B inclus dans A
  Set a = Range("a1:b10")
  Set b = Range("b4:b8")
  Set c = Union(a, b)
  Debug.Print c.Count ' renvoie 20, correct
 
  ' B pas inclus dans A
  Set b = Range("b4:c8")
  Set c = Union(a, b)
  Debug.Print c.Count ' renvoie 30, pas correct
 
End Sub

[pijaku]

Bonjour Jacques, Pierre,

@Jacques, merci de tes encouragements.
Quant à ton regret, je penses que le principal n'est pas l'endroit ou se trouve la contribution, mais le référencement qui y est fait.
Dans ce sens, pas sur qu'une autre rubrique référencera tel ou tel sujet de meilleure façon.

@Pierre : Tu n'as pas tord, et j'aurais du mieux préciser ma pensée (en fait, je suis un bien piètre rédacteur...). Dommage que je ne puisse plus éditer le sujet.

6- La méthode WorksheetFunction.Match
Limite
La limite de cette méthode est de 65 536, en ce qui concerne les variables tableaux.
Ce nombre correspond à la limites de lignes dans Excel 2003.
Cette limite n'existe plus si vous transférez votre tableau dans une feuille.
test :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Sub Demo_Match()
Dim t(1 To 65537, 1 To 1), v$, var, i As Long
   var = "liste21"
   For i = LBound(t) To UBound(t)
      t(i, 1) = "liste" & i
   Next
   On Error Resume Next
   v = Application.Match(var, t, 0)
   If Err.Number > 0 Then MsgBox Err.Description
   On Error GoTo 0
   Range("A1").Resize(UBound(t)) = t
   v = Application.Match(var, Range("A1:A65537"), 0)
   MsgBox v
End Sub

Manipuler un tableau de plus de 16384 colonnes en VBA, c'est loin d'être fréquent, me semble-t-il…

Pas en ce qui concerne une variable tableau d'une seule dimension...

Pour moi, le fait que ça plante avec une des deux plages en Nothing ne me gêne pas.

Moi non plus, il est tout à fait logique de tester au préalable.
Dans ce cas, le comptage est une plus sérieuse lacune, j'ai voulu traiter les deux simultanément.
Cela évite de tester une possible erreur supplémentaire alors qu'on utilise une fonction personnalisée.

[Pierre Fauconnier]

[…]
Pas en ce qui concerne une variable tableau d'une seule dimension...
[…]

Exemple concret d'une utilisation fréquente avec Excel? (Je ne parle pas ici de VBA pour autre chose qu'Excel, car il m'étonnerait que l'on manipule souvent des tableaux de cette taille en Word ou PowerPoint…)

[pijaku]

Utilisation fréquente, je ne sais pas, ne vois pas.
A titre personnel, je m'en suis servi une fois ou deux, notamment pour la gestion d'un dictionnaire en mémoire, avec retranscription sur une feuille.

De plus, on sait que dans le cadre d'un tableau dynamique, seule la seconde dimension est modifiable.
Si celle-ci venait à dépasser les 65536 (je sais cela reste rare), Transpose ne fonctionnerai pas pour placer les valeurs sur une feuille...

Cette limite existe, et mérite, de toutes façons, de figurer ici.

[Pierre Fauconnier]

[…]Il reste exceptionnel de conserver des tableaux, sous Excel, comportant un nombre de lignes et de colonnes risquant de dépasser cette limite.[…]

De quelle limite parles-tu? Conserver des tableaux Excel de plus de 65000 lignes, c'est assez fréquent de nos jours, voire très fréquent. Je n'ai pas bien compris ton propos…

[pijaku]

Je n'ai pas bien compris ton propos…

C'est ce que je disais plus haut, mauvais rédacteur

La limite dont je parle est celle de 65 536.
Il est, en effet, fréquent de stocker des tableaux plus conséquents dans des feuilles.
Néanmoins, si tu travailles ces tableaux en mémoire (variables tableaux), il te faut penser à cette limite qui te gène pour :
> Transpose,
> Match,
> Index...

Lorsque j'évoquais un grand nombre de colonnes (supérieur à 65536), c'est uniquement pour une variable tableau à 1 dimension.
Je me représente, en effet, ce type de tableau comme comportant une ligne et xxx colonnes...

[Pierre Fauconnier]

[…]Je me représente, en effet, ce type de tableau comme comportant une ligne et xxx colonnes...

Ok. Merci

[Qwazerty]

Salut

Dommage que je ne puisse plus éditer le sujet.

C'est vrai que c'est bien malheureux... La seuls solution que je vois, c'est de réaliser un tuto Pijaku! Go
[Edit]
Ou une section dans la Faq plutôt.
Plus simple niveau rédaction en plus vu que c'est ce qui à l'air de te poser problème.
Enfin je dis ça... je dis rien
[Edit]

en fait, je suis un bien piètre rédacteur...

Baa Le message était clair, la relecture technique est la pour préciser certains points et en clarifier d'autre
++
Qwaz

[Pierre Fauconnier]

Ces infos apparaîtront dans la nouvelle faq avec Franck comme auteur

[pijaku]

Salut,

EDIT
Pierre,
Hier nous nous sommes croisés et je viens juste de voir ton message.
Merci de cette marque de confiance.

Je comptais néanmoins ajouter à ces limites et lacunes les deux fonctions Join et Filter.
Toutes deux ne permettent, en effet, de travailler que sur des variables tableaux à 1 dimension et de type String.
Trouvant cela trop limitatif, je vais ouvrir deux sujets sur le forum VBA/Excel pour y obtenir l'aide nécessaire.
Surtout en ce qui concerne la fonction Filter car, toute "amélioration" de cette fonction nécessite une grande connaissance de l'objet Filter lui-même (à moins de se contenter d'une double boucle...).

Pour le coup, je compte sur vous pour participer à ces sujets qui seront traités l'un après l'autre.

Merci encore de vos retours à tous.
A++
\EDIT

[Pierre Fauconnier]

Salut Franck,

Pour la confiance, pas de soucis. Même si je trouve certaines de ces limites théoriques, elles sont tout à fait pertinentes et utiles dans une FAQ. Pas de soucis de ce côté-là, ma joute se voulant amicale et uniquement technique, tu l'auras compris

Pour les autres limites que tu aborderas, on les intègrera également lorsque la problèmatique aura été bien cernée et décortiquée .

La nouvelle FAQ demande pas mal de temps et de ressources, mais elle finira par être utilisable… (Si des bonnes volontés veulent se joindre aux réflexions et à sa rédaction, ils sont les bienvenus… me contacter en mp)

[pijaku]

Salut,

Pas de soucis de ce côté-là, ma joute se voulant amicale et uniquement technique, tu l'auras compris

J'avais bien compris qu'il s'agissait d'éclairer quelques points noirs...

Pour les autres limites que tu aborderas,

C'est déjà en cours...

Si des bonnes volontés veulent se joindre aux réflexions et à sa rédaction, ils sont les bienvenus…

Tu comprendras aisément que je ne souhaite pas me mêler de rédaction...
Par contre, je veux bien aider aux réflexions, dans mes limites...

[pijaku]

Bonjour à toutes et tous,

Me revoici pour combler, non pas une lacune ou une limite, mais pour pallier à quelques défauts.

La fonction Join comporte 2 défauts :

1. Elle ne fonctionne qu'avec un tableau de String (et/ou de Variant/Variant),
2. Elle ne fonctionne qu'avec des Arrays à une dimension.

Ces deux défauts, pris simplement, ne méritent pas forcément que l'on s'y attelle car ils sont très aisément contournables en VBA.

Si l'on regarde de près "l'équivalant" de Join dans les formules Excel, on se rend compte que la fonction CONCATENER comporte également deux défauts :

1. Elle ne fonctionne pas avec des plages entières, ex : =CONCATENER(A1:C10),
2. On ne peut pas y intégrer un séparateur, ex : =CONCATENER(A1;A2;"-")

Fort de ces 4 "défauts", je vous propose aujourd'hui une fonction qui va gérer tout cela.

Code de la fonction JOINT :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
Public Function JOINT(ByRef Tableau As Variant, _
                        Optional ByRef RowSeparator As String = "", _
                           Optional ByRef ColumnSeparator As String = "", _
                              Optional ByRef ByRow As Boolean = True) As String
Dim st() As String, temp As Variant, maVar As Variant, i As Long, j As Long, d As Integer
   If TypeOf Tableau Is Excel.Range Then
      temp = Tableau.Value
   ElseIf IsArray(Tableau) Then
      temp = Tableau
   Else
      JOINT = "Erreur d'exécution '5' : " & vbCrLf & vbCrLf & _
               "Argument ou appel de procédure incorrect" & vbCrLf & _
                  "Argument incorrect : ByRef Tableau As Variant"
      MsgBox JOINT, vbOKOnly + vbCritical, "Microsoft Visual Basic"
      Exit Function
   End If
   On Error Resume Next
   Do While Err.Number = 0
      d = d + 1
      i = UBound(temp, d)
   Loop
   On Error GoTo 0
   Select Case d - 1
      Case 1: maVar = temp((UBound(temp) + LBound(temp)) \ 2)
      Case 2: maVar = temp((UBound(temp) + LBound(temp)) \ 2, LBound(temp))
      Case Else
         JOINT = "Erreur d'exécution '5' : " & vbCrLf & vbCrLf & _
               "Argument ou appel de procédure incorrect" & vbCrLf & _
                  "Argument incorrect : ByRef Tableau As Variant"
         MsgBox JOINT, vbOKOnly + vbCritical, "Microsoft Visual Basic"
         Erase temp
         Exit Function
   End Select
   Select Case VarType(maVar)
      Case 0, 2, 3, 4, 5, 6, 7, 8, 11, 12, 17
         Select Case d - 1
            Case 1
               ReDim st(LBound(temp) To UBound(temp)) As String
               For i = LBound(temp) To UBound(temp)
                  st(i) = temp(i)
               Next
               JOINT = Join(st, RowSeparator)
            Case 2
               If ByRow Then
                  ReDim st(LBound(temp, 1) To UBound(temp, 1)) As String
                  For i = LBound(temp, 1) To UBound(temp, 1)
                     For j = LBound(temp, 2) To UBound(temp, 2)
                        st(i) = st(i) & ColumnSeparator & temp(i, j)
                     Next
                     st(i) = Right(st(i), Len(st(i)) - Len(ColumnSeparator))
                  Next
                  JOINT = Join(st, RowSeparator)
               Else
                  ReDim st(LBound(temp, 1) To UBound(temp, 1)) As String
                  For i = LBound(temp, 2) To UBound(temp, 2)
                     For j = LBound(temp, 1) To UBound(temp, 1)
                        st(j) = temp(j, i)
                     Next
                     JOINT = JOINT & ColumnSeparator & Join(st, RowSeparator)
                  Next
                  JOINT = Right(JOINT, Len(JOINT) - Len(ColumnSeparator))
               End If
         End Select
         Erase st
      Case 20 'LongLong
      Case 1 'VbNull
         JOINT = "Erreur d'exécution '94' : " & vbCrLf & vbCrLf & _
                  "Utilisation incorrecte de Null"
         MsgBox JOINT, vbOKOnly + vbCritical, "Microsoft Visual Basic"
      Case Else 'Objet ou vberror ou autre...
         JOINT = "Erreur d'exécution '5' : " & vbCrLf & vbCrLf & _
                  "Argument ou appel de procédure incorrect"
         MsgBox JOINT, vbOKOnly + vbCritical, "Microsoft Visual Basic"
   End Select
   Erase temp
End Function

Exemples de code appelant :
Depuis VBA :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
Option Explicit
 
Sub Demonstration()
Const NB As Long = 100000
Dim Ra As Range, v, ll(1 To NB, 1 To 10) As Long, i As Long, j As Long, t As Double, dd(1 To NB, 1 To 10) As Double
Dim vt(1000000) As Long, d(1000000) As Double, da(1000000) As Date, errnull(1000) As Variant
Dim obj As Object
   Set obj = CreateObject("Scripting.dictionary")
   For i = LBound(vt) To UBound(vt)
      vt(i) = i
   Next
   For i = LBound(d) To UBound(d)
      d(i) = Timer
   Next
   For i = LBound(da) To UBound(da)
      da(i) = Now
   Next
   For i = LBound(errnull) To UBound(errnull)
      errnull(i) = Null
   Next
   Set Ra = Range("A1:J" & NB)
   Ra.Value = 10
   v = Ra.Value
   For i = 1 To NB
      For j = 1 To 10
         ll(i, j) = 10
      Next
   Next
   For i = 1 To NB
      For j = 1 To 10
         dd(i, j) = Timer
      Next
   Next
   Debug.Print "Erreurs : "
   Debug.Print JOINT(obj, ":", ";")
   Debug.Print "......................................"
   Debug.Print JOINT(errnull, ":", ";")
   Debug.Print ""
   Debug.Print "Test avec un Array à 1 dimension de 1 000 000 éléments."
   t = Timer
   Debug.Print TypeName(vt) & " : Longueur du String obtenu : " & Len(JOINT(vt, "/")) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(d) & " : Longueur du String obtenu : " & Len(JOINT(d, "/")) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(da) & " : Longueur du String obtenu : " & Len(JOINT(da, "/")) & " ==> " & Timer - t & " sec"
   Debug.Print ""
   Debug.Print "Test avec un Array à 2 dimensions de " & NB & " lignes et 10 colonnes."
   Debug.Print "ByRow............"
   t = Timer
   Debug.Print TypeName(Ra) & " : Longueur du String obtenu : " & Len(JOINT(Ra, "/", ":", True)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(v) & " : Longueur du String obtenu : " & Len(JOINT(v, "/", ":", True)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(ll) & " : Longueur du String obtenu : " & Len(JOINT(ll, "/", ":", True)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(dd) & " : Longueur du String obtenu : " & Len(JOINT(dd, "/", ":", True)) & " ==> " & Timer - t & " sec"
   Debug.Print ""
   Debug.Print "ByColumn............"
   t = Timer
   Debug.Print TypeName(Ra) & " : Longueur du String obtenu : " & Len(JOINT(Ra, "/", ":", False)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(v) & " : Longueur du String obtenu : " & Len(JOINT(v, "/", ":", False)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(ll) & " : Longueur du String obtenu : " & Len(JOINT(ll, "/", ":", False)) & " ==> " & Timer - t & " sec"
   t = Timer
   Debug.Print TypeName(dd) & " : Longueur du String obtenu : " & Len(JOINT(dd, "/", ":", False)) & " ==> " & Timer - t & " sec"
 
   Erase vt, d, da, ll, dd, v
   Ra.Clear
   Set Ra = Nothing
End Sub

Depuis une feuille de calcul :

=JOINT(A1:C10;;;VRAI)
=JOINT(A1:C10;;;FAUX)
=JOINT(A1:C10;"_";"|";VRAI)
=JOINT(A1:C10;"/";"|";FAUX)
....

En elle-même, cette fonction n'est pas énorme en terme de lignes de code, mais j'ai tenté de la "blinder" pour éviter, prévenir un maximum d'erreurs.
Son plus gros défaut est un souci de lenteur sur de gros tableaux.

Merci d'un éventuel retour quant à ses performances et également si une amélioration de la vitesse d'exécution est envisageable.
Merci également de me dire que faire avec ces LongLong...

[patricktoulon]

Bonjour a tous et pijaku

j'ai constaté dans ta fonction union que tu utilisais le test intersect pour exepter les cellules en doublons pour palier a la lacune de cette dite fonction (union)
il s'avere que la lacune est simple
en fait union ne sais pas faire le tri entre deux plages de plusieurs cellules(quand elle sont croisée (intersect)ET!! que l'une n'est pas entierement contenu dans l'autre ) tandis que le tri entre une plage de cellules et une cellule est fait

demonstration

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
[Sub test2()
    Dim Rng As Range
    'exemple avec 3 fois le meme(uniquecellule)
    Set Rng = Union(Range("A1:C10"), Range("A1"), Range("A1"), Range("d10"))
    MsgBox Rng.Address & "   " & Rng.Cells.Count & " ici on voit que c'est bon "
    'exemple avec 3 plage qui se croise mais ne sont pas contenues dans une des plages
    Set Rng = Union(Range("A1:C10"), Range("A3:b15"), Range("A6:d18"))
    MsgBox Rng.Address & "   " & Rng.Cells.Count & " ici on voit que c'est pas bon "
    'exemple avec plage2 et 3  contenues dans plage1
    Set Rng = Union(Range("A1:C10"), Range("A3:b9"), Range("A6:b6"))
    MsgBox Rng.Address & "   " & Rng.Cells.Count & " ici on voit que c'est bon "
End Sub

conclusion pour redresser cette lacune null besoins de intersect
on uni les plage et dans la boucle sur les cells et union on recréé la plage avec le rng mis ajour precedement dans la boucle avec la cel itérée

pour reprendre ton exemple
la sub de test

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub test1()
    Dim rang As Range, Rng As Range
    Set rang = Union(Range("A8:C10"), Range("B9:D11"))    ' on uni sans distinction
    Set Rng = UnionSansDoublonsVpat(rang)    ' on la recupere netoyée des doublons
    MsgBox rang.Address & vbCrLf & "il y a " & Rng.Cells.Count & " cellules dans la plage " & Rng.Address
End Sub

la function on ne peut plus simple

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
Function UnionSansDoublonsVpat(ByRef Rng As Range) As Range
    Dim rng2 As Range
    For Each cel In Rng.Cells
        If rng2 Is Nothing Then Set rng2 = cel Else Set rng2 = Union(rng2, cel) 'creation de rng2 avec la cel(1) ou union de rng2 a une seule cellule a chaque tours de boucle
    Next
    Set UnionSansDoublonsVpat = rng2
End Function

[eriiic]

Bonjour,

Gros plantage excel... Je ne peux pas dire à 100% si c'est dû aux essais, j'avais 2 autres classeurs d'ouverts.
Je vais voir si j'arrive à reproduire ça demain avec lui seul.
eric