Using external source for Macros 2

[mts176]

I was wondering if it was possible to use an external source to store and retrieve macros.

I have an excel spreadsheet that has tons of macros and sub routines in it. It is used as a template, so after a while I am going to have a ton of these 200k plus files. I would like to have a seperate sheet that has all of the macros and sub routines on it.

Like in ASP you can use either a style sheet or reference another file that contains all of your functions. I am wondering if it is possible with VBA.

Thanks

 

[dhulbert]

If you put the macors etc into a folder called personal.xls in your xlstart directory they will be loaded each time you open excel but stored in only 1 place.

 

[KenWright]

Who uses or potentially uses the routines?

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[mts176]

Everyone that has access to our network, will have the potential to use these macros.

I read a little about the Global document, is that what I am looking for?

These excel files will be opened through our Intranet via a hyperlink. Does that matter?
They are all saved and stored in one folder, but different folders within the main folder.

I am also looking for a way to delete macros and subroutines with a click of a button.

Thanks for all of the help.

 

[AnotherHiggins]

I have my Personal.xls on a shared network drive and routed Excel to look in that folder for files to open on Startup. I share this file with one other person in the office and we haven't had any trouble yet, but from what I've read a file accessed this way is likely to end up corrupted. In my case that's no biggie since I have a backup copy.

If you're interested, you can go into Tools > Options and on the General tab enter a network location for Alternate startup file location.

Ken might be able to shed more light on whether this is a viable option in your case.

Question 2

I am also looking for a way to delete macros and subroutines with a click of a button.

To explicity list the names of modules to be deleted, use this:

Sub test()
ActiveWorkbook.VBProject.VBComponents.Remove _
    ActiveWorkbook.VBProject.VBComponents("Module1")
ActiveWorkbook.VBProject.VBComponents.Remove _
    ActiveWorkbook.VBProject.VBComponents("Module2")
End Sub

Or if you want to delete all code in the workbook, you can use this:

Sub test()
ModulesCount = ActiveWorkbook.VBProject.VBComponents.Count
For i = 1 To ModulesCount 
On Error Resume Next
    j = ActiveWorkbook.VBProject.VBComponents(i).Name
    ActiveWorkbook.VBProject.VBComponents.Remove _
        ActiveWorkbook.VBProject.VBComponents(j)
Next i
End Sub

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[KenWright]

Must admit i've never been a fan of shared files, though to be fair that was related to people editing shared files which wouldn't be the case here, but I guess you could store all the routines in a single book in it's own folder on the network and then do just as John suggested, have people point their alternate startup paths to this folder. Would probably make the file read only as well though.

Regards
Ken..............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

Would probably make the file read only as well though.

True - but only when others have the file open. If you are the only person with it open at the time, you can edit the VBA. Again, that's easy for me - I'm only dealing with two of us, so if I need to make a change/addition, it's no big deal to call up the other guy and ask him to get out of Excel for a minute.

If you are dealing with dozens of users you might need to make any updates during non-business hours. The good thing is that you can do all of your editing in a copy of the workbook, then just replace it on the shared drive - so you only need a window of about 1 minute when no one else has Excel open to swap file.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[KenWright]

LOL - That's the English language for you - That was meant as a suggestion, not a comment

I would probably make the file read only.

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[AnotherHiggins]

Ah, yes. That's a good idea.

[tt]-John[/tt]
________________________
To get the best answers fast, please read faq181-2886

 

[mts176]

Thanks for the help. I will implement that ASAP.