How can I reference the NAME of a sub procedure within the procedure

Hello
I want to refer to the name of the sub from with in the procedure for error checking.
For Example

ErrMes = "Error - " & Error & " In Sub Procedure " & sub.name??????

Thanks
p-platerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

TommySzalapskiCommented:
You'll have to just hard code it for each sub.
0
p-platerAuthor Commented:
Is there NO way to make it dynamic?
What about reffering to the Module name?
0
experts1Commented:
Declare a public string, and at the beginning of each
procedure you change the text name assigned to the
public string, example below:

Public subname as string

Sub my_sub()
  subname = "my_sub"
 'Your code  or whatever here....
End sub

error subroutine would now be:

ErrMes = "Error - " & Error & " In Sub Procedure " & subname

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

broro183Commented:
hi P-Plater,

I have never tried the sub listed on the below link, but Chip's code is usually fantastic so I recommend having a read of this page: http://www.cpearson.com/excel/InsertProcedureNames.aspx which automates the inclusion of a constant containing the name of the procedure. If you are confident enough, you may even be able to incorporate your error message by modifying Chip's code.

hth,
Rob
0
Rory ArchibaldCommented:
Or download the free MZ Tools addin from here: http://www.mztools.com/v3/mztools3.aspx.

It will make your coding life so much easier!
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
broro183Commented:
I agree Rory :-)

This reminds me... is there a "portable version" of MZ Tools which doesn't need to be installed?

My workplace IT department will not let me install freeware, so my coding life isn't as easy as it could be at work. A few months ago, when I had some coding intensive projects on the go, I actually took my home laptop to the office and swapped files between my machines using a usb stick. There were some questions asked, but, unfortunately people believed and accepted that my approach was more effective than lobbying I.T.!
However, it's not very convenient on a day to day basis, so I'm not doing that at the moment and I know that my coding efficiency is dramatically lower :(

Rob
0
Rory ArchibaldCommented:
Not that I know of, I'm afraid - perhaps you could write one? :)
If you make a donation, would they count it as paid-for?
0
broro183Commented:
That's a bummer, but what I expected. I have thought about writing one - in fact, I have started on a slightly easier challenge by starting to convert the historical zipped version of the Smart Indenter for "excel 5 and 95". It was about two months ago I started to look the code in the addin to see if I could apply the concepts and update the code syntax to work in Excel 2007. However, it's been put on the back burner for the moment...

I wish that a donation would make them consider it as paid-for, but when I asked that question all I got in return was a chuckle from the local IT guy.

Anyway, that's enough thread-hijacking by me...

P-Plater,
Is either Rory's (if you have sufficient permissions for installing MZ Tools - a fantastic product) or my suggestion (Chip's code) more enticing than manually modifying all of your Subs?

Rob
0
Rory ArchibaldCommented:
You may want to have a chat with Lisa at the code cage as she is working on an xlam addin that has some of the MZ tools functionality, I think.
0
broro183Commented:
Thanks Rory, I'll get in touch with her - I'm just reading through her write up now :-)
0
broro183Commented:
For anyone who is interested, the relevant links to Lisa's addin pages are (you may need to complete a free sign-up):
http://www.thecodecage.com/forumz/microsoft-excel-vba-programming/211599-visual-basic-applications-cleanup-project.html
http://www.thecodecage.com/forumz/view.php?pg=vbacleanup

Rob
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.