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

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


--
__________________________________
HTH

Bob

"Maury Markowitz" <(E-Mail Removed)> wrote in message
news:47cf5ace-8d96-43c0-960a-(E-Mail Removed)...
>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
>

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
--
HTH...

Jim Thomlinson


"Maury Markowitz" wrote:

> 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
>
>

On Jun 24, 3:04*pm, Jim Thomlinson <James_Thomlin...@owfg-Re-Move-
This-.com> wrote:
> 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

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" <(E-Mail Removed)> wrote in message
news:47cf5ace-8d96-43c0-960a-(E-Mail Removed)...
>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
>

On Jun 25, 12:01*am, "Tim Williams" <timjwilliams at gmail dot com>
wrote:
> 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

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


On Jun 25, 6:18*am, Maury Markowitz <maury.markow...@gmail.com> wrote:
> On Jun 25, 12:01*am, "Tim Williams" <timjwilliams at gmail dot com>
> wrote:
>
> > 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 opensthe
> > 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