Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: The incredible growing file!

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    alabama
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Newbie here! Nice board..., shoulda been here years ago! I have a file that contains 4 queries and some financial statement type ranges that I publish to a website with VB. The processing time for the file/code has increased gradually over the year as well as the file size. Started @ 500k and grew to 5,000+k in 6 months. Excel 2000 and Win2000 pro. The only changes to the file have been from daily updates to the queries which only contain summary data. The file has slowed to the point of hanging the system. I copied the queries, code, and print ranges to a new file and it flies again. What gives?

  2. #2
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Couple of possibilities:

    Still saving it as a xl2000 file? Also, are you building up layers of named ranges as time passes?

    Food for thought. Cheers, Nate

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    alabama
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    been saving as default xls. Named ranges have not changed. What would added layers look like?

  4. #4
    Legend NateO's Avatar
    Join Date
    Feb 2002
    Location
    Minneapolis, Mn, USA
    Posts
    9,700
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just new names being added....

  5. #5
    New Member
    Join Date
    Mar 2002
    Location
    alabama
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Just a thought...how do you check/insure that the change history and/or undo stack are cleared at file closing?

  6. #6
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Also, check to see if the application is adding links to some other workbook(s).

    Or, in your macro code you may be keeping variables, try to use and then destruct variables. Define variables by type and use. JSW

  7. #7
    New Member
    Join Date
    Mar 2002
    Location
    alabama
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    no links other than the queries. Variables don't persist after close, do they?

  8. #8
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    The value of a variable may change over its lifetime, but it retains some value. When a variable loses scope, it no longer has a value. Each element of a user-defined type variable is initialized as if it were a separate variable.When you declare an object variable, space is reserved in memory, but its value is set to Nothing until you assign an object reference to it using the Set statement.

    If the value of a variable isn't changed during the running of your code, it retains its initialized value until it loses scope.

    A procedure-level variable declared with the Dim statement retains a value until the procedure is finished running. If the procedure calls other procedures, the variable retains its value while those procedures are running as well.

    If a procedure-level variable is declared with the Static keyword, the variable retains its value as long as code is running in any module. When all code has finished running, the variable loses its scope and its value. Its lifetime is the same as a module-level variable.

    A module-level variable differs from a static variable. In a standard module (Public) or a class module, it retains its value until you stop running your code. In a class module, it retains its value as long as an instance of the class exists. Module-level variables consume memory resources until you reset their values, so use them only when necessary.

    If you include the Static keyword before a Sub or Function statement, the values of all the procedure-level variables in the procedure are preserved between calls. When you use the Static statement instead of a Dim statement, the declared variable will retain its value between calls. If a public variable is declared in a standard module or a class module, it can also be used in any projects that reference the project where the public variable is declared. If this statement appears within a procedure, the variable strName can be used only in that procedure. If the statement appears in the Declarations section of the module, the variable strName is available to all procedures within the module, but not to procedures in other modules in the project. To make this variable available to all procedures in the project, precede it with the Public statement. You don't have to supply the variable's data type in the declaration statement. If you omit the data type, the variable will be of type Variant.

    When variables are initialized, a numeric variable is initialized to 0, a variable-length string is initialized to a zero-length string (""), and a fixed-length string is filled with zeros. Variant variables are initialized to Empty. Each element of a user-defined type variable is initialized as if it were a separate variable.

    This is where the use of variables may cause your code memory to grow with each save. JSW




  9. #9
    New Member
    Join Date
    Mar 2002
    Location
    alabama
    Posts
    5
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Lemme see if I understood what I thought you said. I Dim'd my variables before any Sub statements, so that makes them module scope or class scope. Each time I run the proc, it creates "another" instance of the variables instead of using the ones already declared. To remedy this, I could Dim inside the Sub statement and force them to lose scope.

    I checked the process today and found that running the proc added 32k to the file size each time it was ran and saved. Running more than once while not saving had no effect other than the 32k increase after saving. Opening the file without running the proc and then saving had no effect on size.

    The MS KB had an article on something similar with a work around but I couldn't get it to run.

    Seems as if the procedure creates and stores new variable values when run with a new instance of the file open,save process.

  10. #10
    MrExcel MVP Joe Was's Avatar
    Join Date
    Feb 2002
    Location
    Central Florida, USA
    Posts
    7,539
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Yes, Dim your variables below the sub. This way each time the sub is done so is the variables. Use variables outside the sub only if you will need them in a call or another sub. (You can also use outside varaiables in other projects.) These will be Static though.

    From your note it seems that Excel is re-doing each element of your variable each time it is used which could cause a geometric growth in save size?

    Then again I may be sending you a far and the problem may be in some other part of your code?

    I have run into this before and it was the way I was using the variables! A re-write fixed the problem. I also had the same problem that was not the variables.

    In this case I renamed the file, then copied the code to a new workbook, named it the old name, then deleted the original but re-named file. Some how Excel got confused and the above fixed it? JSW

    [ This Message was edited by: Joe Was on 2002-03-15 15:16 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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