What is VBA coding for Excel

Excel-VBA explained in a nutshell

VBA stands for Visual Basic for Applications and is a programming language that can be used to control both Excel and the other Office programs. For you as a user, VBA programming means a more efficient way of working. The programming language automates the workflows of Microsoft Office products.

Subscribe, thumbs up and your questions in the comments 😉

2) Programs you can control with VBA

  • Microsoft Office applications such as Microsoft Excel, Microsoft Word, Microsoft PowerPoint, Microsoft Outlook
  • Project, publisher
  • Visio, Access

3) For what purposes can you use VBA?

  • Automation of regular work steps
  • Construction of Excel tools (e.g. offer & invoice generator, project management tool, etc.)
  • Exchange data, tables and diagrams between Excel and the other Office programs

4) When should you use VBA?

VBA code can be quickly produced and adapted using the macro recorder, but you should ask yourself the following questions before you automate tasks with VBA:
  • How long does the task take if I do it by hand using Excel?
    Tasks that take up a lot of time due to repetitive work steps can often be done faster with VBA.
  • Is it a one-time task or does it repeat itself? How often does this task have to be done? 1x week / 1x per month etc.?
    You can potentially save a lot of time, especially with routine tasks.
  • Is VBA the right tool to automate this task at all?
    When it comes to retrieving and transforming data from multiple sources, Power-Query is the better tool. Power BI is recommended for real-time reports and with Microsoft Flow you can, for example, save file attachments from emails directly in a OneDrive folder. In order to answer this question, you should know the other Excel tools at least too superficially.

If the task takes a lot of time and automation with VBA makes sense, the following question arises:

Own programming or external procurement (make or buy)?

You can often quickly implement simple projects yourself. In the case of complex and extensive projects, it is advisable to consult an external professional. When it comes to an Excel tool for standard tasks, such as preparing offers, there is a high probability that there are ready-made tools on the market. These tools are usually very cheap and well-engineered, so before starting a new project you should check whether a similar tool is already available for purchase.

5) How does VBA programming work in practice?

A finished VBA project can be imagined as a puzzle with many small puzzle pieces. Each of these puzzle pieces carries out a certain operation, e.g. opening a file, creating a new worksheet, writing a value in cells, saving a file, etc.

VBA has been around since the mid-90s, so you can find ready-made code modules for every imaginable standard operation on the Internet. You can find these together and assemble a solution from them. However, in order to understand what exactly the building blocks do and to adapt them to your own needs, you have to understand the VBA basics and be able to read VBA code.

6) How best to learn VBA basics?

Don't worry, you don't have to memorize hundreds of commands, code and variables. It's enough to get started if you can read VBA code and perform minor commands and adjustments.

The first step is often to watch individual videos on YouTube, but these only deal with a small part of VBA programming and do not build on each other. In addition, you cannot initially distinguish which videos are relevant and what you will probably never need in practice. Therefore, I recommend that you learn the basics with the help of my Excel online course. This is structured and aims to familiarize you with the application in the shortest possible time.

With this foundation you can then really understand the VBA documentation from Microsoft and also use Excel blogs and YouTube videos on the topic. Forums are only suitable for specific questions, not to get an insight into VBA.

7) VBA programming - BASICS: EXCEL OBJECT MODEL VS VBA

Subscribe, thumbs up and your questions in the comments 😉

In Excel, this means, for example, cells, worksheets, workbooks, charts, tables, etc. These objects do not exist on their own, but are linked in a hierarchy. For example, without a workbook and worksheet, we wouldn't have any cells to address. All available objects and how they depend on each other are called Object model. The object model is different depending on the application, with PowerPoint we would have e.g. presentations and slide objects.

These objects haveMethods and Properties. The objects can be changed using properties such as size, name, position, color, font. With methods, i.e. certain actions, objects can be created, deleted and selected, for example.

In addition to these objects, there is also a general part that is independent of the program. This is the scripting languageVisual Basic for Applications (VBA), i.e. Visual Basic for different applications. Here, for example, the variables, loops, if-then constructions etc. are meant.

You can also think of the different object models as different screws and VBA as a wrench with which you turn the different screws.

You can find more about the Excel object model here.

Do I have to have prior knowledge of programming in order to learn VBA?

No. As with everything in life, you have to work for that too. The right structure is important here in order to learn the functions correctly and easily. I recommend my Excel course, in which I explained programming to you in a meaningful and practical way so that you know the right commands in certain situations.

Will it help me to automate my tasks through Excel VBA programming?

It depends a lot on the type of task you want to do. If the task takes a long time, because it is either to be performed very often, or is rarely performed but takes a long time, then it is probably worth automating these functions. If the task is rarely performed, it is best to do it by hand. Macros help if you use them consciously to improve the processing of your data with the help of programs.

Is Excel VBA necessary to complete my projects?

No, it only helps you with efficiency!