Recopie d'une variable tableau dans Excel

[chax]

Bonjour à tout le forum,

Après de longues recherches sur internet, je n'arrive pas à résoudre mon problème :
- J'ai une base de données de 15 lignes et 2 colonnes dans "Classeur1.xls"
- A partir d'un autre classeur "SDSD.xlsm", je copie cette base de données dans une variable tableau (14 , 1) => Pas de problème
- Je cherche à copier cette variable tableau dans une feuille de ce classeur "SDSD.xlsm" => Un message d'erreur apparait "Erreur d'exécution 1004"

Voici le code réalisé (et en PJ les fichiers utilisés => Remarque : il faut ouvrir les 2 fichiers avant de lancer le code)

************************************************************************************************
Sub Bouton4_Clic()

Dim dernière_ligne_données, dernière_colonne_données, i, j As Integer
Dim tableau_données() As Variant

dernière_ligne_données = Workbooks("Classeur1.xlsx").Sheets("Feuil1").Range("A" & Rows.Count).End(xlUp).Row
dernière_colonne_données = Workbooks("Classeur1.xlsx").Sheets("Feuil1").Cells(1, Cells.Columns.Count).End(xlToLeft).Column

ReDim tableau_données(dernière_ligne_données - 1, dernière_colonne_données - 1)

For i = LBound(tableau_données, 1) To UBound(tableau_données, 1)
For j = LBound(tableau_données, 2) To UBound(tableau_données, 2)
tableau_données(i, j) = Workbooks("Classeur1.xlsx").Sheets("Feuil1").Cells(i + 1, j + 1).Value
Next
Next

For i = 0 To UBound(tableau_données, 1)
For j = 0 To UBound(tableau_données, 2)
Workbooks("SDSD.xlsm").Sheets("Feuil1").Cells(i, j) = tableau_données(i, j) 'le code bug à cet endroit
Next
Next

End Sub

*********************************************************************************
Voyez-vous d'où l'erreur peut venir ?

Merci d'avance pour votre aide

 

[Dranreb]

Bonsoir.
Ne faites pas de boucles pour ça.
La propriété Value d'un Range représentant plusieurs cellules contigües est directement constituée d'un tableau 2D basé 1.
Il suffit donc d'affecter l'intégralité du tableau en une seule instruction. Ça marche dans les deux sens et en plus c'est très rapide.

 

[mapomme]

Bonsoir à tous,

Une illustration de ce que suggère Dranreb 🙂.

Sub Bouton4_Clic()
Dim derlig As Long, dercol As Long, Tableau

  With Workbooks("Classeur1.xlsx").Sheets("Feuil1")
    derlig = .Range("A" & Rows.Count).End(xlUp).Row
    dercol = .Cells(1, Cells.Columns.Count).End(xlToLeft).Column
    Tableau = .Range(.Range("a1"), .Cells(derlig, dercol)).Value
  End With

  With ThisWorkbook.Sheets("Feuil1")
    'éventuellement (effacement du précédent résultat)
    .Range("a1").CurrentRegion.Clear

    .Range("a1").Resize(UBound(Tableau), UBound(Tableau, 2)) = Tableau
    Application.Goto .Range("a1"), True
  End With
End Sub

Quant à votre erreur à la ligne:

Workbooks("SDSD.xlsm").Sheets("Feuil1").Cells(i, j) = tableau_données(i, j)

Cells(i, j) -> i est forcément supérieur ou égal à 1 ; j est forcément supérieur ou égal à 1 (enfin dans le cas présent)

 

[Dranreb]

Oui mais pourquoi utiliser un Variant contenant un tableau plutôt qu'un tableau, si on peut l'éviter ?

 

[mapomme]

Bonsoir Dranreb 😉,

Oui mais pourquoi utiliser un Variant contenant un tableau plutôt qu'un tableau, si on peut l'éviter ?

Peux-tu développer pour ma compréhension ? 😕
Merci,

 

[Dranreb]

Dim Tableau déclare un Variant qui pourra éventuellement contenir un tableau.
Dim Tableau() déclare un tableau dynamique d'éléments Variant.
Mais j'ai un cas où je range des tableaux comme membres d'une collection, que je suis obligé d'explorer par un For Each Détail In Collection et où Détail doit être un Variant, les tableaux n'étant pas acceptés comme variable de contrôle d'une boucle For Each In

 

[mapomme]

Je vois bien la différence.

Mais pourquoi alors, dans les deux cas, VarType(tableau) renvoie la même valeur (8204 somme de vbArray (8192) et de vbVariant '12)) ?

De même, dans les deux cas, la fenêtre espion indique comme type Variant/Variant(1 to 15, 1 to 3).

Cela signifierait-il que les deux déclarations aboutissent en fin de compte à la même structure en VBA ?

 

[mapomme]

Autre cas,

Si la source ne comprend que la cellule A1 (A1 = 1 , le reste vide):

Pour la déclaration Dim Tableau:
La lecture de Tableau aboutit à une variable simple (type vbDouble). Ce n'est plus un tableau.

Pour la déclaration Dim Tableau():
La lecture de Tableau plante -> Erreur 13 incompatibilité de type

La déclaration simple Dim Tableau semble donc plus tolérante. Il faudra néanmoins tester avec isArray(Tableau) après lecture pour utiliser les mêmes instructions dans suite de la macro.

  If Not IsArray(Tableau) Then
    x = Tableau: ReDim Tableau(1 To 1, 1 To 1): Tableau(1, 1) = x
  End If

 

[Dranreb]

Cela signifierait-il que les deux déclarations aboutissent en fin de compte à la même structure en VBA ?

Non, parce que VarType ne distingue pas un Variant d'une expression différemment typée. Il analyse donc de la même façon ce qu'on lui spécifie d'autre qu'un variant que ce que contiendrait un Variant qui aurait reçu la même expression.
Ça peut planter si le Range ne représente finalement qu'une seule cellule. Mais dans ce cas, ça ne plante pas plus loin ?
Cela dit si le contexte fait que comme ça ça passe dans tous les cas, ça peut être une raison suffisante pour préférer passer par ce Variant intermédiaire.

 

[mapomme]

J'ai l'habitude d'utiliser le type Variant.

Par contre, j'avoue souvent éluder le test isarray après la lecture 🙁

Dans le futur, je réfléchirai (grâce à toi) un peu plus à mes déclarations.

Merci Dranreb 😉

 

[chax]

Je viens de voir voir vos réponses : le code que vous proposez est effectivement plus efficace

Merci à Dranreb et à mapomme pour vos réponses

Bonne soirée