Discussion :

[Ruppert]

Bonjour à toutes et à tous,

Lorsque je manipule les variables tableaux je m'appuie beaucoup sur l'excellente note explicative faite par SilkyRoad sur le sujet.
https://silkyroad.developpez.com/vba/tableaux/

Ci-dessous, 2 extraits de cette note qui permettront d'illustrer mon problème :

1. Pour vous affranchir des particularités d'Option Base, vous pouvez aussi utiliser la clause To, afin de contrôler la plage des indices d'un tableau.
2. La limite inférieure d'une dimension peut être:

• 0 ou 1, en fonction de la valeur de l'instruction Option Base (Consultez le chapitre Option Base pour plus de détails).
• N'importe quelle valeur pour les dimensions définies à l'aide de la clause To.

Je travaille en Option base 1.

J'ai défini une variable tableau "Tab_chro" à 2 dimensions par la méthode suivante :
ReDim Tab_chro(7 To 220, 2 To 7)

Puis je l'alimente par un range de taille identique.
Tab_chro = Range(Cells(7, 2), Cells(220, 7)).Value


En exécutant mon code en mode pas à pas au passage de ces deux lignes, je constate que juste après la ligne "Redim", ma variable tableau "Tab_chro" (vide) a bien les indices min et max suivants:

• Dimension 1 : 7 --> 220
• Dimension 2 : 2 --> 7

Par contre, l'affectation des valeurs du range vers mon tableau abaisse les indices min et max :

• Dimension 1 : 1 --> 214
• Dimension 2 : 1 --> 6

J'ai fait le test en basculant en mode Option Base 0 et j'obtiens exactement la même chose, avec les mêmes indices que précédemment.

Plus bas dans mon code, si par exemple j'appelle la valeur de Tab_chro(218,7), ça plante car les limites des indices ont été abaissés (à mon insu) par l'affectation des valeurs du range et donc ces indices n'existent plus...

Est-ce que quelqu'un comprend pourquoi VBA fait ça (quelle subtilité m'a échappée) ?

Je vous remercie d'avance pour vos explications.

[Patrice740]

Bonjour,

L'explication est relativement simple : il n'est pas possible d'affecter directement les valeurs d'un range à un tableau.

Essaies :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub Test()
Dim Tab_chro(7 To 220, 2 To 7)
  Tab_chro = Range(Cells(7, 2), Cells(220, 7)).Value
End Sub

La variable doit être de type Variant :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
Sub Test()
Dim Tab_chro As Variant
  Tab_chro = Range(Cells(7, 2), Cells(220, 7)).Value
End Sub

Dans ce cas, comme dans tous les cas avec un Variant, l'affectation joue le rôle de Dim en créant un tableau dont la première case est toujours t(1, 1) quelle que soit Option Base.

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
Sub Test()
Dim Tab_chro As Variant
  ReDim Tab_chro(7 To 220, 2 To 7)
  Tab_chro = Range(Cells(7, 2), Cells(220, 7)).Value
  Tab_chro = "Test"
End Sub

L'instruction Redim crée bien un Variant de sous-type tableau (de Variants) avec les dimensions spécifiées.
Mais ce Variant est réaffecté (donc écrasé) par l'instruction de la ligne 4 qui crée un autre tableau dont la première cellule est t(1, 1).
De la même façon, la ligne 5 écrase le tableau pour le remplacer par un Variant de sous-type String, sans créer d'erreur car une variant peut être affecté par tout autre type de variable.

Note : l'instruction Split agit de façon similaire mais cette fois-ci la première case est toujours t(0, 0) quelle que soit Option Base..

[Ruppert]

Bonjour,

Merci beaucoup Patrice740 pour les explications et les exemples.

Donc :

• Si je souhaite conserver les indices 7 et 220 de mon tableau, il faut que je le renseigne par des boucles (j'ai tester ça marche),
• Si je veux renseigner mon tableau par un Range, il n'est pas utile de préciser d'utiliser l'instruction Redim car l'affectation par le Range va automatiquement définir des indices de tableau qui commencent à 1.

J'en prends bonne note pour la suite.

Bonne journée et encore merci !

[Patrice740]

Donc :

• Si je souhaite conserver les indices 7 et 220 de mon tableau, il faut que je le renseigne par des boucles (j'ai tester ça marche),
• Si je veux renseigner mon tableau par un Range, il n'est pas utile de préciser d'utiliser l'instruction Redim car l'affectation par le Range va automatiquement définir des indices de tableau qui commencent à 1.

• Oui, mais pas une boucle sur les cellules (c'est beaucoup trop lent), un boucle sur un tableau intermédiaire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
Option Explicit
Sub Test()
Dim tbl As Variant, Tab_chro As Variant, i As Long, j As Long
  ReDim Tab_chro(7 To 220, 2 To 7)
  tbl = Range(Cells(7, 2), Cells(220, 7)).Value
  For i = 1 To UBound(tbl)
    For j = 1 To UBound(tbl, 2)
      Tab_chro(i + 6, j + 1) = tbl(i, j)
    Next j
  Next i
End Sub

• C'est çà : le ReDim est inutile.

Démo de gain de la rapidité (sur un tableau 100 fois plus gros pour mieux mesurer la différence) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Option Explicit
Sub Test()
Dim tbl As Variant, Tab_chro As Variant, i As Long, j As Long
Dim t0 As Single, t1 As Single, t2 As Single
  ReDim Tab_chro(7 To 22000, 2 To 7)
  t0 = Timer
  For i = 7 To 22000
    For j = 2 To 7
      Tab_chro(i, j) = Cells(i, j).Value
    Next j
  Next i
  t1 = Timer
  tbl = Range(Cells(7, 2), Cells(22000, 7)).Value
  For i = 1 To UBound(tbl)
    For j = 1 To UBound(tbl, 2)
      Tab_chro(i + 6, j + 1) = tbl(i, j)
    Next j
  Next i
  t2 = Timer
  MsgBox "Via cellules : " & t1 - t0 & " sec." & vbCrLf & _
         "Via tableau : " & t2 - t1 & " sec." & vbCrLf & _
         "C'est " & Int((t1 - t0) / (t2 - t1)) & " fois plus rapide."
End Sub

Chez moi c'est 22 fois plus rapide !

[Ruppert]

Bonjour Patrice740,

Merci pour l'astuce !

[Patrice740]

Merci pour l'astuce !

Ça n'a rien d'une astuce, c'est une démarche rationnelle, une méthode de codage.