VBA (Visual Basic for Applications) is a programming language built by Microsoft for use within the Office realm of applications, including Word and Excel, amongst others. Python is an interpreted, high-level, object-oriented programming language with dynamic semantics.
Python is not restricted to working inside Microsoft Office products and can be used to build almost any software application.
Should you learn VBA or Python?
Both VBA and Python are worth learning in their own right. If you spend a lot of time working on Office documents, or if you have critical office documents, then VBA is well worth learning, especially as it is quite a straightforward language in a closed environment.
Python is worth learning if you want to work on software applications outside the Office suite of products. It also can manipulate Office products using different libraries like python-docx, for example, that can manipulate Word files.
Python has a greater barrier to entry, in my opinion, due to the environment required to get Python up and running and learn how to execute Python scripts. This barrier is lower than other non-interpreted programming languages like Java or C#, which require far more knowledge to get a minimal application running.
In terms of the job market, Python jobs are more in demand than VBA jobs. However, a skilled VBA developer can add a tremendous amount of help to any team that uses Office products.
In the data science field, Python is the go-to language in use. This field is quite broad but also requires other proficiencies besides knowing how to program in Python.
What are the differences between Python and VBA?
Python and VBA are both programming languages, and they each have different syntaxes and different ways of defining methods. Among the many differences in the languages, Python relies on a specific indentation in source files to be syntactically correct, whereas VBA doesn’t.
Here you can see an example of manipulating a sheet in an Excel document using both Python and VBA.
from openpyxl import load_workbook
wb = load_workbook('test.xlsx')
ws = wb["sheet1"]
ws[‘A1’] = ‘Text’
ws[‘A2’] = 1
The above code will put the word Text in cell A1 and the number 1 in cell A2.
VBA can use third-party libraries when building solutions. However, Python has a much larger ecosystem and easier methods of finding packages related to the task you’re trying to accomplish.
Is Python faster than VBA?
Comparing the speed of Python and VBA takes a lot of work. As VBA only works in the context of Microsoft Office applications and Python can work on basically anything, the only real test would be seeing how quickly Python can work with spreadsheets and Word documents and how quickly VBA could perform the same tasks.
Unfortunately, there is another layer of ambiguity when testing which language is faster. That ambiguity comes from the library/libraries chosen to work on the Office document. Some libraries will be much faster than others, and on top of that, there may be additional ways to speed up further the Python script that are more complex than the average developer might want to implement.
How do you program in Python?
To write Python scripts, you must go to python.org and follow the installation and setup instructions. There is a getting started section for beginners that will take you through the download, installation and simple script creation.
Once set up, you can open up the command line or terminal in your operating system and invoke the Python command line. Once this is open, you can fire off Python commands, one by one, and the Python interpreter will execute these commands.
To write a script in Python, write a text file with all your Python commands in order. Once you have completed the text file, you can run the script by calling Python <script_file_name.py> from the command line and again, the Python interpreter will process your script file.
How do you program in VBA?
To write code in VBA, you must first access the Visual Basic Editor. You can access the editor from the tools menu under the sub-menu Macros from within any Microsoft Office application.
Once you’ve opened the editor, you can then view your project in the project window. If you double-click any of the Microsoft Excel Objects, it will open the Code window. In the code window, you can then write your functions or subroutines.
For some example Excel functions, you can check out our other article here that discusses the difference between VBA and Google Apps Script.
Is VBA more difficult than Python?
Due to the larger scope that Python covers, Python would have to be deemed the more difficult ecosystem to learn. The language specifics of Python are probably easier to learn than VBA, as Python was developed partly for ease of use and for writing code to be much higher level and closer to English than VBA.
Overall, Python would be more difficult to work with over time as once you’ve understood the basics of VBA and the components of the Office API, not much will change when writing VBA. Python will always have new APIs, which will go through many updates requiring more learning at each update.
Conclusion
VBA is a tailored version of Visual Basic for Microsoft Office applications. Python is a full-featured object-oriented interpreted language created for ease of use and used across many applications.
Whilst both VBA and Python can work with Office documents. Python can reach further and do more due to the many third-party libraries built for it. Data science, machine learning and AI are often performed through Python applications.
VBA and Python are worth learning, and VBA can have a much lower barrier of entry to new developers just wanting to automate or manipulate Office documents. The job market for Python developers is larger than that of VBA application specialists. However, this is mainly due to specific roles requiring Python to complete instead of more generalist roles that might include working with VBA.