Using VBA to Write VBA code

[SB]

I have John Walkenbach's book "Excel 2002 Power Programming with VBA"
and was using the section entitled Using VBA to Write VBA code (pg
797). Unfortunately the code I have entered is not working and I
think it is because of the fact that I have excel 2000 (but I'm not
sure). The line of code which is causing the error is the first one in
the following section

With ActiveWorkbook.VBProject. _
VBcomponents(shtNewNotesSheet.Name).CodeModule
nextline = .CountOfLines + 1
.InsertLines nextline, Code
End With

(where nextline is a numeric variable (dim as double) and where Code
is a string variable which contains the VBA code I wish to insert into
the worksheet)

Is the "VBComponents()" element a new property of the VBProject object
(ie in excel 2002 but not excel 2000)??

If so how can I use VBA to write VBA in excel 2000?

Thanks in advance

 

[Chip Pearson]

What error are you getting? The code you are using should work
in all versions of Excel since Excel97. NextLine should be
defined as a Long not a Double.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Stuart Bisset]

Chip

Thx for quick reply. I'm getting a "subscript out of range" error.
Since posting my first message I typed in the code on pg798 of the book
in a new workbook and it worked fine. It just doesn't work on this
workbook. The error relates to the "With Activeworkbook ....
CodeModule" line.

Your assistance is greatly appreciated.

 

[Chip Pearson]

Stuart,

Change

VBcomponents(shtNewNotesSheet.Name).CodeModule

to
VBcomponents(shtNewNotesSheet.CodeName).CodeModule



--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Stuart Bisset]

Thx Chip - That now works

Much Appreciated.

 

[Stuart Bisset]

Chip,

Hope you're still out there ... strangely it is no longer working. Do
you have any other suggestions?

Kind Regards

 

[Chip Pearson]

Stuart,

Rather than simply stating that it is no longer working, it would
be helpful if you would indicate the exact error message and the
line of code that is causing the error.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com

 

[Stuart Bisset]

Having tested this a little it would appear that the problem occurs (or
not) depending on whether the VB project is password protected, saved
and closed (or not).

If the user is using the workbook the VBE will not be open and the VB
project will be closed and protected. I assume that this is the reason
the procedure can not add additional code to the project. Is there a
way round this??

Cheers

 

[SB]

Chip / Anyone

The problem at the beginning of this thread was a "subscript out of
range error" on the following line of code:

With ActiveWorkbook.VBProject. _
VBComponents(shtNewNotesSheet.Name).CodeModule

Chip suggested changing .name to .codename which seemed to work for a
short while. On testing I got other error messages which was to do
with the VBproject being protected, and hence no code could be added
to it. I have now resolved that problem using some SendKeys code
obtained from the newsgroup. HOWEVER I am now getting the same error
noted above whether I use .name or .codename.

Can anyone help me please?

 

[Frank]

The error reported initially was one of the subscript being out of
range. This one occurs if the name of the Excel sheet has been
changed in Excel at the sheet tab and therefore the name in the sheet
tab is not the same as the name displayed in the VBE list of the
workbook sheets.

The sheetnames in the VBE project sheet list will show the name given
by the user to the sheet tab - which really is only a change in the
caption! - and in brackets the actual name of the sheet (unchanged
when the name in the tab is changed).

So, either address the actual sheet name in your code, or change the
actual sheet name in the VBE to match the name you gave the sheet on
the tab.

This should resolve the subscript out of range problem for you.

 

[Frank Duffy]

Stuart,

I've been wrestling with this exact same problem. What I
found is that the code from the book always write the code
routine to Sheet1. If you have a multi-sheet workbook,
this is not what you want. What worked for me is the
following line of code:

With ThisWorkbook.VBProject. _
VBComponents(NewSheet.CodeName).CodeModule
NextLine = .CountOfLines + 1
.InsertLines NextLine, Code
End With

It seems that .CodeName is the correct reference.

--Frank