[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Caller Function Name - Excel VBA

Posted on 2012-08-17
5
Medium Priority
?
1,209 Views
Last Modified: 2012-08-17
Hello All,

Is there a way to detect the caller function’s name and show it – for example if myFunc2 calls another function [myFunc3], I can also see a message box like :
Executing …myFunc3, this function was originally called by myFunc2


thank you all
Respect
R
callerName.xlsm
0
Comment
Question by:Rayne
  • 2
  • 2
5 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 500 total points
ID: 38305768
0
 
LVL 13

Expert Comment

by:Ryan
ID: 38305791
I briefly looked at the link ssaqibh posted, and it does look like it may do what you're asking, although it was kind of hard to follow.

A simple solution, which is exactly how function calls work is to store them on a stack (LIFO -Last in First Out).  Opposite of a queue.

If you have a global stack object, and every function PUSHES its name onto the stack when it starts, and POPS itself off the stack when it exits, then at any point you'll have the "Stack Trace" of your function calls.

Maybe there's a way to access the real stack trace directly, but this general idea is what is happening.
0
 

Author Comment

by:Rayne
ID: 38305976
Thanks everyone, how do I get Ssaqibh's code to display a message?

Option Explicit
Public stLastProc As String
Public boExecutionDebug As Boolean
Public lnStartLine As Long

Sub InitiateCodeExecutionAnalysis()
  stLastProc = Empty
  boExecutionDebug = True
End Sub
Sub MySubRoutine()
  Application.VBE.ActiveCodePane.GetSelection lnStartLine, 1, 1, 1
  If boExecutionDebug = True Then ExecutionDebug lnStartLine, Application.VBE.ActiveCodePane.CodeModule.Name
End Sub
Sub MySubRoutine2()
  Application.VBE.ActiveCodePane.GetSelection lnStartLine, 1, 1, 1
  If boExecutionDebug = True Then ExecutionDebug lnStartLine, Application.VBE.ActiveCodePane.CodeModule.Name
End Sub
Sub ExecutionDebug(lnLine As Long, stModuleName As String)
  Dim stActiveSubName As String
  stActiveSubName = Application.VBE.ActiveVBProject.VBComponents(stModuleName).CodeModule.ProcOfLine(lnLine, vbext_pk_Proc)
  If stLastProc <> Empty Then
    MsgBox "Procedure: " & stLastProc & ""
  End If
  stLastProc = stActiveSubName
End Sub



Sub Button1_Click()
Call myFunc
End Sub


Sub myFunc()

Call myfunc2
'show myFunc (caller function's name...)

End Sub

Sub myfunc2()

MsgBox "Hello ALL, please take a seat"
Call myfunc3
'show myFunc2 (caller function's name...)

End Sub

Sub myfunc3()

MsgBox "Hello All, please take a cup of coffee and relax"


End Sub
0
 

Author Comment

by:Rayne
ID: 38305979
The code seems a hard thing to follow
0
 
LVL 13

Accepted Solution

by:
Ryan earned 500 total points
ID: 38306236
Here's code to implement a stack.  I tried to understand how that other code works, sorry.

http://www.tek-tips.com/viewthread.cfm?qid=1468970
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

Ever wonder what it's like to get hit by ransomware? "Tom" gives you all the dirty details first-hand – and conveys the hard lessons his company learned in the aftermath.
Lost Word File? Eagerly, need it back? Read ahead; this File Recovery guide is for you.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

831 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