Reading the VBA code in a spreadsheet

[Phil Hibbs]

Is there a way of accessing the code in a module in a spreadsheet? I
can get as far as the VBComponent.CodeModule but can't work out how to
access the actual code.

Phil Hibbs.

 

[Paul C]

Phil,

I think the line you are looking for is this
VBComponent.CodeModule.Lines(X,Y)
X=Start Line
Y=Number of Lines

This will output all code to a single spreadsheet

Sub ShowCode()


Set VBProj = ActiveWorkbook.VBProject
vcompcount = VBProj.VBComponents.Count
For A = 1 To vcompcount
Set VBComp = VBProj.VBComponents(A)
Set CodeMod = VBComp.CodeModule
For B = 1 To VBComp.CodeModule.countoflines
ActiveCell = VBComp.CodeModule.Lines(B, 1)
ActiveCell.Offset(1, 0).Select
Next B
Next A
End Sub

 

[Gary Keramidas]

your code will work with a couple of caveats.

1. none of the variables are dimmed.
2. under macro/security on the trusted publishers tab, "trust access to visual
basic project" needs to be checked.

 

[Chip Pearson]

Once you have a reference to the CodeModule, you read lines with the
Lines method:

S=CodeMod.Lines(100,10)

This puts in S text starting at line 100 for 10 lines.

You also use methods line InsertLine, DeleteLine, and ReplaceLine to
modify the code.

See www.cpearson.com/Excel/VBE.aspx for lots of information and
example cdoe for working with the VBA Editor objects.

Cordially,
Chip Pearson
Microsoft MVP 1998 - 2010
Pearson Software Consulting, LLC
www.cpearson.com
[email on web site]