Manipulation tableau avec VBA

Version imprimable

17/09/2018, 19h57
framjx

1 pièce(s) jointe(s)

Manipulation tableau avec VBA
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:

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:
Pièce jointe 412603

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

Citation:

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 :toutcasse:

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....:sm:
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:

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

Bonjour,

Code:

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:

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:

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:

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

19/09/2018, 19h03
framjx
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:

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:

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?
19/09/2018, 19h23
boisgontierjacques
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:

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
19/09/2018, 21h38
framjx

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:

Citation:

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 ...

Citation:

>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.
20/09/2018, 07h50
unparia

Bonjour

Citation:

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 :

Citation:

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.
20/09/2018, 08h19
Pierre Fauconnier

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?
22/09/2018, 15h52
framjx

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.

re
a analyser tranquillement

Code:

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

22/09/2018, 20h43
boisgontierjacques
Bonsoir,

Pour prendre des colonnes disjointes:
Code:

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
22/09/2018, 20h48
patricktoulon

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

2 pièce(s) jointe(s)

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

Code:

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:

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

23/09/2018, 04h02
Invité

Citation:

Envoyé par boisgontierjacques

Salut Jacques,

ça décoiffe ! :plusser: :ccool:
26/09/2018, 19h26
framjx

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

Bonjour,

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

Code:

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

1 pièce(s) jointe(s)

Bonjour Jacques,
Bravo pour la performance :plusser:

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:

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:

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
Pièce jointe 415933

[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:

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:

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

29/09/2018, 11h42
boisgontierjacques

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
29/09/2018, 12h07
Philippe Tulliez

Bonjour Jacques,

Citation:

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)
29/09/2018, 14h43
RyuAutodidacte

Bonjour,

:plusser: pour vos codes => @boisgontierjacques et @Philippe Tulliez
il est toujours bien de voir les différents types de solutions ;)

Edit : :oops: j'ai oublié Patrick pour la version "test" :plusser: ;)
29/09/2018, 14h55
Philippe Tulliez

Bonjour,
Merci Ryu pour ton vote positif, cela fait toujours plaisir :plusser: