Neat capability of Excel VBA, is Access VBA able to obtain dynamic current location..

[mdlueck]

I found a neat capability in Excel VBA:

Private Sub btnRollCosts_Click()
On Error GoTo Err_btnRollCosts_Click

  Call ObjCostedBOM.WalkBOMTree("COST")

Exit_btnRollCosts_Click:
  Exit Sub

Err_btnRollCosts_Click:
  Call errorhandler_MsgBox([B][COLOR=Blue]"Sheet: " & ActiveSheet.Name & "[/COLOR][/B], Function: btnRollCosts_Click()")
  Resume Exit_btnRollCosts_Click

End Sub

Rather than rely on manually adjusting the MsgBox calls which include the Sheet name statically, I was able to dynamigcally look up that value from ActiveSheet.Name.

Now, in Excel VBA, the Sheets show up in the Project tree along with Classes and Modules. (Perhaps Excel sheets are roughly equivalent to Access Forms?)

So is there ActiveForm, ActiveClass, ActiveModule equiv also available from which I could look up the name of those objects?

ActiveForm seems to be hidden at Screen.ActiveForm.Name... (douh!!! Me.Name would have worked for Forms!!!!! :banghead so perhaps I am onto something... Anyone else with input? TIA!

 

[boblarson]

VBA is quite similar amongst all Office apps. You can expect to find a lot of overlap which includes getting the names of objects.

As for the ActiveForm one, Excel doesn't use forms like Access uses forms, so they are handled a little differently.

 

[mdlueck]

Me.Name

Actually, this looks more precisely like what I was after.

Me.Module.Name

Which produces: Form_projects

Me.Name was the instance name only, not the Form Class which the instance was of. (so short the "Form_" bit).

So Bob, "Sheets = Forms" I see that now. Anyway to interrogate the execution location within VBA Classes and Modules? In other words, a dynamic way to refer to the current Class / Module / Function / Subroutine / etc...?

 

[mdlueck]

So Bob, "Sheets = Forms" I see that now. Anyway to interrogate the execution location within VBA Classes and Modules? In other words, a dynamic way to refer to the current Class / Module / Function / Subroutine / etc...?

So, prior to me going through this application swapping static form names for Me.Module.Name, was there a way to also determine the Class / Module / Function / Subroutine name with similar calls?

 

[mdlueck]

OK, back on this topic... a bit more progress.

In an Access VBA class, I am able to dynamically look up the name of the class via the following code:

Call errorhandler_MsgBox("Class: " & [B]TypeName(Me)[/B] & ", Subroutine: Clear()")

Now, I ask again, is it possible to look up the current Function / Subroutine name in the class somehow?

 

[mdlueck]

So, I found that the TypeName(Me) syntax to obtain the module name works universally across Forms / Reports / VBA Classes, so I have moved to using that.

1) Is there anyway to dynamically obtain the current Function / Subroutine / etc... name of where the code is currently executing?

2) Is there anyway to dynamically look up the name of the Module where the code is currently executing? Referring to Me in Module code generates a compile error.

 

[mdlueck]

2) Is there anyway to dynamically look up the name of the Module where the code is currently executing? Referring to Me in Module code generates a compile error.

This appears to be the solution for this one:

Debug.Print "Application.VBE.ActiveCodePane.CodeModule.Name=" & Application.VBE.ActiveCodePane.CodeModule.Name

Application.VBE.ActiveCodePane.CodeModule.Name=[B]modshared_uiutils[/B]

 

[mdlueck]

This appears to be the solution for this one:

Debug.Print "Application.VBE.ActiveCodePane.CodeModule.Name=" & Application.VBE.ActiveCodePane.CodeModule.Name

Application.VBE.ActiveCodePane.CodeModule.Name=[B]modshared_uiutils[/B]

No.... that only outputs the file which is actively open in the VBE window. If the window is closed, that LOC raises an error! Not a solution in that case!

Both points are thus still open. Suggestions please.

 

[DJkarl]

http://www.everythingaccess.com/products-services.htm

They have product that seems to do what you want which means it can be done. However I don't know if it can be done purely through VBA. This type of information is usually retrieved from the call stack which can be done be reading CPU registers, however that requires some type of ASM calls which are difficult at best to do in VBA.

 

[mdlueck]

Thank you for the link, DJkarl. It looks like since that requires insallation of a DLL on workstations, that might not be an option considering at the present organization I do not report through IT, thus no means to deploy out / register a custom DLL. As-is, I have come up with ways to deploy this application to workstations at the tightest user permissions (Restricted User) and have no security warnings popup.

 

[jdraw]

Michael,
I don't know how relevant this site is to what you are doing, but you may get some ideas.

http://www.fmsinc.com/tpapers/vbacode/Debug.asp