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.
