Consulting

Results 1 to 15 of 15

Thread: Using an addin to package all my source code

  1. #1
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location

    Using an addin to package all my source code

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

  2. #2
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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).
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  4. #4
    VBAX Wizard
    Joined
    Apr 2007
    Posts
    7,006
    Location
    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
    Last edited by Paul_Hossler; 08-08-2019 at 12:59 PM.
    Paul

    ------------------------------------------------------------------------------------------------------------------------
    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s)
    (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  5. #5
    Knowledge Base Approver VBAX Guru
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    4,902

  6. #6
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Joined
    Apr 2005
    Posts
    25,147
    Location
    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.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  7. #7
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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

  8. #8
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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

  9. #9
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    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

  10. #10
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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.

  11. #11
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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

  12. #12
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    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
    Last edited by snb; 08-09-2019 at 06:24 AM.

  13. #13
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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

  14. #14
    Knowledge Base Approver VBAX Guru
    Joined
    Apr 2012
    Posts
    4,774
    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.
    Attached Files Attached Files

  15. #15
    VBAX Regular
    Joined
    Aug 2019
    Posts
    8
    Location
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •