On May 14, 7:29*am, "RB Smissaert"
wrote:
In general using VBA code instead of formula's will make your workbook
smaller and
also more robust and if possible I would always go for the first option, but
that may just
be a personal preference.

RBS

wrote in message

...



I just rewrote a program that I had pieced together over the years
with the goal of cleaning it up and making it smaller, less prone to
error, and easier to e-mail to associates that use it. I replaced some
VBA with formulas, and as a result of my work to get rid of
redundancies, etc., I expected the program to be a lot smaller. *To my
surprise and dismay, it is almost 3 times the size of the original
program. *I do not understand why.

Here is the situation. *The program uses about 20 worksheets, and I
checked the sheets for unused cells that have been activated - that is
not the problem. So, I have 5 questions that I have not been able to
find the answers to that may be related;

1) Does using VBA to make calculations when the user opens a worksheet
make for a smaller program than using formulas in Excel that
automatically recalculate?

2) In my program, the user enters control information on the first
page. *As the program runs, other pages constantly refer back to that
control information. *Is this, by any chance, causing the program to
grow in size? *Would it be better to use a macro and plant the control
characters somewhere on each page once the user has entered them?

3) I have read that formatting cells in the same manner saves space.
Then I read that you should format cells as a group rather than
individually to save even more space. *Is that correct? *If so, is the
space saving significant?

4) Does it reduce the program size if I format a lot of individual
cells the same way, even if the values and/or formulas in the cells
are different?

5) I am using quite a few loops in VBA - when I first wrote the
program I did not know how to do this, so I used individual statements
(copying them and then just changing the variable). *Do loops take
significantly more or less space than retyping the information and
manually changing the variable?

I do not know how to find the answers to these questions, and I really
appreciate anyone's help.- Hide quoted text -

- Show quoted text -

Thanks for your response - not quite sure why it would be more
robust? Do you mean that there are more things that can go wrong with
data input that will foul up an excel formula and not a VBA formula?
Just trying to understand. I appreciate it.