Update Sheet code using VBA - question for Chip Pearson

[Basilisk96]

Hi,

I am working on a VBA project that will systematically update VBA code
in multiple workbooks based on the code in another workbook. Chip
Pearson's "Programming in the VBA Editor" page at http://www.cpearson.com/excel/vbe.aspx
has been very helpful with this.

But I have come across a problem: the CopyModule function gives
unexpected results when the VBComponent in question is a Sheet module
or the ThisWorkbook module. In VBA lingo, it is of the type
"vbext_ct_Document". Such a module cannot be removed via the Remove
method. Hence, the Remove method in the CopyModule function fails
(without any indication - there is no error check there), and the
source module is imported as a class module with a "1" appended to the
module name. This is most undesirable.

So what is the fix for this situation?
I tried the following simple code in a code module:

Sub foo()
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Set proj = ThisWorkbook.VBProject
Set comp = proj.VBComponents("Sheet2")
With comp.CodeModule
.DeleteLines 1, .CountOfLines
.AddFromFile "D:\mypath\Sheet2.cls"
End With
End Sub

....but the problem here is that the AddFromFile method blindly brings
in the full contents of the .cls file. So, you get the undesirable
meta-fluff like:

VERSION 1.0 CLASS
BEGIN
MultiUse = -1 'True
End

....which is always saved in an exported module file, but is illegal
inside the code pane.

The only other way I can see around this issue is to find, delete and
insert Procedures one by one. It seems feasible, though rather
hackish.

Any other ideas?

Cheers,
-Basilisk96

 

[Basilisk96]

I found that if I pre-process the exported text file by removing those
first four lines, I can use the AddFromFile method with no problems.

How consistent is that metadata across versions of Excel?

Cheers,
-Basilisk96

 

[Chip Pearson]

It has been a long time since I really looked at that page, but it appears
that I intended that CopyModule be used only on standard code modules (Type
= vbext_ct_StdModule) or class modules (Type = vbext_ct_ClassModule), not
Document modules or UserForms. This limitation didn't make it on the the web
page. I'll revise the code on the page to support other types of modules.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Basilisk96]

It has been a long time since I really looked at that page, but it appears
that I intended that CopyModule be used only on standard code modules (Type
= vbext_ct_StdModule) or class modules (Type = vbext_ct_ClassModule), not
Document modules or UserForms. This limitation didn't make it on the the web
page. I'll revise the code on the page to support other types of modules.

Actually, it seems that UserForms work well with the existing code,
because they are programmatically removeable.
Here's a simple example, where there is an existing UserForm1 object
in a project:

Sub foo()
Dim proj As VBIDE.VBProject
Dim comp As VBIDE.VBComponent
Set proj = ThisWorkbook.VBProject
Set comp = proj.VBComponents("UserForm1")
With comp
exportedName = Environ("Temp") & "\" & .Name & ".frm"
.Export exportedName
End With
With proj.VBComponents
.Remove comp
.Import exportedName
End With
Kill exportedName
Kill Left(exportedName, Len(exportedName) - 4) & ".frx"
End Sub

This code runs without any glitches, and cleanly exports, removes, and
re-imports the user form object. Note that you have to also take care
of the FRX blob file at the end.

In other news, I adopted the approach of pre-processing the Sheet file
to remove the metadata, with flawless success. I suppose that to make
it more generic, I should expect a "Version" line, followed by a
"BEGIN" line, then some stuff, then finally a "END" line. After that
point, the rest of the text should be written to another file and
added in.

Cheers,
-Basilisk96