Remove and Re-import modules check

[adds007]

Hi,
I am designing a VBA tool for some number of people. In order to avoid re-distributing the file to everyone when a mistake or bug is corrected, I decided to add a module called, updateCode. This will basically remove all current modules (except updateCode ofcourse), and re-import them from shared drive with a click of a button (which everyone can do). See a working code below FYI.

Please Login or Register  to view this content.

If there is a bug in other modules, i can correct that and tell people to press the button, and voila bug fixed.
HOWEVER, if a bug appears when a 100 people used it in updateCode module, i can;t really fix it.
So my question is: Am i missing anything in this code that will make it error-free and "monkey-proof"?

Thanks a lot

[snb]

Why don't you put the tool on the shared drive and let the users start it from their location ? In that case you only have to replace the tool by the improved one if any bug has to be fixed.

[adds007]

I wish I can, but it's more complicated than that.

The situation is and will always be like following:
A template of this tool will be uploaded to a "master matabase moftware". People will go in and download that template to their personal drive and input their stuff. Each person will have different stuff on it. In other words, there will be like 100 of these files with different things on each file. If a bug is corrected, I dont want to re-upload the template and having people to transfer their old data to this new template.
Instead, just click the button and bug is fixed and no need for transfer of data.

Thanks for the response tho.
Is it dangerous to do this? I have seen it work on my computer seamlessely, but everyone's computer is not same as mine, although same excel 2003 version atleast.

[snb]

In that case I would stick to my earlier suggestion.
To me it seems more easy to store the 'personal stuff'; load a fresh template and import the 'personal stuff'.

[adds007]

I like your suggestion, but I would like to use my aproach instead. It seems like a lot less work and less time to excute the code, instead of maybe copying over hundreds of lines from 4 sheets to new template.
Besides, it works just fine on my computer. I just needed to check if it works fine on all computers. I can't go to every computer in my company to test this out. I will try it out on a freind's old computer. If it works on an old computer, it should work on any newer computers.
Thanks for the responses tho

[shg]

You could instead implement your code as a read-only add-in on a network share. If people can't run the add-in directly from the share (e.g., because they need to be offline for some reason), then your code could be reduced to just downloading the current version of the add-in.

I would have thought your IT department would take a very dim view of your approach.

[adds007]

Thanks for the response
I will do more research on how to create an add-in.

The IT department isnt really on this. It's just me as the programmer with no one to check it. There is a huge procedure to go through just to talk to IT department about this. But i am confident it works really good and gets its objectives done.

Learning something new everyday!

[broro183]

I agree with Snb & Shg, that having one version of the file/addin is a better approach than your current preference.

Some other points to consider:
- I think every user will have to have a Reference set to "Microsoft VBA Extensibility...".
- If excel 2007 is adopted by your company there may be more hoops to jump through using multiple templates rather than an addin.
- If the code is so likely to change, then (in my state of ignorance) I believe the file structure may change as well, resulting in a mis-match between OLD file & new code.
- why is your code seemingly so suceptible to change?
Perhaps you could post sections of the code on the Forum & we could help you improve it so that you have a more polished product...?
- and, I'm sure I had another point... but it escapes me at the moment.

Here are some addin links:
http://www.ozgrid.com/VBA/excel-add-in-create.htm
http://www.cpearson.com/excel/CreateAddIn.aspx
http://www.jkp-ads.com/articles/distributemacro00.asp

hth
Rob

[adds007]

Brace your selves for a long post

broro183,
Thank you very much for your post. I did some research on add-ins, and its a much better approach that i used. Already started working on it.

Comments on your points:

- I think every user will have to have a Reference set to "Microsoft VBA Extensibility...".

-I did reference "Microsoft VBA Extensibility..." in my code. And i also added a command to add that reference automatically on workbook open, incase other users don't have it. FYI:

Please Login or Register  to view this content.

- If excel 2007 is adopted by your company there may be more hoops to jump through using multiple templates rather than an addin.

-Fortunately, excel 2003 is used in our company. 2007 seems a little harder to deal with.

If the code is so likely to change, then (in my state of ignorance) I believe the file structure may change as well, resulting in a mis-match between OLD file & new code.

-I believe I will be using the common add-in file approach. Hopefully, that won't cause file structure change

why is your code seemingly so suceptible to change?

-From my past VBA tool and my programming experience, I know there will be bugs along the way as more and more people start using it. Add-in approach seemed really easy to fix that problem. If there is a bug, I fix the bug in the add-in. And next time a user opens the file again, the updated bug-free add-in will be referenced (seems to work in my head)

Perhaps you could post sections of the code on the Forum & we could help you improve it so that you have a more polished product...?

The whole program is too big to post. But it works for the most part. If in future some problem does appear that I can;t fix I will definetly post a snippet of the code here in the forum to get help from all of you wonderful people

and, I'm sure I had another point... but it escapes me at the moment.

lol, I appreciate you helping me out. It means a lot to me. I know so much more about add-ins than I knew before.


There is one thing that i would like to bring it here. I have a workbookopen event that references a procedure from the add-in. The add-in has to be installed before using this tho, and the reference for it also has to be added. Because, I like to make everything automated for user-benefit and less-suspetible to problems (pardon me for my arrogance), i found commands online to do that in VBA. See below code:

Please Login or Register  to view this content.

When workbookopens, it's gives compiler error that WorkbookOpenEvent is not found (which is obvious why). Is there a way to avoid this?
Yes, I want to have the procedure in the reference, such that i can modify that too in case of bugs, just like i did to all my worksheet events also.
Yes, I know I can just tell the user to add the add-in and it's reference manually. I prefer not to use this method tho. Automation is more efficient and reliable.

Thank you very much for the responses so far. I believe I can get this done through the help of all you kind and VBA experts with effiency and reliability. I apologize for the long post.

Thanks again

[snb]

To start macro 'macro1' in sheet1 in addin 'snb.xla':

Please Login or Register  to view this content.

[adds007]

Thank you snb,
I don't know why I didn't think of that even after know about it before posting the question.
Thanks again for all the help

[broro183]

hi Adds007,

It looks like you're using some quite advanced (well for me anyway!) code already, so the following link may not be very informative. otoh, we all have a few gaps in our knowledge & the threads are very varied so...

These links may provide some ideas for making your existing code more robust &/or efficient:
http://www.excelforum.com/the-water-...hers-wont.html
http://www.excelforum.com/excel-prog...id-in-vba.html

Rob

[adds007]

Thanks for the links Boro183.

The credit for my add-in code actually goes out to VBA experts like you online. I just compiled snippets of codes from intenet.

You all deserve reputation points for great responses