Utilisation des tableaux structurés en VBA

Version imprimable

06/06/2013, 14h42
Invité

Utilisation des tableaux structurés en VBA
Bonjours, j'suis nouveau ici.

Je suis un peu Lewis29 dans cette discussion, je n'arrive pas à tirer le bénéfice qu'offre les tableaux dans les calculs. Par contre, pour créer un tableau et utiliser les référence du tableau, c'est bel et bien comme l'a dit issoram, à savoir l'option ListObjet.

Tu peut déclarer un :

Code:

Dim Table As ListObject

, puis :

Code:

Set Table = ThisWorkbook.Worksheets("Feuil1").ListObjects("Tableau4")

Si ton tableau s'appel Tableau4 dans Excel, puis
Code:

1 2 3 With Table.Range(i + 1, 1) .Value = i * 10 End With
Ici, le i est un

Code:

Dim i As Long

dans cette exemple
et le +1 du Table.Range signifie que c'est la 1ère ligne utilisable du tableau (la 1ère ligne correspond au titre de la 1ère colonne du tableau)
et le 2ème 1 est la 1ère collonne du Tableau.

Cependant, je suis comme toi, je n'arrive pas à utiliser les références structurées.

Bonjour,

Ce à quoi se réfère ton lien (et que je citais dans mon message précédent) ce sont bien les références structurées qui s'appliquent aux objets Tableaux (ListObjet en VBA) et permettent d'accéder plus facilement à des éléments de ces derniers.
Après 30 secondes de recherche sur le net voici ce que je trouve:
Code:

1 2 3 4 ' NomTableau désigne le nom du tableau (ListObject) Range("nomTableau[nomColonne]").Select 'Pour sélectionner une colonne entière sans les entêtes ou totaux Range("nomTableau[#ALL],[nomColonne]]").Select 'Pour sélectionner une colonne entière avec les entêtes et totaux Range("nomTableau[#ALL]").Select 'Pour sélectionner toute la table sauf les entêtes et totaux
Fais un petit effort de recherche (et de lecture surtout)!!!

Wouaou,

Merci issoram,

Je n'arrivais pas moi non plus a utiliser les références structuré en VBA. Je vais essayer ta solution un peu plus tard.

Mais je ne comprends pas:
Selectionner une colonne entière ? Pour selectionner une seule ligne provenant d'une colonne, comment fait-on ?
Par rapport aux calculs, si on prends mon exemple:
Code:

1 2 3 With Table.Range(i + 1, 1) .Value = i * 10 End With
Ca donnerai quoi ? On peut remplacer le i par quoi ? Par [nomColonne]] ? Si la colonne s'appel nomColonne .

Bon, j'essaierai plus tard, merci.

J'ai essayé mais il doit manquer quelque chose.

Ne pourrai-tu pas donner un tout petit bout de code pour voir ce qu'il faut utiliser ? Doit-on ajouter un With/ End With (en principe oui pour les Objet) et quel syntaxe utiliser ?
En l'état des choses, cela ne marche pas. Merci.

Voici ce que j'utilise pour ma part:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 Sub EcrireColonnen°1() Dim ListObj As ListObject Dim i As Long, k As Long Dim mStr As String 'Définit le tableau dans la feuille de calcul : appelé Table1 dans la feuille de cacul, renommé ici (VBA) en ListObj Set ListObj = Worksheets("Feuil2").ListObjects("Table1") 'Définit la variable 'k' : Compte le nbres de lignes dans le Tableau k = Range(ListObj).Rows.Count 'Boucle pour ajouter une série de chiffres dans chaque cellule 'de la 1ère colonne du tableau allant de 0 à k-1 en terme de valeurs For i = 1 To k With ListObj.Range(i + 1, 1) .Value = i - 1 End With Next i End Sub
P.S.:
La ligne de code:

Code:

k = Range(ListObj).Rows.Count

n'est pas conseillé dutout car c'est une fonction volatile qui demande trop de temps inutilement. A utiliser seulement si obligé.

Cela donnerai quoi en utilisant les références structurées ici avec ce code par exemple ?

bonsoir,

L'objet ListObject de la collection ListObjects est doté de méthodes et propriétés dont certaines renvoient des objets. La manipulation de cet objet est assez agréable.

Les propriétés suivantes renvoyant des objets Range sont utiles dans ton cas.

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
 
 
dim lc as ListColumn, lr as ListRow, Tb as ListObject, data as Range, Total as Range, enTete as Range
 
    Set TB = Feuil1.ListObjects(1)
 
    Set rs = TB.ListRows ' collection de lignes du tableau
    Set cs = TB.ListColumns' contient les colonnes
 
    Set r = rs(2) ' un élément de la collection ListRows. Une ligne du tableau. 
    Set c = cs("Client") 'désigne une colonne étiqueté "client"
 
    Set c = cs(1)
 
    Set data = Tb.DataBodyRange 'renvoi la zone de données du tableau
 
    tb.ShowTotals = True 'affiche la ligne de totaux
    set total = tb.TotalsRowRange
 
    Set enTete = Tb.HeaderRowRange  'renvoi un objet range contenant la ligne d'en tête.
 
    c.DataBodyRange.ClearContents 'efface le contenu des données de la colonne
    c.TotalsCalculation = xlTotalsCalculationCountNums
 
    r.Range(1, 3).Select ' selectionne la troisième cellule de la ligne.

Merci pour cette réponse. Cependant, montre t-elle que les références structuré ne marche pas ? Ou ?

Voici un code que j'avais fais à mes début:

Créer un tableau dans une feuille:
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 Sub CréerTableau() Dim Ws As Worksheet Dim NomTable As String Dim mStr, nStr As String 'Définition des 4 variables: Set Ws = Worksheets("Feuil2") NomTable = "Table1" mStr = Range("X7") 'Adresse de début du futur tableau nStr = Range("Y7") 'Adresse de fin du futur tableau 'Construction du tableau suivant les adresses de 'mStr' et de 'nStr' With Ws .ListObjects.Add(xlSrcRange, .Range(mStr, nStr), , xlYes).Name = NomTable .ListObjects(NomTable).TableStyle = "TableStyleMedium2" End With End Sub
Ici, l'adresse de début du tableau est rentré dans une feuille de calcul à la main (=> mStr) et l'adresse de fin (=> nStr) idem.

Supprimer Tableau:
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 Sub SuppressionTableau() Dim mStr As String 'Etape 1 'Selectionne l'objet (implicitement) nommé 'Table1' '...seulement la partie des donnée (sans la 1ère ligne) Worksheets("Feuil2").Range("Table1").Select 'Etape 2 (Facultatif) 'Selectionne cette fois-ci tout le tableau complet comprenant la 1ère ligne '...basé sur la séléction précédente Range(Selection, Selection.End(xlUp)).Select 'Etape 3 'Supprime l'objet (Tableau) nommé 'Table1' Worksheets("Feuil2").ListObjects("Table1").Unlist 'Etape 4 (Facultatif) ' Grâce à la séléction précédente ' 1/ Efface le contenu ' 2/ Nettoie le format de cellule tel que: ' - arrière plan (couleur) ' - Bordure etc... Selection.ClearContents Selection.Borders.LineStyle = xlNone With Selection.Interior .Pattern = xlNone .TintAndShade = 0 .PatternTintAndShade = 0 End With 'Etape 5 (Facultatif) 'Selectionne une cellule quelconque hors du tableau mStr = Range("X7") Range(mStr).Offset(2, -2).Select End Sub
Ecrire das la 1ère colonne (déjà donné au dessus).

On peut même retailler un tableau et faire beaucoup d'autres choses:
Code:

1 2 3 'Redimmensionne l'objet 'ListObj' en fonction des 'variables texte 'mStr' et 'nStr' ListObj.Resize Range(mStr, nStr)
Créer une formule:
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 Sub CréerFormule() 'Désactive l'affichage (temporairement si réactivé en fin de Macro) Application.ScreenUpdating = False 'Passe en mode de calcul manuel Application.Calculation = xlCalculationManual Dim ListObj As ListObject Dim i, k As Long Dim mStr As String 'Définit le tableau dans la feuille de calcul Set ListObj = Worksheets("Feuil2").ListObjects("Table1") 'Définit la variable 'k' : Compte le nbres de lignes dans le Tableau k = Range(ListObj).Rows.Count 'Boucle pour ajouter une série de chiffres dans chaque cellule 'de la 2ème colonne du tableau allant de 1 à k For i = 1 To k With ListObj.Range(i + 1, 2) .Formula = "= [@Colonne1]*2^4" ' inconvénient par rapport à Excel de l'utilisation d'une boucle sur le ListObjet.Range '.Value = ListObj.Range(i + 1, 1).Value * 2 ^ 4 'Rame comme pas possible : problème et pas terrible face aux ref. structurées End With Next i 'mStr = Range("X7") 'Range(mStr).Offset(2, -2).Select 'Réactive le calcule automatique Application.Calculation = xlCalculationAutomatic 'Réactive l'affichage Application.ScreenUpdating = True End Sub
Ici, il s'agit d'une référence structuré. C'est ce dont parle Lewis29 et moi-même.
La différence entre .Formula (Excel) et .Value (VBA) reflète bien la différence de comportement (et de souplesse et facilité) entre VBA et Excel.

Cela ne fonctionne pas avec des .Value : but d'un code VBA en règle générale, car ça permet d'éviter de faire ramer un fichier Excel initulement qui peut devenir très lourd à gérer avec trop de formule, et c'est un inconvénient.

Ici, en utilisant une référence structuré, on fait appel au nom de la colonne souhaité comme dans Excel hors VBA.

Ce qu'on aimerai, c'est par exemple pouvoir utiliser des .Value tel que
Table1[@Colonne2].Value = Table1[@Colonne1] * 2^4

Ici, j'exagère un peu au niveau de la facilité, mais c'est presque comme ça que Excel fonctionne, on met = [@Colonne1] * 2^4, et il fait glisser les formule de haut en bas (délimité par la fin du tableau) sans faire appel à une boucle.

Voyez-vous la différence majeur entre les 2 ?

Mais cela dit, j'avais déjà abandonné les Tableaux à références structuré VBA pour utiliser les tableaux multidimensionnel qui permettent de travailler avec la mémoire vive et donc nettement plus rapidement.

07/06/2013, 02h53
NVCfrm

re,

Citation:

Ce qu'on aimerai, c'est par exemple pouvoir utiliser des .Value tel que
Table1[@Colonne2].Value = Table1[@Colonne1] * 2^4

Ce problème est largement compensé par la méthode Evaluate d'Excel.

Code:

ListObj.Range(i + 1, 2) = Evaluate("= [@Colonne1]*2^4")

Wouaou wouahou wahou :ccool: :ccool: :ccool: :lahola:

Après beaucoup beaucoup de galère et grâce à vous (vous m'avez mis sur la bonne voie, la voie de la simplicité), j'ai enfin réussi à utiliser les références structurées !!!
Code:

1 2 3 4 5 With ListObj Evaluate("Table1[Colonne2]").Value = Evaluate("=Table1[Colonne1]*2^4") Evaluate("Table1[Colonne3]").Value = Evaluate("=Table1[Colonne2]*10*4") Evaluate("Table1[Colonne4]").Value = Evaluate("=Table1[Colonne3]*cos(2/4)") 'etc... etc... End With
Le plus simplement du monde. Ici, il faut enlever le @ du [@Colonne2] pour éviter d'utiliser une boucle For ou Do While.
Egalement, mettre le nom du tableau avant le [Colonne2] ou [Colonne1]. Bref,

1/Pas de boucle
2/ Utilisation des références structurées de A à Z sans utiliser de ListObject.Range(i+1,1)
3/ Rapidité absolu
4/ Les 3 lignes (ici 25600 lignes x 3 en environ 1s) est calculé d'une seule traite
5/ Simplicité de code

Magnifique !

Ça interessera forcément Lewis29 et bien d'autres personnes je pense car comme l'a dit Lewis29, en cherchant sur le net,
pas moyen de trouver quoi que ce soit sur les références structurées en VBA avec des .Value.

Donc merci beaucoup NVCfrm !
Je vais faire des tests de rapidité pour voir, mais ça à l'air rapide à vue d'oeil.

P.S.: Les titres de chaque colonnes sont facile à éditer. Il suffit de leur donner le nom voulu (si possible avec une seule lettre pour être encore plus court au niveau programme (et plus clair).

P.S. 2: Le .Value est inutile car implicite.
Reste éventuellement à réussir à enlever le Table1 pour être encore plus clair et là, ça serai carrément le top.

07/06/2013, 10h34
Didier Gonard

Bonjour,

Bravo pour ces efforts conjoints ;)

Citation:

Envoyé par Nouveau2

P.S. 2: Le .Value est inutile car implicite.

Attention, c’est un des dangers d’Excel et du VBA : leur pouvoir d’interprétation !
Le Value, n’est jamais inutile car son mode implicite n’est bien analysé que si le contexte ne mène pas à des ambiguïtés dans le processus d’interprétation.
Voir détails et mise en évidence du danger récemment : ici
Excel, manie les plages de cellules via des Array, ce qui permet d’ailleurs les injections directes plage/Array et inversement.
Il y quatre propriétés fondamentales pour manier les contenu des cellules qui sont : Value, Formula, Text et Value2, le fait qu’elles retournent souvent la même chose est justement très dangereux car générateur de surprises quand le contexte change si on a pas été assez explicite ou qu’on ne sait à quoi s’en tenir.

cordialement,

Didier

Bonjour,

Je met mon code finale, mais je ne suis pas satisfait (hélas, hélas :mrgreen:).

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
44
45
46
47
Sub CréerFormule()
Dim ListObj As ListObject
 
'Désactive l'affichage (temporairement si réactivé en fin de Macro)
Application.ScreenUpdating = False
 
'Passe en mode de calcul manuel
Application.Calculation = xlCalculationManual
 
'Définit le tableau dans la feuille de calcul
Set ListObj = Worksheets("Feuil2").ListObjects("Table1")
 
'Renomme les titres des colonnes souhaitées
'Remplissage du Tableau
With ListObj
   .Range(1, 1).Value = "N"
   .Range(1, 2).Value = "t"
   .Range(1, 3).Value = "f"
   .Range(1, 4).Value = "p"
   .Range(1, 5).Value = "q" 'ne marchera pas sur la ligne se reportant à cette colonne
 
      [Table1[t]] = Evaluate("Table1[N]*2^4")
      [Table1[f]] = [Table1[t]*10*4] 'syntaxe différente
      [Table1[p]] = [Table1[f]*sin(2/4)] 'etc... etc...
 
'-------- Ne marche plus à partir d'ici : Zone à supprimer(début) -----------------------
 
      [Table1[q]] = [Sin(Table1[@f])] 'ne marche pas comme espéré: seul le numéro de ligne correspondant à la ligne
      'où se trouve le pointeur de la souris est calculé, puis dupliqué dans les autres lignes comme s'agissant d'un .Select (selection)
      Stop ' ligne de code à supprimer
      [Table1[q]] = [Sin(Table1[f])] 'ne marche pas comme espéré: seul la 1ère ligne est calculé, puis dupliqué
      Stop ' ligne de code à supprimer
      [Table1[q]] = Sin([Table1[@f]]) ' idem, insatisfaisant
      Stop ' ligne de code à supprimer
      [Table1[q]] = Sin([Table1[f]]) ' Erreur type 13
 
'-------- Ne marche plus à partir d'ici (fin) -----------------------
 
End With
 
'Réactive le calcule automatique
Application.Calculation = xlCalculationAutomatic
 
'Réactive l'affichage
Application.ScreenUpdating = True
 
End Sub

Les fonctions ne marche pas avec les références structurées.

Citation:

Il y quatre propriétés fondamentales pour manier les contenu des cellules qui sont : Value, Formula, Text et Value2

Ah, je ne connaissais pas le .Value2, une chose de plus à assimiler :mrgreen:

Sinon, il y a la sélection qu'on peut ajouter à la liste et que je viens de découvrir d'ailleurs dans le code du dessus.

08/06/2013, 20h31
Invité

Bonjour, bonjour,

Je reviens à la charge :D

Si quelqu'un a une idée sur comment utiliser les fonctions (Sin, Cos, aTan...) avec ce type de références un peu spéciales, il ne faut pas hésiter surtout.

Il y a 2 types de fonctions, celle sans point (peu nombreuses) et Membres de 'Math' tel que:
- Abs
- Sgn
- Sin
- Log
etc...

Avec point (infiniment plus nombreuses) et Membres de Excel.WorksheetFunction, tel que:
- .Sum
- .Fact
- .Atan
- .Product
- .Transpose
- .Radians
etc...

Qu'est-ce que cela pourrai donner avec l'une ou l'autre type de fonction ?