Programmatically Adding Code
Last Updated on Friday, 13 November 2009 20:45 Written by Administrator Friday, 13 November 2009 20:29
You can add code at run time by accessing the VBA object module. It's worth deleting the code components you don't want first using a technique like the one described in Deleting Code Modules Programmatically.The following code will load the modules, class and userform files that it finds in the folder you supply.
Option Explicit
Private Const STR_MODULE_EXTENSION = "*.bas"
Private Const STR_CLASS_EXTENSION = "*.cls"
Private Const STR_FORM_EXTENSION = "*.frm"
Public Sub LoadComponents(project As VBProject, folder As String, loadModules As Boolean, loadClasses As Boolean, loadForms As Boolean)
If loadModules Then LoadComponentSet project, folder, STR_MODULE_EXTENSION
If loadClasses Then LoadComponentSet project, folder, STR_CLASS_EXTENSION
If loadForms Then LoadComponentSet project, folder, STR_FORM_EXTENSION
End Sub
' Loads a set of components for the supplied extension.
Private Sub LoadComponentSet(project As VBProject, folder As String, extension As String)
Dim fileList() As String
' Try modules first
fileList = GetFileList(folder, extension)
If UBound(fileList) > 0 Then
Dim fileCounter As Long
For fileCounter = 1 To UBound(fileList)
Debug.Print fileList(fileCounter)
project.VBComponents.Import fileList(fileCounter)
Next fileCounter
End If
End Sub
' Based on code found at http://www.ozgrid.com/forum/showthread.php?t=35241
Public Function GetFileList(folder As String, fileMask As String) As Variant
With Application.FileSearch
.NewSearch
.fileName = fileMask
.LookIn = folder
If .Execute = 0 Then Exit Function
Dim fileList() As String
Dim numberOfFiles As Long
numberOfFiles = .FoundFiles.Count
ReDim fileList(numberOfFiles)
Dim fileCounter As Long
For fileCounter = 1 To numberOfFiles
fileList(fileCounter) = .FoundFiles(fileCounter)
Next fileCounter
End With
GetFileList = fileList
End Function
Private Const STR_MODULE_EXTENSION = "*.bas"
Private Const STR_CLASS_EXTENSION = "*.cls"
Private Const STR_FORM_EXTENSION = "*.frm"
Public Sub LoadComponents(project As VBProject, folder As String, loadModules As Boolean, loadClasses As Boolean, loadForms As Boolean)
If loadModules Then LoadComponentSet project, folder, STR_MODULE_EXTENSION
If loadClasses Then LoadComponentSet project, folder, STR_CLASS_EXTENSION
If loadForms Then LoadComponentSet project, folder, STR_FORM_EXTENSION
End Sub
' Loads a set of components for the supplied extension.
Private Sub LoadComponentSet(project As VBProject, folder As String, extension As String)
Dim fileList() As String
' Try modules first
fileList = GetFileList(folder, extension)
If UBound(fileList) > 0 Then
Dim fileCounter As Long
For fileCounter = 1 To UBound(fileList)
Debug.Print fileList(fileCounter)
project.VBComponents.Import fileList(fileCounter)
Next fileCounter
End If
End Sub
' Based on code found at http://www.ozgrid.com/forum/showthread.php?t=35241
Public Function GetFileList(folder As String, fileMask As String) As Variant
With Application.FileSearch
.NewSearch
.fileName = fileMask
.LookIn = folder
If .Execute = 0 Then Exit Function
Dim fileList() As String
Dim numberOfFiles As Long
numberOfFiles = .FoundFiles.Count
ReDim fileList(numberOfFiles)
Dim fileCounter As Long
For fileCounter = 1 To numberOfFiles
fileList(fileCounter) = .FoundFiles(fileCounter)
Next fileCounter
End With
GetFileList = fileList
End Function
You could call this code in the following manner:
Public Sub Test()
LoadComponents Application.VBE.ActiveVBProject, ThisWorkbook.Path, True, True, True
End Sub
LoadComponents Application.VBE.ActiveVBProject, ThisWorkbook.Path, True, True, True
End Sub
You will need to add a reference to the Microsoft Visual Basic for Applications Extensibility 5.3 library and also allow access to the VBA object model.
The VBA project isn't protected by a password!