Page 1 of 2 12 LastLast
Results 1 to 10 of 13
Like Tree2Likes

cell formula vs VBA code

This is a discussion on cell formula vs VBA code within the Excel Questions forums, part of the Question Forums category; Hello everyone! As I looked through "Excel Questions" forum, I was amazed by variety of cell-formulas' usage (don`t know if ...

  1. #1
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    204

    Default cell formula vs VBA code

    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?

  2. #2
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,173

    Default Re: cell formula vs VBA code

    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?
    If posting code please use code tags.

  3. #3
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    204

    Default Re: cell formula vs VBA code

    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 by S_Wish; Jan 12th, 2017 at 04:01 AM.

  4. #4
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,173

    Default Re: cell formula vs VBA code

    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?
    If posting code please use code tags.

  5. #5
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    204

    Default Re: cell formula vs VBA code

    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 by S_Wish; Jan 12th, 2017 at 04:26 AM.

  6. #6
    Board Regular J.Ty.'s Avatar
    Join Date
    Feb 2012
    Location
    University of Warsaw, Poland
    Posts
    708

    Default Re: cell formula vs VBA code

    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.
    Rick Rothstein and taurean like this.
    My Excel uses ";" to separate arguments in functions, and they sometimes stay there when I copy-paste or insert a screenshot. In such cases please replace ";" by "," everywhere.

  7. #7
    Board Regular
    Join Date
    Jan 2017
    Location
    Ukraine
    Posts
    204

    Default Re: cell formula vs VBA code

    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.

  8. #8
    Board Regular J.Ty.'s Avatar
    Join Date
    Feb 2012
    Location
    University of Warsaw, Poland
    Posts
    708

    Default Re: cell formula vs VBA code

    Quote Originally Posted by S_Wish View Post
    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.
    My Excel uses ";" to separate arguments in functions, and they sometimes stay there when I copy-paste or insert a screenshot. In such cases please replace ";" by "," everywhere.

  9. #9
    Board Regular J.Ty.'s Avatar
    Join Date
    Feb 2012
    Location
    University of Warsaw, Poland
    Posts
    708

    Default Re: cell formula vs VBA code

    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.
    My Excel uses ";" to separate arguments in functions, and they sometimes stay there when I copy-paste or insert a screenshot. In such cases please replace ";" by "," everywhere.

  10. #10
    Board Regular Norie's Avatar
    Join Date
    Apr 2004
    Location
    Stirling
    Posts
    72,173

    Default Re: cell formula vs VBA code

    J. Ty.

    9 - I never knew that, what a revelation.


    PS What is a 'no UNDO'?
    If posting code please use code tags.

Page 1 of 2 12 LastLast

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com