How to Reduce the size of VBA Program
 

Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar

How to Reduce the size of VBA Program
 

Hi Shazad4u,

See Debra Dalgleish's suggestions for
resetting the used range of a worksheet
at:

http://www.contextures.com/xlfaqApp.html#Unused




---
Regards.
Norman


wrote in message
...

Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar

How to Reduce the size of VBA Program
 

You may want to look for shapes (pictures???) that are embedded in your
worksheets, too.



wrote:

Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar

--

Dave Peterson

How to Reduce the size of VBA Program
 

Two further suggestions:
1. Avoid sheet functions/formula's and instead use VBA.
2. Run the CodeCleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm

RBS


wrote in message
...

Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar

How to Reduce the size of VBA Program
 

On May 31, 6:07*pm, "RB Smissaert"
wrote:
Two further suggestions:
1. Avoid sheet functions/formula's and instead use VBA.
2. Run the CodeCleaner:http://www.appspro.com/Utilities/CodeCleaner.htm

RBS

wrote in message

...





Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar- Hide quoted text -

- Show quoted text -



Hi,

Pls clear to me about your suggession no. 1
1. Avoid sheet functions/formula's and instead use VBA.

what does it mean, I dont understand.

I used some auto sum function in worksheet module and I also used many
times range name in many sheets. i.e. B1: X36000 or C5:H1500 like
this.
is it wrong practive. for the filtration of records. or calculations.

pls help me.

Regards.


shahzad

How to Reduce the size of VBA Program
 

Nothing wrong with sheet functions/formula's, but they will make workbooks
larger.
To give a simple example, say we have values in the whole column A and in
column B we have =RC[-1]/2
in all cells. Saving this Wb will give a bigger file then doing instead:
Same in column A, nil in column B, but instead have VBA code that will do
the same, so something like:

Sub test()

Dim i As Long

For i = 1 To 2
Cells(i, 2) = Cells(i, 1) / 2
Next i

End Sub

Apart from making wb's smaller I also find VBA code easier to maintain and I
avoid worksheet formula's/functions
whenever I can.

RBS




wrote in message
...
On May 31, 6:07 pm, "RB Smissaert"
wrote:
Two further suggestions:
1. Avoid sheet functions/formula's and instead use VBA.
2. Run the CodeCleaner:http://www.appspro.com/Utilities/CodeCleaner.htm

RBS

wrote in message

...





Dear Sir,

I prepared a VBA Program having 7 UserForms, and 10 worksheets, and
Macros.
for the Engineering Store Inventory.

The size of my database is become 9.24MB, and one month data is feeded
near about 700 records.

I think this size is too much, it should not be 1 or 2 mb.

Because I prepared an other program having almost same functions and
its size is 500 kb.

You are requested to tell me how to reduce the size of my VBA Program,
so it work faster.....

Any suggession if you have please let me know.

Regards.

Syed Shahzad Zafar- Hide quoted text -

- Show quoted text -



Hi,

Pls clear to me about your suggession no. 1
1. Avoid sheet functions/formula's and instead use VBA.

what does it mean, I dont understand.

I used some auto sum function in worksheet module and I also used many
times range name in many sheets. i.e. B1: X36000 or C5:H1500 like
this.
is it wrong practive. for the filtration of records. or calculations.

pls help me.

Regards.


shahzad