Advanced VBA - add-in to apply/unapply procedure names as constants?

[mc84excel]

I am trying to create an error log in VBA for capturing details of errors in code. (See: http://www.excelforum.com/excel-prog...-file-txt.html ) However I am running into a problem with capturing the name of the procedure where the error was triggered.

I think I need a .COM add-in that I could run on a VBA project and have it loop through all the procedures in the project and add a single line of code to each - a constant which records the name of that procedure. (The idea being that when the error log prints, it will record the name of the last run constant - thus capturing the procedure name that had the error).

To make it more difficult, I often like to change a procedure name months/years later - so the add-in would need to have the ability to recognise the constant procedure names it has already created and remove them (so I can reapply straight afterwards).

[molotok]

mc84excel,

this routine would add a following line at the beginning of every proc/func in a module or form - replace "*********" with the module/form name.

Please Login or Register  to view this content.

[mc84excel]

this routine would add a following line at the beginning of every proc/func in a module or form - replace "*********" with the module/form name.

Thank you for the assistance molotok. This code looks like what I was seeking. I have a few questions:

1. Where do I copy this code to? (I have never created a .COM module)
2. Would it be possible to have the code loop through project modules as well as the procedures? (I use a lot of modules you see)
3. I need to unapply this code in future if required. Would it be possible to create code that would recognise the lines applied with this code?

[molotok]

mc84excel,

1. You do not need a COM module. Just put this code into a regular Excel/VBA module where you put your other code
2. To go thru all the modules do something like:

Please Login or Register  to view this content.

3. To "unapply" - do code similar to the original answer but use .DeleteLines instead of .InsertLines

Good Luck

[mc84excel]

To go thru all the modules do something like:
Dim vbc As VBIDE.VBComponent
Dim fvb As VBIDE.VBProject

molotok, thank you for the code provided. I am running into problems. I get a 'User-defined type not defined' error on the VBIDEs. (My current version of the code below). What am I doing wrong?

Please Login or Register  to view this content.

[mc84excel]

Don't worry. I have found the solution to VBIDE. (Thanks to Chip Pearson & Bob Phillips in this thread: http://www.excelforum.com/excel-gene...component.html )

(In VBA, go to the Tools menu, choose References, and select "Microsoft Visual Basic For Applications Extensibility Library".)

[molotok]

exactly - what I was going to suggest

[mc84excel]

I can not get the code to work. I receive a 91 run-time error on line 10. Code below:

Please Login or Register  to view this content.

[molotok]

You removed an important part of the code - your fvb object is not set.
I had - With ThisWorkbook.VBProject.VBComponents("********").CodeModule

In your code you should add

set fvb=thisWorkbook.VBProject

[mc84excel]

You removed an important part of the code - your fvb object is not set.

My mistake, sorry. Your solution worked perfectly. Thank you.

[mc84excel]

I am posting my latest version of the code below.

I would like it to do the following:

1. How do I make the code check if the line has already been applied? (Line 120 in both subs. I can't get it to work).
2. How do I make the code to add the lines to procedures in form modules (in addition to the standard modules)?
3. How do I make the code to skip the AddProcNames and RemoveProcNames subs when adding/deleting process names?

Please Login or Register  to view this content.

[romperstomper]

1. You need to use ProcOfLine again to read each line of code and check if it is the line of interest. Currently you are comparing a line number to the text you are interested in. I would also suggest you iterate through all the code lines rather than assuming it will always be two lines after the procedure header - especially given the apparently random coding method you use.
2. You need to allow for the component type to be vbext_ct_ClassModule or vbext_ct_MSForm or vbext_ct_Document and not just vbext_ct_StdModule.
3. Don't understand the question.
4. I don't know why you would change procedure names months or years later but a simple find/replace will be a lot simpler.

[molotok]

Re romperstompers answer #2 - you do not need to use vbext_ct_MSForm since what you are really looking for is code module behind the form and it's type is vbext_ct_ClassModule. I also think vbext_ct_Document has no code so it is only vbext_ct_ClassModule that you need to add

[romperstomper]

I believe you are incorrect on both counts. ThisWorkbook and Sheet modules are type vbext_ct_Document and userform modules are type vbext_ct_MSForm.

[molotok]

RS - just checked - you are right

[romperstomper]

Yes, I know

[mc84excel]

4. I don't know why you would change procedure names months or years later

I can not decide on a consistent naming format for modules & subs. Every now & then, I find a new format and I adopt it. Then I go back and rename my sub & module names to match (only on active projects but even still). It is sad. (Think of it as a quest for continual improvement with clean code & consistent logical naming formats as the ideal!)

a simple find/replace will be a lot simpler.

Oh I quite agree. But I am lazy. I know that once I have this working, I wont bother to update the 2nd line (i.e. the const process name) whenever I update the procedure name. And it is important that the const name always reflects the current procedure name (because of a VBA Error Log project I am trying to create - I have this a thread on this forum btw). So if I can automate it all...

[mc84excel]

3. How do I make the code to skip the AddProcNames and RemoveProcNames subs when adding/deleting process names?

3. Don't understand the question.

I will give a visual example:

First few lines of AddProcNames before I run the AddProcNames sub.

Please Login or Register  to view this content.

First few lines of AddProcNames afterI run the AddProcNames sub.

Please Login or Register  to view this content.

I don't want the add/remove code to apply it to the add/remove proc names procedures! (Because I can't run the code again until I remove the second line - "Duplicate declaration in current scope")

I have an idea: Would it be possible to just skip the module that contains these subs?
Something like: If vbc.module.name = ActiveModule.name then next module in loop??

[romperstomper]

I would suggest that you put the update code (if you really insist on it) in a separate workbook - much safer.

However, regarding laziness, that's precisely why I suggested Find/Replace. It will update the proc name as well as the constant value and any instances where the proc is called, all in one go. Although perhaps you don't have routines calling other routines and just use one giant routine each time.

[mc84excel]

I would suggest that you put the update code (if you really insist on it) in a separate workbook - much safer.

Agree with safety. However then I would have to add a file picker to select the XLSM that I want to update and I protect most of my VBA projects... So probably more convenient to import a single .BAS file containing the subs to apply constant names to all procedures in project, no?

However, regarding laziness, that's precisely why I suggested Find/Replace. It will update the proc name as well as the constant value and any instances where the proc is called, all in one go. Although perhaps you don't have routines calling other routines and just use one giant routine each time.

It's not that I don't use Find/Replace to alter procedure names; I do. (And yes, I favour the method of having a 'master' macro calling other routines - makes code more portable & cleaner).

None of my projects would have these constants in the first place. So I need the sub to add constants to each procedure in each module of the current workbooks VBA project. And for convenience, I would rather have a 'remove all constant procedure' names as a precaution. (Just in case one day I forgot to do a Find/Replace when changing a procedure name).

[romperstomper]

No, I still wouldn't recommend that - just have one button to choose the file and one that runs on the active project.

If you don't do a Find/Replace you will probably have bigger problems than an incorrect constant.

[mc84excel]

just have one button to choose the file and one that runs on the active project.

Sounds out of my comfort zone. (Remember I have no experience with using VBA to edit VBA). Would you be able to provide code for this?

If you don't do a Find/Replace you will probably have bigger problems than an incorrect constant.

You win. But can I just have the ability anyway?

[mc84excel]

This thread has veered slightly off topic so I will upload my current workbook and my current questions:

1. How do I make the code check if the line has already been applied? (Line 120 in both subs. I can't get it to work).
2. How do I make the code to skip the module which contains the AddProcNames and RemoveProcNames subs when adding/deleting process names?

[mc84excel]

BUMP?

Can anybody help on this?

[shg]

If I were going to automate this at all (and I wouldn't), I'd copy the code to Word and do it there.

You seem intent on doing this, but you might be interested in doing a straw poll among people that have reasonable experience with VBA as to whether they think it's a productive exercise.

[shg]

In response to your PM, yes, I do.

[JosephP]

probably not foolproof but should get you started anyway

Please Login or Register  to view this content.

[mc84excel]

probably not foolproof but should get you started anyway

Yee ha! That was more than I was hoping for! +1

I will delve into the code sometime later this week and update this thread with the final result.

[JosephP]

ftr this should not be taken to mean I have changed my opinion in any way ;-)

[mc84excel]

ftr this should not be taken to mean I have changed my opinion in any way ;-)

Understood and Thank you for taking the trouble to provide code.