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?
-
If you would like to post, please check out the MrExcel Message Board FAQ and register here. If you forgot your password, you can reset your password.
The incredible growing file!
- Thread starter noahcount
- Start date
Joe Was
MrExcel MVP
- Joined
- Feb 19, 2002
- Messages
- 7,539
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
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
Upvote
0
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.
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.
Upvote
0
Joe Was
MrExcel MVP
- Joined
- Feb 19, 2002
- Messages
- 7,539
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
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
Upvote
0
Similar threads
- Question
- Solved
- Question
