programmatically open VBE and go to a specified procedure in target module?

Discussion in 'Microsoft Excel Programming' started by DataFreakFromUtah, Jul 9, 2004.

  1. Hello all,

    Win 2000
    Excel 2000

    I am wondering if there is a way to programmatically open the VBE and
    go to a specific
    procedure or macro in the target module (in this example: basModule1).
    I have the code to go
    to the target module, but I can't seem
    to figure out a way for the procedure to open at the target procedure
    or macro.
    I spent some time looking at the object model and searching the
    microsoft.public.excel.programming newsgroup, no luck.
    Might anyone out there have an answer?
    Thank you in advance for any information you may provide.
    Cheers,
    Tom


    Sub OpenVBE()
    ' Open the Visual Basic Editor Programmatically

    Application.VBE.MainWindow.Visible = True
    'The next line of code goes to a specified module
    ThisWorkbook.VBProject.VBComponents("basModule1").Activate

    End Sub
     
    DataFreakFromUtah, Jul 9, 2004
    #1
    1. Advertisements

  2. DataFreakFromUtah

    Jake Marx Guest

    Hi DataFreakFromUtah,

    I'm not well-versed with the VBE objects, but this seems to work:

    Sub OpenVBE()
    Dim lStartLine As Long

    ThisWorkbook.VBProject.VBComponents("basModule1").Activate

    With Application.VBE.ActiveCodePane.CodeModule
    lStartLine = .ProcStartLine("test2", 0)
    .CodePane.SetSelection lStartLine, 1, lStartLine, 1
    End With
    End Sub


    Just change the "test2" to the name of your procedure.

    --
    Regards,

    Jake Marx
    MS MVP - Excel
    www.longhead.com

    [please keep replies in the newsgroup - email address unmonitored]


    DataFreakFromUtah wrote:
    > Hello all,
    >
    > Win 2000
    > Excel 2000
    >
    > I am wondering if there is a way to programmatically open the VBE and
    > go to a specific
    > procedure or macro in the target module (in this example: basModule1).
    > I have the code to go
    > to the target module, but I can't seem
    > to figure out a way for the procedure to open at the target procedure
    > or macro.
    > I spent some time looking at the object model and searching the
    > microsoft.public.excel.programming newsgroup, no luck.
    > Might anyone out there have an answer?
    > Thank you in advance for any information you may provide.
    > Cheers,
    > Tom
    >
    >
    > Sub OpenVBE()
    > ' Open the Visual Basic Editor Programmatically
    >
    > Application.VBE.MainWindow.Visible = True
    > 'The next line of code goes to a specified module
    > ThisWorkbook.VBProject.VBComponents("basModule1").Activate
    >
    > End Sub
     
    Jake Marx, Jul 9, 2004
    #2
    1. Advertisements

  3. Thanks Jake!
    I was able to make the procedure work by adding one line of code to
    it:
    Application.VBE.MainWindow.Visible = True
    Apparently the procedure wouldn't work if the VBE wasn't all ready
    open.
    I am in Win 2000, Excel 2000, with VBE>Tools>References>Microsoft VBA
    Extensibility 5.3 checked.
    The procedure that worked for me is below:



    Sub OpenVBEGoToSpecificMacro()

    Dim lStartLine As Long
    Application.VBE.MainWindow.Visible = True
    ThisWorkbook.VBProject.VBComponents("ModuleNameHere").Activate

    With Application.VBE.ActiveCodePane.CodeModule
    lStartLine = .ProcStartLine("VBAProcedureNameHere", 0)
    .CodePane.SetSelection lStartLine, 1, lStartLine, 1
    End With
    End Sub






    "Jake Marx" <> wrote in message news:<#>...
    > Hi DataFreakFromUtah,
    >
    > I'm not well-versed with the VBE objects, but this seems to work:
    >
    > Sub OpenVBE()
    > Dim lStartLine As Long
    >
    > ThisWorkbook.VBProject.VBComponents("basModule1").Activate
    >
    > With Application.VBE.ActiveCodePane.CodeModule
    > lStartLine = .ProcStartLine("test2", 0)
    > .CodePane.SetSelection lStartLine, 1, lStartLine, 1
    > End With
    > End Sub
    >
    >
    > Just change the "test2" to the name of your procedure.
    >
    > --
    > Regards,
    >
    > Jake Marx
    > MS MVP - Excel
    > www.longhead.com
    >
    > [please keep replies in the newsgroup - email address unmonitored]
    >
    >
    > DataFreakFromUtah wrote:
    > > Hello all,
    > >
    > > Win 2000
    > > Excel 2000
    > >
    > > I am wondering if there is a way to programmatically open the VBE and
    > > go to a specific
    > > procedure or macro in the target module (in this example: basModule1).
    > > I have the code to go
    > > to the target module, but I can't seem
    > > to figure out a way for the procedure to open at the target procedure
    > > or macro.
    > > I spent some time looking at the object model and searching the
    > > microsoft.public.excel.programming newsgroup, no luck.
    > > Might anyone out there have an answer?
    > > Thank you in advance for any information you may provide.
    > > Cheers,
    > > Tom
    > >
    > >
    > > Sub OpenVBE()
    > > ' Open the Visual Basic Editor Programmatically
    > >
    > > Application.VBE.MainWindow.Visible = True
    > > 'The next line of code goes to a specified module
    > > ThisWorkbook.VBProject.VBComponents("basModule1").Activate
    > >
    > > End Sub
     
    DataFreakFromUtah, Jul 12, 2004
    #3
  4. DataFreakFromUtah

    Peter T Guest

    Have you tried, simply:

    Sub Test()
    Application.Goto "MyRoutine"
    End Sub

    Regards,
    Peter

    >-----Original Message-----
    >Thanks Jake!
    >I was able to make the procedure work by adding one line

    of code to
    >it:
    > Application.VBE.MainWindow.Visible = True
    >Apparently the procedure wouldn't work if the VBE wasn't

    all ready
    >open.
    >I am in Win 2000, Excel 2000, with

    VBE>Tools>References>Microsoft VBA
    >Extensibility 5.3 checked.
    >The procedure that worked for me is below:
    >
    >
    >
    >Sub OpenVBEGoToSpecificMacro()
    >
    > Dim lStartLine As Long
    > Application.VBE.MainWindow.Visible = True
    > ThisWorkbook.VBProject.VBComponents

    ("ModuleNameHere").Activate
    >
    > With Application.VBE.ActiveCodePane.CodeModule
    > lStartLine = .ProcStartLine

    ("VBAProcedureNameHere", 0)
    > .CodePane.SetSelection lStartLine, 1,

    lStartLine, 1
    > End With
    >End Sub
    >
    >
    >
    >
    >
    >
    >"Jake Marx" <> wrote in message

    news:<#>...
    >> Hi DataFreakFromUtah,
    >>
    >> I'm not well-versed with the VBE objects, but this

    seems to work:
    >>
    >> Sub OpenVBE()
    >> Dim lStartLine As Long
    >>
    >> ThisWorkbook.VBProject.VBComponents

    ("basModule1").Activate
    >>
    >> With Application.VBE.ActiveCodePane.CodeModule
    >> lStartLine = .ProcStartLine("test2", 0)
    >> .CodePane.SetSelection lStartLine, 1,

    lStartLine, 1
    >> End With
    >> End Sub
    >>
    >>
    >> Just change the "test2" to the name of your procedure.
    >>
    >> --
    >> Regards,
    >>
    >> Jake Marx
    >> MS MVP - Excel
    >> www.longhead.com
    >>
    >> [please keep replies in the newsgroup - email address

    unmonitored]
    >>
    >>
    >> DataFreakFromUtah wrote:
    >> > Hello all,
    >> >
    >> > Win 2000
    >> > Excel 2000
    >> >
    >> > I am wondering if there is a way to programmatically

    open the VBE and
    >> > go to a specific
    >> > procedure or macro in the target module (in this

    example: basModule1).
    >> > I have the code to go
    >> > to the target module, but I can't seem
    >> > to figure out a way for the procedure to open at the

    target procedure
    >> > or macro.
    >> > I spent some time looking at the object model and

    searching the
    >> > microsoft.public.excel.programming newsgroup, no luck.
    >> > Might anyone out there have an answer?
    >> > Thank you in advance for any information you may

    provide.
    >> > Cheers,
    >> > Tom
    >> >
    >> >
    >> > Sub OpenVBE()
    >> > ' Open the Visual Basic Editor Programmatically
    >> >
    >> > Application.VBE.MainWindow.Visible = True
    >> > 'The next line of code goes to a specified module
    >> > ThisWorkbook.VBProject.VBComponents

    ("basModule1").Activate
    >> >
    >> > End Sub

    >.
    >
     
    Peter T, Jul 12, 2004
    #4
  5. DataFreakFromUtah

    Jake Marx Guest

    Peter T wrote:
    > Have you tried, simply:
    >
    > Sub Test()
    > Application.Goto "MyRoutine"
    > End Sub


    Nice catch, Peter! I didn't think of that one.

    --
    Regards,

    Jake Marx
    MS MVP - Excel
    www.longhead.com

    [please keep replies in the newsgroup - email address unmonitored]
     
    Jake Marx, Jul 12, 2004
    #5
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. phil

    VBE(Visual Basic Editor) Keep focus in VBE

    phil, Aug 9, 2005, in forum: Microsoft Excel Programming
    Replies:
    6
    Views:
    453
    keepITcool
    Aug 11, 2005
  2. Guest
    Replies:
    4
    Views:
    191
    Tushar Mehta
    Jan 19, 2006
  3. Replies:
    2
    Views:
    232
  4. Aaron

    Code from Powerpoint VBE not Working in Excel VBE?

    Aaron, Feb 20, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    332
    Jon Peltier
    Feb 20, 2008
  5. K

    How to open correct Module window in VBE by macro

    K, Sep 17, 2008, in forum: Microsoft Excel Programming
    Replies:
    1
    Views:
    163
    NoodNutt
    Sep 23, 2008
Loading...

Share This Page