Comment rendre un tableau VBA accessible dans un projet

Le 10/02/2016 à 19:20

Bonsoir,

Je voudrais savoir s'il est possible de mettre une plage dans un tableau vba et que ce dernier soit accessible dans tout le projet.

M'étant inspiré d'un exemple trouvé sur le site de Boisgontier, pour faire un userform avec des comboboxs en cascade en utilisant les tableaux vba. Mais j'ai dû, pour chaque contrôle déclaré les variables et le tableau. De plus ce même tableau est utilisé dans une autre macro.

Voici mon code, peut-on le simplifier en déclarant les variables et surtout le tableau en public (ligne avec ***)

Option Explicit
Dim F

Private Sub UserForm_Initialize()
    Dim MonDico As Object, Dlg As Long, tb, I As Long
    Set F = Sheets("FBD.PC")

    Set MonDico = CreateObject("Scripting.Dictionary") '***
    Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
    tb = F.Range("A2:AA" & Dlg).Value  '***

   For I = LBound(tb) To UBound(tb)
   If tb(I, 18) = "On" Or tb(I, 18) = "On/Off" Then MonDico(tb(I, 18)) = tb(I, 18)
    Next I
    Me.Cb1.List = MonDico.items  
    Me.Cb1.ListIndex = -1
End Sub
'----------------------------------------------------------------------------------------
Private Sub Cb1_Change() 
Dim MonDico As Object, Dlg As Long, tb, I As Long, temp
    On Error Resume Next
    Set MonDico = CreateObject("Scripting.Dictionary") '***
    Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
    tb = F.Range("A2:AA" & Dlg).Value  '***

    For I = LBound(tb) To UBound(tb)
    If Cb1 = tb(I, 18) Then MonDico(tb(I, 2)) = tb(I, 2)
    Next I

    temp = MonDico.items        ' dico date
    Call TriDcr(temp, LBound(temp), UBound(temp))       'tri dico date
    Me.Cb2.List = temp
    Me.Cb2.ListIndex = -1
    Me.Cb3.ListIndex = -1
    Me.Cb4.ListIndex = -1

End Sub
'----------------------------------------------------------------------------------------
Private Sub Cb2_Change()      'date
Dim MonDico As Object, Dlg As Long, tb, I As Long, temp

    On Error Resume Next
    Set MonDico = CreateObject("Scripting.Dictionary") '***
    Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
    tb = F.Range("A2:AA" & Dlg).Value  '***

    For I = LBound(tb) To UBound(tb)
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) Then MonDico(tb(I, 4)) = tb(I, 4) 'dico famille
    Next I

    Me.Cb3.List = MonDico.items    '=4
    Me.Cb3.ListIndex = -1  '=4
    Me.Cb4.ListIndex = -1   '=2
End Sub
'----------------------------------------------------------------------------------------
Private Sub Cb3_Change()  'famille
Dim MonDico As Object, Dlg As Long, tb, I As Long, temp

    On Error Resume Next
   Set MonDico = CreateObject("Scripting.Dictionary") '***
    Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
    tb = F.Range("A2:AA" & Dlg).Value  '***

    For I = LBound(tb) To UBound(tb)
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) Then MonDico(tb(I, 5)) = tb(I, 5) 'dico famille
    Next I

    If MonDico.Count > 0 Then
        temp = MonDico.items
        Me.Cb4.List = temp    '=4
        Me.Cb4.ListIndex = -1  '=4

    End If

End Sub
'----------------------------------------------------------------------------------
Private Sub Cb4_Change()      'sous-famille
Dim MonDico As Object, Dlg As Long, tb, I As Long, temp

       Bt_Valider.Enabled = IIf(Me.Cb1 <> "" And Me.Cb2 <> "" And Me.Cb3 <> "", 1, 0)

     On Error Resume Next
    Set MonDico = CreateObject("Scripting.Dictionary") '***
    Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
    tb = F.Range("A2:AA" & Dlg).Value  '***

    For I = LBound(tb) To UBound(tb)
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T1 = tb(I, 6) 'dico famille
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T2 = tb(I, 3) 'dico famille
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T3 = tb(I, 24) 'dico famille
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T4 = tb(I, 25) 'dico famille
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T5 = tb(I, 26) 'dico famille
    If Me.Cb1 = tb(I, 18) And Me.Cb2 = CDate(tb(I, 2)) And Me.Cb3 = tb(I, 4) And Me.Cb4 = tb(I, 5) Then Me.T6 = tb(I, 27) 'dico famille

    Next I

End Sub

C'est pour rendre le tableau tb accessible dans d'autres procédures.

En vous remerciant.

Le 10/02/2016 à 19:26

Bonjour

Pour rendre une variable accessible dans tout le projet, il faut la déclarer dans un module Standard

A placer en haut d'un module avant la première macro

Public Tb

Penses à supprimer tes déclarations de Tb dans chaque procédure

Le 10/02/2016 à 19:34

Bonsoir Banzai64,

Je te remercie pour ta rapidité. Ce que je voudrais exactement, ce n'est pas simplement déclaré tb en public mais ne plus réécrire dans chaque procédure tb = F.Range("A2:AA" & Dlg).Value, où dois-je écrire cette ligne afin que je puisse accéder aux données de tb dans tout le projet vba.

Merci beaucoup.

Le 10/02/2016 à 22:35

Bonsoir,

Comme te l'a dit Banzai, une variable déclarée publique dans un module standard est accessible dans tout le projet en utilisant simplement son nom.

Public tb()

Si tu la déclares dans un module de feuille comme publique, elle sera également accessible de l'extérieur mais en précisant le module : Userform1.tb...

Cependant, s'agissant d'un Userform, il convient que le Userform soit chargé en mémoire (instruction Load). Donc si tu veux que la variable demeure indépendamment du Userform, un module standard reste préférable.

A partir du moment où un variable est accessible si une procédure lui affecte une valeur, plusieurs ou un objet selon le cas, la variable conservera cette affectation jusqu'à ce qu'une autre instruction la modifie (ou que tu fermes Excel).

Cordialement.

Le 10/02/2016 à 23:40

Bonsoir

Bonsoir MFerrand

CP4 a écrit :

mais ne plus réécrire dans chaque procédure tb = F.Range("A2:AA" & Dlg).Value

Tu l'écris une fois dans Private Sub UserForm_Initialize() et dans le cas que Dlg ne change pas tu n'as plus besoin de initialiser ce tableau dans les autres procédures

Et comme Dlg est recalculé à chaque procédure tu pourras effacer sa déclaration et son initialisation, car à part le fait de t'aider à initialiser Tb, cette variable ne sert pas

Exemple

Private Sub Cb1_Change()
'Dim MonDico As Object, Dlg As Long, tb, I As Long, temp
Dim MonDico As Object,  I As Long, temp
    On Error Resume Next
    Set MonDico = CreateObject("Scripting.Dictionary") '***
   'Dlg = F.Range("A" & Rows.Count).End(xlUp).Row  '***
   'tb = F.Range("A2:AA" & Dlg).Value  '***

    For I = LBound(tb) To UBound(tb)
    If Cb1 = tb(I, 18) Then MonDico(tb(I, 2)) = tb(I, 2)
    Next I

    temp = MonDico.items        ' dico date
   Call TriDcr(temp, LBound(temp), UBound(temp))       'tri dico date
   Me.Cb2.List = temp
    Me.Cb2.ListIndex = -1
    Me.Cb3.ListIndex = -1
    Me.Cb4.ListIndex = -1

End Sub

Comme la variable Dlg ne servait que dans ces procédures, pas la peine non plus de la déclarer en Public

Le 11/02/2016 à 14:01

Bonjour,

Excusez mon retour tardif. Je vous remercie beaucoup pour cous explications.

Excusez aussi mon manque de clarté. N'étant pas informaticien, je ne maitrise pas très bien le jargon du métier.

je voulais en fait, mettre une plage de ma feuille bd (ligne=2 à la dernière ligne "Dlg") dans un tableau (tb) VBA afin de pouvoir l'utiliser que ce soit pour l'userform ou dans d'autres procédures (module standard ou code feuille) et ce jusqu'à la fermeture du fichier.

Avec mes remerciements.

Le 11/02/2016 à 14:28

Bonjour

Fais ce qui a été indiqué dans les précédents messages, et ton tableau Tb sera accessible de partout

Remarque:

Il est impératif d'ouvrir (exécuter) l'userform afin d'initialiser ton tableau avant toute utilisation de ton tableau en dehors du module de l'userform

Le 11/02/2016 à 14:52

Merci pour le retour rapide

Remarque:

Il est impératif d'ouvrir (exécuter) l'userform afin d'initialiser ton tableau avant toute utilisation de ton tableau en dehors du module de l'userform

Ce n'est pas exactement le résultat souhaité. Je voudrais initialiser (voilà c'est le terme adéquat) le tableau avant même son utilisation dans un module standard ou userform.

Encore merci.

Le 11/02/2016 à 15:09

Bonjour

Pour initialiser ton tableau tu es obligé de passer par une macro qui le fera

Comme tu n'as indiqué que les procédures de l'userform, c'est pour cela que je t'ai dis d'exécuter l'userform

Rien n'empêche de créer une macro qui le fasse, mais avant toute autre chose il faudra passer celle-ci

Sub Init()
  With Sheets("FBD.PC")
    tb = .Range("A2:AA" & .Range("A" & Rows.Count).End(xlUp).Row).Value
  End With
End Sub

Ensuite dans toutes les macros, plus besoin de l'initialiser

Le 11/02/2016 à 15:44

Re, il y a des choses qui m'échappent, ça parait simple mais je n'arrive pas à concrétiser.

Je joins un fichier que je viens de terminer.

Merci beaucoup pour ton aide.

ps: Merci beaucoup, j'ai trouvé mes erreurs. C'est le résultat escompté.

Merci à vous. Je joins le fichier fonctionnel.

139variable-public.xlsm (253.24 Ko)
Rechercher des sujets similaires à "comment rendre tableau vba accessible projet"