VBA Vs. Python Speed

(Note:  We may earn commissions on products/services you click on.  This is at no extra cost to you.)

Table of Contents

Both VBA and Python are very useful for data analysis. VBA is a programming language used in Excel, while Python is a high-level, general-purpose, and open-source programming language created by Guido van Rossum.

Click this affiliate link to register for Microsoft Excel VBA Training.

 

VBA vs Python speed
mobile-phone-1513945_1920 (1)

Unlike VBA, using Python for data analysis is cleaner and provides better version control. As a result, most firms are transitioning from VBA to Python. More so, being an open-source enables many users to modify and alter the Python code in creative and more meaningful ways.

Several data analysts and software developers today consider Python a worthy replacement tool for VBA due to the benefits it offers, but the question that is often asked is, “Is Python faster than VBA?”

This is because when it comes to executing actions and performing tasks, faster is always better.

Click this affiliate link to register for Python Certification Training.

VBA Vs. Python: Speed

In most operations, it’s undeniable that VBA is faster than Python. From repeated observations from using both plain VBA and plain Python, it’s obvious that Python is about two to three times slower than VBA. However, this is because both are not called directly from their native environment.

Both VBA and Python are very good languages but have different purposes. VBA is great for Office automation and is typically used for developing Access and Excel apps that not only save time but also enable keeping up with and searching through data very simple for the users. 

Python, on the other hand, is also great for many purposes but not for Office automation with the same ease or performance as using VBA. Hence, in terms of speed, both Python and VBA are fast when they are used for their intended purposes.

More so, for non-intensive mathematical operations where speed is very important, VBA is faster as you would spend significant extra coding time to use Python. However, for intensive mathematical operations, Python offers a fairly easy-to-use and efficient compiled code that is faster than VBA.

Click this affiliate link to register for Microsoft Excel VBA Training.

How Is Speed Measured?

Now that it has been ascertained that both VBA and Python can be very fast when used for their intended purpose but VBA being faster in most applications, let’s examine how the application speed is measured for both programming languages.

Whether you have prior experience under your belt or are new to programming, there will always be a time when you need to measure the amount of time it takes for a procedure or task to complete. This also applies to procedures executed on VBA and Python.

How to Measure Speed and Performance for VBA?

In VBA, this can be done through two major ways using:

∎ The Intrinsic Timer Function

∎ timeGetTime API function (Winmm.dll)

The latter method was used to create a Stopwatch class in VBA. It can measure code for longer periods or performance more accurately than the VBA Timer function.

Intrinsic Timer Functions

VBA has many built-in time functions (members of VBA.DateTime) as shown in the figure below:

Irrespective of how much you want VBA date or time values to track elapsed time, they are not created to do so. Hence, the VBA date and time values store a particular point in time, not a span of time, and they cannot be store later than 24hrs in a given date or time variable.

The Stopwatch Class

The stopwatch class utilizes the “timeGetTime” API Function rather than the intrinsic VBA Timer function. This makes the stopwatch class more accurate and can be used to measure longer periods.

How to Measure Speed and Performance of Python?

You can use either time.time() or time.clock() functions to measure time off a program’s execution. The python docs state that these functions should be used for benchmarking purposes.

Example:

Output:

This will give the output −

The timeit module can be used to get proper statistical analysis of the execution time of a code snippet. This module runs the snippet numerous times and then informs the time taken for the shortest run. This module can be used as follows:

Example:

def f(x):

return x * x

import timeit

timeit.repeat(“for x in range(100): f(x)”, “from __main__ import f”, number=100000)

Click this affiliate link to register for Python Certification Training.

Output

This will give the output –

Factors That Make Excel Quick

This section looks at factors that speed up VBA and make macros run faster.

1. Turn Off Automatic Spreadsheet Calculation

If a new value is entered into an Excel worksheet cell, Excel will recalculate all the other cells that refer to this worksheet cell. So, if the VBA macro is writing values into the worksheet, VBA will have to wait until the worksheet is finished recalculating each entry before it can resume. This takes lots of unnecessary time, but you can turn off this time-wasting automatic calculation by entering this command at the beginning of the macro:

Application.Calculation = xlCalculationManual

If you need to recalculate spreadsheet values while the macro is running, use any of the following commands: 

“Calculate”…. this recalculates the entire workbook. 

“Worksheets(“sheet1”).Calculate”…. this recalculates only a specific sheet. 

“Range(“A1:C5”).Calculate”…. this recalculates only a specific Range.

When you need to turn back on the automatic calculation, use the following command:

Application.Calculation = xlCalculationAutomatic.

2. Turn Off Screen Updates

Each time VBA macros write data to the Excel sheet, it also causes the screen image to refresh. Refreshing the screen image caused the system performance to drag. Therefore, use this command to turn off screen updates.

Application.ScreenUpdating = FALSE

After executing the VBA macro, you should use this command to turn on screen updates.

Application.ScreenUpdating = TRUE

3. Reduce the Traffic Between VBA and The Excel Worksheet

Immediately the VBA macros begin to run; it’s important to stop all unnecessary references to the Excel worksheet. Making reference or grabbing data from the Excel worksheet will slow down performance. More so, if possible, do not read or write worksheet data within loops. It’s faster and better to read data just once and save it in memory than re-reading it every time.

4. Avoid Using Variants in Formulas

Avoid declaring a numerical variable as the Variant except where necessary. Based on repeated tests, the variable types from fastest to slowest in mathematical equations are – Constant, Single, Double, Long, Integer, Variant.

5. Avoid Evaluating Strings

Strings (text) are slow to evaluate. Try to avoid evaluating Strings in code in this manner:

Select Case Gender

Case “Male”

(insert code here)…

Case “Female”

(insert code here)…

Case “Unisex”

(insert code here)…

End Select

Enumeration assigns a constant numerical value to a variable. VBA is able to process enumerated values rapidly while maintaining readable code. Enumeration will assign default numerical values or specific values.

Public Enum enumGender

Male = 0

Female = 1

Unisex = 2

End Enum

Dim Gender as enumGender

Select Case Gender

Case Male

(insert code here)…

Case Female

(insert code here)…

Case Unisex

(insert code here)…

End Select

Boolean operators are simply TRUE or FALSE switches that process really quickly. In the example below, bMale, bFemale, and bUnisex are Boolean variables. The Boolean code is roughly ten times faster than using Strings.

If bMale Then

(insert code here)…

ElseIf bFemale Then

(insert code here)…

ElseIf bUnisex Then

(insert code here)…

End If

6. Don’t Select Specific Worksheets Unless Necessary

You generally do not need to use the Select command to read or write to a worksheet. It is about 30 times faster not to Select a worksheet.

Avoid this:

Worksheets(“sheet1”).Select

Amount1 = Cells(1, 1)

Do this instead:

Amount1 = Worksheets(“sheet1”).Cells(1,1)

7. Avoid Overuse of Statusbar Updates

VBA handled mathematical processes faster than it can display or update the StatusBar. Writing to the StatusBar is a further example of traffic between VBA and Excel. Writing one out of every 100 scenarios to the StatusBar is about 90 times faster than writing every scenario to the StatusBar.

8. Avoid Unnecessary Math

Each mathematical operation slows down the process. So, if your coding uses four numerical within the loop, your operation will be about four times slower. Also, bear in mind that exponentiation is slower than addition, subtraction, multiplication, or division.

Click this affiliate link to register for Microsoft Excel VBA Training.

9. Don’t Copy and Paste

Finally, avoid the Copy and Paste (or PasteSpecial) functions. They are slow. It is about 25 times faster to avoid them and instead use the following command to copy and paste required values.

Range(“A1:Z100”).value = Range(“A101:Z200”).value

Factors That Make Python Slow

Python is a scripting language higher than VBA. This means Python can abstract the details of the computer system from you, i.e., pointers, memory management, etc. It also enables you to write programs that are closer to how human beings reason. This adds to its slowness.

Truly, VBA macros run faster than Python code if only the execution time is the measure, but if the development time is included, then Python may perform better.

Also, another reason why Python code seems to execute more slowly is that it is interpreted at runtime rather than being compiled to native code at compile time. Hence, the reason why Python is slower is that it is an interpreted language.

Click this affiliate link to register for Python Certification Training.

VBA Vs. Python for Data Analysis

Excel is a common tool used for data analysis, and it is frequently used to carry out analytical operations in the financial industry. Nevertheless, Excel can be more complex to use since it requires a VBA application. VBA macros can be very complex to operate. As a result, they make Excel very hard to use when dealing with many operations during data analyses.

∎ Python Is More Powerful

Python and Excel are able to handle similar functions when it comes to data automating and analysis. However, Python is capable of handling much larger volumes of data than Excel. Also, calculations are faster, and formulas can be more complex and specific compared to Excel’s VBA. 

Many of the basic standard Python libraries or extensions, including NumPy and Pandas, can perform data automation and analysis with a few lines of code, whereas Excel may take ten times as many commands to perform the same work. 

Other Python libraries such as Plotly and D3 can visualize that data into interactive charts and graphs that are more creative and visually appealing than the few charts and graphs that are at Excel’s disposal. 

Python also comes with a wide range of preinstalled libraries, which saves time for developers and analysts who would otherwise be required to create projects from scratch.

Python’s power comes from its libraries. Python also has libraries that integrate AI and machine learning, enabling users to create forecasting and predictive with Python that Excel users can’t create.

∎ Functional Integrations are better in Python

A good data analysis software should integrate with other types of analytical and non-analytical software. Python fits well into this category since it integrates well with other programs. Users are able to import and export various kinds of file formats into Python.

To illustrate, Python is compatible with SQL syntax and can also run it within its framework so as to extract data and tables to its environment. The Python environment is also very efficient at automating tasks, like importing data and writing analyzed data to Excel or CSV functions for the purpose of data analysis.

Moving from using Excel to using Python is a justifiable act when considered from the point of view of functional integration. Firstly, Python is user-friendly, and both beginners and experienced analysts can use the language with ease. Excel uses VBA language, which is a personalized platform that makes use of macros to automate tasks for data analysis.

Using macros to automate tasks is more complex than the automating tasks in the Python environment. And the fact that Python can be easily integrated with other programs makes it more suitable for data analysis.

∎ Python has Improved Code Compatibility

Data analysis code in Python can be stored as scripts for further manipulation and reuse. Python code is compatible and reproducible, which makes it suitable for further manipulation by other contributors who are running independent projects. Unlike with the VBA macros used in MS Excel, Python generates a cleaner data analysis and also offers a more superior version control.

Even better is Python’s ability to be consistent and accurate in the execution of code. Other users can also replicate the original code and still maintain a smooth execution at the same level as per the original code. The ability to reproduce code makes Python a lot more efficient than Excel because users can bypass the initial coding process and start off with a framework that is already functioning.

∎ Python has Higher Scalability and Efficiency

Data scientists have a preference for Python over Excel due to its ability to handle large data sets, as well as incorporate modeling and machine learning. Though VBA seems faster, when a large amount of data is to be handled, it takes a longer time for Excel to complete calculations than Python. If data is loaded onto the two programs at the same time, Excel will lag behind Python since it’s not built to handle large amounts of data.

More so, it takes a longer time for Excel to import data created with other analytical tools. It is even slower still when the amount of data being imported into the spreadsheet is large. Python bridges the gap because it is a more efficient tool for importing and exporting data in different formats, which makes it ideal for data scraping. 

When compared to Excel, Python can handle complex calculations, automating tasks, and data pipelines better. It also comes with a wide pool of manipulation tools and libraries.

∎ Python Has a Vast Community

Python code supports numerous extensions and libraries, and while Excel also has some to a certain extent, Python is one of the most popular and developing programming languages with several libraries that exceed tho of Excel. 

But what happens if you’re stuck? What happens if you have problems with your code? Need to generate a chart? There’s Stack Overflow for that. The community is very active and continually makes contributions so that the knowledge base and depth of creativity are mined like no other. Stack Overflow is among the largest online communities where questions are answered, and solutions are delivered.

Nevertheless, there are times when Excel is more appropriate for a certain data analysis task and much more efficient than Python.

∎ Python Handles Larger Data

Although Python is considered a more efficient data analysis tool for complex calculations and large volumes of data, Excel still enjoys greater popularity than Python, and it is used by many people in financial analysis.

While Excel is not the ideal application for handling large volumes of data, it is a more suitable tool for those organizations that have small volumes of data that need simple calculations. 

On the other hand, Python is more efficient than Excel when the organization handles large volumes of data that require automation to produce results within a short period.

xlwings Vs. VBA

Are you a modern Excel developer but want to enjoy some of the efficiencies of Python in Excel, then xlwings is an innovative tool for you. With xlwings, you can use Python to make working in Excel faster. xlwings enables users to take advantage of the combined power of Python and Excel.

xlwings is a Python library that makes certain data analysis features of Python available in an Excel instance, which includes support for NumPy arrays and pandas Series and DataFrames. Like any other Python library, xlwings can be installed via common methods such as pip or conda; however, you can access the documentation for xlwings here if you require additional details.

Xlwings has four major object types, these are (in decreasing hierarchical order): App (in this case the MS Excel), Book, Sheet, and Range.

With xlwings, you can write Excel tools with Python instead of VBA and call your code directly from within Excel, for example, via a button on the sheet. This can also work well when prototyping web apps in macOS and Windows.

Here are what xlwings enables you to do in Excel:

∎ Automate Excel from Python, for example, to produce reports or to interact with Jupyter notebooks.

∎ Write macros in Python that can be run from buttons in Excel, for example, to load in data from an external API or database.

∎ Write UDFs (user-defined functions) and leverage the power from, e.g., NumPy, Pandas, and machine learning libraries.

∎ Remote control Excel, e.g., on an Eikon or Bloomberg terminal or Windows Server via the REST API.

Click this affiliate link to register for Microsoft Excel VBA Training.

Speed of User Versus Speed of Processor

Whether you’re using Python or VBA, the speed of the user is not really a determining factor in the speed of operation or efficiency of data automation and analysis. However, the speed of the processor is very important.

Normally, all Python programs run as a single process and use a single CPU. If your computer was manufactured during the last ten years, there’s a good chance it has four or more CPU cores. Therefore, about 75% of your computer power is left unused while you wait for your Python program to finish running on a single core.

The same thing goes for Excel VBA too. VBA macros cannot take advantage of multiple cores; they only run in a single thread. Execution and calculation time is generally dependent on the speed of your processor. Hence, the speed comes down to how good your processor’s single-threaded performance is.

If your single-thread processor speed is low, it will negatively affect macro performance and vice versa. Any computer with a recent i5-8xxx processor will be about 100% faster than the i3-2330M in macros and about 200% to 300% faster in general calculation performance.

More so, 4 GB RAM is low these days. Always aim for at least 8 GB RAM.

Click this affiliate link to register for Python Certification Training.

Conclusion

From the post above, you have seen that VBA is faster than Python in terms of execution time. But this doesn’t mean that VBA is better than Python. In fact, Python is more useful and valuable than VBA. Coupled with the power of xlwings, you can leverage the power of Python from and with Excel.

BOTi offers certification courses in Excel VBA and Python for those looking to take their careers to the next level in the data analysis and programming fields. Contact us today to keep learning and advancing your career.

Click this affiliate link to register for Microsoft Excel VBA Training.

 

Luis Gillman
Luis Gillman

Hi, I Am Luis Gillman CA (SA), ACMA
I am a Chartered Accountant (SA) and CIMA (SA) and author of Due Diligence: A strategic and Financial Approach.

The book was published by Lexis Nexis on 2001. In 2010, I wrote the second edition. Much of this website is derived from these two books.

In addition I have published an article entitled the Link Between Due Diligence and Valautions.

Disclaimer: Whilst every effort has been made to ensure that the information published on this website is accurate, the author and owners of this website take no responsibility  for any loss or damage suffered as a result of relience upon the information contained therein.  Furthermore the bulk of the information is derived from information in 2018 and use therefore is at your on risk. In addition you should consult professional advice if required.