Is VBA The Same As Macros? If Not What Is The Difference? -

You often hear the terms VBA and macros used interchangeably when talking about the automation of Microsoft Excel. So are these things the same? Or is there a difference between them if so what is that difference?

Macros is a general term design to describe the automation of programs. The term macro is most closely associated with Excel primarily because many people use macros to automate many of their daily tasks. The language that has traditionally been used to automate Excel is VBA which is a programming language that is primarily used within the Microsoft suite of products to automate them.

However, the important distinction between the two is that macros can be created with a range of different languages with VBA being just one of them. For example within Google sheets there is a specific language called Google script which is used to automate tasks.

Additionally, within Excel itself VBA can still be used, however, due to the development of file sharing the use of VBA has become limited because it is not really designed to work with shared files. The limitation of using VBA in shared files is that they cannot be altered once the file has been shared.

As a result of this Office 365 now contains a second language that is designed for sharing files called Office. However there are significant limitations to office script at present compared to VBA.

How Are Macros Created In Excel

There are generally two methods that can be used to create macros which are the macros can either be recorded or the VBA code can be manually written. Recorded macros are significantly easier to create in Excel and only require a few minutes to learn the process.

However, recorded macros have significant limitations compared to writing the code manually but they are still extremely useful to automate many basic tasks. Many people use the recording of macros as the first step to learning VBA So it is therefore considered a useful stepping stone.

What Are The Advantages Of Writing VBA Code Vs Recording Macros?

The primary advantage of learning to write VBA code is that you can create more complex automated solutions to problems. The reason for this is because VBA coding allows you to include if statements and also call for user input. These two features mean that it is possible to design code that will respond based on user input which makes writing VBA significantly more powerful than recording it.

In addition to these features VBA is also capable of commanding other programs to interact with Excel. Common examples of how this is used is to send emails out via Excel rather than having to go to Outlook or calling results into Excel from an access database.

To see the power of learning to write VBA consider watching the video below which goes through a few examples of how VBA has been used to automate processes within a business environment.

Will Office Script Replace VBA. Which One Should I Learn?

VBA is a programming language that has been around in Excel for a very long time however Microsoft has announced that it will not provide any further updates for this programming language which is an indication that at some point in the future VBA will be discontinued.

However, it is quite likely that you will still be able to use VBA for at least 10 to 15 years, possibly significantly longer. This means that it is still well worth learning VBA because it has significant advantages over office script.

Office script is a relatively new addition to Excel that can be used to record macros in a similar way to what is currently done in the desktop version of Excel. However, there are significant limitations to office script at present which mean that VBA I can do much much more.

For example office scripts at present cannot control objects such as userforms which means that office scripts currently cannot get user input limiting its usefulness. The primary advantage of a script is that a design for the web version of Excel which can be used to share files readily.

However, the majority of companies still use the desktop version of Excel at this point in time. So while office scripts looks like it has a brighter future compared to VBA in the longer term it requires significant upgrades to it before it can fully replace VBA. As such I would highly recommend that you learn VBA coding because it will give you a huge number of advantages over recorded macros.

Does VBA Take Long To Learn?

VBA can take a little while to learn particularly if you do not have any programming experience at all. However, a lack of programming experience is not an insurmountable obstacle as I personally teach 15 year olds how to code VBA at a high school.

Typically, the strongest students can develop the capacity to code VBA in around 4 to 6 weeks. However, if you are trying to learn VBA code it is important to put time aside at home rather than trying to do it in a workplace as it is something that you really need to concentrate on.

When learning to write VBA code it is important to start learning some basic commands about how to reference cells, use variables and do loops. Once you have reached that stage the biggest learning curve is learning how to debug code. As soon as you can do that you can take code from the internet and modify it easily over time and you skill will improve exponentially.