Quickly Update Macros/VBA in file used by many?

[NewYears1978]

I don't think its possible in the way we have everything set up here, but is it possible to quickly update macros that I have updated in a local file (on my computer) to all other people's files?

I have a document I created that all the salespeople use, but I have fixed and updated VBA code that needs to be distributed out. There's no easy way to do that is there? The only way I could think of was if I had originally created an Add-in and just had them update the add-in instead.

[Keebellah]

The Add-In is the best option

[NewYears1978]

Alright, never done it before guess I will have to learn. Worried my existing thing will not work...also there are 5 or 6 modules full of code to convert that into an add-in is probably difficult.

Assume I would have to remove all those modules from their existing file, then reinstall them as an add-in

[Keebellah]

No, you just save the file as Addin-n (xlam) file
You don't have to install it just place it in the same folder as the the files and have them double click it to load or just include it in the workbook_Open event of the target file

[NewYears1978]

Yeah seems very easy based on my reading.

Here's the problem I am unsure how to solve. The current excel file everyone uses already has about 6 or 7 modules in it, the add-in would then have the same modules....there's no easy way for me to remove the modules from their existing files and replace with the add-in - without having everyone start with a new file stripped of those modules, correct?

I also have a custom ribbon with icons embedded into their file which I did with Custom UI editor, I assume those can be saved into the template as well, but again that means I need a way to remove those from their existing file.

I can't just distribute a new file unfortunately because everyone has 3,000 data entry lists with custom edits in their files.

[Keebellah]

My solution would be a new version as addin with all the code an Customized UI and make a slow transition
If you need help with that, let me know (and attach a file with the code and Custom UI)

[Zer0Cool]

I am going to go against the grain here. I do agree that an Addin is a viable option, however your scenario may be best for simply using a network shared macro file.

Simply put, your issue is you have a single base of code that many people need to use. What complicates that is updates. The complication becomes distribution. How do you update the file and then get it to everyone?

An addin does not address the issue any more than a file does, as you still need to get it to everyone. Its likely to be even more difficult as now not only do they all need to have the addin but they all have to remove the old one and know how to activate the new one.

If however you did a xlsm/xlsb file on a network location you could simply have everyone use it from that location. It could be as simple as they all copy it from that location to their computer or more advanced like having a group policy to add the file/location to their trusted locations or even an XLSTART folder.

In the above, you could set things up to have 1 file in a central location that potentially adds the benefits of being up to date the next time anyone goes to open it. IN this fashion distribution is no longer an issue. Updates become seamless.

Just my 2 cents

[NewYears1978]

An add in does if I have the file open the add-in on a network drive though right? Then I can just update the add-in.
The issue is sometimes i need to update the main file


The problem with the way you suggest is that the file cannot be easily updated once they use it. Hard to explain but basically everyone already uses my file from a shared drive but once they run my macros it loads a TON of data into it that is specific to them. They update this daily with my macros which pulls new data with existing data without removing some other changes they have made. So they no longer use the network file, so if I update it..it doesn't help them.

Really not a "great" solution for this actually. Having a base file that uses and add-in where I can update the add-in is probably the only viable option..doesn't help to update the existing file though.

Probably best to just let them use what they have now and not try to make updates - haha.

[Zer0Cool]

The idea here would be to make your code independent from the data. You can do this just the same in a file as in an addin.

Ideally you want people running the code from a centralized copy of the code (be it an addin or a file), as that means when you update it you only update 1 copy AND do not need to redistribute it.

If you do an Addin vs a file, you then add a layer of complexity by needing to load/unload the addin on each machine (your changes are unlikely to be available until they reload the addin by either closing/opening Excel or by removing and adding the addin back). The addin file will also be "in use" as long as anyone using the addin has Excel open, which may prevent you from editing it (if say bob in accounting leaves his Excel open and goes on vacation).

To me, there is no benefit of an addin vs a file in centralized locations but there are downsides.

In any case go with whatever works best for you.

[NewYears1978]

But like I said the file won't work because each person imports data into their file that is permanent and/or needs to stay in the file. So when they start over with the new updated file they would lose all their data.

Or maybe I am misunderstanding. Probably be very hard to rework what I have already to do what I need..I am not smart enough =D
Oh you're saying the code could be in it's own file...I misread that. I don't know how to go about that though...I guess it's too hard to explain via text

[Zer0Cool]

The code being separate from the functional file is key, regardless of if you do an add-in or a file. An addin (xlam) is just a different kind of file, loaded differently with the sheets hidden. As such an addin has to be loaded/unloaded into Excel via the addins menu. The file approach offers more flexibility in terms of how its loaded.

It would be a matter of updating your code to be independent of the the data/files you process with it. The benefit of this is that you dont end up replicating and storing the code in each copy of the file either, taking up space and allowing the chance of people being stuck on using an old version of the code.

The overall gist of my response was simply that an addin requires extra steps and for your use, but doesnt give extra benefits, if anything it makes it more difficult.

[Keebellah]

I can't say I entirely agree or disagree with @Zer0Cool, I write custom xlam files and you should see this file as the 'engine' that keeps the working (with the data) up and running.
Yes, you may consider it more difficult (to implement), you have to think up what you want it to do and include all the options, I add a Custum UI which shows/hides the buttons depending on the file which is opened and if it's the expected data file, etc etc.
This require a lot of code and planning, but once it runs it can reduce maintenance.
If a new macro is required you just update the xlam file and replace the original one once it's tested, replacing occurs when all are logged out.
Version control is imho a must
To make a long story short, the first steps will take time but once it's up-and-running you can relax and wait for Murphy to show himself

[CK76]

I've used PowerShell Script along with Group Policy to automate add-in at my old job, 5 or so years ago.
I've since, moved away from using VBA in a lot of things. You may find below article useful.
http://www.electricmonk.org.uk/2014/...n-an-rds-farm/