Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading the VBA code in a spreadsheet
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading the VBA code in a spreadsheet
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 -- If this helps, please remember to click yes. "Phil Hibbs" wrote: 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. . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading the VBA code in a spreadsheet
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. -- Gary Keramidas Excel 2003 "Paul C" wrote in message ... 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 -- If this helps, please remember to click yes. "Phil Hibbs" wrote: 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. . |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Reading the VBA code in a spreadsheet
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] On Thu, 7 Jan 2010 07:32:18 -0800 (PST), Phil Hibbs wrote: 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Accurately reading the contents of another spreadsheet | Excel Programming | |||
Problem reading value from Excel spreadsheet | Excel Programming | |||
reading data from com port into excel spreadsheet | Excel Programming | |||
Reading from a .xls spreadsheet based on a cell name | Excel Programming | |||
reading cells from a spreadsheet using VB | Excel Programming |