Using an addin to package all my source code

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

[lafkyc]

Full disclosure: I have also posted this on stack and on Mr Excel, the stack question contains some of my code, link is below. I only did so because this is important and I really need the help! thanks! https://stackoverflow.com/questions/...ation-of-my-re

[Bob Phillips]

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?

(didn't mean to post on MrExcel).

[Paul_Hossler]

In MyAddin.xlam

Option Explicit

Sub Times2(x As Long)
    MsgBox 2 * x
End Sub

Sub Times3(x As Long)
    MsgBox 3 * x
End Sub

In your calling macro, you can use Application.Run

Option Explicit

Sub UsingRun()
    Dim sAddinPath As String
    
    sAddinPath = "'" & Application.UserLibraryPath & "MyAddin.xlam'!"
    'MsgBox sAddinPath
    
    Application.Run sAddinPath & "Times2", 100
    Application.Run sAddinPath & "Times3", 100
End Sub

Edit -- I assumed that you meant to have an open xlsm use the macros in the addin. If you want the addin macros to just operate on the open xlsx, I'd use the custom ribbon approach in my addin that others have suggested

[p45cal]

full link to the MrExcel thread:
https://www.mrexcel.com/forum/excel-...oject-add.html

[Bob Phillips]

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]

There is a specific frontend xlsm that every job will have as its copy. Each needs access to over 170 pages of code. I do not even have the ribbon visible for users, it is all run through form controls and they have very little power besides predefined tasks and reports. All of these however are fairly complex userforms or processes so I am at a bit of a loss on how to turn that small scale example (which totally makes sense to me) into something that works for userforms and processes that have many functions subs and steps

[lafkyc]

backup july 31 10am.xlsm
I am attaching a copy of my program file. It is massive but if anyone looks you may get an idea for what I mean. you can use username and password both as codementor if you enter it. This version is a week or two old so def some bugs

[snb]

Start reducing the code:

- avoid any 'select' or 'activate' in VBA

- use 1 multipage in 1 userform instead of 20 Userform.

- most of your 'functions' are redundant

e.g.

msgbox format(date,"mm/dd/yyyy")

instead of

Public Function todaysDate() As Date
    todaysDate = Now                                         ''SETS DATE
    todaysDate = Format(todaysDate, "mm/dd/yyyy")                ''formats date
End Function

Use loops:

e.g

Sub M_snb()
  for each it in sheets
    it.Visible=-1
  next
End sub

Instead of"

Sub unhideAll()                         ''this unhides all sheets that are backend for use by admins
Worksheets("Sheet1").Visible = True
Worksheets("Admin modules").Visible = True
Worksheets("PM Modules").Visible = True
Worksheets("Change Log").Visible = True
Worksheets("CO Tracking").Visible = True
Worksheets("FIELD REPORT LOG").Visible = True
Worksheets("REPORTS").Visible = True
Worksheets("PRTV Log").Visible = True
End Sub

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

[lafkyc]

thank you snb! that is something else I am currently working on I will prioritize that and use your suggestions as well as your site

[snb]

You don't need any interaction with the workbook to get the weekend date (ISO).
No benefit to put it into a separate function either.

Sub M_snb()
  'saturday
  MsgBox DateAdd("d", 6-Weekday(Date, 2), Date)
    'sunday
    MsgBox DateAdd("d", 7-Weekday(Date, 2), Date)
End Sub

E.g the Initialize Event in Userform1:

Private Sub UserForm_Initialize()
'  UserForm5.Show
'  Call Module1.todaysDate                           ''call date function to fill text box on form
'  Call Module1.centerForm(UserForm1)                ''call center function to center form in view
'   todaysDate                 ''set text box to date
'  Call Module1.sDate                                ''call sunday date func
'   sDate               ''set text box to sunday date
    ''POPULATES COMBO BOXES WITH COST CODES from table reference
'    With Me
'      .CostCode2.List = codes
'      .CostCode2.List = codes
'      .CostCode3.List = codes
'      .CostCode4.List = codes
'      .CostCode5.List = codes
'      .CostCode6.List = codes
'   End With

   startupposition = 2    ' should be stored in design mode
   dateIn.Value = Date
   sundayDate.Value = DateAdd("d", 7 - Weekday(Date), Date)
   
   sn = Range("cCodes")
   For j = 1 To 5
     Me("CostCode" & j).List = sn
    Next
  
End Sub

[lafkyc]

Yes I have realized that, I would like every piece of advice youre willing to offer though this is actually the first thing i have ever coded and I did all of this in about two months

[snb]

Do not post files that contain any protection.

To illustrate what I mean I rewrote Userform1.

The way data are being stored should be in compliance with common database practices.

[lafkyc]

wow thanks again that looks great I am going to figure that out and model more of my code after it, thanks for your time! I am still learning the more efficient ways to do most things, what you wrote looks great.