Déclarer un classeur comme variable globale

Votre question implique que vous voulez mondiale classeur constante, pas une variable. Parce que VBA ne permet pas que des objets soient initialisés en dehors d'une procédure, vous ne pouvez pas avoir de constante objet. Le meilleur que vous pouvez faire est d'avoir un classeur public variable initialisée à un événement.

Vous pouvez déclarer une variable globale, mais vous ne pouvez pas exécuter de code pour affecter une valeur en dehors d'une procédure:

Public myBook As Excel.Workbook

Sub AssignWorkbook()
    Set myBook = Workbooks.Open("C:\SomeBook.xlsx") '// <~~ valid, inside sub
End Sub

Sub TestItWorked()
    MsgBox myBook.Name
End Sub

Donc dans un module normal you could have:

Public myBook As Excel.Workbook

Et Workbook_Open() événement:

Private Sub Workbook_Open()
    Set myBook = Workbooks.Open("C:\SomeOtherBook.xlsx")
End Sub

alors vous pouvez utiliser myBook ailleurs dans votre code sans devoir le réattribuer.

Il pourrait être utile d'avoir un coup d'oeil à Puce Pearson article sur la portée des variables en VBA ici

ce que vous voulez est une sorte d'usine avec des propriétés statiques, par exemple dans un module séparé

mFactoryWkbs

Private m_WkbLocations           As Workbook
Private m_WkbMergeBook           As Workbook

Public Property Get LOCATIONS() As Workbook
    If m_WkbLocations Is Nothing Then
        Set m_WkbLocations= Workbooks.Open("wherever")
    End If
    Set LOCATIONS = m_WkbLocations
End Property

Public Property Get MERGEBOOK () As Workbook
    If m_WkbMergeBook Is Nothing Then
        Set m_WkbMergeBook = Workbooks.Open("wherever")
    End If
    Set MERGEBOOK = m_WkbMergeBook 
End Property

pour utiliser, il suffit d'appeler la propriété où et quand vous en avez besoin, pas de variables supplémentaires (ou des ensembles pour eux) requis.

TotalRowsMerged = MERGEBOOK.Worksheets("Sheet1").UsedRange.Rows.Count

C'est le mieux que je puisse trouver jusqu'à maintenant. Le résultat est qu'il n'y a maintenant qu'un seul endroit pour changer le nom du fichier, cependant j'ai encore besoin de copier la fonction SET dans chaque sous-programme. Pas complètement idéal, mais mieux que rien.

Public Const DESTBOOK = "DURUM IT yields merged.xlsm"

Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

Sub Fill_CZ_Array()
Set Locations = Application.Workbooks("locXws.xlsx")
Set MergeBook = Application.Workbooks(DESTBOOK)
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

Chaque fois que je tombe sur ce, je déclare wb public constante chaîne de caractères:

public wb as string = "c:\location"

puis, tout au long du code dans le projet, vous pouvez vous référer à

workbooks(wb).anything

C'est le genre de chose que je fais habituellement quand j'ai des variables globales qui doivent être correctement initialisées:

dans un module de code général, mettez le code suivant:

Public Initialized As Boolean
Public Locations As Workbook

Sub Initialize()
    If Initialized Then Exit Sub
    Const fname As String = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"
    On Error Resume Next
        Set Locations = Workbooks(Dir(fname))
    On Error GoTo 0
    If Locations Is Nothing Then
        Set Locations = Workbooks.Open(fname)
    End If
    Initialized = True
End Sub

puis dans le module de code du classeur mettre:

Private Sub Workbook_Open()
    Initialize
End Sub

de plus, dans n'importe quelle Sous-Fonction Ou fonction de" passerelle " (par exemple, gestionnaires d'événements, UDFs, etc.) qui pourrait lancer votre code, mettre Initialize (ou peut-être: If Not Initialized Then Initialize) comme première ligne. Généralement, la plupart des sous-marins ne seront pas lancés directement et peuvent compter Locations avoir correctement paramétré par l'appelant. Si vous avez besoin de tester quelque chose qui ne fonctionnera pas correctement si la variable n'est pas définie, alors vous pouvez taper initialize directement dans la Fenêtre exécution.

Vous pouvez aussi le faire avec un module de classe et de s'appuyer sur la classe initialiser à faire le travail pour vous quand il est utilisé dans le module:

module de classe appelé cLocations:

Public Workbook As Workbook

Private Sub Class_Initialize()
    Set Workbook = Workbooks.Open("C:\Temp\temp.xlsx")
End Sub

Et où vous le souhaitez dans votre module, ou n'importe où ailleurs:

Dim Locations As New cLocations

Sub dosomething()
    Locations.Workbook.Sheets(1).Cells(1, 1).Value = "Hello World"
End Sub

Et puis, vous pouvez simplement utiliser Locations.Workbook pour désigner l'emplacement du classeur, et ThisWorkbook pour consulter le classeur le code s'exécute et ActiveWorkbook pour consulter le classeur qui a concentrer. De cette façon, vous pouvez exécuter votre code à partir d'un classeur (ThisWorkbook), à l'aide du classeur des emplacements (Locations.Workbook) comme référence et itérate par rapport à d'autres classeurs (ActiveWorkbook) pour ajouter un autre niveau d'automatisation.

Si vous parcourez le code, vous verrez que la classe n'est initialisé lorsque vous avez touché une ligne de code qui l'exige, et non pas lorsque le classeur est chargé.

je dois ajouter cependant, dans ce cas, je pense que si vous nous donnez une image un peu plus grande de ce que vous êtes essayer de réaliser nous pourrions être en mesure de vous donner une solution à un meilleur problème que celui que vous avez frappé en codant.

vous pouvez aussi aller plus loin, et abstraire au niveau de l'application, garder le classeur des localisations caché, et même fournir intellisense pour les feuilles nommées si vous connaissez leur position ou leur nom explicitement:

module de classe:

Private App As Application
Public Workbook As Workbook
Public NamedSheet As Worksheet

Private Sub Class_Initialize()
    Set App = New Application
    App.Visible = False
    App.DisplayAlerts = False
    Set Workbook = App.Workbooks.Open("C:\Temp\temp.xlsx") 'maybe open read only too?
    Set NamedSheet = Workbook.Sheets("SomethingIKnowTheNameOfExplicitly")
End Sub

Public Sub DoSomeWork()
    'ThisWorkbook refers to the one the code is running in, not the one we opened in the initialise
    ThisWorkbook.Sheets(1).Cells(1, 1).Value = Wb.Sheets(1).Cells(1, 1).Value
End Sub

Public Function GetSomeInfo() As String
    GetSomeInfo = NamedSheet.Range("RangeIKnowTheNameOfExplicitly")
End Function

Et puis dans votre module, la première fois que vous utilisez la variable sera initialisée en une seule ligne de code:

Dim Locations As New cLocations
Dim SomeInfo

Sub DoSomething()
    SomeInfo = Locations.GetSomeInfo 'Initialised here, other subs wont re-initialise

    Locations.Workbook.Sheets(1).Cells(1, 1).Value = _ 
        ThisWorkbook.Sheets(1).Cells(1, 1).Value

    Locations.NamedSheet.Cells(1,1).Value = "Hello World!"

    Locations.Workbook.Save
End Sub

Cette solution ne fonctionnera que si vous connaissez les numéros et les noms de toutes les feuilles de travail que vous utiliserez à partir du classeur référencé.

dans votre module, déclarez la variable publique de la feuille de travail pour toutes vos feuilles de travail comme suit:

Public sht1 As Worksheet
Public sht2 As Worksheet
Public sht3 As Worksheet
...

instanciez ces variables publiques dans l'événement Application load.

Sub Workbook_Open()

    Workbooks.Open ("your referenced workbook")

    'Instantiate the public variables
    Set sht1 = Workbooks("Test.xlsm").Sheets("Sheet1")
    Set sht2 = Workbooks("Test.xlsm").Sheets("Sheet2")
    Set sht3 = Workbooks("Test.xlsm").Sheets("Sheet3")

End Sub

vous pouvez maintenant renvoyer ces feuilles de travail globales dans votre sub.

Par exemple:

Sub test()
    MsgBox sht1.Range("A1").Value
    MsgBox sht2.Range("A1").Value
    MsgBox sht3.Range("A1").Value
End Sub

si vous créez un Module dites ExcelMod et que dans ce Module vous avez une fonction publique ou un sous-programme Initialize() et un autre appelé Terminate() vous pouvez initialiser et terminer les variables de niveau du Module en utilisant ces routines. Par exemple, j'ai utilisé cette avant: (Notez que les variables de module sont la première chose déclarée en haut du module.)

Dim excelApp As Object, wb As Workbook, ws As Worksheet

Sub Initialize()
    Set excelApp = CreateObject("Excel.Application")
    Set wb = Workbooks.Open("C:\SomeOtherBook.xlsx")
End Sub

Sub Terminate()
    Set excelApp = Nothing
    Set wb = Nothing
End Sub

Les variables font partie du module entier et ne sont initialisées et terminées qu'avec ces sous-programmes. Vous pouvez passez les variables à l'intérieur et à l'extérieur du module comme vous le souhaitez et utilisez-les dans tous les sous-programmes de ce module sans avoir à les définir à nouveau. Si vous devez utiliser dans un autre module, vous devrez le passer à ce module comme vous le feriez normalement.

comme d'autres l'ont mentionné, vous pouvez utiliser l'événement workbook_Open pour appeler la sous-fonction d'initialisation pour créer les objets et ne les définir qu'une seule fois si nécessaire.

Est-ce que vous êtes après?

si je comprends bien votre question, vous créez un code qui devrait fonctionner au niveau de l'application et non au niveau du classeur. Dans ce cas, pourquoi ne pas vous créer un complément.

Tout le code à l'intérieur de l'add-on avoir accès à tous les classeurs ouverts au niveau de l'application.

vous pouvez créer un Add-In, ou utiliser un module de classe pour travailler avec les propriétés, ...

Mais je ne suis pas sûr que ça va être que nettoyeur qu'un simple déclaration dans un module normal et un appel à cette procédure au classeur est ouvert va faire le truc juste trop belle .

(j'ai été en utilisant cette méthode pour un certain temps et n'ai pas été dérangé)

alors vous pouvez l'utiliser dans un (dédié ou non) module:

'Set the path to your files
Public Const DESTBOOK = "M:\My Documents\MSC Thesis\Italy\Merged\DURUM IT yields merged.xlsm"
Public Const LOCBOOK = "M:\My Documents\MSC Thesis\Italy\Merged\locXws.xlsx"

'Declare all global and public variables
Global Locations As Workbook
Global MergeBook As Workbook
Global TotalRowsMerged As String

'Set all variable (Procedure call from Workbook_Open)
Sub Set_All_Global_Variables()
    Set Locations = Set_Wbk(LOCBOOK)
    Set MergeBook = Set_Wbk(DESTBOOK)
    TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
    '...
End Sub

'Function to check if the workbook is already open or not
Function Set_Wbk(ByVal Wbk_Path As String) As Workbook
    On Error Resume Next
        Set Set_Wbk = Workbooks(Dir(Wbk_Path))
    On Error GoTo 0
    If Set_Wbk Is Nothing Then
        Set Set_Wbk = Workbooks.Open(Wbk_Path)
    End If
End Function

Et appeler la procédure définissant toutes les variables dans le module ThisWorkbook:

Private Sub Workbook_Open()
    Set_All_Global_Variables
End Sub