No idea why you'd want to, but.....
- Add a reference in the VBE to
Microsoft Visual Basic For Applications Extensibility 5.3.
- Enable programmatic access to the VBA Project.
In Excel 2010 select the Developer
tab and click the Macro Security
button.
Under macro settings tick Trust access to the VBA project object model.
Use code similar to this:
Sub AddProcedureToModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Dim x As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule
x = 1
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "Public Sub MyProcedureName()"
LineNum = LineNum + 1
Do While Sheet1.Cells(x, 1) <> ""
.InsertLines LineNum, " " & Sheet1.Cells(x, 1)
x = x + 1
LineNum = LineNum + 1
Loop
.InsertLines LineNum, "End Sub"
End With
End Sub
This will copy whatever is in Sheet1 column A into the VBE.
http://www.cpearson.com/excel/vbe.aspx
Edit:
After re-reading your question, this code will add the value in U6 as a comment to the bottom of any code in Sheet1 module:
Sub AddCommentModule()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents("Sheet1")
Set CodeMod = VBComp.CodeModule
With CodeMod
LineNum = .CountOfLines + 1
.InsertLines LineNum, "'" & Sheet1.Range("U6")
End With
End Sub
Note - in these instances, Sheet1 is the sheets codename and not necessarily the name that appears on the sheet tab. To use that use ThisWorkbook.Worksheets("Sheet1").
instead of just Sheet1
.
Edit 2 (as I'm waiting for 5:30pm to go home):
Add this code into Sheet1 module and it will auto-update the comments whenever you type into cell U6:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$U$6" Then
AddCommentModule
End If
End Sub