Extracting procedure code from code component.

[HighAndWilder]

Hi All

I'm trying to extract the code for specific procedures from a code component.

I can produce a list of components and procedures but I cannot find a way to just extract the code for a given procedure.

I can also extract the entire code for each component.

Any ideas anyone apart from looping through the lines of the component code.

Thanks

 

[Joe4]

That is a very general question, and we cannot really give you a detailed answer without having more details from you.
It is really key for us to see what your data looks like, and what your expected results are.
So it would be extremely helpful for us to see a small sample of your data and expected results.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[HighAndWilder]

For example: I have a code module called 'modSomeCode' and three procedures in it.

I would like to be able to pass the name of the module and the procedure name to a function, e.g. fncGetCode('modSomeCode','subProcedureTwo') to return an array of the lines of code that make up this function.

arrCode(1) ='Public Sub subProcedureTwo()'
arrCode(2) =' ' Some code here.'
arrCode(3) ='End Sub'

Option Explicit

Public Sub subProcedureOne()
    ' Some code here.
End Sub

Public Sub subProcedureTwo()
    ' Some code here.
End Sub

Public Sub subProcedureThree()
    ' Some code here.
End Sub

 

[Joe4]

OK, that is not what I thought you were trying to do at all.
I do not know how to extract the VBA code contained in a VBA procedure with VBA code.
(Not saying that it cannot be done, I just don't know how, though in my almost 20 years on this site, I don't know that I have ever seen that done).

Just curious, what is the purpose of doing this?
Its a shot in the dark, but if we have an understanding of what you are ultimately hoping to accomplish by doing this, we may be able to offer alternative solutions.

 

[HighAndWilder]

I can do it by interrogating each line in turn looking for the name of the procedure in the line and checking that it is a procedure declaration line
but I just wondered if there was a better way.

 

[RoryA]

Everything you need to know is here: Programming In The VBA Editor

You'll want ProcStartLine and ProcCountLines particularly.

 

[HighAndWilder]

Thanks RoryA

From that site and www.bettersolutions.com I've managed to get all of the information that I need.