Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Architecture / Design (WinXP/ O2003 / XL)

    Hi,

    Got an .xla with a constantly increasing number of functions. Some handles database connections and data retrieval. Some deals with formatting and colors and a last category are department specific functions.

    To make it more lean and efficient I am considering to spilt it into 3 separate files - maybe even a DLL or an XLL...

    But I am not certain of the pros and cons of this approach and lack experience with this.

    A few obvious questions are:
    1. How can my 'database-project' get hold of functions in eg. the 'formatting-rpoject'
    2. Are there any performance consequences?
    3. Will Excels 'security settings' prevent this from working - and in case at what level does it interfere?
    4. Considerations about locally installed files in comparison to network installed files (distribution aspects)
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  2. #2
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Architecture / Design (WinXP/ O2003 / XL)

    Before you get into this knee-deep:

    - Why are you considering the split up?
    - How big is the current xla?
    - How big is the VBA project (you can use MZTools to determine your project stats)??

    For instance. My Name Manager addin (which works fine) has these stats:

    Filesize of xla: 1.7mb
    # of code lines: 9,413
    # of modules: 10
    # of userforms: 9
    # of class modules: 4
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  3. #3
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Architecture / Design (WinXP/ O2003 / XL)

    Consideration is:

    Some don't need the DB part, others not the department specific part etc.
    We got a very limited profile space per user <15mb. Thus I would like to keep the .xla minimal in size and only containing what the various users need.

    Size is around 2mb.
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Architecture / Design (WinXP/ O2003 / XL)

    You can limit profile consumption by putting your xla on a network share. Make the xla file readonly and you can even update it "live".
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Architecture / Design (WinXP/ O2003 / XL)

    Hi Jan,

    Thanks for the advice...

    Part of the architecture consideration is, that it must be available both when the user is on- and offline, thus the location in the profile.

    An alternative would be to locate it in a separate folder somewhere on the harddisk, where it is not part of the profile count. But doing so there is the re-distribution issue as you mention and also the drawback, that it will no longer be selfregistering - contrary to the behaviour when it is located in the 'profileaddins' folder.

    Having read Bullen/Bovey/Greens 'Pro Excel Development' (ISBN: 0-321-26250-6) I'm just curios as to whether a split or XLL conversion could be relevant alternatives worth considering???
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Architecture / Design (WinXP/ O2003 / XL)

    Well, you could consider building a COM addin using VB6. Very easy to re-use your code and I bet the dll will be smaller than the Excel xla. But I hardly know anything on using multiple dll's.
    If the code in part A needs some code in part B, then two addins isn't very feasible, as you'd need a reference from A to B, which will likely cause havoc when a user only as A or B.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    3 Star Lounger
    Join Date
    Aug 2002
    Location
    Denmark
    Posts
    347
    Thanks
    0
    Thanked 0 Times in 0 Posts
    ...a minor continuum of this thread...

    Considering to make a single "dll like item" (what is the most recommended method?)

    I have a logon procedure and some standard database calls that I want to use across multiple workbooks.
    .. and instead of making an .XLA I was considering a DLL or the like as I expect it to work faster and integrate seamlessly with Excel.

    However I'm rather new to this part and are fumbling around here - so any tips and guidance are highly appreciated.

    TIA
    Bests,
    RD


    PS: Wish there were a knob on the TV to turn up the intelligence. There's a knob called "brightness," but that doesn't work

Posting Permissions

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