excel - publicas - Declara un libro de trabajo como una variable global

variables publicas

Question

Estoy empezando a escribir un código que se aplicará a varios libros de trabajo, pero siempre usa el mismo libro de referencia. El código tendrá muchas subscripciones, y como estoy tratando de evitar atenuar una variable al libro de referencia en cada sub, me gustaría declararlas globales.

Primero tuve:

Global Locations As Excel.Workbook
Set Locations = Workbooks.Open("M:/My Documents/MSC Thesis/Italy/Merged/locXws.xlsx")

Lo que me dio:

"Error de compilación: procedimiento externo no válido"

Después de algunas búsquedas en Google encontré el siguiente bit de código en alguna parte:

Public Const Locations As Excel.Workbook = "Workbooks.Open("M:/My Documents/MSC Thesis/Italy/Merged/locXws.xlsx")"

Lo que me dio:

"Error de compilación: esperado: nombre de tipo"

Editar:

Utilizando:

Public Const Locations As Excel.Workbook = "Workbooks.Open(''M:/My Documents/MSC Thesis/Italy/Merged/locXws.xlsx'')"

(Las comillas simples dentro de la instrucción Workbooks.Open) dan como resultado el mismo error que cuando se usan comillas dobles.

¿Quién sabe lo que estoy haciendo mal?

Edit2:

También intenté declarar las variables en el "ThisWorkbook", siguiendo esta respuesta usando:

Private Sub Workbook_Open()
Dim Locations As Excel.Workbook
Dim MergeBook As Excel.Workbook
Dim TotalRowsMerged As String


Locations = Workbooks.Open("M:/My Documents/MSC Thesis/Italy/Merged/locXws.xlsx")
MergeBook = Workbooks.Open("M:/My Documents/MSC Thesis/Italy/Merged/DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count
End Sub

Pero luego vuelve una

"Objeto requerido"

dentro de mi modulo

Edit3:

Ahora tengo esto que funciona, pero tiene el inconveniente de tener que copiar las líneas SET en cada Sub, ¿tiene que haber una mejor manera de hacerlo?

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("DURUM IT yields merged.xlsm")
TotalRowsMerged = MergeBook.Worksheets("Sheet1").UsedRange.Rows.Count

Answer 1

Cada vez que me encuentro con esto, declaro wb como una cadena constante pública:

public wb as string = "c:/location"

Luego, a lo largo del código en el proyecto, puede referirse a

workbooks(wb).anything

Answer 2

Creo que la forma más universal para la variable global del libro de trabajo sería crear un módulo con un procedimiento de Public Property Get . Puede referirse a él sin llamar a ningún código primero, y no tiene que preocuparse si el archivo está abierto o no.

Aquí está el código del módulo de ejemplo para una de las variables:

Private wLocations As Workbook

Public Property Get Locations() As Workbook
  Const sPath As String = "M:/My Documents/MSC Thesis/Italy/Merged/locXws.xlsx"
  Dim sFile As String

  If wLocations Is Nothing Then
      ''extract file name from full path
      sFile = Dir(sPath)

      On Error Resume Next

      ''check if the file is already open    
      Set wLocations = Workbooks(sFile)

      If wLocations Is Nothing Then
        Set wLocations = Workbooks.Open(sPath)
      End If

      On Error GoTo 0
  End If
  Set Locations = wLocations
End Property

Puedes usarlo en cualquier parte del código como una variable global:

Sub Test()
  Debug.Print Locations.Worksheets.Count
End Sub

Answer 3

Es posible que desee crear un complemento o usar un módulo de clase para trabajar con propiedades, ...

Pero no estoy seguro de que sea más limpio que una simple declaración en un módulo regular y una llamada a ese procedimiento en la apertura del libro de trabajo también funcionará bien.

(He estado usando este método varias veces y no me han molestado)

Así que puedes usar esto en un módulo regular (dedicado o no) :

''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

Y llame al procedimiento configurando todas las variables en el módulo de ThisWorkbook :

Private Sub Workbook_Open()
    Set_All_Global_Variables
End Sub

Answer 4

Este es el tipo de cosas que suelo hacer cuando tengo variables globales que deben inicializarse correctamente:

En un módulo de código general ponga el siguiente código:

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

A continuación, en el módulo de código del libro de trabajo poner:

Private Sub Workbook_Open()
    Initialize
End Sub

Además, en cualquier sub o función de "puerta de enlace" (por ejemplo, controladores de eventos, UDF, etc.) que pueda iniciar su código, coloque Initialize (o tal vez: If Not Initialized Then Initialize ) como la primera línea. Normalmente, la mayoría de los subs no se iniciarán directamente y pueden confiar en que el llamante establezca correctamente las Locations . Si necesita probar algo que no se ejecutará correctamente si la variable no está establecida, simplemente puede escribir initialize directamente en la ventana Inmediato.

Answer 5

Esto es lo mejor que se me ocurre hasta ahora. El resultado es que ahora solo hay un lugar para cambiar el nombre del archivo, sin embargo, todavía necesito copiar la función SET dentro de cada subrutina. Aún no es completamente ideal, pero mejor que nada.

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

Answer 6

Lo que desea es algún tipo de Factory con propiedades estáticas, por ejemplo, en un módulo separado

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

Para usarlo, solo llame a la propiedad donde y cuando la necesite, no se requieren variables adicionales (o Conjuntos para ellos).

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

Answer 7

Si crea un Módulo, digamos ExcelMod y dentro de ese Módulo, tiene una función pública o subrutina Inicializar () y otra llamada Terminar () puede inicializar y terminar las variables de nivel del Módulo usando esas rutinas. Por ejemplo, lo he usado antes: (tenga en cuenta que las variables del módulo son lo primero que se declara en la parte superior del módulo).

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

Las variables forman parte de todo el módulo y solo se inicializan y terminan con estas subrutinas. Puede pasar las variables dentro y fuera del módulo como desee y utilizarlas en TODAS estas subrutinas de módulos sin tener que configurarlas nuevamente. Si necesita usar otro módulo, deberá pasarlo a ese módulo como lo haría normalmente.

Además, como otros han mencionado, puede usar el evento workbook_Open para llamar al sub de inicialización para crear los objetos y configurarlos solo una vez si es necesario.

¿Es esto lo que buscas?

Answer 8

Si entiendo su pregunta correctamente, está creando un código que debería funcionar en el nivel de la aplicación y no en el nivel del libro de trabajo. En este caso, ¿por qué no creas un complemento?

Todo el código dentro del complemento tendrá acceso a todos los libros abiertos en el nivel de la aplicación.

Answer 9

Su pregunta implica que desea una constante de libro global, no una variable. Debido a que VBA no permite que los objetos se inicialicen fuera de un procedimiento, no puede tener una constante de objeto. Lo mejor que puedes hacer es tener una variable de libro público que se inicialice en un evento.

Puede declarar una variable global, pero no puede ejecutar código para asignar un valor fuera de un procedimiento:

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

Así que en un módulo normal podrías tener:

Public myBook As Excel.Workbook

Y en tu evento Workbook_Open() :

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

Luego, puede usar myBook en otro lugar de su código sin tener que volver a asignarlo.

Podría valer la pena echar un vistazo al artículo de Chip Pearson sobre el alcance variable en VBA here

Answer 10

También puede hacerlo con un módulo de clase y confiar en el inicializador de clase para que haga el trabajo por usted cuando se use en el módulo:

Módulo de clase llamado cLocations:

Public Workbook As Workbook

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

Y donde te guste en tu módulo, o en cualquier lugar para esa materia:

Dim Locations As New cLocations

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

Y luego, puede usar Locations.Workbook para referirse al libro de ubicaciones, y ThisWorkbook para referirse al libro en el que se ejecuta el código y ActiveWorkbook para referirse al libro que tiene el foco. De esta manera, puede ejecutar su código desde un libro de trabajo ( ThisWorkbook ), utilizando el libro de ubicaciones ( Locations.Workbook ) como referencia e iterar sobre otros libros de trabajo ( ActiveWorkbook ) para agregar otro nivel de automatización.

Si recorres el código, verás que la clase solo se inicializa cuando llegas a una línea de código que lo requiere, no cuando se carga el libro.

Sin embargo, debo agregar que, en este caso, creo que si nos da un panorama un poco más amplio de lo que está tratando de lograr, podríamos darle una solución a un problema mejor que el que ha detectado durante la codificación.

También puede llevar esto un paso más allá y abstraerse al nivel de la aplicación, mantener oculto el libro de ubicaciones e incluso proporcionar inteligencia para las hojas con nombre si conoce su posición o su nombre explícitamente:

Módulo de clase:

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

Y luego en su módulo, la primera vez que use la variable se inicializará en una línea de código:

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

Answer 11

Esta solución solo funcionará si conoce los números y nombres de todas las hojas de trabajo que usará del libro de referencia.

En su módulo, declare la variable pública de la hoja de trabajo para todas sus hojas de trabajo de la siguiente manera:

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

Crea una instancia de estas variables públicas en el evento de carga de la aplicación.

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

Ahora puede consultar estas hojas de trabajo globales en su sub.

Por ejemplo:

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