Copying modules from one sheet to another, in code?

[Maury Markowitz]

I have a huge library of extremely complex code that we run every
night. In general terms, 1/3rd of that code is used only once a day
when the book is created. Another 1/3rd is utility code that could be
removed to some other location. Finally, there is the last third that
needs to stay with the book and the users interact with.

The code is currently located in a 2.8 meg XLA on a network drive.
This is also a problem because the first person that opens the book
"locks" the XLA and I can no-longer save changes to it. Additionally,
if the network falters even for a second, the link to the XLA "goes
bad" and every command starts returning random errors.

So what I'm thinking about doing is moving the code the user touches
into a separate library of some sort, and then saving it directly into
the book. That should eliminate both problems, as well as ensure they
have the right code version for the book they have open.

So finally, here's my question: if I place the code that needs to be
with the book into an XLA, can I copy it into the spreadsheet's
modules via VBA? Are there general-purpose commands for moving code
around in general?

Maury

 

[Bob Phillips]

Workbooks("Book1").VBProject.VBComponents("Userform1").Export _
Filename:="C:\temp.frm"
Workbooks("Book3").VBProject.VBComponents.Import _
Filename:="C:\temp.frm"
Kill "C:\temp.frm"

 

[Jim Thomlinson]

If the code is protected then you are not going to have much luck. Otherwise
check out this link...

http://www.cpearson.com/excel/vbe.aspx

 

[Maury Markowitz]

If the code is protected then you are not going to have much luck. Otherwise
check out this link...

Thanks! And no, I don't bother protecting my code.

This might not be the right way to do this though, I might be looking
for a tech solution to a user-space problem. I'll keep thinking about
it.

Maury

 

[Tim Williams]

Shared XLA's can be a problem - particularly if you need to do updates.
Something which has worked for me is to store the main xla in a web
(intranet) location and have some code in the main workbook which opens the
xla (thus copying it into the "temporary internet files" folder on the
user's PC). That way the main xla is not locked and it can be updated any
time.

Tim

 

[Maury Markowitz]

Shared XLA's can be a problem - particularly if you need to do updates.
Something which has worked for me is to store the main xla in a web
(intranet) location and have some code in the main workbook which opens the
xla (thus copying it into the "temporary internet files" folder on the
user's PC).  That way the main xla is not locked and it can be updated any
time.

Hmm, this sounds very interesting. I've never linked to an XLA over
the intranet though, how do you do this?

The other problem with my current line of thinking is that it's
annoying to refer to code in one XLA from another. This really works
against modularizing the code. Someone suggested using References for
this, but I have yet to get this to work. I could also re-write the
really low-level utility routines in pure VB and link to them, but
then you loose all debugging capability, which is a huge price to pay.

Maury

 

[Tim Williams]

Something like this:

'********************************************
'Info on name and location of add-in
Const LOCAL_TESTING As Boolean = False
Const QB_ADDIN_PATH As String = "http://blah/xxx/Apps/QB/"
Const QB_ADDIN_NAME As String = "QB_0001.xla"

Private Sub Workbook_Open()

'only need to load the add-in if it's not already open
If LOCAL_TESTING Then
MsgBox "Local testing: opening local copy of add-in!",
vbInformation
Workbooks.Open ThisWorkbook.Path & "\" & QB_ADDIN_NAME 'local
testing
Else
Workbooks.Open QB_ADDIN_PATH & QB_ADDIN_NAME 'Production
End If

End Sub
'**************************************

The add-in builds any menus and so I don't create any explicit
references between the workbook and the add-in: code called from the
menu just operates on the ActiveWorkbook. That might or might not be
appropriate for your situation.

Tim