File Size - VBA vs Excel Formulas
 

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.

File Size - VBA vs Excel Formulas
 

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.
--
Regards,
Eddie
http://www.HelpExcel.com


" wrote:

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.

File Size - VBA vs Excel Formulas
 

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.

File Size - VBA vs Excel Formulas
 

On May 13, 11:58*pm, " wrote:
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.

File Size - VBA vs Excel Formulas
 

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.

File Size - VBA vs Excel Formulas
 

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


"RJQMAN" wrote in message
...
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.

File Size - VBA vs Excel Formulas
 

On Fri, 15 May 2009 15:34:32 -0700 (PDT), RJQMAN
wrote:


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

File Size - VBA vs Excel Formulas
 

On May 17, 2:17*pm, FatBytestard
wrote:
On Fri, 15 May 2009 15:34:32 -0700
wrote:



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. *:-)

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.