Importing upgraded VBA code 9

[krinid]

I'm creating a system to download upgrades to Excel VBA code without having to resend the entire file. Anyone know a good way to delete existing modules and add new modules via VBA code?

I'm considering putting all code in worksheet objects instead of modules, and then just copying/deleting the worksheets from the update server as necessary. I've got the framework for this solution prepared, but this is kind of a roundabout solution; I'd rather copy the actual modules if possible.

 

[Zathras]

If you play around with this, you should get an idea or two:
[blue]

Option Explicit

Sub ReplaceModule1()
  With Workbooks("1.xls").VBProject.VBComponents("Module1").CodeModule
    .DeleteLines 1, .CountOfLines
    .AddFromFile ("z.bas")
  End With
End Sub

[/color]

 

[krinid]

Zathras,
Perfect. Got it working, thanks a bunch!

 

[FaneDuru]

You may delete the existing module and replace it with the new one from the file having the next code:

Sub Copy_Module(X As String)
 On Error Resume Next [COLOR=green]'to work also without "X"[/color green]
 ActiveWorkbook.VBProject.VBComponents.Remove _
             ActiveWorkbook.VBProject.VBComponents.Item(X)
 On Error GoTo 0
 ThisWorkbook.VBProject.VBComponents(Staf).Export "X.bas"
 ActiveWorkbook.VBProject.VBComponents.Import ("X.bas")
 Kill ("X.bas") 
End Sub

Sub Replace_Module()
 Call Copy_Module("Module1")
End Sub

I hope this helps...

 

[krinid]

FaneDuru, Brilliant! Thanks for the slick code.
Btw, offhanded question, what does "Goto 0" do? Similar to "Resume 0" ?

 

[combo]

You can also directly copy contents from one module to another:

[tt]Sub TransferModuleContents()
Dim CodeM1 As CodeModule, CodeM2 As CodeModule
Set CodeM1 = ThisWorkbook.VBProject.VBComponents("Module1&quot.CodeModule
Set CodeM2 = Workbooks("Project.xls&quot.VBProject.VBComponents("Module1&quot.CodeModule
With CodeM2
.DeleteLines 1, .CountOfLines
.InsertLines 1, CodeM1.Lines(1, CodeM1.CountOfLines)
End With
End Sub[/tt]

Note that you can have problems with some AV software and, when working in office XP, you have to have permission to change VB project.

combo

 

[krinid]

combo,
I don't seem to be able to use the "CodeModule" type--is there a library I need to include? Works fine using "Object" instead, but it would be nice to have early binding.

 

[combo]

A reference to "Microsoft Visual Basic for Applications Extensibility 5.3" ("VBIDE" in object browser) is necessary. This is also a tool to manage VB editor by code.

combo

 

[krinid]

Combo, That did it! Thanks.

 

[rasage]

I have a problem with using this type of code. Everytime I insert the code to update the "ThisWorkbook" object module, and save it, it seems to delete all my code modules! I almost had a heart attack, but luckily, as always I backed up the modules of code. However, no matter what I do to modify the code, my code keeps getting erased. Please help me. Take a look at my code...

The code works, but when I save, close, and open Excel to view the code, all of the code in my module1 and module2 is gone. I had originally set it up with the "ThisWorkbook" object.

Sub write_code()

Dim code_obj As Object
Dim linecount As Long, codeline As Long

'Set object reference
Set code_obj = ActiveWorkbook.VBProject.VBComponents("module2&quot.CodeModule

'count lines in module
'linecount = code_obj.countoflines
'Set codeline varible to first line
codeline = code_obj.countoflines + 1

'write sub routine in 'code_obj' module
With code_obj
.insertlines codeline, "Private Sub Workbook_Open()"

codeline = codeline + 1
.insertlines codeline, "myVar =" & Chr(34) & "Please work" & Chr(34)

codeline = codeline + 1
.insertlines codeline, "End Sub"
End With

End Sub

 

[Zathras]

I can't see anything in the code you posted that would cause destruction of anything. Other than the fact that a sub with the name Workbook_Open belongs in the "ThisWorksheet" module (which you already knew), there is little that needs changing (as long as you run the macro only once).

Do you have code elswhere in your project. Do you have a personal.xls with code in it? Any add-ins that you have created? The problem is most likely somewhere other than in the code you posted.

 

[krinid]

Zathras, combo,
What was working before is no longer working! It seems to be hanging on VBProject references, for example in the line:

Set CodeM1 = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule

I can't even run

Debug.Print ActiveWorkbook.VBProject.name

without getting an error.

I've doublechecked that "Microsoft Visual Basic for Applications Extensibility 5.3" is still enabled in the references.

Any ideas?

 

[krinid]

Found the problem - somehow "Trust access to Visual Basic project" was turned off! (not sure why...)

I know there's no way to change this value from code (there wouldn't be any point in having the feature if there was), but is there a way to check it's current value? I'd like to inform the user that this is required for certain functions and how to enable it if it's disabled.

 

[combo]

You can use error handling to test it, maybe together with testing office version. As any reference to VBProject's components means access to the project, this works:

On Error Resume Next
i = ThisWorkbook.VBProject.VBComponents.Count
errNo = Err.Number
On Error GoTo 0
If errNo <> 0 Then
    MsgBox "Please enable programmatic access to VB project"
Else
    ' do the stuff
End If

combo

 

[krinid]

Thanks again combo, works like a charm! Wish I could give you another star for this.

 

[xlbo]

krinid - that'd be my pleasure - this thread has so many useful bits of code, it's in my favourites now
Have a purple pointy thing Combo

Rgds, Geoff

 

[krinid]

Thanks Geoff (for Combo's well-deserved star). You're right, this has lots of good info in it. How to manipulate code dynamically, what reference is required to allow it, Excel side setting to allow VBproject references, and how to check if that Excel side setting is set - sounds like FAQ material.

Anyone interested in writing this up as FAQ? I think it would be useful to other users.

 

[combo]

Thanks to both of you, Geoff and Krinid. It is always a pleasure to know that someone found a piece of your work useful.
VBA (and VB) has plenty of hidden treasures, with little help from MS. Recently, while debugging my project, I used 'Locals window'. When in break mode, it is a great tool together with Watch window', with easy access to used variables and object variables.

combo

 

[innov]

Hi folks. I know this thread is a year old but I just came across it in a search and it's got some relationship to my issue, so I'm hoping this audience has some expertise in my problem area. Any help is GREATLY appreciated!

I've got an Excel XP VBA app that recently was installed on a Office 2003 machine. I'm trying to figure out if it's 2003 or what I did (my hypothesis below) that is causing my headaches, but I'm getting corrupted vbprojects (i.e. code will just exit in the middle of a procedure, or objects no longer found.) Really strange stuff USUALLY related to a workbook event.

SUSPECTED ACTION THAT CAUSED PROBLEM (unless it's an '03 issue):
--------------------
I had made several changes to Workbook_xxx events (Open, SheetChange, SheetActivate, BeforeClose, etc.) and needed to copy the code to apps at other locales. I've been very successful exporting and importing modules, BUT THIS WAS THE FIRST TIME I DID IT WITH THISWORKBOOK. I imported the (what I know now is a CLASS module), so I (a) copied the code from the class module and pasted into the ThisWorkbook module, then (b) deleted the ThisWorkbook Class module.
---------------------

Whether or not youhave an answer to the above, is it possible to remove the vba project altogether and replace it from another workbook's project INCLUDING the ThisWorkbook module?


THANKS SO MUCH IN ADVANCE! I'm At WITs END!!!

innov

 

[innov]

Cancel that! I've got it resolved. Here's the problem in case anyone ever encounters this bizarre thing.

Somewhere in the midst of numerous cell formula modifications involving MROUND, the workbook created a LINK to the Toolpak addin, ATPVBAEN.XLA!!! From that point on, it manifested as eratic VB macro execution, including just jumping out of the middle of a procedure.

Anyway, I broke the link ("Edit Links"), which coverted all affected cells to values, and then re-did the formulas.

All is well IN MY VBA CODE now ?!?!

Thanks to anyone who may have spent time on this.

Innov