INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Return the text of a VBA function

Return the text of a VBA function

(OP)
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:

CODE --> VBA

Function GetFunctionCode (MyFunction as ???) as string

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

End 

RE: Return the text of a VBA function

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.

RE: Return the text of a VBA function

>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.

RE: Return the text of a VBA function

2
Sure, and it is fairly easy:

CODE

Option Explicit

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

' Need to add reference to Microsoft Visual Basic for Applications Extensibility library
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 

RE: Return the text of a VBA function

(OP)
Thanks strongm!

Needed to make minor change:

CODE -->

With Application.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.

RE: Return the text of a VBA function

(OP)
To make the result prettier:

CODE -->

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 

RE: Return the text of a VBA function

>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?

RE: Return the text of a VBA function

(OP)
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.

RE: Return the text of a VBA function

>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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close