Excel vba add code to sheet module programmatically

Question

How to put the programmatically generated workbook an event code similar to below:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim nextTarget As Range

    Set nextTarget = Range(Selection.Address) 'store the next range the user selects

    Target.Columns.Select 'autofit requires columns to be selected
    Target.Columns.AutoFit

    nextTarget.Select
End Sub

So you're basically trying to create a workbook and then copy code to the freshly created working? — SilentRevolution, Jan 17 '16 at 11:04
Options: use a template with the code in it, use the vba ide interface to programmatically add the code, or use an application level event in another workbook to run the code — chris neilsen, Jan 17 '16 at 11:10

score 29 · Accepted Answer · answered Jan 17 '16 at 12:15

29

Use this to add a workbook and place a worksheet change event into the Sheet1 module.

Sub AddSht_AddCode()
    Dim wb As Workbook
    Dim xPro As VBIDE.VBProject
    Dim xCom As VBIDE.VBComponent
    Dim xMod As VBIDE.CodeModule
    Dim xLine As Long

    Set wb = Workbooks.Add

    With wb
        Set xPro = .VBProject
        Set xCom = xPro.VBComponents("Sheet1")
        Set xMod = xCom.CodeModule

        With xMod
            xLine = .CreateEventProc("Change", "Worksheet")
            xLine = xLine + 1
            .InsertLines xLine, "  Cells.Columns.AutoFit"
        End With
    End With

End Sub

When you 1st run the code you may get an error.

Hit the Stop Icon and select the tools menu and "References"

Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.

Run the code again and it should work.

answered Jan 17 '16 at 12:15

Davesexcel

6,896
2
27
42

Nice one. I never noticed you could create an event procedure in the VBIDE like that. – Doug Glancy Jan 17 '16 at 18:51
is it possible to apply late-binding to above code? I got the error ActiveX component can't create object. – Kelaref Jan 11 '17 at 15:00
Great. This needs to be completed by [cpearson doc](http://www.cpearson.com/excel/vbe.aspx), which needs itself to be adjusted to the VBA and windows version (32/64 bits) See [mdsn](https://msdn.microsoft.com/en-us/library/ee691831(loband).aspx#odc_office2010_Compatibility32bit64bit_IntroducingVBA7CodeBase) and this [SO question](https://stackoverflow.com/questions/3072356/what-are-the-differences-between-vba-6-0-and-vba-7-0) – hornetbzz Feb 01 '18 at 05:10
2

I recommend against using CreateEventProc. I've found it causes the VBA Editor to unceremoniously open up in front of the user (or me). I just tested again to be certain. Instead, use InsertLines like this: InsertLines xLine, "Private Sub MyButton_Click()", followed by the procedure code, and an End Sub. From anything I've been able to determine, CreateEventProc doesn't do anything special that can't be done with InsertLines, except perhaps it simply glues the two parameters together with an underscore to get a procedure name in the format "ObjectName_EventName". – spinjector Jul 11 '19 at 20:02
Is there a way to change the With property to insert the code into a specified pre-existing module? For example, say we want the code added by .InsertLines to be inserted into Line 5 in Module4? – Majora Nov 10 '19 at 01:58

Excel vba add code to sheet module programmatically

1 Answers1

When you 1st run the code you may get an error.

Hit the Stop Icon and select the tools menu and "References"

Then find "Microsoft Visual Basic for Applications Extensibility 5.3 library" and check it.

Linked

Related