plage vers tableau en VBA/ExcelRésolu

Question

Bonjour,

Liminaire : mes interventions sur VBA/Excel pourraient donner à penser que je suis déjà "avancé" dans ce domaine. Il n'en est rien ! (Je l'ai déjà exposé dans une autre discussion). Je n'en suis, dans ce domaine particulier, qu'aux balbutiements. Mes réponses ne faisaient quant à elles qu'appel à des connaissances en VB6 et à peu de connaissances en VBA.
Méthode d'approche : la même que celle que j'ai utilisée en ce qui concerne VB : "décorticage" systématique en vue d'améliorer certaines performances
Optique et souci du jour, dans ce but : comprendre ce que fait (et pourquoi) VBA/Excel en matière de transposition de plages continues (ne marche pas si plages discontinues) en tableaux dynamiques.

Ceci étant dit ===>>> la voilà, la question :

VBA/Excel permet (chacun le sait) d'obtenir un tableau dynamique à partir d'une plage de cellules.
Ainsi (exemple) :
  Dim T
  T = Range("A1:A3").Value)
fait que T est un tableau dynamique contenant toutes les valeurs de la plage "A1:
On le voit ensuite fort bien, en affectant à une autre plage les valeurs de T, ainsi, par exemple :
Range("B1:B3").Value =  T
Fort bien ...
Mais essayons de voir ce que contient T
 Essai 1  :
  For i  = 1 To UBound(T)
    MsgBox T(i)
  Next
Aboutit à yune erreur, selon laquelle "l'indice n'appartient pas à la sélection" !

Bizarre, me dis-je ===>> et me voilà lancé sur des essais en tous genres ===>>> et ho, miracle ! ceci marche par contre fort bien et sans erreur, et avec les bons résultats :
For i = 1 To UBound(T)
  MsgBox T(i, 1)
Next

tout se passe comme si le tableau constitué (T) était un tableau à 2 dimensions, dont seule la seconde colonne contiendrait les données à récupérer ! (je vois mal ce que contient la première colonne)

Mes soucis, maintenant : 
1) je n'aime pas "naviguer dans le brouillard" (ce n'est jamais bon) et préfère toujours connaître et maîtriser les tenants et aboutissants de chaque chose (je suis un vieux c**)
2) si je veux utiliser des tableaux dynamiques issus de plage en vue d'optimisation de traitements, j'ai vraiment besoin de savoir :
------ a) pourquoi Excel proicède ainsi ?
------ b) s'il existe une possibilité de procéder autrement, mais toujours en utilisant ce qu'offre Excel dans ce domaine (donc sans boucle pour créer soi-même un tableau dynamique, bien évidemment)

Et enfin : ma question : D'autres se sobnt-ils déjà penchés sur cet aspect ? Si oui : leurs conclusions (pas la constatation du fait, ce que j'ai déjà fait) et mleui'rs explications m'intéressent au plus haut point, car elles me permettront probablement d'approcher certains traitements d'une manière nettement plus performante.

Je remercie d'avance tous ceux qui auront lu cette discussion jusqu'au bout, déjà...
Je remercie surtout tous ceux qui, l'ayant fait (lu et analysé jusqu'au bout), me feront part de leurs expériences, conclusions et explications personnelles sur cette manière dont VBA/Excel transpose les ploages en tableaux dynamiques.

Je suis persuadé de ce qu'ensemble, nous avons mille fois plus de chances d'arriver à du "plus mieux" dans certains traitements.

Je n'en voudrai pas à ceux qui ne répondront pas, ni à ceux qui, répondant, ne seront pas certains de leurs explications et conclusions.
J'en voudrai par contre à ceux qui se contenteront de "constater un état de fait" (puisque j'ai déjà fait une telle constatation et que leurs "conseils d'utilisation" ne sraient certes pas une explication... celloe que j'attends).




____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

cs_Jack · Accepted Answer

Salut
Es-tu sûr que le nombre de dimensions augmente avec le nombre de colonnes ?
Je ne le pense pas.
T, non dimensionné, fais penser à un tableau, certes, mais c'est un Range.
A mon avis, il a toujours deux dimensions : La colonne et la ligne.
Prenons pour exemple une feuille dans laquelle j'ai mis les valeurs suivantes :
Colonne A : Ligne 1 à 4 : Chiffres de 1 à 4
Colonne B : Ligne 1 à 4 : Chiffres de 11 à 14
Colonne C : Ligne 1 à 4 : Chiffres de 21 à 24

et ce petit code :
Sub xxx()
    Dim T
    Dim r, z
    T = Range("A1:C4").Value
    On Error Resume Next
    For r = 1 To UBound(T, 1)
        For z = 1 To UBound(T, 2)
            Debug.Print "Ligne "; r, "Colonne "; z, "Valeur "; T(r, z)
        Next z
    Next r
End Sub
Le tableau T en question n'a que 2 dimensions.

Par contre, cela ne marche plus si les cellules ne sont pas contigües :
    T = Range("A1:A4,C1:C4").Value
ne renverra qu'un tableau à une seule colonne, la colonne A.

Dans l'essai en question, on assimile T à un tableau, mais Excel est plus riche car c'est en réalité un Range et les attributs d'un Range ne se limitent pas à .Value

Vala
Jack, MVP VB
NB : Je ne répondrai pas aux messages privés

Le savoir est la seule matière qui s'accroit quand on la partage (Socrate)

pile_poil · Answer

Sub macro1()
    t = Range("A1:B3").Value
    For i = 1 To UBound(t, 1)
      For j = 1 To UBound(t, 2)
        MsgBox t(j, i)
      Next
    Next
End Sub

comme tu peux le constater j'ai poussé un peu plus loin ton test
et on a bien deux dimmensions ! ce qui semble logique la feuille excel (et par là-même le Range) ayant deux dimensions (ligne, colonne)

comme excel commence à la ligne 1 et non pas à la ligne 0 le lbound de l'array est fixé lui aussi à 1 le code suivant donnant le meme résultat que le précédent

Sub macro1()
    t = Range("A1:B3").Value
    For i = LBound(t, 1) To UBound(t, 1)
      For j = LBound(t, 2) To UBound(t, 2)
        MsgBox t(i, j)
      Next
    Next
End Sub
en espérant que tu ne m'en voudra pas des cette réponse qui n'en est pas vraiment une 

si c'est la solution,						 penser : REPONSE ACCEPTEE

ucfoutu · Answer

Oui, pile-poil, oui (j'avais fait ce test, mais ai voulu, pour raisons de clarté, ne traiter que le cas, criant, d'une seule colonne) : une première colonne est systématiquement ajoutée (une colonne ==> 2 dimensions - 2 colonnes ===> 3 dimensions, n colonnes ===>> n+1 dimensions, quel que soit le cas de figure. (et je voudrais savoir pourquoi, précisément)
Ce travail de compréhension (ou de tentative de compréhension) de ce que fait (et pourquoi) VBA/Excel n'est à mon sens ni inutile, ni futile. Si on  arrive à en déterminer la raison (et non le résultat/mécanisme constaté), on s'offrira  mon sens des chances supplémentaires d'en profiter ici et là....



____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

ucfoutu · Answer

On le voit encore mieux avec ceci :
Dim T
T = Range("A1:A3").Value ' donc une seule série de valeurs qui devrait tenir dans une seule dimension

For i = 0 To 10 '10 pour être sûr de dépasser. 0 pour cçompmpencer "en deçà"
  On Error Resume Next
  MsgBox LBound(T, i)
  If Err Then
    MsgBox "pas de dimension " & i
  End If
  On Error GoTo 0
Next


____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

pile_poil · Answer

je ne suis pas d'accord avec toi ! 
il n'y a jamais plus de deux dimensions   quelque soit le nombre de colonnes sélectionnées
c'est simplement le UBound qui change
pour une colonne LBound 1 UBound 1
pour deux colonnes LBound 1 UBound 2
pour trois colonnes LBound 1 UBound 3
 
pour mémoire un array à deux dimensions c'est Array(x,y)
à trois dimensions c'est Array(x,y,z)
dans un tableau dynamique seule la derniere dimension est modifiable par l'instruction redim
la taille de chaque dimension est accessible  par LBound et UBound
dans le cas d'un Array à trois dimensions Array(x,y,z)
si tu fais une boucle pour explorer toutes les données dans la dimension Y tu écriras
for boucle = LBound(Array,2) to UBound(Array,2)
pour faire la meme chose dans la dimmension Z
for boucle = LBound(Array,3) to UBound(Array,3)

il me semble que tu fais la confusion entre nombre de dimensions et nombre d'enregistrements dans une dimension

un tableau excel a deux dimensions : lignes et colonnes
T = Range("A1:A2") 
est strictement équivalent à
Dim T(1 to 1,1 to 2) as variant
T(1,1)= range("A1").value
T(1,2)= range("A2").value

tu remarqueras d'ailleurs que Range("A1") te donnes bien les deux dimensions colonne "A" ligne "1"
autre preuve que ce sont deux dimmensions et rien d'autre c'est que :
Range ("A1") est strictement identique à Cells(1,1)
avec la particularité que dans le range c'est colonne puis ligne 
alors que dans Cells c'est ligne puis colonne

dans le cas de:
T = Range("A1:Z999")  
le Dim devient
Dim T(1 to 26,1 to 999) as variant
pour remplir le tableau à ce moment là vaut mieux faire une boucle


si c'est la solution,						 penser : REPONSE ACCEPTEE

ucfoutu · Answer

Oui, mais (voir plus haut) on devraitr pouvoir éviter la boucle, puisqu'EZxcel sait le faire !
pour mémoire :
Range("B1:B3").Value = T
restitue bien les valeurs du tableau dynamique
En ce qui concerne maintenant loe fait quer T serait "une plage" (un range, donc :
Non, et on peut le vérifier ainsi :
Dim T, plage As Range
T = Range("A1:A3").Value
Set plage = Range("A1:A3")
Dim cell As Range
For Each cell In plage ' et là : pas d'erreur
  MsgBox cell.Value 
Next
For Each cell In T ' etr là : erreur assurée car T est vraiment un tableau dynamique et non un "range" !
  MsgBox cell.Value
Next

J'appelle par ailleurs l'attention sur le fait que :
T = Range("A1:A3").Value
ne constitue pas une plage, mais vraiment un tableau de valeurs
Va falloir chercher plus finement, selon ce que je pense 
____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

ucfoutu · Answer

etr d'ailleurs, il suffirait de :
Dim T As Range, plage As Range
T = Range("A1:A3").Value

pour constater que T ne saurait être un "range" (erreur assurée)
Pour avoir un range, il faut un Set et pas de .value !
____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

ucfoutu · Answer

Et enfin, ceci démontre encore mieux que nous avons bien affaire à un tableau dynamique :

Dim T As Variant, plage As Range
MsgBox TypeName(T) ' =>> vide
T = Range("A1:A3").Value
MsgBox TypeName(T) '===>> tableau de variants



____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

ucfoutu · Answer

Je me demande si ce petit essai ne serait pas plus efficace pour justifier mon étonnement (et par conséquent la question posée) en ce qui concerne la transposition, par VBA, de plage en Tableau dynamique

Sur une feuille : 2 bouytons de commande
On clique le premier, on voit ... puis on clique le second ...

Private Sub CommandButton1_Click()
  'on constitue ici une petite plage de travail
  Range("A1") = "A1"
  Range("B1") = "B1"
  Range("C1") = "C1"
  Dim T As Variant
  T = Range("A1:c1").Value
  MsgBox LBound(T, 1) & "  " & UBound(T, 1) '  << === pas d'erreur : affichera 1
  MsgBox LBound(T, 2) & "  " & UBound(T, 2) ' <<< === pas d'erreur et affichera 3
 'on a donc
  For i = 1 To 3
    MsgBox T(1, i) ' on voit bien un tableau à 2 dimensions, dont seule la seconde est utilisée
  Next
  'on envoit maintenant ce tableau (tel quel, avec ses 2 dimensions) dans plage A4:C4
  Range("A4:C4").Value2 = T ' <<<<<<======= COMPARONS CECI (1) AVEC CE QUI EST ANOTE (2) PLUS BAS
  MsgBox "observez que c'est bon et que la plage A4 a bien été remplie comme prévu a partir de T en 2 dimensions"
  ' on va maintenant redimensionner T (sans préserve) et le remplir comme yun tableau habituel, de l'indice 0 à l'indice 2
  ' par les mêmes cellules. Pour y voir polus clair, on va transfortmer le 2ème article
  ReDim T(2)
  T(0) = Range("A1").Value
  T(1) = Range("B1").Value & "z"
  T(2) = Range("C1").Value
  Range("A5:C5").Value = T '  <<<<<<======= COMPARONS CECI (2) AVEC CE QUI EST ANOTE (1) PLUS HAUT
  MsgBox "regardez : même résultat (mise à part la petite transfo témoin) alors que tableau dimensionné de manière classique"
  ' pourquoi alors VBA a-t-il décidé de traiter différemment son tableau (le 1er) ? (lers résultats sont les mêmes !)
End Sub

Voyons maintenant :

Private Sub CommandButton2_Click()
  'Essayons alors, ma foi, de refaire manuellement ce que semble faire VBA
   Dim T(1, 3)
     T(1, 1) = Range("A1").Value
     T(1, 2) = Range("B1").Value
     T(1, 3) = Range("C1").Value
     Range("A6:C6").Value = T
     MsgBox "rien ne se passe !... qu'a donc ajouté et caché VBA ?"
End Sub
 
C'est à mon sens plus parlant quant à mes interrogations ...
____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

pile_poil · Answer

tu oublies juste une chose 
c'est qu'un tableau excel commence à l'indice 1
alors qu'un array si tu ne le lui stipule pas une valeur de départ il commence à l'indice 0
donc ton tableau dimensionné par 
Dim T(1, 3)
est en réalité dimmensionné de la façon suivante
Dim T(0 to 1, 0 to 3)' deux colonnes et quatre lignes
alors que quand tu fais
T = Range("A1:B1")
cela implique une déclaration (et elle est sous entendue)équivalente à 
dim T(1 to 2,1 to 1) as variant 'deux colonnes et 1 ligne
si c'est la solution,						 penser : REPONSE ACCEPTEE

CTAC · Answer

Bonjour,

On obtient un tableau a 2 dimensions (lignes et colonnes)

Pour obtenir un tableau a 1 dimension

Function TabDyn(Plg As Range)
    If Plg.Columns.Count = 1 Then
        TabDyn = Application.Transpose(Plg)
    ElseIf Plg.Rows.Count = 1 Then
        TabDyn = Application.Transpose(Application.Transpose(Plg))
    End If
End Function

Sub testColonne()
    MsgBox TabDyn(Range("A1:A3"))(2)
End Sub

Sub testLigne()
    MsgBox TabDyn(Range("A1:C1"))(2)
End Sub


Ceci sur XL2007

ctac

ucfoutu · Answer

Merci, Pile-poil...
Mon énervement m'avait faitr oublier que je devais forcer à démarrer à 1
J'ai donc apporté la correction à mon click sur CXommandButton2, qui maintenant réagit comme attendu.
Private Sub CommandButton2_Click()
   Dim T(1 To 1, 1 To 3)
     T(1, 1) =  Range("A1").Value
     T(1, 2) = Range("B1").Value
     T(1, 3) = Range("C1").Value
     Range("A6:C6").Value = T
End Sub

Mais c'est piren, car ce click-là et le résultat obtenu montrent bien que le T du bouton CpmmùandButton1 était bien un tableau dynamique à part entière (et non autrre chose  , puisque l'on peutr intégralement le reconstituer et que, une fois terminéez la reconstitution :
Range("A6:C6").Value = T
remplit la plage A6:C6 exactement comme le faisait le T issu de 
T =  Range("A1:c1").Value
Bien ... fort bien... :
LA QUESTION PREMIERE reste donc on ne peut plus crucialement présente !
  Pourquoi   diable VBA agit-il ainsi, alors que nous avons pu voir plus haut qu'un tableau dimensionné et alimenté de manière habituelle   ===>>>
pour mémoire :
ReDim T(2)
  T(0) = Range("A1").Value
  T(1) = Range("B1").Value & "z"
  T(2) = Range("C1").Value
  Range("A5:C5").Value = T 
aboutissait au même (rigoureusement le même) résultat
C'est ce "POURQUOI" qui m'interepelle et m'intéresse grandement.

A CXTAC : oui, mais ce n'est pas là le propos de ma question. (Merci quand-même).


____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

pile_poil · Answer

parce que quand tu explores un array tu vas de LBound à UBound
et que tu donnes lors du dim à LBound n'importe quelle valeur (0 par défaut en VB6, 1 par défaut dans le cas qui nous intéresse ou bien pourquoi pas 10 voire 100) ça reste le premier élément dans la dimension concernée
ainsi 
dim array(1 to 2)  fait la meme taille que 
dim array(100 to 101)  ou que 
dim array(0 to 1)

dans le cas qui nous intéresse il est plus commode de démarrer l'array à 1 pour garder la meme logique d'indice avec la feuille excel 

si c'est la solution,						 penser : REPONSE ACCEPTEE

ucfoutu · Answer

Merci, pile-poil, mais toujours pas convaincu, dès lors que mes tests (en boucle) de vitesse sont de même résultat que l'on alimente ensuite une plage à partir d'un tableau "de type défini par VBA" ou qu'on le fasse à partir d'un tableau "classique". La justification (si présente) du choix de ce mécanisme est probablement ailleurs... mais où, alors  ? 

____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

pile_poil · Answer

relis attentivement l'aide de VB6 concernant les Array 
tu verras que mon explication est la bonne

si c'est la solution,						 penser : REPONSE ACCEPTEE

ucfoutu · Answer

Bon...
Nous vous arrivés au bout de ce cirque, après enquête poussée ici et là  :
L'équipe de développement de VBA n'a pas voulu se compliquer la tâche en traitant différents cas de figure en matière de transposition de plage en tableau.
Elle a opté pour une seule méthode, universelle, quel que soit le cas de figure (une seule colonne, une seule ligne ou une plage continue comportant plusieurs lignes et colonnes) : dans tous les cas : 2 dimensions (même quand une seule pourrait suffire).
Elle a dans le même élan décidé que le 1er indice, tant des lignes que des colonnes, serait 1.

Parallèlement et pour faciliter le développement (et l'importation de tableaux), elle a mis sur pied un mécanisme admettant des tableaux à une seule dimension commençant à 0 ou 1, peu importe. Ceci dans le but de faciliter des importations autres. Rien n'a toutefois été fait dans le sens inverse (en vue d'exportations), ce qui fait qu'un tableau importé depuis une appli autre puis traité sur une feuille Excel et enfin réexporté vers l'appli d'origine doit être "retraité/recomposé" dans le cas d'une seule colonne (c'est bien dommage)
Merci à tous 




____________________
Très intéressante fable, L'OISELEUR, L'AUTOUR ET L'ALOUETTE !
Cliquer sur "Réponse acceptée" (en bas d'une solution avérée adéquate) rendra service à d'autres. PENSEZ-Y.

Plage vers tableau en VBA/Excel

16 réponses

Votre réponse

Discussions similaires