Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. :-) |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I shrink a file size w/ a pivot table and many formulas? | Excel Worksheet Functions | |||
Unusual File Size in Excel file | Excel Discussion (Misc queries) | |||
code to check file size everytime an Excel file is opened | Excel Programming | |||
Unable to open excel file and when view the file size show as 1 KB | Excel Discussion (Misc queries) | |||
How to get the size of the excel file, a sheet size (in bytes)? | Excel Programming |