Link to home
Start Free TrialLog in
Avatar of Ron Kidd
Ron KiddFlag for Australia

asked on

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
Avatar of TommySzalapski
TommySzalapski
Flag of United States of America image

You'll have to just hard code it for each sub.
Avatar of Ron Kidd

ASKER

Is there NO way to make it dynamic?
What about reffering to the Module name?
Avatar of experts1
experts1

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

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
ASKER CERTIFIED SOLUTION
Avatar of Rory Archibald
Rory Archibald
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
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?
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
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.
Thanks Rory, I'll get in touch with her - I'm just reading through her write up now :-)
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