It utilizes the concept of exporting/importing code modules.
First, I create a text file and write the cell contents to the file.
(the first line contains the new code module name)
then, I remove the module (if it exists) and import the file
as a new module.
Sub Load_Module()
Dim RowNum
Dim fso, f, ModFileName, ModName, NewMod
Dim vbaModules
NewMod = "TestMod"
ModFileName = "C:\temp\" & NewMod & ".bas"
Set fso = CreateObject("Scripting.FileSystemObject")
'---------------------------
'Write Cells to Module file
'---------------------------
If (fso.fileexists(ModFileName)) Then
fso.deletefile ModFileName
End If
Set f = fso.createTextFile(ModFileName)
f.writeline "Attribute VB_Name = ""TestMod"""
For RowNum = 1 To 1000
If Cells(RowNum, 1) = "" Then Exit For
f.writeline Cells(RowNum, 1)
Next RowNum
f.Close
'vafilename = "C:\temp\Module2.bas"
'---------------------------
' Remove Existing Module
'---------------------------
Set vbaModules = ThisWorkbook.VBProject.VBComponents
On Error Resume Next
With ActiveWorkbook.VBProject
For cnt = .VBComponents.Count To 1 Step -1
ModName = .VBComponents(cnt).Name
If (UCase(ModName) = UCase(NewMod)) Then
.VBComponents.Remove .VBComponents(cnt)
End If
Next cnt
End With
'---------------------------
' Import module
'---------------------------
VBProject.VBComponents.Import Filename:=ModFileName
End Sub