Problems converting large vba project to add in

[lafkyc]

I am coding a large vba project for work, and I am working on putting all of the source code in an add in, so that all 50-100 copies of the program that will be in circulation pull their code from the right place. The internet seems to agree that this is the best way to do it, however I can only find information and examples of very small macros turned into add ins, where as my source code is over a hundred pages and I wish to store it in the addin while still using all of it’s functions, subroutines and userforms from inside the main workbook. How should I go about this? My references are not fully qualifying for some reason, and I cannot figure out if I need to keep some other code in the main sheet to harvest code from my add in, or if I need to make my add in modules class modules and then harvest that, or how I would go about any of this. I believe I can figure out the semantics but I simply do not know where to start or how to go about it. Please let me know if you can offer any advice on my best route, or any resources on this. I have read chip pearsons site and a lot of other stuff online, however all explanations are for putting a couple functions in add ins or something of the sort whereas I wish to put a ton of source code there and have it run as it did when all that code was within the main book.

Thanks!!

Full disclosure: I have also posted this on stack and on VBAExpress, the stack question contains some of my code, link is below. I am not super worried about my code I just need to conceptually figure this out and I can implement.
I only did so because this is important and I really need the help! https://stackoverflow.com/questions...potential-problem-with-qualification-of-my-re
http://www.vbaexpress.com/forum/showthread.php?65671-Using-an-addin-to-package-all-my-source-code

EDIT:
In future please supply all crosspost links

 

[theBardd]

Depends what exactly you mean by functions. Do you mean custom formulas, UDFs, or do you mean methods that will work on a user's particular data?

 

[lafkyc]

Depends what exactly you mean by functions. Do you mean custom formulas, UDFs, or do you mean methods that will work on a user's particular data?

It would be all of these things good sir - It would be manipulating data in the users spreadsheets, userforms who's result is put into their sheets etc

Right now what I am thinking is rather than an add in for all the code that I just use extensibility routines/subs through an add in to perform any code changes I need. What do you think?

 

[theBardd]

I would create an addin and add a custom ribbon, or a custom group on an existing ribbon, to provide access to my functions. That is typically the way I do it, and I can write big addins as well.

 

[lafkyc]

I would create an addin and add a custom ribbon, or a custom group on an existing ribbon, to provide access to my functions. That is typically the way I do it, and I can write big addins as well.

Well so thats kind of what is messing with me, my "functions" really dont have anything to do with them, and they are just clicking buttons on the worksheet to perform different tasks. When a button is clicked it either brings up a userform or runs a report etc. Would you be able to package all of the code as an add in? Everyone seems to just make each function a different add in but I have over 170 pages of code...... I really only want one maybe two add ins and i dont even have the toolbar visible for users, I designed all the code to run through form controls

 

[lafkyc]

The other thing that adds to it is I do NOT want all of this available or visible or any of my things to run in any other excel files except for their project books. To give some background this is for a contractor, so each jobsite has its own program. I do not want the code running except in the project managers job program as it will not have any results and may mess up other files.

 

[theBardd]

Well so thats kind of what is messing with me, my "functions" really dont have anything to do with them, and they are just clicking buttons on the worksheet to perform different tasks. When a button is clicked it either brings up a userform or runs a report etc. Would you be able to package all of the code as an add in? Everyone seems to just make each function a different add in but I have over 170 pages of code...... I really only want one maybe two add ins and i dont even have the toolbar visible for users, I designed all the code to run through form controls

Of course you can package all of that, you can package anything in an addin. The stuff you don't want visible you can make as Private, as Functions, or just add Option Private Module at the start of the module.

Personally, I abhor form controls, and userforms for that matter, I would rather have toolbars, ribbon, context menus, and the Excel grid. After all, it is 20198, not 1995.

Make sure you have lots of modules, don't try and cram it all into one or two,

The other thing that adds to it is I do NOT want all of this available or visible or any of my things to run in any other excel files except for their project books. To give some background this is for a contractor, so each jobsite has its own program. I do not want the code running except in the project managers job program as it will not have any results and may mess up other files.

​

As I mentioned before, but if you don't want themn using it, why include it?

​

 

[lafkyc]

Of course you can package all of that, you can package anything in an addin. The stuff you don't want visible you can make as Private, as Functions, or just add Option Private Module at the start of the module.

Personally, I abhor form controls, and userforms for that matter, I would rather have toolbars, ribbon, context menus, and the Excel grid. After all, it is 20198, not 1995.

Make sure you have lots of modules, don't try and cram it all into one or two,

​

As I mentioned before, but if you don't want themn using it, why include it?

Totally see where you are coming from with form controls - I am working to make this program easy to use and intuitive for older project managers and field workers however and those do make things the simplest. I want them using the functions but only within the project book for the things its supposed to do.... they have no knowledge of code and in another blank book this code would not serve much value as it is mostly based off of named tables etc in sheet. When I attempted to package it as an add in, it seemed like my biggest issues was my references. Do I package every part of the code in an add in? Either way how does that change how I reference pieces of my code, and the sheet operating the code

 

[theBardd]

I am not sure I fully understand the problem, but don't forget you can always reference ActiveWorkbook and Activesheet, which will be (should be) the objects your code is running in. You can also reference Application.Caller, which should be the control initiating the call, and get to the parent (sheet) via the .Parent property.

Another technique I often use is to add a hidden name with the value True to my 'special' workbooks, and check that the actievworkbook has that name before doing anything

 

[lafkyc]

That is helpful I will continue to work on it and research