CODEWITHSUNDEEP
excelvba
Alex
Alex

Reputation: 1642

Print a variable's name

I might be bad at googling as I couldn't find the answer to below question: 

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

While we all know it's easy to use msgbox to print "Hello world", is it possible to print out the variable's name (which in this case, "sthstring") and how?

EDIT: please do not provide with answer such as: 

Dim someotherstring as string  
someotherstring = "sthstring"

as I meant to find a way to print the 'name' of the variable, thanks

Upvotes: 8

Views: 9715

Answers (4)

brettdj
brettdj

Reputation: 55692

You can go a step further that simply accessing the code.

Updating the excellent code from Pearson to

  • show that setting the reference to Microsoft Visual Basic for Applications Extensibility 5.3 isnt a pre-requisite to access the VBIDE
  • actually parse out any Dim XX As String in any codemodule 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

Upvotes: 5

Monty Wild
Monty Wild

Reputation: 4001

This is difficult to do directly, as VBA does not have reflection, i.e. cannot directly reference itself.

However;

Since in a comment you mention that you want to write code that references itself, you can do so by referencing Microsoft Visual Basic for Applications Extensibility 5.3 in the tools/references dialog. This gives you the ability to reference VBA code, and from there you could write code that prints itself out.

See

Upvotes: 3

user2140173
user2140173

Reputation:

After reading comments I think you may find this answer useful.

VBA doesn't support reflection just like @Monty Wild has mentioned already but adding references to Microsoft Visual Basic for Applications Extensibility 5.3 grants you the access the VBE object. You can then iterate object modules in your VBA Project and retrieve the entire code for a module in a String format.

Consider the following (Stick it in a new workbook's Module1)

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

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

You may want to write/use some sort of a Find function to disassemble the String components to retrieve variable names and values but I wouldn't recommend doing it as it's can get quite tricky.

Upvotes: 5

Aaron J Spetner
Aaron J Spetner

Reputation: 2155

That is likely impossible, as variable names are just pointers to data. You can have several variables that all point to the same object (although in VBA I do not believe strings are treated as objects so that would not be possible, with the exception of a ByRef function call).

Upvotes: 1

Related Questions