How to Reduce the size of VBA Program

[shahzad4u_ksa]

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

 

[Norman Jones]

Hi Shazad4u,

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

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

 

[Dave Peterson]

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

 

[RB Smissaert]

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

 

[shahzad4u_ksa]

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














- 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

 

[RB Smissaert]

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

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














- 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