Return the text of a VBA function 2

[mintjulep]

How can the "code" of a function be returned as a string?

I'm sure it's possible, but I'm not at all familiar with this aspect of the environment object model.

Something like:

Function GetFunctionCode (MyFunction as ???) as string

GetFunctionCode = MyFunction.{the complete text of the function}

End

 

[Andrzejek]

I am sure somebody smarter than me will answer your question, but my question is: why would you want it?
Let's say you do get back the 'code' from the function as some String. What do you want to do with it?

Or is it just the case of - I want to do it just for the sake of it?

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[HughLerwill]

>why would you want it?
Maybe to pop up a message saying "Error in MyFunction" in an error trapping routine; but I think there is no way other than hardcoding it.

 

[strongm]

Sure, and it is fairly easy:

[blue]Option Explicit

Public Sub spoon()
   MsgBox GetFunctionCode("spoon")
End Sub

[green]' Need to add reference to Microsoft Visual Basic for Applications Extensibility library[/green]
Public Function GetFunctionCode(MyFunction As String) As String

    With VBE.ActiveVBProject.VBComponents("Module1").CodeModule
        GetFunctionCode = .Lines(.ProcStartLine(MyFunction, vbext_pk_Proc), .ProcCountLines(MyFunction, vbext_pk_Proc))
    End With

End Function[/blue]

 

[mintjulep]

Thanks strongm!

Needed to make minor change:

With [b]Application[/b][COLOR=#4E9A06][/color].VBE.ActiveVBProject.VBComponents("Module1").CodeModule

As to why I want to do this:

I want to be able to document the code of UDF's in a text box in the worksheet.

 

[mintjulep]

To make the result prettier:

Public Function GetFunctionCodeLines(MyFunction As String) As String
Dim StartLine As Integer
Dim EndLine As Integer
Dim Count As Integer

    With Application.VBE.ActiveVBProject.VBComponents("Module1").CodeModule
        StartLine = .ProcStartLine(MyFunction, vbext_pk_Proc)
        EndLine = .ProcCountLines(MyFunction, vbext_pk_Proc)
        
        For Count = StartLine To (StartLine + EndLine - 1)
            GetFunctionCodeLines = GetFunctionCodeLines & .Lines(Count, 1) & vbCrLf
            
        Next Count
        
    End With

End Function

 

[strongm]

>Need to make a minor change

That'll be because you missed the comment in my code: ' Need to add reference to Microsoft Visual Basic for Applications Extensibility library


>To make the result prettier

Not sure I understand this. In both Excel and Word all the (simple) formatting that you'd see in the Code Pane (i.e. tabs, vbcrlf) is retained (although a msgbox isn't really a good way to show the result, particularly if there are long lines involved, but it was just there for the sake of the example). What is your VBA being hosted in?

 

[mintjulep]

Thanks,

I did add the reference to the library. Would I need to close/open the application for this to "take"?

I see now that CR and LF is retained if the string is zapped to a textbox or msgbox but tabs are lost.

Working in excel.

 

[strongm]

>Would I need to close/open the application for this to "take"?

Shouldn't. Most odd.

>but tabs are lost

Well, no, not really. Or, at least, not by the function. The VB IDE converts tabs to (4) spaces (although it maintains some metadata about where the actual tab used to be). If you are using a proportional font in msgbox (basically unavoidable) and textbox then it might well give the appearance that the tabs are being stripped.