programación - Control de fuente de los módulos de código de Excel VBA

vba excel 2016 pdf español

Question

Me gustaría poder controlar los módulos VBA de mi hoja de cálculo de Excel (actualmente usando Excel 2003 SP3) para poder compartir y administrar el código utilizado por un grupo de hojas de cálculo diferentes, y por lo tanto me gustaría volver a cargarlos desde archivos cuando se abre la hoja de cálculo.

Tengo un módulo llamado Loader.bas, que utilizo para hacer la mayor parte del trabajo de donkey (cargar y descargar cualquier otro módulo que sea necesario), y me gustaría poder cargarlo desde un archivo tan pronto como la hoja de cálculo se abre.

He adjuntado el siguiente código al evento Workbook_Open (en la clase ThisWorkbook).

Private Sub Workbook_Open()
    Call RemoveLoader
    Call LoadLoader
End Sub

Donde RemoveLoader (también dentro de la clase ThisWorkbook) contiene el siguiente código:

Private Sub RemoveLoader()
    Dim y As Integer
    Dim OldModules, NumModules As Integer
    Dim CompName As String

    With ThisWorkbook.VBProject
        NumModules = ThisWorkbook.VBProject.VBComponents.Count
        y = 1
        While y <= NumModules
            If .VBComponents.Item(y).Type = 1 Then
                CompName = .VBComponents.Item(y).Name
                If VBA.Strings.InStr(CompName, "Loader") > 0 Then
                    OldModules = ThisWorkbook.VBProject.VBComponents.Count
                    .VBComponents.Remove .VBComponents(CompName)
                    NumModules = ThisWorkbook.VBProject.VBComponents.Count
                    If OldModules - NumModules = 1 Then
                        y = 1
                    Else
                        MsgBox ("Failed to remove " & CompName & " module from VBA project")
                    End If
                End If
            End If
            y = y + 1
        Wend
    End With
End Sub

Lo cual es probablemente un poco demasiado complicado y un poco tosco, ¡pero estoy intentando todo lo que puedo encontrar para cargar el módulo externo!

A menudo, cuando abro la hoja de cálculo, la función RemoveLoader encuentra que ya hay un módulo "Loader1" incluido en el proyecto VBA que no se puede eliminar, y también falla al cargar el nuevo módulo Loader desde el archivo.

¿Alguna idea si lo que estoy tratando de hacer es posible? Excel parece muy aficionado a agregar un 1 a estos nombres de módulos, ya sea al cargar o eliminar (no estoy seguro de cuál).

Answer 1

Hay una excelente solución al problema de control de versiones de vba aquí: https://github.com/hilkoc/vbaDeveloper

Lo bueno de esto es que exporta su código automáticamente , tan pronto como guarda su libro de trabajo. Además, cuando abres un libro de trabajo, importa el código.

No necesita ejecutar ningún script de compilación ni comandos de Maven, y no necesita realizar ningún cambio en sus libros de trabajo. Funciona para todos.

También solucionó el problema de importación donde los módulos como ModName se importan como ModName1 en un módulo duplicado. La importación funciona como debería, incluso cuando se hace varias veces.

Como beneficio adicional, viene con un formateador de código simple, que le permite formatear su código vba a medida que lo escribe dentro del Editor VBA.

Answer 2

Llevo meses trabajando exactamente en esto. Creo que lo descubrí.

Si el Proyecto VB está intentando eliminar un módulo que contiene algo en la pila de llamadas, demora la eliminación hasta que la pila de llamadas haga saltar el módulo que se está reemplazando.

Para evitar que un módulo esté en la pila de llamadas, inicie su código con Application.OnTime

Private Sub Workbook_Open()

    ''WAS: module_library (1)

    Application.OnTime (Now + TimeValue("00:00:01")), "load_library_kicker_firstiter"

End Sub

Si se está recuperando automáticamente su código como lo soy yo, también tendrá que iniciar el código que sobrescribe el código de "llamada" con la misma estrategia.

Todavía no realicé pruebas exhaustivas, estoy en modo de celebración total, pero esto me acerca mucho al código de auto-recuperación del 99.9% en un archivo .xls independiente sin ningún otro truco.

Answer 3

Mira la página de VBAMaven. Tengo una solución de cosecha propia que utiliza los mismos conceptos. Tengo una biblioteca común con un montón de código fuente, una compilación de ant y un script VB de ''importación''. Ant controla la construcción, que toma un archivo de Excel en blanco e inserta el código necesario en él. @Mike es absolutamente correcto: cualquier definición de módulo duplicado tendrá automáticamente un número agregado al nombre del módulo. Además, las clases de módulos de clase (como en Hoja y ThisWorkbook) requieren un tratamiento especial. No puede crear esos módulos, debe leer el archivo de entrada y escribir el búfer en el módulo apropiado. Este es el script VB que actualmente uso para hacer esto. La sección que contiene @ texto delimitado (es decir, @build file @) son marcadores de posición: la compilación ant reemplaza estas etiquetas con contenido significativo. No es perfecto, pero funciona para mí.

''''
'' Imports VB Basic module and class files from the src folder
'' into the excel file stored in the bin folder. 
''

Option Explicit

Dim pFileSystem, pFolder,  pPath
Dim pShell
Dim pApp, book

Dim pFileName

pFileName = "@build file@"

Set pFileSystem = CreateObject("Scripting.FileSystemObject")

Set pShell = CreateObject("WScript.Shell")
pPath = pShell.CurrentDirectory

If IsExcelFile (pFileName) Then
    Set pApp = WScript.CreateObject ("Excel.Application")
    pApp.Visible = False
    Set book = pApp.Workbooks.Open(pPath & "/build/" & pFileName)
Else
    Set pApp = WScript.CreateObject ("Word.Application")
    pApp.Visible = False
    Set book = pApp.Documents.Open(pPath & "/build/" & pFileName)
End If


''Include root source folder code if no args set
If Wscript.Arguments.Count = 0 Then
    Set pFolder = pFileSystem.GetFolder(pPath & "/src")
    ImportFiles pFolder, book
    ''
    '' Get selected modules from the Common Library, if any
    @common path@@common file@
Else
    ''Add code from subdirectories of src . . .
    If Wscript.Arguments(0) <> "" Then
        Set pFolder = pFileSystem.GetFolder(pPath & "/src/" & Wscript.Arguments(0))
        ImportFiles pFolder, book
    End If
End If





Set pFolder = Nothing
Set pFileSystem = Nothing
Set pShell = Nothing


If IsExcelFile (pFileName) Then
    pApp.ActiveWorkbook.Save
Else
    pApp.ActiveDocument.Save
End If

pApp.Quit
Set book = Nothing
Set pApp = Nothing


'''' Loops through all the .bas or .cls files in srcFolder
'' and calls InsertVBComponent to insert it into the workbook wb.
''
Sub ImportFiles(ByVal srcFolder, ByVal obj)
    Dim fileCollection, pFile
    Set fileCollection = srcFolder.Files
    For Each pFile in fileCollection
        If Right(pFile, 3) = "bas _
          Or Right(pFile, 3) = "cls _
          Or Right(pFile, 3) = "frm Then
            InsertVBComponent obj, pFile
        End If
    Next
    Set fileCollection = Nothing
End Sub


'''' Inserts the contents of CompFileName as a new component in 
''  a Workbook or Document object.
''
''  If a class file begins with "Sheet", then the code is
''  copied into the appropriate code module 1 painful line at a time.
''
''  CompFileName must be a valid VBA component (class or module) 
Sub InsertVBComponent(ByVal obj, ByVal CompFileName)
    Dim t, mName
    t = Split(CompFileName, "/")
    mName = Split(t(UBound(t)), ".")
    If IsSheetCodeModule(mName(0), CompFileName) = True Then
        ImportCodeModule obj.VBProject.VBComponents(mName(0)).CodeModule, _
                         CompFileName
    Else
        If Not obj Is Nothing Then
            obj.VBProject.VBComponents.Import CompFileName
        Else
            WScript.Echo  "Failed to import " & CompFileName
        End If
    End If 
End Sub

''''
'' Imports the code in the file fName into the workbook object
'' referenced by mName.
'' @param target destination CodeModule object in the excel file
'' @param fName file system file containing code to be imported
Sub ImportCodeModule (ByVal target, ByVal fName)
    Dim shtModule, code, buf    
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject") 
    Const ForReading = 1, ForWriting = 2, ForAppending = 3
    Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0

    Set buf = fso.OpenTextFile(fName, ForReading, False, TristateUseDefault)
    buf.SkipLine
    code = buf.ReadAll

    target.InsertLines 1, code
    Set fso = Nothing
End Sub


''''
'' Returns true if the code module in the file fName
'' appears to be a code module for a worksheet.
Function IsSheetCodeModule (ByVal mName, ByVal fName)
    IsSheetCodeModule = False
    If mName = "ThisWorkbook" Then
       IsSheetCodeModule = False
    ElseIf Left(mName, 5) = "Sheet" And _
       IsNumeric(Mid (mName, 6, 1)) And _
       Right(fName, 3) = "cls Then
       IsSheetCodeModule = True
    End If
End Function

''''
'' Returns true if fName has a xls file extension
Function IsExcelFile (ByVal fName)
    If Right(fName, 3) = "xls" Then
        IsExcelFile = True
    Else
        IsExcelFile = False
    End If 
End Function

Answer 4

No puedo dejar comentario para comentar.

Hay una excelente solución al problema de control de versiones de vba aquí: https://github.com/hilkoc/vbaDeveloper

Acerca de guardar VBAProjects personalizados utilizando este XLAM. Prueba esto en Build.bas:

''===============
Public Sub testImport()
    Dim proj_name As String
    Dim vbaProject As Object

    ''proj_name = "VBAProject"
    ''Set vbaProject = Application.VBE.VBProjects(proj_name)

    Set vbaProject = Application.VBE.ActiveVBProject
    proj_name = vbaProject.name

    Build.importVbaCode vbaProject
End Sub
''===============
Public Sub testExport()
    Dim proj_name As String
    Dim vbaProject As Object

    ''proj_name = "VBAProject"
    ''Set vbaProject = Application.VBE.VBProjects(proj_name)

    Set vbaProject = Application.VBE.ActiveVBProject
    proj_name = vbaProject.name

    Build.exportVbaCode vbaProject
End Sub
''===============

Esto exportará / importará el proyecto activo de VBA.

Answer 5

Por lo general, la cosa "Loader1" sucede cuando se le pide a Excel que importe un módulo y ya existe un módulo con el mismo nombre. Por lo tanto, si importa "Loader", luego vuelva a cargarlo y obtendrá "Loader1". Esto se debe a que Excel no sabe (o quizás simplemente no le importa) si realmente es lo mismo o una nueva parte de la funcionalidad que simplemente tiene el mismo nombre de módulo, por lo que lo importa de todos modos.

No puedo pensar en una solución perfecta, pero creo que me inclino a intentar poner la lógica de carga / descarga en un complemento. Esa cosa de Workbook_Open parece un poco vulnerable y tenerla en todos los workbooks va a ser una Gran dolor si el código alguna vez necesita cambiar (nunca digas nunca). La lógica XLA podría ser más compleja (más difícil de atrapar los eventos necesarios, por una cosa) pero al menos solo existirá en un lugar.