Listing Word macros

[Nigel_DG]

Hi,

I am working on a project for designers to create automated templates for
authors to work into. I am creating a set of standard macros that our
designers can amend and take into the code window of a template document, and
I would then like to be able to access the macro names to automate the
creation of a custom toolbar containing the macros used in the document.

Is there a way to access macros as a collection (as you do documents,
tables, etc.)? If not, is there any way of getting this information from the
active document easily? I have currently written a routine to access the code
window, strip out the text to a new document, delete all lines that are not
macro declarations and copy them back into the main document to iterate
through with a second macro that populates a new custom toolbar. While this
works, it is not easily extensible and is pretty inflexible, so I am keen to
find a more elegant solution if one exists! I am thinking something along the
lines of ActiveDocument.macros(1).name kind of simplicity...

Many thanks,

Nigel

 

[Jezebel]

This seems strangely ill-conceived.

a) Your macros shouldn't be in the active document at all. They should be in
an add-in.

b) In a development environment such as you describe, your macros should be
controlled and documented. You shouldn't NEED to run other code to work out
what macros are there. You should know already.


For what it's worth, yes, you can access the macros collection directly:
look at the properties of the VBE object. However, security settings may
block your access.

 

[Nigel_DG]

I may need to clarify here, to save any confusion.

My goal is simply this:
I want to run a routine that will automatically populate a new custom
toolbar with a button for each macro I have created, with no intervention
from the user apart from a single click. with the way I have conceived the
project, this process seems to rely on extracting the names of the macros I
want on the toolbar in some way.

Jezebel - you did offer some (possibly) helpful advice, if you could expand
on it for me. I know I can access the code module collection (and therefore
the code window I am interested in) as a property of the VBE object - but can
I access the macros defined within it individually? I think its an
interesting question beyond the project too - how does Word deal with
populating the declarations drop-down at the top of the code window, for
instance?

Thanks!

 

[Jezebel]

Jezebel - you did offer some (possibly) helpful advice, if you could
expand
on it for me. I know I can access the code module collection (and
therefore
the code window I am interested in) as a property of the VBE object - but
can
I access the macros defined within it individually? I think its an
interesting question beyond the project too - how does Word deal with
populating the declarations drop-down at the top of the code window, for
instance?

There's probably a better way than this, but you can do it using the
ProcOfLine() function: you iterate the VBComponents collection, select the
ones that contain the functions you're interested in (ie skipping the forms
and class modules), then iterate the lines of each component to retrieve the
names of the functions that those lines are within. Extract the unique names
from that list, then examine the first line of the procedure in each case --
use ProcStartLine() -- to check if it's a Sub that is not private and takes
no argument (since those are the only ones you can assign to toolbars).

Very cumbersome; but it seems the code designers also assumed you would know
already what procedures you were dealing with. In most development
circumstances, putting EVERY available function onto a toolbar would be
absurd.

Where are you going to get the toolbar captions from?

 

[Nigel_DG]

Thanks - it sounds like the simple answer (to my actual request) here is 'no'
then!

One small point. It may help other users of this forum if you reply to their
actual queries without trying to tell them that they are doing something
'absurd' or 'strangely ill conceived'. The points you make are all valid, but
are all things that I am well aware of. I came to this forum only after
trying a number of ways to get this to work and a lot of searching to make
sure I wouldn't be wasting people's time and I find the tone of your replies
patronising and quite offensive, given that you don't know the background of
what I am doing or the mechanics of how the project is working. I don't want
to be petty and rant on like this, but it really bugs me when people feel
they can be so dismissive on forums like these. I could reply and enumerate
exactly what measures I have in place to overcome all the things you
mentioned that are 'wrong' with what I am asking, but life's too short...

Although having said that, I would like to specifically respond to one point
you make - of course it's foolish to put all macros on one toolbar. One of
the benefits of accessing the macros collection programmatically is that I
can easily reference their names and only put the relevant ones on the
toolbar. The naming convention I have used means that all macros are easily
identified by name so it would be a simple task to weed out only those that
make sense to give toolbar access to.

 

[Jezebel]

Glad you agree that your original idea was silly. In this sort of context
it's simpler to call things as one sees them, rather than trying to second
guess. If you can't be bothered explaining what you're actually trying to
do, then it's rather churlish to complain about the answers you get.

 

[Nigel_DG]

Thank you for your helpful response and I am sorry that I have wasted
people's time on this forum with my question. Apologies, Jezebel, for not
providing the information you needed, I hope it wasn't too inconvenient for
you to have to share your frustration with everyone.

 

[Helmut Weber]

Hi Nigel,

have a look at this one and much more
from German fellow MVP Christian Freßdorf:

http://www.chf-online.de/vba/vbalistmakronamen.htm

Here is the code with annotations in English:

Option Explicit
Sub ListMacros()
Dim oApp As Word.Application
Dim myProject As VBProject
Dim myComponent As VBComponent
Dim strNames As Variant, strDocNames As String
Dim strFile() As String
Dim iCount As Integer
Dim strProc As String
Set oApp = GetObject(, "Word.Application")
' Process all projects
For Each myProject In VBE.VBProjects
strNames = ""
' process only unprotected projects
If myProject.Protection = vbext_pp_none Then
On Error Resume Next
If myProject.VBComponents.Count > 1 Then
strFile() = Split(myProject.FileName, "\")
strNames = strNames & myProject.Name & " (" & _
strFile(UBound(strFile())) & ")" & vbCrLf
On Error GoTo 0
' process all modules
For Each myComponent In myProject.VBComponents
With myComponent
' get module type
If .Type = vbext_ct_StdModule Then
strNames = strNames & vbTab & .Name & vbTab & " (bas)" &
vbCrLf
ElseIf .Type = vbext_ct_ClassModule Then
strNames = strNames & vbTab & .Name & vbTab & " (cls)" &
vbCrLf
ElseIf .Type = vbext_ct_MSForm Then
strNames = strNames & vbTab & .Name & vbTab & " (frm)" &
vbCrLf
ElseIf .Type = vbext_ct_Document Then
strNames = strNames & vbTab & .Name & vbTab & " (doc)" &
vbCrLf
End If
' get declaration
If .CodeModule.CountOfDeclarationLines > 0 Then
For iCount = 1 To .CodeModule.CountOfDeclarationLines
If .CodeModule.Lines(iCount, 1) <> "" Then
strNames = strNames & vbTab & vbTab & "Declaration" &
vbTab & " (" & _
.CodeModule.CountOfDeclarationLines & " Z.)" & vbCrLf
Exit For
End If
Next iCount
End If
' get procedures
strProc = ""
For iCount = 1 To .CodeModule.CountOfLines
If .CodeModule.ProcOfLine(iCount, vbext_pk_Proc) <> strProc Then
strProc = .CodeModule.ProcOfLine(iCount, vbext_pk_Proc)
strNames = strNames & vbTab & vbTab & strProc & vbTab & " ("
& _
.CodeModule.ProcCountLines(strProc, vbext_pk_Proc) & " Z.)"
& vbCrLf
End If
Next iCount
End With
Next myComponent
'MsgBox strNames
strDocNames = strDocNames & strNames & vbCrLf
End If
End If
Next myProject
' write to document
Dim oDoc As Document
Set oDoc = Documents.Add
oDoc.Range.InsertAfter strDocNames
End Sub

Beware of linebreaks by the newsreader,
which you know anyway.

Regarding Jezebel, read regularly here,
try to answer, and you might understand better,
that often to explore the question behind the question
is the way to a solution. That's what Jezebel tried to do, I think.

Greetings from Bavaria, Germany

Helmut Weber, MVP, WordVBA

"red.sys" & chr$(64) & "t-online.de"
Win XP, Office 2003

 

[Jezebel]

Oh very droll. And have you stopped beating your wife?

 

[Nigel_DG]

Wow - that's great, Thanks for taking the time to reply Helmut. I wasn't
expecting such a detailed response, but I'm very pleased you took the time to
let me have one.

Thanks again!

Nigel