Getting inconsistent results with running code from VBE object

[SBendBuckeye]

I am trying to determine the currently running module in Excel VBA. The code is in a macro file that is hidden in Excel. If I single step through the code and print the following in the immediate window it has a value.

Application.VBE.ActiveCodePane.CodeModule.name

If I do the following in code, it comes back vbNullString:

strName = Application.VBE.ActiveCodePane.CodeModule.name

But it is inconsistent. It does not always come back empty. I believe that I also tried but I don't honestly remember:

ThisWorkBook.Application.VBE.ActiveCodePane.CodeModule.name

Any ideas why this would not come back properly 100% of the time? Thanks in advance for any help or suggestions you can give me!

Have a great day!

 

[SBendBuckeye]

As a followup, the values are Null, NOT empty strings as I reported in my first post. I have tried:

Application.ActiveWorkbook.VBProject.VBE.ActiveCodePane.CodeModule.Name

ThisWorkbook.Application.VBE.ActiveCodePane.CodeModule.Name

In each case, if I step into the Macro or have a break point set, it immediately becomes available. Its almost like the properties haven't been set yet. How can the properties for executing code not be available at run time?
Am I missing something really obvious here?

Thanks!

 

[RobBroekhuis]

The VBE object is merely the Editor itself. The active code pane is the one shown in the code window, not necessarily the one from which code is executing. Why do you need this information? There's probably a workaround.
Rob

 

[SBendBuckeye]

Thanks, Rob.

Here is what I want to do and here is a work-a-round that will work. If you have a better idea, let me know.

I have created a macro control file which essentially calls the first macro in another workbook. I automatically generate the macro code based on a file name and path entered by the user in worksheet1.

Creating the macro requires the user to have the cursor on a valid line of code. The macro name is based on a process name defined by the user (eg GL Month End autogenerates a macro named GL_Month_End).

When the user selects macro GL_Month_End out of the list to run, I want to ensure the cursor is on the correct line so that the proper path and filename is used to run the other workbooks macro.

What I wanted to do was spin through the worksheet used data range matching the macro name to the process name entered by the user. Apparently this will not work.

A better solution and one I should probably have used in the first place would be to create a RangeName in the worksheet when the Macro code is generated. This will allow me to get to the correct row directly so the proper workbook file is opened.

I'm open to any other ideas or suggestions you may have.

Thanks for the input, not only here but in many other threads where I see your name!

Have a great day!

 

[RobBroekhuis]

I'm still fuzzy on your requirements. What "line" does the user need to have their cursor on? A row in the worksheet? What information is in the worksheet? How does it relate to the macro you are running? Is that macro in a different workbook? Is your "nacro control file" a standard workbook or an add-in?
It's hard to come up with the best solution without understanding the true requirements, in addition to your approach so far.
From what I think I understand, it sounds like a workable solution shouldn't be too difficult.
Rob

 

[SBendBuckeye]

Sorry for the lack of clarity. Here is an example.

Macro Control File - Used to minimize macros in Run list

WorkSheet1

Process Excel File Path Start Macro
GL Month End GLMonEnd.xls c:\My Documents ProcessMonEnd
Corp Upload CUpload12.xls h:\Corp\Upload AR_Corp_Upload

Lets say the above 2 lines are 5 and 6 respectively. When the user fills in the required data as above and runs the create macro, it creates a macro named GL_Month_End if the active cell was in line 5 or one named Corp_Upload if it was in line 6.

When the user requests macro GL_Month_End macro be run, it looks at line 5, opens c:\My Documents\GLMonEnd.xls and run its macro ProcessMonEnd.

Since the above data functions as a control file and any of the data could change from time to time, I need to pick up the current settings whenever I run GL_Month_End. At first, I was spinning through the worksheet usedrange collection looking for a match on the process name assigned by the user (eg GL Month End) and the macro name which was derived from it (eg GL_Month_End) but I was having a hard time extracting the macro name from the running code.

Currently, I build a variable in the generated code which contains the actual macro name. If I create a RangeName in the worksheet with consistent naming conventions I could then tie the two together that way.

As an example, I could create a RangeName GL_Month_End when the macro is created. Then I can get its row from that RangeName and get the column number from RangeNames defined for MacroProcess, MacroFile, MacroPath and MacroStart using cells notation (eg cells(RangeName1.Row, RangeName2.Col)).

Thanks again for your interest!

 

[RobBroekhuis]

When the user requests macro GL_Month_End macro be run, it looks at line 5, opens c:\My Documents\GLMonEnd.xls and run its macro ProcessMonEnd.

How does the user request this? Is it a macro fired by a button, a selection from the Macros... dialog, or something else?
Rob

 

[SBendBuckeye]

The user selects Tool, Macro, Macros. Based on the above scenario, she would see 4 things in her macro list:

MacroFileAdd - Generate module code for new macro
MacroFileDelete - Delete module code for existing macro
GL_Month_End - Open file and fire macro based on line 5 data
Corp_Uploda - Open file and fire macro based on line 6 data

Eventually I would like to put the above on a custom macro toolbar or menubar, but I have to get it working consistently first.

Thanks!

 

[RobBroekhuis]

Okay, I get it now. Yes, named ranges would make most sense, although your first approach (finding the right row based on the macro name) should also work, if programmed properly. But what if the user changes the macro name itself in the macro table?
Rob

 

[SBendBuckeye]

That's why I want to eventually put them on a custom menu bar of one kind or another. Then everything is under programmatic control (users call it overkill, I call it robust code). This user is very nervous and will not do anything unless there is documentation to support it so the renaming shouldn't be an issue here.

I still don't know how to get the running macro name while the code is running. I finally just gave up and hardcoded the macro name to a variable in the code generation step but it bugs me to do that. Excel has to know what is currently running, but I sure can't find out where it is tucked away. It doesn't seem to make a difference whether the workbook is hidden or visible.

Thanks again for your help!

 

[RobBroekhuis]

I thought I understood your situation, but maybe not - why do you need the name of the macro that's running? Any self-respecting macro will know its own name, no? I really know of no way to get that information on the fly.
Rob