Using vba in excel to calculate time -VBA test report to calculate time-

I recently exchanged emails with an Excel expert, and he concluded that VLOOKUP is the fastest search method in Excel. He said it was faster than index matching. To support this surprising conclusion, he sent me his test macro.

Macros are cleverly written, but there are two fatal defects. First of all, his test method is too expensive, which covers up the calculation time he wants to test. Secondly, the calculation method of indirect cost is different.

This is the reason why his conclusion is incorrect.

In this article, I will show you a macro to test the calculation time of alternative report design. Macros are simple, but they can work. You can download it here. Calculation timer? Macro.

The next article (volatile workbook for testing the calculation time of Excel lookup method) will explain how to ensure that the report can be really calculated when running these tests. This is an important consideration, because if you don't handle this step correctly, the workbook will not be fully calculated and the test may return wrong results.

Finally, the third article (the fastest search method of Excel: test results) will summarize the knowledge I have learned from the actual calculation time of VLOOKUP and INDEX-MATCH. You will find that the fastest way needs to use the right search function and the right search design.

Remember, however, that relative computing time does not always determine your spreadsheet design. Compared with fast but difficult design, slow and easy design may be a better choice.

Even so, when you make these trade-offs, it is helpful to know their relative calculation time. This is what this macro can give you.

Calculate timer macro * *

This is the content of Sheet 1 in CalcTimer.xls workbook. As you can see, I set two range names.

Loop specifies the number of times the macro loops during calculation.

The result contains the total number of seconds spent on all calculations in the loop.

When I create a new computing time test, I usually use two loops to run the first test. Once you see how long it takes them to calculate, you can estimate how many cycles are actually needed. On the other hand, if I enter a lot of numbers at the beginning, I don't know whether Excel needs to wait five minutes or five hours to complete the test.

This is the CalcTimer macro. You can learn more here.

This macro is really simple. It only loads the gStartTime variable for the number of seconds that have passed since midnight. It counts the number of times specified by Excel. Then subtract the start time from the current time to calculate the calculation time.

Finally, it writes the calculation time into the worksheet.

In the next article, I will explain how to set up data and worksheets to test the search method.

Marked as: index matching, VBA, VLOOKUP