Introduction

When beginning your coding career, you will certainly ask yourself questions like, “What exactly is a programming language?”, “Which programming language should I learn”, “What is the difference between language A and language B”, etc.

The answer to these questions and many others can be very challenging because there are an impressive number of programming languages on the market.

The world of computer programming is more or less like soccer with each programming language having supporters that are very vocal about the merits of the language that they are using. In this article, I will talk about programming languages in general before focusing on Python and VBA.

What is a Computer Program and a Programming Language?

A computer program is a detailed plan or procedure for solving a problem with a computer. More specifically, it is an unambiguous, ordered sequence of instructions necessary to achieve a solution.

The distinction between computer programs and equipment is often made by referring to the former as software and the latter as hardware.

A programming language is a set of commands, instructions, and other syntax use to create software. There are many programing languages on the market and they differ from each other by their syntax and purpose.

The History of Programming Languages

Programming languages have a long history, a product of the fact that the types of problems to be solved kept changing and became more and more complex with time.

The genesis of the computer revolution was in a machine. The first programming languages were by then machine-oriented. Thus, machine language was born. As you would imagine, machine language consisted of binaries – ‘0s’ and ‘1s’ representing the switches of the CPU.

Computer programming was reserved only to few experts who understood the structure of the CPU. Then came Assembly languages in 1947 that was easier for humans to understand and read. Almost no one these days writes programs in machine language, but assembly language is still used.

Programming languages such as FORTRAN, BASIC, PASCAL, COBOL, SIMULA, Smalltalk and C were derived from assembly language. These languages are big improvements over assembly language, but they still required you to think in terms of the structure of the computer rather than the structure of the problem you were trying to solve.

The famous C language for example, was designed primarily for the UNIX operating system. Many programming languages that followed such as C++, Java, C#, Visual Basic, PHP, Perl, Python, Ruby, etc., were influenced in one way or another by C, or any of the first ‘imperative’ languages mentioned earlier.

More importantly, programming languages evolve to solve real world problems. Therefore, the evolution of programming languages and the birth of new ones are either driven by industry, commercial or academic needs.

Unfortunately, programming languages have historically sparked almost bitter religious wars. The hatred of one language over another, is an unfortunate reality. Behind the development of programming languages, are large and powerful companies with various vested interests coupled with high rivalry. This rivalry tends to spread to the people working in those companies and to the industry at large.

Types of Programming Languages

As you might have learnt from the history narrated above, there are two types of programming languages: high-level and low-level languages

High-level languages

High-level languages are close to human language and are designed to be easy to be read and understood by humans. They are used by most programmers to write source code in a natural fashion, using logical words and symbols.

In a high-level language, you will have reserved words like If, Else, Do, While etc. or Symbols like <, >, ==, and != which are common operators.

The good news is that many high-level languages are similar enough making it possible for a programmer to easily switch from one language to another. Examples of high-level languages include C++, Java, Perl, and PHP.

High level languages can be grouped in 2 categories which are “compiled languages” and “interpreted languages”. The difference between the two categories is that with a compiled language, the source code must be compiled – converted to a machine language – in other to run, while an interpreted language can be run through an interpreter without being compiled.

Some examples of compiled languages include C++ and Java while Perl and PHP are interpreted languages. Generally, compiled languages are used to create software applications, while interpreted languages are used for running scripts, such as those used to generate content for dynamic or interactive websites.

Low-level languages

Low-level languages are more difficult to read than high-level languages. They are not designed to be human readable but are machine readable. They include assembly and machine languages.

An assembly language contains a list of basic instructions. An assembler can be used to translate the assembly code into machine code. The machine code, or machine language, contains a series of binary codes that are understood directly by a computer’s CPU.

The figure below gives a summary of the type of programming languages:

Visual Basic for Applications (VBA) Vs Python

Which programing language is better between VBA and Python? It is not unusual to read such question on the web. It is more appropriate to ask: which language is more suitable for what I want to achieve rather than knowing which language “is better.”

Computer programming languages are like tools in a toolbox. Different tools are designed to solve different problems. For example, suppose you want to drive a nail through a piece of wood. The ideal tool for this purpose would be a hammer. The same hammer would not be useful if you want to tighten a nut to a bolt, that will be the job a spanner.

Your preference for the hammer over the spanner will not in any way influence the specific use of each tool. Do you get the picture? This is just to say that the choice of a programming language is contextual.

Coming back to our topic of interest, I’ll make the following observations concerning VBA and Python:

• Phyton and VBA are both high-level languages.
• Phyton and VBA are both interpreted languages.
• Python is a General-Purpose Language (GPL) while VBA is a Domain Specific Language (DSL) made just for the Windows environment.
• As its name suggests, VBA is closely related to Visual Basic and uses the Visual Basic Runtime Library. However, VBA code normally can only run within a host application, rather than as a standalone program. VBA can, however, control one application from another using OLE Automation. For example, VBA can automatically create a Microsoft Word report from Microsoft Excel data that Excel collects automatically from polled sensors. It is worth mentioning that VBA is also implemented, at least partially, in applications published by companies other than Microsoft, including ArcGIS, AutoCAD, CorelDraw, LibreOffice, Reflection, SolidWorks, WordPerfect, and UNICOM System Architect (which supports VBA 7.1).
• Visual Basic for Applications enables building user-defined functions (UDFs), automating processes and accessing Windows API and other low-level functionality through dynamic-link libraries (DLLs). It’s actually a versatile language with many applications.
• VBA is limited to automation in Microsoft Office products, while Python can run under all sorts of operating systems, for a wide variety of purposes. Dropbox, for example is written in Python, as are large parts of Google’s software for instance.
• Python is an Open Source language: Unlike VBA, Python allows users to modify and alter the code in creative ways. What this means is that many libraries have been developed to aid in complex and creative tasks that would be impossible or limited in VBA. Anyone can create and develop a library to support Python, which means the possibilities are unlimited.
• Python and VBA can handle similar functions when it comes to automating, but Python is capable of dealing with much larger volumes of data than VBA. With Python, calculations are faster and more complex formulas can be handled as compared to Excel’s VBA.
• Python’s power comes from its libraries. Many of the basic standard libraries, or extensions allow it to perform highly complex tasks with just a few lines of code where Excel-VBA may take much more time and lines of code to perform the same work. Some libraries like plotly and D3 can visualize that data into interactive charts and graphs that are more creative and visually appealing than those of Excel. There are even libraries that incorporate Artificial Intelligence and machine learning, allowing Python users to create predictive and forecasting models that VBA-Excel cannot.
• Python has a vast community. Python is one of the most popular and growing programming languages. Its community is very active and continually contributes so that the knowledge base and depth of creativity is mined like no other. Stack Overflow is one of the largest online communities where questions are answered and solutions are delivered.

It is obvious from the observations above that Python is by far more powerful than VBA but like I said earlier, what matters is what you intend to achieve. There is no need killing a mosquito with an atomic bomb when a simple spray can do the job.

At times I find VBA-Excel more appropriate for a certain task and much more efficient than Python. If you want to build independent (standalone) applications, then learn Python. On the other hand, if you simply want to automate laborious and repetitive task in Office applications, then go with VBA.

Finally, if you have limited time, then you should definitely learn VBA. Not because Python is hard, but because picking up the basics of VBA is very simple.

Conclusion

When picking a programming language, it is unwise to love or hate one over another. By so doing, you are not only reducing your effectiveness as a programmer but also minimizing your earnings potential. Most of my earnings as a freelancer come from my skills in Excel and VBA. A word to the wise is sufficient.