Code to operate within the code window?

[TheAntiGates]

Here are a couple of things I would like to know if they're doable.
1. Search code but more sophisticated than Control-F permits - e.g. a compound search - of the code window(s)
2. Code to go to the next breakpoint (the "F9 breakpoints").

I was hoping there might be a readable "breakpoints collection" with properties that I could navigate to. I don't expect that there is - just dreaming here.

Those are two things off the top of my head requiring code that operate within/upon the VBA window, though over time I've had other situations that I don't recall at this moment. I also remember discussions over the years about how or whether to do "code that writes out code" but I don't remember the conclusions, and my search engine IQ seems to be crashed today....

[mikerickson]

If you instal the Visual Basic for Applications Extensibility library, the Object Browser will give you access to
VBComponent and CodeModule objects that would get you started on writing "code that writes code".

[TheAntiGates]

Okay, I've found strong sources for extensibility lib information so I'll have to get into these -
http://www.cpearson.com/excel/vbe.aspx
http://msdn.microsoft.com/en-us/library/aa189282(v=office.10).aspx

VBA extensibility does not appear in [Excel 2003] tools/references (nothing appears in that dialog). Moreover, I don't yet know how to tell if it's installed, or if it's an add-in or what. AAR this works[vba]Sub AddAnEvent()
Dim StartLine As Long
With ThisWorkbook.VBProject.VBComponents("Sheet1").CodeModule
StartLine = .CreateEventProc("Change", "Worksheet") + 1
.InsertLines StartLine, _
"Msgbox ""Changed a cell!"",vbOkOnly"
End With
End Sub [/vba]Notably, With ThisWorkbook.VBProject: End With: ?Err = 0 gives True so I'm wondering if that means that I have it...

[mikerickson]

A quick way to tell if the library is installed is to search for VBComponent in the Object Browser.

(Note you can write code that refers to VBComponents with the library not installed, but they must be dimensioned as Object not VBComponent)

[Bob Phillips]

It is always installed, it comes as standard with VBA, it is just whether you reference it from within a particular project.

[Aflatoon]

Regarding your #2, how would you plan to run your code while you are in break mode?

[GTO]

Regarding your #2, how would you plan to run your code while you are in break mode?

By gosh, that would seem a tad challenging. Maybe just my sense of humor, but ROFL!

[Aflatoon]

I confess, it was a somewhat glib remark as I imagine that this was not a run-time request but rather a design time one. The simplest workaround I can think of would be to bookmark the breakpoints, then no code is required to move between them; I struggle to see a real need for this though.

[Kenneth Hobs]

Nothing appears in the Tools > References...?

Maybe you don't know the name of the object. It is in the comments in the 2nd Sub of this example.

[VBA]'http://www.mrexcel.com/forum/showthread.php?t=359785
Sub Test()
Dim the_Calc As String
the_Calc = "Range(""B13"").Formula = ""=SUM(D1214)"""
CommandAsString the_Calc
'CommandAsString Range("B5").Value
End Sub

Sub CommandAsString(cmdString As String)
'Add: Tools > Reference > Microsoft Visual Basic for Applications Extensibility 5.3 > OK
Dim MyComponent As VBComponent
Set MyComponent = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_StdModule)

Dim MyCodeString As String: MyCodeString = "Public Sub VBACommandAsString" & vbCrLf
MyCodeString = MyCodeString & cmdString & vbCrLf
MyCodeString = MyCodeString & "End Sub" & vbCrLf

MyComponent.CodeModule.AddFromString MyCodeString

Application.Run "VBACommandAsString"

'Debug.Print ThisWorkbook.VBProject.VBComponents.Count

'ThisWorkbook.VBProject.VBComponents.Remove ThisWorkbook.VBProject.VBComponents.Item(MyComponent.Name)
End Sub
[/VBA]