Créer un Range avec une variable tableau, c'est possible ?

[SERIEUXETCOOL]

Bonjour le Forum,

Dans l'avancement de mon projet je bute sur un point important. Un coup de main, un conseil serait le bienvenu.

L'idée est la suivante :

Je créer un tableau dynamique, le redimensionne et le remplit. Jusque la tout va bien. J'obtiens donc une variable Tableau qui contient des éléments.

Ensuite je souhaite créer un Tableau Croisé Dynamique (TCD) à partir de ma variable Tableau précédente. Le problème se pose au niveau de la définition du "SourceData". Je ne sais pas comment sélectionner l'intégralité de ma variable Tableau sans passer par un Range classique.

La seule solution que j'ai trouvé, est de transférer tout le contenu de ma variable Tableau dans une feuille Excel, puis de mettre le "SourceData" comme un Range sur toute la plage.

Sauf que moi je ne veux justement pas transférer le contenu de ma variable Tableau dans une Feuille Excel ! Cette variable Tableau est dynamique et peut contenir environ 10 000 lignes sur 8 colonnes. Ça alourdit le fichier, et surtout ça augmente considérablement les temps de calculs si je transfère le contenu de la variable Tableau dans la feuille Excel. De plus je n'ai pas besoin d'avoir les 10 000 lignes de données, c'est complètement inutile.

Je place ci-après un bout de code qui illustrement de manière simple mon problème.

Option Explicit

Sub TEST()

Dim i As Long 'Déclaration des variables
Dim Tableau As Variant

ReDim Tableau(1 To 5, 1 To 3) As Variant 'On définit la taille et la dimension du tableau dynamique

'On remplit le tableau dynamique
For i = 1 To 5
    Tableau(i, 1) = 1
    Tableau(i, 2) = 2
    Tableau(i, 3) = 3
Next

Range(Cells(1, 1), Cells(UBound(Tableau, 1), UBound(Tableau, 2))) = Tableau 'On transfère les éléments du tableau dans la feuille de calcul

'=========================================================================================
'Crétion du Tableau Croisé Dynamique
'=========================================================================================
Sheets.Add 'On ajoute une feuille qui va contenir le TCD

'On ajoute le TCD
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sheets(ActiveSheet.Index + 1).Range("A1:C5"), _
Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Range("A1"), _
TableName:="Tableau croisé dynamique", DefaultVersion:=xlPivotTableVersion12

End Sub

Voila, j'espère avoir été clair... N'hésitez pas à demander plus d'infos si besoin est. Je reste dans les parages.


Bien cordialement,

André


Ps : Bonnes fêtes de pâques

 

[MJ13]

Re : Créer un Range avec une variable tableau, c'est possible ?

Re

Sinon, peut-être comme ceci:

Sub TEST2()
Dim i As Long 'Déclaration des variables
Dim Tableau As Variant, NF
NF = ActiveSheet.Name
ReDim Tableau(1 To 5, 1 To 3) As Variant 'On définit la taille et la dimension du tableau dynamique
'On remplit le tableau dynamique
For i = 1 To 5
    Tableau(i, 1) = 1
    Tableau(i, 2) = 2
    Tableau(i, 3) = 3
Next
Tableau = [A1].CurrentRegion.Address
'Tableau = Sheets("Feuil15").Range(Cells(1, 1), Cells(UBound(Tableau, 1), UBound(Tableau, 2))).Address  'On transfère les éléments du tableau dans la feuille de calcul
'=========================================================================================
'Crétion du Tableau Croisé Dynamique
'=========================================================================================
Sheets.Add 'On ajoute une feuille qui va contenir le TCD
'On ajoute le TCD
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Tableau, _
Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Range("A1"), _
TableName:="Tableau croisé dynamique", DefaultVersion:=xlPivotTableVersion12
End Sub

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

MJ13, as tu une erreur en testant ton code ?

Chez moi j'obtiens une erreur malheureusement. Je ne peux donc pas conclure pour le moment dsl.

Erreur "Cette commande requiert au moins 2 lignes de données sources..."

André

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

Quelqu’un saurait il par hasard utiliser SourceType:=xlSrcExternal et ensuite pointer sur le tableau dynamique ?

Cela pourrait bien fonctionner qui sait...

Je ne parviens pas comprendre le fonctionnement d'une source externe.


André

 

[MJ13]

Re : Créer un Range avec une variable tableau, c'est possible ?

Re

Alors teste ainsi:

Sub TEST2()
Dim i As Long 'Déclaration des variables
Dim Tableau As Variant, NF
NF = ActiveSheet.Name
ReDim Tableau(1 To 5, 1 To 3) As Variant 'On définit la taille et la dimension du tableau dynamique
'On remplit le tableau dynamique
For i = 1 To 5
    'Tableau(i, 1) = 1
    'Tableau(i, 2) = 2
    'Tableau(i, 3) = 3
Cells(i, 1) = 1
Cells(i, 2) = 2
Cells(i, 3) = 3
Next
Tableau = [A1].CurrentRegion.Address
'Tableau = Sheets("Feuil15").Range(Cells(1, 1), Cells(UBound(Tableau, 1), UBound(Tableau, 2))).Address  'On transfère les éléments du tableau dans la feuille de calcul
'=========================================================================================
'Crétion du Tableau Croisé Dynamique
'=========================================================================================
Sheets.Add 'On ajoute une feuille qui va contenir le TCD
'On ajoute le TCD
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Tableau, _
Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Range("A1"), _
TableName:="Tableau croisé dynamique", DefaultVersion:=xlPivotTableVersion12
End Sub

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

MJ13 ton code fonctionne maintenant sans erreurs. Mais je ne vois pas l'utilité par contre ?

Je veux dire, le but c'est de ne rien mettre dans les feuilles Excels. Or avec ton code, ça reviens exactement au même que le code que je propose dans le premier post (sauf erreur de ma part).

En gros tu stocke toutes les infos sur la feuille et tu y accède ensuite par un range. Ce n'est pas la volonté de ce post.

Si tu parviens à faire la même chose mais sans stocker les données physiquement dans la feuille, alors oui ce serait top. Il faut utiliser le tableau qui lui contient toutes les infos nécessaires.

On devrait y arriver. Faut essayer après tout.

Merci à toi MJ13. Bonne esprit.

André

 

[Staple1600]

Re : Créer un Range avec une variable tableau, c'est possible ?

Bonsoir à tous , [EDITION2=Bonsoir MJ13 ]

EDITION: Désolé, il manquait un bout dans mon message.
Sauf erreur de ma part, je ne vois pas comment passer par un Array pour faire fonctionner un TCD.

Voir plus en détails ce que dit l'aide VBA sur les TCD.

Extrait de l'aide VBA:

L'exemple suivant montre comment créer un rapport de tableau croisé dynamique à partir d'une base de données Microsoft Excel (contenue dans la plage A1:C100).

Cette propriété renvoie la source de données pour le rapport de tableau croisé dynamique, comme le montre le tableau suivant. Type de données Variant en lecture-écriture.

Source de données	Valeur renvoyée
Liste ou base de données Microsoft Excel	Référence de cellule, texte.
Source de données externe	Tableau. Chaque cellule contient une chaîne de connexion SQL (Structured Query Language) et les éléments restants qui sont une chaîne de requête divisée en segments de 255 caractères.
Plusieurs plages de consolidation	Tableau à deux dimensions. Chaque ligne contient une référence et les éléments de champ de page associés.
Autre rapport de tableau croisé dynamique	Une des trois sortes d'informations ci-dessus.

 

[MJ13]

Re : Créer un Range avec une variable tableau, c'est possible ?

Re

Si tu parviens à faire la même chose mais sans stocker les données physiquement dans la feuille, alors oui ce serait top. Il faut utiliser le tableau qui lui contient toutes les infos nécessaires.

A la désolé, mais je ne suis pas très spécialisé en tableaux (à mon grand regret ), mais en TCD, je me débrouille pas mal .

Par contre, pour savoir ce que tu voulais depuis le début, la .

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

Merci Staple1600 pour le descriptif issu de l'aide VBA. Je m'y suis frotté une petite partie de la journée mais je n'ai rien capté à leur truc. Tu as peut être raison quand tu dis que ce n'est pas possible de réaliser ma demande sans stocker les données sur la feuille.

Je n'y connais pas grand chose après tout. Je vais quand même chercher encore...


Merci MJ13. Ce n'est pas grave si tu n'y arrive pas. Je n'y parviens pas plus non plus. En même temps, je ne trouve presque aucune trace de ma demande sur le net. A croire que personne n'a pensé faire ça. Ou alors tout le monde sait que ça n'a fonctionne pas comme je le voudrais^^

Merci à vous en tout cas.

Si quelqu'un a un éclair de génie, je suis preneur !

André

 

[Misange]

Re : Créer un Range avec une variable tableau, c'est possible ?

Bonjour
J'ai fouillé dans mes liens de référence mais non, je ne n'ai rien trouvé suggérant que l'on puisse utiliser un array comme source d'un TCD. Et franchement ça ne m'étonne pas plus que ça car je n'en vois pas trop l'intérêt.
Les TCD travaillent eux même en créant en quelque sorte un array en mémoire vive, réorganisant les données et faisant les calculs nécessaires sans passer par la feuille. Ils font toutes leurs opérations en langage machine ce qui est autrement plus rapide que de le faire en VBA ou avec des formules matricielles, ce qui fait l'immense intérêt de cet outil.
En fait la question est : où sont stockées les données que tu transfères dans ton array ?
Pourquoi penses tu que le fait de les mettre dans un array plutôt que de les stocker sur la feuille soit plus rapide ?
Tu peux aussi créer ton ycd à partir de données externes à excel (pas seulement access)

 

[Dranreb]

Re : Créer un Range avec une variable tableau, c'est possible ?

Bonjour
Ça vaudrait le coup d'essayer à tout hasard de renvoyer le tableau dans une fonction perso et de spécifier cette fonction comme source ou (dernière ruse) un nom de plage (ça c'est permis non ?) mais qui a pour référence =LaFonction(). Mais je doute que ça marche. Si la source est en quelque sorte virtuelle, le TCD l'est aussi. Autant créer carrément le tableau image de faux TCD en VBA. Ce n'est pas si compliqué en utilisant mes fournitures Récap qui savent faire des classement internes et des regroupements. J'essayerais bien de vous adapter quelque chose, mais par pitié joignez moi seulement du .xls et surtout pas du .xlsm: mon convertisseur met un numéro barbare comme nom de fichier et laisse des objets commandes de feuilles mal définis et inutilisables.
À +

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

Bonjour à tous,

Misange :

En fait la question est : où sont stockées les données que tu transfères dans ton array ?

Toutes mes données sont, à la base, calculées par VBA et stockées dans un tableau dynamique PAR VBA (exactement comme l'exemple qui est mis au premier post). Ce n'est rien de plus qu'un tableau de variables au final. Je peux accéder à une case du tableau, mais ne peut pas sélectionner une "plage" de ce tableau autrement que par une énumération.

Pourquoi penses tu que le fait de les mettre dans un array plutôt que de les stocker sur la feuille soit plus rapide ?

Alors la c'est très simple, il suffit de faire l'expérience. Stocker 10 000 lignes de tableur Excel dans une feuille est beaucoup plus long que de les stocker en mémoire uniquement. L'aide Excel montre d'ailleurs cet atout. Ils disent un gain de temps d'environ 20 fois.
Et pour l'avoir tester c'est véridique. Si je stocke mes 10 000 lignes dans le tableur alors je vais prendre 6 min environ. Si je ne stocke rien sur la feuille mais uniquement en mémoire, alors je vais prendre quelques secondes à peine. Il n'y a pas photo, mes données n'ont rien à faire sur une feuille^^


Dranreb :

Ça vaudrait le coup d'essayer à tout hasard de renvoyer le tableau dans une fonction perso et de spécifier cette fonction comme source

Je pense qu'utiliser une source externe peut avoir ses chances. Mais j'ai déjà cherché de ce coté la...et je ne comprend pas le principe.

Je vais jeter un coup d’œil à ton fichier Excel. Je reposterai un fichier.Xls si le code est compatible.


Enfin, si vraiment ça ne parait pas possible ce que je demande alors je donnerais plus d'éléments sur ce que je cherche à faire au final. Peut être que la méthode que j'emploie n'est pas la bonne.

J'attends vos dernière réactions et je changerai de stratégie d'approche.

Bien cordialement,

André

 

[Misange]

Re : Créer un Range avec une variable tableau, c'est possible ?

OUps j'avais zappé le fichier attaché du premier post
Tu n'as certes pas besoin de me convaincre que de travailler avec un array est plus rapide que de le faire via la feuille...
voir ici pourquoi :
Ce lien n'existe plus (et les 7 pages suivantes !)
Tu peux en VBA transférer tes données dans un fichier texte et l'utiliser comme source mais je ne pense pas que tu gagnes du tout en rapidité.
J'ai recherché à nouveau mais je n'ai pas trouvé. Mais j'ai posé la question à de vrais cadors... attendons de voir si ils ont une réponse !

 

[SERIEUXETCOOL]

Re : Créer un Range avec une variable tableau, c'est possible ?

RE,


Misange :

Tu n'as certes pas besoin de me convaincre que de travailler avec un array est plus rapide que de le faire via la feuille

On est d'accord la dessus

Tu peux en VBA transférer tes données dans un fichier texte et l'utiliser comme source mais je ne pense pas que tu gagnes du tout en rapidité

Ah zut !

J'ai recherché à nouveau mais je n'ai pas trouvé. Mais j'ai posé la question à de vrais cadors... attendons de voir si ils ont une réponse !

Merki merki.




Bien comme je vois que ma demande est relativement galère, je propose maintenant de donner quelques raisons qui m'ont conduis à créer ce post. Ça peut probablement donner des idées nouvelles.

Donc j'utilise bien un tableau dynamique pour stocker mes données via VBA. Ça c'est fondamental. J'obtiens un tableau à 8 colonnes et avec environ 10 000 lignes. Une fois ce tableau virtuellement remplit, je cherche à tracer un graph de surface (un graph 3D) sur 3 des colonnes. Sauf que le problème arrive maintenant !!!
Pour tracer un graph 3D, il ne faut pas 3 colonnes de données, mais une matrice carrée. C'est pourquoi j'ai décidé d'utiliser les TCD. Comme ça j'obtiens sans effort la bonne matrice et je peux ensuite tracer mes graphs de surface. Une fois les graphs 3D réalisés, je supprime la feuille contenant le TCD pour ne garder finalement que les graphs 3D.

Jusque la tout va allait bien sauf que pour tracer les TCD, il faut au préalable transférer toutes les données stockées dans mon array vers une feuille Excel. Donc je tourne en rond pour rien au final.

Si ça peut donner des idées du coup ? J'ai un tableau virtuel, et je veux tracer des graph 3D.


Bien cordialement,

André

 

[Misange]

Re : Créer un Range avec une variable tableau, c'est possible ?

Tu ne peux pas définir des noms pour les séries de ton graphique à partir de ton array ?

 

[Dranreb]

Re : Créer un Range avec une variable tableau, c'est possible ?

Deux questions:
Quelle est la source de 8 colonnes ?
Que contiennent elles qui va se retrouver dans la matrice carrée ?

P.S. Ah sur 3 des colonnes vous avez dit. On peut supposer Coordonnées X, Y puis une Hauteur.
Rien de plus facile alors que de construire directement la matrice !
À +