Results 1 to 7 of 7
  1. #1
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Does an Addin use memory? (Excel 2000)

    I've created an addin, that I'm using to collect various tools that I'm creating for a client.
    The lastest tool uses a huge sheet of data which I've made part of the addin (and the addin's file size has increased correspondingly).
    Now I'm wondering if I took the best approach. Will a huge addin file slow Excel down?
    Any comments?

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Does an Addin use memory? (Excel 2000)

    I don't know if if will be a big deal if it is just text or values. It can slow it down if you have a lot of calcs, since calculations will calc even the addin which coluld slow it down.

    I guess it could depend on what you are doing with the data and what are the alternatives. If your code reads it into memory, it might take of less space to code it into a macro rather than use a sheet, and grab it.

    But it might be more of a pain to deal with all of the contents coded.

    On some level that info must be stored somewhere and it will take up a minimum amount of space. The question is: does it take up "significantly" more in the spreadsheet or in another place. SIgnicant will depend on alternatives and working with the alternatives. You could give up file size and improve the code exceution: I can read an array from a spreadsheet in "one fell swoop" the code to "fill" the same array all via VB would be significantly longer and probably run much slower, though it might take up less file space.

    Steve

  3. #3
    Bronze Lounger
    Join Date
    Jun 2001
    Location
    New York, New York, Lebanon
    Posts
    1,449
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Does an Addin use memory? (Excel 2000)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> Genii

    To add to Steve's reply, it depends if the add-in is loaded at startup or called when needed. From your post I see it is loaded at startup. In this case, sure it will need to be in RAM memory, and not just that, but it also gets in the VBA Stack, because the code needs to be ready to run when its called.

    Having the worksheet in the add-in is not your biggest worry, its how the data in it is used. I am hoping you went with some array construct, but then, what and how the code does with that data is key to making the add-in small, and compact.

    What is the Excel file size?

    Wassim
    <img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

  4. #4
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Does an Addin use memory? (Excel 2000)

    Addin size 4186kb. Fortunately there are no calculations in the large sheet that I've added to the addin. All that happens is that the sheet is copied from the addin into the file being worked on. From that point, an array from the current file is used as the source for a series of find actions. When a match is found in the workbook, some formatting takes place so that matching values from the addin and the current file stand out. I'm simplifing here but that is it in a nutshell.

    Cheers
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Does an Addin use memory? (Excel 2000)

    4 meg is a pretty hefty addin. Is it all the worksheet? can it be "pared down"? maybe generate some of it via code rather than entering everything, perhaps even reading the data in from a text file or something?

    Steve

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Does an Addin use memory? (Excel 2000)

    Catherine,

    If the sole purpose of the addIn is to house a sheet for insertion into another workbook, consider saving that sheet as a single sheet workbook. You could then include it in any workbook with the following line of code :

    Sheets.Add Type:= "PathFilename.xls"

    where "PathFilename.xls" is tthe path to the saved sheet.

    Andrew

  7. #7
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does an Addin use memory? (Excel 2000)

    If your fact pattern does not match that which Andrew is suggesting, consider having the data as a second Add-in - with say a .UTL extension. The UTL file can then be dropped/closed by the code running in the XLA once the data has been loaded into the target. That way, the rest of the code should not take a performance hit. HTH
    Gre

Posting Permissions

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