File Size - VBA vs Excel Formulas

[RJQMAN]

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.

 

[HelpExcel.com]

I did a project where this kept happening. My DBA actually figured it out,
when it hits me, I'll post it here. I "think" it had something to do with
saving it as an XLA and then a spreadsheet. None of the causes you posted
looks problematic.

 

[RB Smissaert]

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

 

[RJQMAN]

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.

Still searching for the answer. I just do not understand.

 

[RJQMAN]

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













- 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.

 

[RB Smissaert]

It is just that it is more likely that users will mess up sheets than the
VBE.
As for size: compare a large range of formula's with a few lines of code.

RBS

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













- 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.

 

[FatBytestard]

Still searching for the answer. I just do not understand.

Perhaps an excel "formula" is really an elaborate VBA script so that
your construct of a particular function will always have a lot more
overhead than using VBA directly. (I do not know. It is likely direct C
constructs or assembler even) (see, I have no Idea).

Chewing right on the bone, as it were, as opposed to having to gnaw on
the gristle for a while first.

 

[RJQMAN]

  Perhaps an excel "formula" is really an elaborate VBA script so that
your construct of a particular function will always have a lot more
overhead than using VBA directly. (I do not know. It is likely direct C
constructs or assembler even) (see, I have no Idea).

 Chewing right on the bone, as it were, as opposed to having to gnaw on
the gristle for a while first.  

I tried re-writing a major section of the program in VBA - it seemed
to lower the program size slightly - about 10% - but not as much as I
had hoped for. Just a FYI. Thanks for responding.