Formula vs VBA, who should I choose?

Students often ask this question: Is the formula running fast, or is VBA running fast? When should I use a formula and when should I write a macro using VBA? …… Talk about my personal understanding today.

First of all, it must be stated: no matter from the perspective of running speed, function level or writing convenience, formula and VBA are different, and there is no absolute who wins and who bears. So let’s start from these three perspectives and look at their respective strengths and applicable situations.

(1) running speed

If you execute a single statement (a formula), you can basically guarantee that Excel’s own formula runs fast. Because these formulas are all followed by Excel, developed in C/C++ and compiled, and the VBA code we wrote still has to be translated to be executed, so the execution speed must be slower. So for simple calculations that can use the formula to satisfy all the requirements, we don’t have to write VBA code.

But from another point of view, because there is no such mechanism as “clearly declaring the data type” when using the formula (the knowledge about the data type is explained in detail in the “Improvement” 1-7 episode), sometimes the formula must be executed at the time of execution. Type judgments or even “guessing” result in reduced efficiency. In our own VBA, we can clearly understand the characteristics of the data we want to process, so we can write code in a targeted way to improve efficiency.

In addition, if there are a lot of formulas in the worksheet and there may be associations with each other (such as a change in the content of one cell will cause other cells to change), then the default setting (that is, set to “automatic recalculation”) Every time you open a workbook workbook or do other things, all the table formulas are automatically recalculated. When the amount of data is large (for example, there are tens of thousands of cells with formulas), the worksheet will take a long time to display, even giving a feeling of crash. I believe many of my friends have encountered this situation. The code written in VBA, although the single execution speed may be slower than the formula, but the advantage is that the user can specify when to recalculate (such as when clicking the button or when selecting “run macro”), and can be in accordance with their own needs, in the program The Auto Recalculation setting is automatically turned on or off during operation, which saves a lot of unnecessary recalculation time.

(2) Functional level

In fact, for the main requirements of computing, most of the functions that VBA can do can be done using formulas (the cumbersomeness of writing long nested formulas is not considered here). But some functions can only be achieved through VBA, mainly in “format” “interaction” “cross-file” and “external interface” several aspects. The so-called “format” refers to the operation of deleting a column, adding a worksheet, etc.; “interaction” means that it is similar to “running by pressing a button”, “a message box pops up when a user enters a number in a cell” And the requirement to enter data using the Windows-style form interface; “cross-file” is similar to summarizing all text files or excel files under a folder. These operations are obviously difficult to complete using formulas. The external interface refers to the communication between Excel and external data sources such as databases, the Internet, etc., as well as calls to external functions like the Windows API. For example, suppose we need to extract web page information in Excel, then it is very difficult to use the formula. To take a step back, even if you don’t consider the problem of web page downloading, the analysis of web page text strings can be difficult to imagine if you only use various string formulas. But in VBA, combined with tools such as regular expressions, we can easily do this.

There is also a situation where it is difficult to use a formula, that is, when we need to modify the cell itself based on the current content of a cell. In this case, writing the formula directly in this cell is not feasible because of the “circular reference” problem.

In addition, as mentioned in (1), for some more complicated operations, although the formula can be used, the use of VBA code allows us to design high-efficiency algorithms and codes for the characteristics of the problem, so that the execution speed Better.

(3) Writing convenience

In the end, it is the development efficiency of the formula, or the development of a VBA code is faster, this can only be seen by the benevolent seeing the wise. If the requirements are not particularly complicated, it is certainly easier and faster to write the formula. However, if you can gradually adapt to VBA code styles and common routines (such as traversing each row and column in a double loop, locating cells with cells, etc.), the difference will be smaller and smaller. Especially when the requirements are more complicated, the program code seems to be more clear and easy to understand, and it is easy to troubleshoot. However, several layers or even dozens of brackets in the formula are easy to make people feel dizzy.

(4) Applicable software

Finally, one of the most insurmountable problems of the formula is that the formula belongs to Excel only, and VBA belongs to the entire Office, and even other non-Microsoft formula software (such as the previous versions of AutoCAD). Therefore, if we need to modify the format of multiple word files in batches, or automatically transfer some content in Word files in Excel, then it is very difficult to use formulas.

In summary, our conclusion is:

(1) If the demand is simple and can be easily expressed by formula, then resolutely not write VBA! Because of this situation, the formula can win regardless of development speed or speed.

(2) If the task is complex, you need to write a lot of layer formulas; or to write a large number of interrelated formulas in the worksheet in order to complete the task, then it is best to use VBA, because the code is more clear and easy to read, and has the opportunity to achieve targeted efficiency. Optimization may also save a lot of unnecessary automatic recalculation time.

(3) If it involves operations such as formatting, graphical forms, files, and external interactions, I’m afraid I can only write VBA because it is likely to go beyond the capabilities of the formula.

(4) If it involves an Office function call other than Excel, the formula can only give way to VBA.

Of course, if you simply deal with data, there are more powerful tools in excel than formulas, such as PivotTable, Power Pivot, and Power BI. They can help us with more data analysis work that was previously only possible with VBA. As long as you look at the above several perspectives, you can know when it is appropriate to use these tools and when it is appropriate to use VBA.

In short, that sentence: Tools are thousands, suitable for the king!