programmatically add reference and module in one macro

[mocnak]

hi, I am trying to add module with code to Sheet1 in Excel, and this module requires "Microsoft Visual Basic For Applications Extensibility 5.3" reference.


reference :


Public Sub reference()


ThisWorkbook.VBProject.References.AddFromGuid "{0002E157-0000-0000-C000-000000000046}", 1, 0
'Application.VBE.ActiveVBProject.References.AddFromFile "C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA6\VBE6EXT.OLB"

End Sub

and module :


Public Sub module()


Dim CodeMod As VBIDE.CodeModule
Dim S As String
Set CodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
S = _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
"MsgBox (""hello world"")" & vbNewLine & _
"End Sub" & vbNewLine
With CodeMod
.InsertLines .CountOfLines + 1, S
End With


End Sub

it works OK when I run them separately, is it possible to merge these 2 macros ?
when I tried, I get error : "Compile Error : User defined type not defined", because it first recognize I use "VBIDE.CodeModule" reference, before it starts.

[lecxe]

Hi

One solution is to not set the reference and use late binding, like:



Public Sub module()

Dim CodeMod As Object
Dim S As String

Set CodeMod = ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
S = _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
"MsgBox (""hello world"")" & vbNewLine & _
"End Sub" & vbNewLine

With CodeMod
.InsertLines .CountOfLines + 1, S
End With

End Sub

[ranman256]

You have a sub (aka macro) called 'module'.
But Subs must exist in a 'box' called a 'module' in vba.
(i wouldnt call a sub Module...its confusing)

If you are missing a reference, in vba environ, click TOOLS, REFERENCES.
checkmark the missing reference you error says it wants.

[mocnak]

thank you all, i found the best way how to do it :



Public Sub tosheet1()

ThisWorkbook.VBProject.VBComponents(Sheets("Sheet1").CodeName).CodeModule.AddFromString _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
"" & vbNewLine & _
"MsgBox (""It's working !"")" & vbNewLine & _
"" & vbNewLine & _
"End Sub"
End Sub

[snb]

crossposting isn't being appreciated.
See the forum rules.

http://www.ozgrid.com/forum/showthread.php?t=188395&p=713648#post713648

[GTO]

Greetings,

If you are adding this to newly created workbooks (i.e. - not actually ThisWorkbook), I would think your code should be dependable; though I would ditch the parentheses around the prompt argument. In case you are adding to pre-existing workbook(s), maybe ensure that a codename of 'Sheet1' exists; and that it doesn't already have a selection change sub added.



Option Explicit

Public Sub AddCode()

On Error Resume Next
With ThisWorkbook.VBProject.VBComponents("Sheet1")

If Err.Number <> 0 Then
On Error GoTo 0
Exit Sub
End If

On Error GoTo 0

If Not .CodeModule.Find("Private Sub Worksheet_SelectionChange(ByVal Target As Range)", 1, 1, -1, -1, True, True) Then

.CodeModule.InsertLines .CodeModule.CountOfLines + 1, _
vbNewLine & _
"Private Sub Worksheet_SelectionChange(ByVal Target As Range)" & vbNewLine & _
vbTab & "MsgBox ""hello world"", 0&, vbNullString" & vbNewLine & _
"End Sub"
End If

End With

End Sub

Hope that helps,

Mark