cell formula vs VBA code

S_Wish

Board Regular
Joined
Jan 4, 2017
Messages
216
Hello everyone! As I looked through "Excel Questions" forum, I was amazed by variety of cell-formulas' usage (don`t know if named it right. its smth like writing in c1 "=a1+b1") along with VBA codes.
What is the advantage of cell formulas? only time? or there is something else?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
What exactly are you referring to?

Using code to enter a formula in a cell?

Using code to do calculate the result of a formula and putting that in a cell?

Something else?
 
Upvote 0
I mean is it neccessary to study in cell formulas if I can write all I need using VBA? for example, while working with huge variety of data? I read in some books that using "native" excel formulas in VBA codes instead your own VBA formulas saves some time. but what about long and hardly understanding cell formulas vs pretty simple in understanding "how it works" VBA modules?

Question to people who use cell formulas is: will I recall what it does in a year?
 
Last edited:
Upvote 0
If you mean to do all calculations in code and only have values in cells here's a question for you.

Will I know how the value in that cell was derived, what calculation it represents etc. next week, never mind next year?
 
Upvote 0
well, making modules, I make comments what it does generally and for some operations inside, so I can easily understand what it is doing
Using cell-formulas, it is sometimes hard to recall what it does, when it has like 10 links to other cells, especially to other sheets.
but working with ~500R/~40C, I saw that my modules started working, khm.. 5-10 seconds. and I see, that it is possible to make operations in cell-formulas and maybe they would become faster?
Is it like: I loose clarity, but gain rapid data processing?

PS: just wondering how you all work with cell formulas and make them clear to you. Need some advice, maybe)
 
Last edited:
Upvote 0
Let me make a few comments.


0. If you do not want to use formulas at all, is there still any good reason to use Excel? I would then keep my data in a text file and write my code in Python or any other language which is nicer, faster and more user friendly than VBA.


1. VBA content is not transferrable between systems. Even VBA on Excel for Mac is not 100% equivalent to Excel for Windows. Other spreadsheets typically do not support VBA at all (even MS Excel Online, MS Excel for Android, MS Excel for Windows Phone do not do it). So if you write VBA, you are restricted to Windows on a PC forever.


2. Macro viruses are becoming popular again, so others might be reluctant to accept and open your spreadsheet, if you decide to share it.


3. It is not easy to write in VBA a full equivalent of an Excel function. It typically handles in a consistent, ducumented manner all atypical inputs, including Excel error values, texts where numbers are expected (and vice versa), incorrect input range sizes, etc. Recreating all that in VBA takes time and effort. Implementations of Excel functions underwent years of testing by millions of users, which guarantees they compute correctly. You will never be able to match this level of testing for your own code.


4. An algorithm expressed by formulas yields not only the final result, but also its own trace of computation, which allows the user to verify the reason why an unexpected result is computed and find its provenence. This does not work for VBA.


5. Functions are much faster than VBA. Moreover, an algorithm expressed by many fomulas in many cells is exectued in parallel by Excel, utilizing as many cores as you permit it to (the defalut is to use them all). VBA is generally sequential and uses only one core.


6. Calculations expressed by Excel formulas benefit from built-in modification mechanisms. E.g., if you change the layout of your data by cut and paste operations, deletions and insertions of rows/columns, formulas will be automagically modified to reflect the changes. This does not work for VBA macros.


7. Formulas are recomputed automatically (unless you turn this off), so it is impossible to change the data and forget to re-run macros to modify the results of computations.


8. Formulas computing results in a spreadsheet can easily be (re-)used for automatic data validations or for conditional formatting. You cannot use VBA macros for these purposes without considerable changes.




Comments 6, 7 and 8 apply to macros. If you write your code in the form of user defined functions (UDFs), you will be able to benefit from those mechanisms.

Best,

J.Ty.
 
Upvote 0
Thanks for such a detailed explanation, J.Ty!
Of course I use excel sheet functions for previous calculations. And talking abouttext files - the only reason I use excel instead fortran is to build graphs. They are good and takes little time to build.
Happily, VBA macroses on Excel are used only by me and coleages with Windows on PCs.
I am very glad to find the answer on one of questions that bothered me (5th). So I should try to use more functions on sheet. And what if I got a UDF? will it be done using 1 core, or as many as allowed?

Sergej.
 
Upvote 0
Thanks for such a detailed explanation, J.Ty!
I am very glad to find the answer on one of questions that bothered me (5th). So I should try to use more functions on sheet. And what if I got a UDF? will it be done using 1 core, or as many as allowed?
Sergej.

Dear Sergej,

I suspect it will use all available cores, but admit not making experiments myself.

And you should know that an Excel formula is likely to to be 100 times faster than VBA, while using many cores gives you only speedup by a factor of 4 or 8, depending on the number of cores and other factors. So this will not offset the decrease of speed.

However: I might be interested to offer you help in expressing your computations by spreadsheet formulas. I do it for research purposes, so I do not expect to be paid in any form. Potentially I might want to publish a research paper about the experiences gathered by doing it.

What do you think about it?

J.Ty.
 
Upvote 0
One more comment, for those who might read this thread in future:

9. There is no UNDO for operations performed by VBA macros.

J.Ty.
 
Upvote 0
J. Ty.

9 - I never knew that, what a revelation. :eek:


PS What is a 'no UNDO'?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top