Print Variable Name

Question

Perhaps I am not good at googling, because I can not find the answer to the question below:

Sub TEST() Dim sthstring as string sthstring = "Hello World" end sub 

Although we all know that it is easy to use msgbox to print "Hello world" , is it possible to print the variable name (in this case, "sthstring" ) and how?

EDIT: please do not provide an answer, for example:

 Dim someotherstring as string someotherstring = "sthstring" 

since I wanted to find a way to print the "name" of the variable, thanks

Answer 1

After reading the comments, I think you may find this answer helpful.

VBA does not support reflection, as @Monty Wild already mentioned, but adding links to Microsoft Visual Basic for Applications Extensibility 5.3 gives you access to the VBE object. You can then iterate over the object modules in the VBA project and extract all the code for the module in String format.

Consider the following (insert it into the new Module1 book)

 Sub Main() Dim code As String code = GetCodeModule("Module1") Debug.Print code End Sub Private Function GetCodeModule(ByVal codeModuleName As String) As String Dim VBProj As VBIDE.VBProject Dim VBComp As VBIDE.VBComponent Dim CodeMod As VBIDE.CodeModule Set VBProj = ThisWorkbook.VBProject Set VBComp = VBProj.VBComponents(codeModuleName) Set CodeMod = VBComp.CodeModule GetCodeModule = CodeMod.Lines(1, CodeMod.CountOfLines) End Function 

Now your code variable stores the exact code that is in your Module1 , you can check this by opening the Immediate Window ctrl + g .

You might want to write / use some Find function to parse the String components to get the names and values ​​of the variables, but I would not recommend doing this, as this can become quite complicated.

Answer 2

You can take another step by simply accessing the code.

Updating excellent code from Pearson to

• show that linking to Microsoft Visual Basic for application extensibility 5.3 is not a prerequisite for accessing VBIDE
• actually parse any Dim XX As String in any code module and report it

sample output

Dim sthstring As String (Module1 at: Line: 2)
Dim strSub As String (Module2 at: Line: 2)
Dim FindWhat As String (Module2 at: Line: 11)
Dim ProcName As String (Module3 at: Line: 9)

code

  Sub GetVariable() Dim strSub As String strSub = "As String" Call SearchCodeModule(strSub) End Sub 

  Function SearchCodeModule(ByVal strSub) Dim VBProj As Object Dim VBComp As Object Dim CodeMod As Object Dim FindWhat As String Dim SL As Long ' start line Dim EL As Long ' end line Dim SC As Long ' start column Dim EC As Long ' end column Dim Found As Boolean 

  Set VBProj = ActiveWorkbook.VBProject For Each VBComp In VBProj.VBComponents Set CodeMod = VBComp.CodeModule With CodeMod SL = 1 EL = .CountOfLines SC = 1 EC = 255 Found = .Find(target:=strSub, StartLine:=SL, StartColumn:=SC, _ EndLine:=EL, EndColumn:=EC, _ wholeword:=False, MatchCase:=False, patternsearch:=False) Do Until Found = False If Left$(Trim(.Lines(SL, 1)), 3) = "Dim" Then Debug.Print Trim(.Lines(SL, 1) & " (" & CStr(VBComp.Name) & " at: Line: " & CStr(SL)) & ")" EL = .CountOfLines SC = EC + 1 EC = 255 Found = .Find(target:=strSub, StartLine:=SL, StartColumn:=SC, _ EndLine:=EL, EndColumn:=EC, _ wholeword:=True, MatchCase:=False, patternsearch:=False) Loop End With Next VBComp End Function 

Answer 3

This is difficult to do directly, since VBA is not reflected, that is, it cannot directly refer to itself.

but

Since you mention in a comment that you want to write code that references itself, you can do this by specifying Microsoft Visual Basic for Applications Extensibility 5.3 in the tool / link dialog box. This gives you the ability to reference VBA code, and from there you can write code that prints itself.

Cm.

• MSFT link 1 and
• MSFT Link 2

Answer 4

This is probably not possible, since variable names simply point to data. You can have several variables that all point to the same object (although in VBA I don't think that strings are treated as objects, so that would be impossible, except by calling the ByRef function).