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.