Comment créer des noms de variables dynamics VBA

J'essaie de créer un nombre de variables dynamics dans VBA en fonction de la valeur d'une cellule. Essentiellement, j'aimerais terminer avec Team1, Team2... to TeamX . Toute aide est grandement appréciée

 Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Dim i, x As Integer Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Définir x = Plage ("J4"). Valeur Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Dim Team (1 à x) en tant que string Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Dim Manager (1 à x) en tant que string Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Gamme ("A3"). Sélectionnez Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer Pour i = 1 à x Dim i, x As Integer Set x = Range("J4").Value Dim Team(1 To x) As Ssortingng Dim Manager(1 To x) As Ssortingng Range("A3").Select For i = 1 To x Dim Team(i) As Integer

Un dictionary aiderait probablement dans ce cas, il est conçu pour les scripts, et même s'il ne vous permet pas de créer des variables «dynamics», les éléments du dictionary sont dynamics et peuvent servir à des fins similaires à celles de «variables».

 Dim Teams as Object Set Teams = CreateObject("Scripting.Dictionary") For i = 1 To x Teams(i) = "some value" Next Dim Teams as Object Dim Teams as Object Set Teams = CreateObject("Scripting.Dictionary") For i = 1 To x Teams(i) = "some value" Next Définir les équipes = CreateObject ("Scripting.Dictionary") Dim Teams as Object Set Teams = CreateObject("Scripting.Dictionary") For i = 1 To x Teams(i) = "some value" Next Pour i = 1 à x Dim Teams as Object Set Teams = CreateObject("Scripting.Dictionary") For i = 1 To x Teams(i) = "some value" Next Equipes (i) = "une certaine valeur" Dim Teams as Object Set Teams = CreateObject("Scripting.Dictionary") For i = 1 To x Teams(i) = "some value" Next

Plus tard, pour interroger les valeurs, appelez simplement l'élément comme:

 MsgBox Teams(i)

Les dictionarys contiennent des paires de keys / valeurs, et les keys doivent être uniques. L'atsortingbution à une key existante replacea sa valeur, par exemple:

 Teams(3) = "Detroit" Teams(3) = "Chicago" Debug.Print Teams(3) '## This will print "Chicago" Équipes (3) = "Détroit" Teams(3) = "Detroit" Teams(3) = "Chicago" Debug.Print Teams(3) '## This will print "Chicago" Équipes (3) = "Chicago" Teams(3) = "Detroit" Teams(3) = "Chicago" Debug.Print Teams(3) '## This will print "Chicago"

Vous pouvez vérifier l'existence en utilisant la méthode .Exist si vous devez vous soucier de l'écrasement ou non.

 If Not Teams.Exist(3) Then Teams(3) = "blah" Else: 'Teams(3) already exists, so maybe we do something different here End If Si Non Teams.Exist (3) Ensuite If Not Teams.Exist(3) Then Teams(3) = "blah" Else: 'Teams(3) already exists, so maybe we do something different here End If Équipes (3) = "blah" If Not Teams.Exist(3) Then Teams(3) = "blah" Else: 'Teams(3) already exists, so maybe we do something different here End If 'Les équipes (3) existent déjà, alors peut-être que nous faisons quelque chose de différent ici If Not Teams.Exist(3) Then Teams(3) = "blah" Else: 'Teams(3) already exists, so maybe we do something different here End If

Vous pouvez get le nombre d'éléments dans le dictionary avec la méthode .Count .

 MsgBox "There are " & Teams.Count & " Teams.", vbInfo

Les keys d'un dictionary doivent être entières ou en string, mais les valeurs peuvent être n'importe quel type de données (y compris les arrays et même les types de données Object , comme Collection , Worksheet , Application , Dictionnaires nesteds, etc., en utilisant le mot-key Set ), donc, par exemple, vous pourrait dicter les feuilles de travail dans un classur:

 Dim ws as Worksheet, dict as Object Set dict = CreateObject("Scripting.Dictionary") For each ws in ActiveWorkbook.Worksheets Set dict(ws.Name) = ws Next Dim ws comme feuille de calcul, dict comme object Dim ws as Worksheet, dict as Object Set dict = CreateObject("Scripting.Dictionary") For each ws in ActiveWorkbook.Worksheets Set dict(ws.Name) = ws Next Set dict = CreateObject ("Scripting.Dictionary") Dim ws as Worksheet, dict as Object Set dict = CreateObject("Scripting.Dictionary") For each ws in ActiveWorkbook.Worksheets Set dict(ws.Name) = ws Next Pour chaque ws dans ActiveWorkbook.Worksheets Dim ws as Worksheet, dict as Object Set dict = CreateObject("Scripting.Dictionary") For each ws in ActiveWorkbook.Worksheets Set dict(ws.Name) = ws Next Définir dict (ws.Name) = ws Dim ws as Worksheet, dict as Object Set dict = CreateObject("Scripting.Dictionary") For each ws in ActiveWorkbook.Worksheets Set dict(ws.Name) = ws Next

Cela vous amènera à démarrer. Mais avant de commencer, je recommand de regarder ce didacticiel WiseOwlTutorials sur Youtube:

Sélection des cellules (scope, cellules, activation, fin, décalage)
Feuilles de travail, charts et feuilles
Variables
Arrays

     Dim i, x As Integer
     x = Gamme ("J4"). Valeur
     Dim Team () As Integer
     Dim Manager () As Ssortingng

     ReDim Team (1 à x) en entier
     ReDim Manager (1 à x) en tant que string

     Gamme ("A3"). Sélectionnez


     Pour i = 1 à x

         Équipe (i) = i

     Prochain