Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

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

Posted on 2011-10-10
11
Medium Priority
?
494 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:p-plater
11 Comments
 
LVL 37

Expert Comment

by:TommySzalapski
ID: 36944967
You'll have to just hard code it for each sub.
0
 

Author Comment

by:p-plater
ID: 36944989
Is there NO way to make it dynamic?
What about reffering to the Module name?
0
 
LVL 9

Expert Comment

by:experts1
ID: 36945069
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
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 10

Expert Comment

by:broro183
ID: 36945272
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
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 36947070
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
 
LVL 10

Expert Comment

by:broro183
ID: 36950811
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36952291
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
 
LVL 10

Expert Comment

by:broro183
ID: 36952621
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36953698
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
 
LVL 10

Expert Comment

by:broro183
ID: 36971732
Thanks Rory, I'll get in touch with her - I'm just reading through her write up now :-)
0
 
LVL 10

Expert Comment

by:broro183
ID: 36971812
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

Featured Post

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
Quickbooks hosting can do wonders to your enterprise but considering the points elaborated in the article which will help you to better analyze the outcomes. So scan your business, its needs and then move to the new world of limitless benefits.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question