Results 1 to 12 of 12

Thread: Module Name

  1. #1
    kelliel
    Guest

    Module Name

    I have been working through the help files, but to no avail.

    How, if it is possible, would I find the name of the "macro" (procedure or function) that is currently running, output as a string.

    In other words, if in the middle of a macro, I were to put:

    msgbox Name of the Procedure/function currently running

    I would want the actual name of that macro in the message box.

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Module Name

    Lawrence

    If it is the Subroutine name you want as opposed the the module name, then the following should help : <pre>Sub TestProcName()
    Dim ThisProc As String
    Dim xStartLine As Long, XStartColumn As Long, xEndLine As Long, xEndColumn As Long
    With Application.VBE.ActiveCodePane
    .GetSelection xStartLine, XStartColumn, xEndLine, xEndColumn
    With .CodeModule
    ThisProc = .ProcOfLine(xStartLine, vbext_pk_Proc)
    MsgBox (ThisProc)
    End With
    End With
    End Sub</pre>


    This code actually worked in a function as well for me. I have not tested it extensively so I will not guarantee complete success, but hopefully it is enough to get you started.

    Andrew

  3. #3
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    Andrew,

    I get "vbext_pk_Proc" not defined. Do I need a reference to an external dll or something?

    This looks to be a part of the VBE windo. Do you get the tight answer even if the VBE window is not open?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Module Name

    Geoff,

    Sorry, you do need to have MS Visual Basic for Applications Extensibility 5.3 set up in your references.

    It worked fine for me with the VBE window closed.

    Andrew

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    (snip!)

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Module Name

    (snip!) ??

    As a matter of interest vbext_pk_Proc seems to evaluate to 0, at least in Subs and Functions, so it can be replaced by 0 and the ref to VBA Extensibility may not be required at all.

    Andrew

  7. #7
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    I think by "snip" Chris meant "I want to take a copy of that".

    I like it. It means that I can generalise error handling. I don't have to set the name of each procedure within the error handler.

    I'd just make it a little more succinct, as it's code I might like to use frequently. So in light of your last suggestion, how about:
    <pre>Dim lngStartLine As Long, lngStartColumn As Long, _
    lngEndLine As Long, lngEndColumn As Long

    Sub TestProcName()
    With Application.VBE.ActiveCodePane
    .GetSelection lngStartLine, lngStartColumn, lngEndLine, lngEndColumn
    MsgBox .CodeModule.ProcOfLine(lngStartLine, 0)
    End With
    End Sub
    </pre>


    The dim is outside of the proc to make it global, so I don't have to define separately for each routine.

    Now as an error handler:

    <pre>Dim lngStartLine As Long, lngStartColumn As Long, _
    lngEndLine As Long, lngEndColumn As Long
    Private Sub UserForm_Initialize()
    On Error GoTo HandleErr

    dim i as Integer
    i = i / 0

    Exit Sub

    HandleErr:
    With Application.VBE.ActiveCodePane
    .GetSelection lngStartLine, lngStartCol, lngEndLine, lngEndCol
    MsgBox "Error " & Err.Number & ": " & Err.Description, _
    vbCritical, Me.Name & "." & .CodeModule.ProcOfLine(lngStartLine, 0)
    End With
    End
    End Sub


    </pre>



    Now, can this work in VB? There's no Application object in VB, but can I get access to the VBE object in another way?
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2000
    Location
    Queanbeyan, New South Wales, Australia
    Posts
    3,730
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    Aaaaargh!

    Now, having just posted that, after having tested OK- I close down Word, open it up, and run the code- and instead of a nice error message I get "Automation Error". The code is running from an Add-in (initiated from a toolbar button), and runs OK if I open up the add-in, open up the code window in VBE, and then close VBE.

    So I'm back to the old methods. I think.
    Subway Belconnen- home of the Signboard to make you smile. Get (almost) daily updates- follow SubwayBelconnen on Twitter.

  9. #9
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    > (snip!) ??



    It's the sound scissors make late at night when they are busy cutting a piece of code prior to its being pasted, er, elsewhere.

  10. #10
    kelliel
    Guest

    Re: Module Name

    I, too, found it didn't work under "real" conditions. I think in order for it to work, if I remember correctly, is for the IDE to be open.

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Module Name

    >.GetSelection lngStartLine, lngStartColumn, lngEndLine, lngEndColumn

    Yes. The GetSelection suggests that the module is open for editing.


    We need a MUCH better solution than that. Now, where do I have it ...?

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Module Name

    If this is the case, add the line
    <pre> Application.VBE.MainWindow.Visible = True</pre>

    and see if it works. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •