PC Review


Reply
Thread Tools Rate Thread

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

 
 
DataFreakFromUtah
Guest
Posts: n/a
 
      9th Jul 2004
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
 
Reply With Quote
 
 
 
 
Jake Marx
Guest
Posts: n/a
 
      9th Jul 2004
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

 
Reply With Quote
 
 
 
 
DataFreakFromUtah
Guest
Posts: n/a
 
      12th Jul 2004
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" <(E-Mail Removed)> wrote in message news:<#(E-Mail Removed)>...
> 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

 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      12th Jul 2004
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" <(E-Mail Removed)> wrote in message

news:<#(E-Mail Removed)>...
>> 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

>.
>

 
Reply With Quote
 
Jake Marx
Guest
Posts: n/a
 
      12th Jul 2004
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]
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Code from Powerpoint VBE not Working in Excel VBE? Aaron Microsoft Excel Programming 1 20th Feb 2008 05:55 PM
'Procedure or function <stored procedure name> has too many arguments specified',,,ARGH! Mike P Microsoft ASP .NET 0 19th Jun 2006 02:19 PM
How do you change code in the VBE without opening the target workb =?Utf-8?B?QnJldHQgU21pdGg=?= Microsoft Excel Programming 4 19th Jan 2006 11:10 PM
VBE(Visual Basic Editor) Keep focus in VBE phil Microsoft Excel Programming 6 11th Aug 2005 12:25 AM
Programmatically switch off "Variable declaration reqiured" in VBE? Alan Microsoft Excel Programming 6 15th Dec 2004 11:31 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 09:37 AM.