Using VBA to Write VBA Code

The example in this section demonstrates how you can write VBA code that writes more VBA code. The AddButtonAndCode procedure does the following:

1. Inserts a new worksheet.

2. Adds an ActiveX CommandButton control to the worksheet.

3. Adjusts the position, size , and caption of the CommandButton.

4. Inserts an event handler procedure for the CommandButton named CommandButton1_ Click in the sheet's code module. This procedure simply activates Sheet1 .

The AddButtonAndCode procedure follows .

 Sub AddButtonAndCode()     Dim NewSheet As Worksheet     Dim NewButton As OLEObject '   Add the sheet     Set NewSheet = Sheets.Add '   Add a CommandButton     Set NewButton = NewSheet.OLEObjects.Add _       ("Forms.CommandButton.1")     With NewButton         .Left = 4         .Top = 4         .Width = 100         .Height = 24         .Object.Caption = "Return to Sheet1"     End With '   Add the event handler code     Code = "Sub CommandButton1_Click()" & vbCrLf     Code = Code & " On Error Resume Next" & vbCrLf     Code = Code & " Sheets(""Sheet1"").Activate" & vbCrLf     Code = Code & " If Err <> 0 Then" & vbCrLf     Code = Code & " MsgBox ""Cannot activate Sheet1.""" _      & vbCrLf     Code = Code & " End If" & vbCrLf     Code = Code & "End Sub"     With ActiveWorkbook.VBProject. _       VBComponents(NewSheet.Name).CodeModule         NextLine = .CountOfLines + 1         .InsertLines NextLine, Code     End With End Sub 

Figure 28-6 shows the worksheet and the CommandButton control that were added by the AddButtonAndCode procedure.

Figure 28-6: This sheet, the CommandButton, and its event handler were added by using VBA.

CD-ROM  

This example is available on the companion CD-ROM. The filename is  add button and code.xlsm .

The tricky part of this procedure is inserting the VBA code into the code module for the new worksheet. The code is stored in a variable named Code , with each instruction separated by a carriage return and linefeed sequence. The InsertLines method adds the code to the code module for the inserted worksheet.

The NextLine variable stores the number of existing lines in the module incremented by one. This ensures that the procedure is added to the end of the module. If you simply insert the code beginning at line 1, it causes an error if the user 's system is set up to add an Option Explicit statement to each module automatically.

Figure 28-7 shows the procedure that is created by the AddButtonAndCode procedure in its new home in the code window.

Figure 28-7: VBA generated this event handler procedure.