Module / Sub as string

[Richard]

Hi

Is it possible to read either the module and/or the sub name and use it as a
string within the code?

R

 

[Harald Staff]

Hi R

Afaik no, sorry. It would make error handling far easier if it was.

Best wishes Harald

 

[Richard]

Ho Hum, is the only polite answer I have

Thanks anyway thought I was going barmy

 

[Richard]

Harald

I got it,

Dim ClientName As String

ClientName = Application.VBE.ActiveCodePane.CodeModule.Name

This enables me to give each module the client name, which in turn allows me
to use the ClientName string as part of the file path. No need to search
through and make code changes for each new client and the sub name can be
whatever I like, which is handy as in some cases I have more than 1 sub for
the same client.

Richard

 

[Gary''s Student]

A very useful tip.

 

[Richard]

can you recommend a book which will help me learn how to use the vba interface.
I am (very) slowly getting my head around the vba concepts, but feel that
knowing how to use the object explorer, debug tools etc would reduce the
amount so questions I have to ask.

I guess this is an issue for lots of (so called) self taught coders.

 

[Gary''s Student]

There are many good books. I use:

Walkenbach's Excel 2003 Power Programming with VBA
Frye, Freeze, Buckingham's Excel2003 Programming
and
Bullen, Bovey, Green's Professional Excel Development

There are also many good on-line resources.

 

[Richard]

thanks for the tips.

I have found a problem with my suggestion, in that you need to make the
module for the sub you are running, the active module, otherwise you get bad
results. If you can cure this, then it becomes a useable suggestion

 

[Harald Staff]

Hi Richard

I've ran code like this

Sub test3()
ClientName = Application.VBE.ActiveCodePane.CodeModule.Name
MsgBox ClientName
End Sub

placed in three different modules. They all returned "Module3", not the
module containing the running code. Am I missing something ?

Best wishes Harald

 

[Richard]

no that is the point I made, if the last module you updated was module 3 that
module seems to retain the active setting. I don't know how to set the
module for the sub you are running, as the active code window, or perhaps
some other method needs to be found