Manipulation tableau avec VBA

**framjx** · 17/09/2018, 19h57

Bonjour,

Je débute en VBA et je me demandais s'il est possible d'attribuer les données de colonnes non-accolées d'un tableau excel à une variable tableau d'un code vba:
Par exemple, j'aimerais attribuer les données de la cellule A2 jusqu'à la dernière cellule A non-vide dans la première 'colonne' de ma variable et les données de la cellule C2 jusqu'à la dernière cellule C non-vide dans la deuxième 'colonne' de ma variable; les données de la colonne B étant ignorées.

Je pensais que le code suivant pouvait fonctionner mais ce n'est pas le cas, seules les données de la colonne A sont récupérées. Je ne comprends pas pourquoi et je n'arrive pas à trouver l'explication.

Code :

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

1
2
3
4
5
6
Sub test()
 
tableau01 = Union(Range("A2", Range("A2").End(xlDown)), Range("C2", Range("C2").End(xlDown)))
MsgBox (UBound(tableau01, 1))
 
End Sub

j'ai proposé un fichier d'exemple aussi:
test.xlsm

Si jamais quelqu'un a une idée et l'envie d'expliquer, je suis preneur !
Merci d'avance.
Adrien

Invité · 18/09/2018, 04h03

Envoyé par framjx

Bonjour,

Vous aurez certainement des réponses qui vont vous torcher votre tableau en 2 lignes de code et la mienne paraîtra plus que "lourdingue" mais c'est ce que j'utilise

Option Explicit en tête d'un module, userform oblige à déclarer les variables. Réglage par défaut dans Option Editeur, c'est ch... au début, mais vous allez gagner du temps au final.
La variable Tableau est déclarée Public pour une utilisation dans tous les modules ou Userform.
Pour plus de clarté, j'utilise des procédures paramétriques à partir d'un programme principal. Exemple, ici pour charger la matrice Tableau01, j'indique que le tableau est à charger dans l'onglet Feuil1. Quand je travaille sur la procédure, je n'ai plus à me soucier du nom de mon onglet au risque de me tromper. Et c'est plus facile quand vous devez replonger dans votre code plusieurs mois après.
J'utilise des variables objet plutôt que travailler directement avec des onglets ou des ranges. Vous bénéficiez ainsi de l'intellisens. Une fois instanciée votre variable avec Set XXX =, si vous mettez un point derrière votre variable vous aurez accès à toutes les propriétés, méthodes et événements applicables à l'objet, c'est vraiment un gain de temps lorsque vous commencez (et même après).
Donnez des noms clairs à vos variables ou expliquez vos conventions à ceux qui vont maintenir votre code par la suite....
Dernier point, chaque Set XXX = en début de procédure, doit avoir son Set XXX = Nothing en fin de procédure. Les procédures intermédiaires utilisant la variable sont passées en paramètres. Là aussi c'est un gain de temps pour repérer une erreur dans un programme complexe si vous utilisez des variables Public ou éviter les plantages par saturation de pile.

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
 
Option Explicit
 
Public Tableau01() As Variant
 
Sub ProgrammePrincipal()
 
Dim ShTableau01 As Worksheet
Dim J As Long
 
    Set ShTableau01 = Sheets("Feuil1")
    ChargerLeTableau01 ShTableau01
 
    For J = LBound(Tableau01, 1) To UBound(Tableau01, 1)
        Debug.Print Tableau01(J, 0) & ", " & Tableau01(J, 1)
    Next J
 
    Set ShTableau01 = Nothing
 
End Sub
 
Sub ChargerLeTableau01(ByVal FeuilleTableau As Worksheet)
 
Dim DerniereLigne As Long, I As Long, Colonne1 As Long, Colonne2 As Long
Dim AireSelectionnee As Range, Cellule As Range
 
    With FeuilleTableau
         DerniereLigne = .Cells(.Rows.Count, 1).End(xlUp).Row
         Colonne1 = 1
         Colonne2 = 3
         Set AireSelectionnee = .Range(.Cells(2, Colonne1), .Cells(DerniereLigne, Colonne1))
 
         ReDim Tableau01(AireSelectionnee.Rows.Count - 1, 1)
         I = 0
         For Each Cellule In AireSelectionnee
             Tableau01(I, 0) = Cellule
             Tableau01(I, 1) = Cellule.Offset(0, Colonne2 - Colonne1)
             I = I + 1
         Next Cellule
         Set AireSelectionnee = Nothing
    End With
 
End Sub

**boisgontierjacques** · 18/09/2018, 05h44

Bonjour,

Code :

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

1
2
3
4
5
Sub Test()
  Set Rng = [A2:C6]
  Tbl = Application.Index(Rng, Evaluate("Row(1:" & Rng.Rows.Count & ")"), [{1,3}])
  [H2].Resize(UBound(Tbl), UBound(Tbl, 2)) = Tbl
End Sub

Avec une fonction Function FiltreArrayCol(tableau, ColResult) (réutilisable):

Code :

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

1
2
3
4
5
Sub FiltreColonnes()
  a = [A2:C5]                                   ' tableau a()
  b = FiltreArrayCol(a, Array(1, 3))     ' on prend les colonnes 1, 3
  [A11].Resize(UBound(b), UBound(b, 2)) = b
End Sub

Code :

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

1
2
3
4
5
6
7
8
9
10
Function FiltreArrayCol(tableau, ColResult)
  Dim b(): ReDim b(LBound(tableau) To UBound(tableau), 1 To UBound(ColResult) - LBound(ColResult) + 1)
  decal = 1 - LBound(ColResult)
  For i = LBound(tableau, 1) To UBound(tableau, 1)
    For c = LBound(ColResult) To UBound(ColResult)
      b(i, c + decal) = tableau(i, ColResult(c))
    Next c
  Next i
  FiltreArrayCol = b
End Function

Fonction PrendPartieArray(TblE, Début, Taille, ColRécup) :

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 PrendPartieArrayEssai()
  TblE = Range("A1:C22")
  TblS = PrendPartieArray(TblE, 10, 5, Array(3, 1, 2))
  If Not IsEmpty(TblS) Then [e2].Resize(UBound(TblS), UBound(TblS, 2) - LBound(TblS, 2) + 1) = TblS
End Sub
 
Function PrendPartieArray(TblE, Début, Taille, ColRécup)
  n = 0
  fin = Début + Taille - 1: If fin > UBound(TblE) Then fin = UBound(TblE)
  Dim TblS(): ReDim TblS(1 To Taille, LBound(ColRécup) To UBound(ColRécup))
  For i = Début To fin
    n = n + 1
    For k = LBound(ColRécup) To UBound(ColRécup): TblS(n, k) = TblE(i, ColRécup(k)): Next k
  Next i
  If n > 0 Then PrendPartieArray = TblS
End Function

Boisgontier

**framjx** · 19/09/2018, 19h03

Bonjour,

Je vous remercie tous les 2 pour votre retour (et je m'excuse pour ma réponse tardive !)

Je ne sais pas si vous avez bien compris ma demande; je ne souhaite pas avoir le résultats affiché dans excel. Je souhaite juste que ma variable dans l'algorithme ne comporte que les colonnes A et C. Je suis surpris qu'il n'y ait pas un moyen simple de faire ça (sans passer par une boucle for). Je suis d'autan plus surpris que si on fait:

Code :

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

1
2
 
Union(Range("A2", Range("A2").End(xlDown)), Range("C2", Range("C2").End(xlDown))).select

La bonne plage de cellule est sélectionnée (dans l'exemple A2:A6 et C2:C6).

Mais si on fait:

Code :

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

1
2
 
mavariable = Union(Range("A2", Range("A2").End(xlDown)), Range("C2", Range("C2").End(xlDown))).value

Ben la variable ne prend que les valeurs de A2:A6. C'est quand même surprenant, non?

**boisgontierjacques** · 19/09/2018, 19h23

Bonsoir,

>Je ne sais pas si vous avez bien compris ma demande

C'est toi qui n'a rien compris.
Ta solution ne prend que la première colonne.
Entre mes solutions qui fonctionnent et ta solution qui ne fonctionne pas, il faut savoir choisir.

>je ne souhaite pas avoir le résultats affiché dans excel.

C'est pour tester.

Tbl() est un Array() qui contient A2:A6 et C2:C6

Code :

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

1
2
3
4
5
 
Sub Test()
  Set Rng = [A2:C6]
  Tbl = Application.Index(Rng, Evaluate("Row(1:" & Rng.Rows.Count & ")"), [{1,3}])   ' Tbl est un Array()
End Sub

>Ben la variable ne prend que les valeurs de A2:A6. C'est quand même surprenant, non?
ça ne prend que la première colonne

Boisgontier

**framjx** · 19/09/2018, 21h38

Je ne comprends pas pourquoi vous vous énervez, je ne pense pas avoir été agressif. Ce n'était pas mon but en tout cas.

Je n'ai effectivement pas bien compris votre solution, excusez moi. C'est effectivement ce que je cherchais

Par contre:

Ta solution ne prend que la première colonne.
Entre mes solutions qui fonctionnent et ta solution qui ne fonctionne pas, il faut savoir choisir.

Merci, je l'ai bien compris que ma solution ne prend que la première colonne, c'est tout l'objet de ma question ...

>Ben la variable ne prend que les valeurs de A2:A6. C'est quand même surprenant, non?
ça ne prend que la première colonne

Merci, j'ai compris, la première fois suffisait.

Maintenant, qu'on a dit tout ça quelqu'un sait expliquer pourquoi ma solution ne fonctionne pas ? Je ne comprends pas dans le principe pourquoi elle ne fonctionne pas.

**unparia** · 20/09/2018, 07h50

Bonjour

Maintenant, qu'on a dit tout ça quelqu'un sait expliquer pourquoi ma solution ne fonctionne pas

Parce que VBA ne sait pas mettre dans une seule matrice les données de plusieurs plages distinctes

Comme tu l'as remarqué toi-même, il est par contre aisé de constituer par la méthode Union une plage discontinue, que la méthode Range.copy te permet d'injecter, sur une feuille tremplin, sous la forme d'une seule plage de données (continue, elle). Il te suffit alors d'attribuer à une matrice (un tableau) de ton choix, les valeurs de cette plage de ta feuille tremplin.
Tu auras ainsi ta matrice telle que tu la voulais et donc limitée à ce seul but :

je ne souhaite pas avoir le résultats affiché dans excel. Je souhaite juste que ma variable dans l'algorithme ne comporte que les colonnes A et C

Je ne comprends pas à quoi elle pourrait être "utile", mais c'est là ta seule affaire.

**Pierre Fauconnier** · 20/09/2018, 08h19

Salut.

Au delà des réponses fournies à ta question, je serais curieux de connaître la finalité de cette manipulation par tableau de colonnes discontinues. Que vas-tu faire de ton tableau, après?

**framjx** · 22/09/2018, 15h52

Encore une fois désolé pour ma réponse un peu tardive!

@unparia : Merci d'avoir répondu à ma question!

@unparia et Pierre Fauconnier:

Concernant la finalité de ma demande:
En me renseignant sur le VBA, j'ai cru comprendre que les calculs étaient beaucoup plus rapides en passant par des manipulations de variables plutôt que par des manipulations de tableau excel. Dans mon cas, les tableaux que je dois manipuler ne font pas 3 colonnes, ils en ont beaucoup et les colonnes d'intérêts ne sont pas accolées. Par ailleurs, je ne dispose pas de la possibilité de créer mon propre template de tableau, plus adapté.

Maintenant, je n'en suis pas sûr, mais il me semble qu'un code sera d'autant plus efficace que les variables tableau manipulées sont de petite taille. Quel intérêt de se traîner un gros tableau quand seulement un nombre limité de colonnes est intéressant? Vu que le mode '.select' arrivait à sélectionner ce que je voulais, j'avais l'impression que le mode '.value' devait fonctionner mais que je l'utilisais mal.

Sinon, ce n'est pas ma finalité dans l'immédiat, mais j'ai l'impression que si le mode '.value' fonctionnait avec des colonnes discontinues, cela pourrait être intéressant pour faire des opérations matricielles.

**patricktoulon** · 22/09/2018, 20h39

re
a analyser tranquillement

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
Sub test()
    With ActiveSheet
        With Union(.Range("A2", .Range("A2").End(xlDown)), .Range("C2", .Range("C2").End(xlDown)))
            x = .Rows.Count
            .Select
            .Copy Destination:=.Cells(Rows.Count - x, Columns.Count - 2)
            'le fait de copier l'union vers une destination dans le meme ou un autre sheet te merge les colonnes
        End With
        ' on recupere le tableau avec le .value de la destination
        With .Cells(Rows.Count - x + 1, .Columns.Count - 2).Resize(x, 2)
            tableau = .Value
            .Clear
        End With
    End With
    'ici on bien notre tableau et les valeur et type de valeur sont identiques a la plage initiale
    'regardons voir
    Cells(2, 6).Resize(UBound(tableau), UBound(tableau, 2)) = tableau
End Sub
 
 
Sub test2()
    tableau = Union(Range("A2", Range("A2").End(xlDown)), Range("C2", Range("A2").End(xlDown))).Value
    MsgBox UBound(tableau, 2)
    'il est clair qu'ici la colonne b n'est pas suprimée on ne peut pas injecter des conlonne discontinues dans une variable tableau
    ' la variable tableau se dimentionne en fonction de la 1°cells et la derniere en l'occurence ici "C6" donc 3 colonne
End Sub
 
 
Sub test3()
    With ActiveSheet: Set P = .Range("A2:C" & .Cells(Rows.Count, 1).End(xlUp).Row): End With
    Vlig = Evaluate("ROW(1:" & P.Rows.Count & ")")
    tableau = Application.Index(P.Value, Vlig, Array(1, 3))
    'visualisation sur le sheets ou utilisation
    Cells(2, 6).Resize(UBound(tableau), UBound(tableau, 2)) = tableau
    '*********************************************************************
    'ici donc visuellement ca marche mais tes donnes sont du texte
    'donc a reconvertir pour travailler avec en tant numeric si ca en est
    'date si ca en est etc......
    'cette methode n'est destiné a produire une liste en format texte pour remplir une listebox multicolonne par exemple 
    '********************************************************************
End Sub

**boisgontierjacques** · 22/09/2018, 20h43

Bonsoir,

Pour prendre des colonnes disjointes:

Code :

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

1
2
  Set Rng="A2:C6")
  Tbl = Application.Index(Rng, Evaluate("Row(1:" & Rng.Rows.Count & ")"), [{1,3}])

C'est difficile de faire + simple.

Boisgontier

**patricktoulon** · 22/09/2018, 20h48

Oui boisgontier mais attention au type de valeur ici toute les valeurs sont du texte et pour exploiter c'est misère

**boisgontierjacques** · 23/09/2018, 00h05

>mais attention au type de valeur ici toute les valeurs sont du texte

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
Sub SelectionColonnes()
  Set champ = Range("A2:D7")
  b = FiltreChampColonnes(champ, Array(1, 4, 2))
  [I2].Resize(UBound(b), UBound(b, 2)) = b
  x = b(3, 1)
  y = x * 2
  MsgBox y
End Sub
 
Function FiltreChampColonnes(Rng, ColResult)
   FiltreChampColonnes = Application.Index(Rng, Evaluate("Row(1:" & Rng.Rows.Count & ")"), ColResult)
End Function

Et ça, c'est la méthode la + rapide

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
 
Sub FiltreColonnes()
  a = [A2:C5]                            ' tableau a()
  b = FiltreArrayCol(a, Array(1, 3))     ' on prend les colonnes 1, 3
  [A11].Resize(UBound(b), UBound(b, 2)) = b
End Sub
 
Function FiltreArrayCol(tableau, ColResult)
  Dim b()
  ReDim b(LBound(tableau) To UBound(tableau), 1 To UBound(ColResult) - LBound(ColResult) + 1)
  decal = 1 - LBound(ColResult)
  For i = LBound(tableau, 1) To UBound(tableau, 1)
    For c = LBound(ColResult) To UBound(ColResult)
      b(i, c + decal) = tableau(i, ColResult(c))
    Next c
  Next i
  FiltreArrayCol = b
End Function

Boisgontier

Invité · 23/09/2018, 04h02

Envoyé par boisgontierjacques

Salut Jacques,

ça décoiffe !

**framjx** · 26/09/2018, 19h26

Merci pour vos différents retours. Je passe la discussion en résolue.

**boisgontierjacques** · 29/09/2018, 05h46

Bonjour,

Fonction de conversion Tableau(champ union) d'un champ Union en Array()

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 essai()
  Set Rng = Range("A2:A10,C2:C10,E2:E10")   ' champ union
  Tbl = Tableau(Rng)
  [I2].Resize(UBound(Tbl), UBound(Tbl, 2)) = Tbl
End Sub
 
Function Tableau(Rng)
  NbLig = Rng.Rows.Count: NbCol = Rng.Areas.Count
  Dim Tbl():  ReDim Tbl(1 To NbLig, 1 To NbCol)
  For i = 1 To NbCol
    For j = 1 To NbLig: Tbl(j, i) = Rng.Areas(i)(j): Next j
  Next i
  Tableau = Tbl
End Function

Boisgontier

**Philippe Tulliez** · 29/09/2018, 10h21

Bonjour Jacques,
Bravo pour la performance

Cependant, après avoir regardé le fichier proposé au départ par l'initiateur de ce post, je constate que l'on peut résoudre cela facilement avec la méthode AdvancedFilter.
Je ne fais pas du prosélytisme pour cette méthode et certes la vitesse d'exécution ne rivalise pas avec la manipulation de variables tableaux mais en général je privilégie les outils natifs d'excel et surtout la facilité de maintenance en paramétrant les desiderata de mes utilisateurs clients.
En combinant les méthodes AdvancedFilter et TextToColumn, il est aisé de changer en quelques secondes les besoins de l'utilisateur final.
Alors évidemment que j'ai déjà pris l'option de l'utilisation de variable tableau pour de gros travaux de comparaison de millions de lignes et sur plusieurs fichiers qui auraient pris deux heures pour obtenir le résultat final et que j'avais ramené à 10 minutes mais bien souvent les besoins des utilisateurs ne sont pas aussi importants.

Je me permets donc de proposer une solution avec cette méthode (sur 1.000.000 de lignes avec 4 colonnes en sources et 3 colonnes en exportations et sans critères +/-10 secondes ce qui évidemment avec un tableau aurait mis sans aucun doute 1 seconde. Mais si l'on traite une centaine de milliers de lignes nous serons à 2 secondes avec AdvancedFilter ce qui pour moi est très acceptable

Procédure d'exportation

Code :

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

1
2
3
4
5
6
7
8
9
Function ExportByAdvancedFilter(areaSource As Range, areaTarget As Range, LabelList As String)
  With areaTarget
  .CurrentRegion.Cells.Clear
  .Value = LabelList
  .TextToColumns Semicolon:=True, DataType:=xlDelimited
   Set areaTarget = .CurrentRegion
  End With
  areaSource.AdvancedFilter xlFilterCopy, CopyToRange:=areaTarget
End Function

Procédure qui invoque la fonction

Code :

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

1
2
3
4
5
6
7
8
9
Sub testExportByAdvancedFilter()
  Dim rngSource As Range, rngTarget As Range, Labels As String
  With ThisWorkbook.Worksheets("feuil1")
    Set rngSource = .Range("A1").CurrentRegion
    Set rngTarget = .Range("G1")
  End With
  Labels = "XXX1;XXX4;XXX3" ' Etiquettes que l'on veut exporter
  ExportByAdvancedFilter rngSource, rngTarget, Labels
End Sub

Nom : 180929 AdvancedFilter Image.png
Affichages : 1012
Taille : 28,4 Ko

[EDIT]
Et bien curieusement, j'ai testé la procédure de Jacques avec mêmes données et j'arrive à 25 secondes avec les mêmes fichiers. J'avoue moi-même être surpris.
J'ai fait un enregistrement sous un autre nom et refait les tests et c'est identique.

Procédures testées

Code :

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

1
2
3
4
5
Sub essai()
  Set Rng = Range("A2:A1000000,C2:C1000000,B2:B1000000")   ' champ union
  Tbl = Tableau(Rng)
  [I2].Resize(UBound(Tbl), UBound(Tbl, 2)) = Tbl
End Sub

Code :

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

1
2
3
4
5
6
7
8
Function Tableau(Rng)
  NbLig = Rng.Rows.Count: NbCol = Rng.Areas.Count
  Dim Tbl():  ReDim Tbl(1 To NbLig, 1 To NbCol)
  For i = 1 To NbCol
    For j = 1 To NbLig: Tbl(j, i) = Rng.Areas(i)(j): Next j
  Next i
  Tableau = Tbl
End Function

**boisgontierjacques** · 29/09/2018, 11h42

1- Je voulais répondre à la demande initiale de la personne qui tenait absolument à utiliser l'union de champs.
2 - Je me doutais bien que cette méthode n'est pas la + rapide mais pour 1000 lignes, le temps doit être correct.
3 - Le inconvénients du FA(titres de colonnes, champ critère). Pour des opérations classiques sur les Arrays, il est + simple de recourir à une bibliothèque de fonctions persos que d'interfaçer avec le FA (création d'un champ critère).
4- Je ne manque pas d'exemples sophistiqués avec le FA.

Boisgontier

**Philippe Tulliez** · 29/09/2018, 12h07

Bonjour Jacques,

3 - Le inconvénients du FA(titres de colonnes, champ critère). Pour des opérations classiques sur les Arrays, il est + simple de recourir à une bibliothèque de fonctions persos que d'interfaçer avec le FA (création d'un champ critère).

Il n'y a justement pas de plages des critères.
C'est un des gros avantages justement des filtres avancés et de la méthode AdvancedFilter sur la méthode Copy lorsque l'on veux copier une partie des colonnes et ce sans aucun critères.

J'ai bien entendu écrit une procédure plus sophistiquée que celle que j'ai proposée où en fonction du besoin de l'utilisateur j'exporte avec ou sans critères sur la même feuille, une autre feuille, un autre classeur, toutes les colonnes ou une partie des colonnes, sur un plage simple ou un tableau structuré avec ou pas de doublons et simplement en modifiant les paramètres que je place dans une feuille spécifique et cachée (xlVeryHidden)