'In Excel-VBA, is there any way to get the process name 'I am running in? For example, in Excel VBA I am 'executing a sub mysub(). Within this sub I want 'to cache the sub name "mysub" in to a variable 'for certain use later. How it can be achieved?
Sub mysub() getmysubname = howtoget 'this is my variable to catch the sub name Debug.Print getmysubname 'sub name is printed in debug window End Sub 'Kindly provide the method to do this
The answer is no. There is no generic way, but to use a variable that's named like the sub you're in.
public CurrentSub as string sub mysub() CurrentSub ="mysub" ' do as you wish after end sub 
There is a way to get the VB Project Module your Sub is in, if you use a variable (or constant) to save your Sub name.
See the code below:
Option Explicit Sub mySub() Const PROC_NAME = "mySub" Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Dim i As Long Dim ModuleName As String Set VBProj = ActiveWorkbook.VBProject ' loop through all modules, worksheets and other objects in VB Project For Each VBComp In VBProj.VBComponents Set CodeMod = VBComp.CodeModule ' loop through all code line inside current module For i = 1 To CodeMod.CountOfLines If Len(CodeMod.Lines(i, 1)) > 0 Then ' if the name of current sub is found within the current code line If InStr(CodeMod.Lines(i, 1), PROC_NAME) > 0 Then ModuleName = CodeMod.Name '<-- get the current Module name MsgBox "Sub " & PROC_NAME & " found in " & ModuleName & " module" Exit Sub End If End If Next i Next VBComp End Sub In order to access the VB Project Module, you need to follow the 2 steps below:
Step 1: Add "Trust access to the VBA project object model" , go to Developer >> Macro Security >> then add a V to the Trust access to the VBA project object model.
Step 2: Add Reference to your VB project, add "Microsoft Visual Basic for Applications Extensibility 5.3"
That's it, you are ready to give it a go !
"mySub", not "Module1" or whatever), and (b) it will (if I read it correctly) return the first occurrence of the PROC_NAME which might be in a completely different module (e.g. where the subroutine is being called from).
Function and Sub is placed.Edit/Find or, more likely, View/Definition (because usually when you are looking for where a Sub is placed it is because you have come across a mention of it in the code and you want to go and see what it does). I can't think of much use of knowing the code module at run-time.