The incredible growing file!

noahcount · Mar 12th, 2002, 03:26 PM

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?

NateO · Mar 12th, 2002, 04:14 PM

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

noahcount · Mar 12th, 2002, 04:25 PM

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

NateO · Mar 12th, 2002, 04:26 PM

Just new names being added....

noahcount · Mar 12th, 2002, 04:38 PM

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

Joe Was · Mar 12th, 2002, 04:43 PM

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

noahcount · Mar 12th, 2002, 04:47 PM

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

Joe Was · Mar 13th, 2002, 10:11 AM

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

noahcount · Mar 13th, 2002, 05:17 PM

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.

Joe Was · Mar 15th, 2002, 04:09 PM

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 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 12th, 2002, 03:26 PM	#1
noahcount New Member Join Date: Mar 2002 Location: alabama Posts: 5	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?

Mar 12th, 2002, 04:14 PM	#2
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	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

Mar 12th, 2002, 04:25 PM	#3
noahcount New Member Join Date: Mar 2002 Location: alabama Posts: 5	been saving as default xls. Named ranges have not changed. What would added layers look like?

Mar 12th, 2002, 04:26 PM	#4
NateO Legend Join Date: Feb 2002 Location: Minneapolis, Mn, USA Posts: 9,704	Just new names being added....

Mar 12th, 2002, 04:38 PM	#5
noahcount New Member Join Date: Mar 2002 Location: alabama Posts: 5	Just a thought...how do you check/insure that the change history and/or undo stack are cleared at file closing?

Mar 12th, 2002, 04:43 PM	#6
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	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

Mar 12th, 2002, 04:47 PM	#7
noahcount New Member Join Date: Mar 2002 Location: alabama Posts: 5	no links other than the queries. Variables don't persist after close, do they?

Mar 13th, 2002, 10:11 AM	#8
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	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

Mar 13th, 2002, 05:17 PM	#9
noahcount New Member Join Date: Mar 2002 Location: alabama Posts: 5	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.

Mar 15th, 2002, 04:09 PM	#10
Joe Was MrExcel MVP Join Date: Feb 2002 Location: Central Florida, USA Posts: 7,541	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 ]