What is Excel VBA?
VBA, short for Visual Basic for Application, is a programming language that can be used in Microsoft Office applications such as Excel. VBA was created in the 1990s to unify macro languages across individual applications. Using VBA in Excel is a good way to automate workflows and generate practical tools to manage projects and accounts. VBA programming allows for the exchange of data, tables, and diagrams across individual Microsoft 365 applications.
Register a domain name
Build your brand on a great domain, including SSL and a personal consultant!
How does VBA work?
The VBA programming language has been available in Excel since version 95 and thanks to a plethora of VBA tutorials available online, it is relatively easy to learn even for non-programmers. Much like any Office application, Excel is composed of multiple objects including spreadsheets, cells, and tables. Methods and features can be manipulated and adjusted manually or using VBA programming. If you find yourself executing the same activities over and over again, Excel Visual Basic programming could save you lots of time. Using Excel VBA, you can create, select, or delete objects on command.
But how does that work? Excel integrates a VBA Editor which can be used to generate automatic operations. It’s not as complicated as it sounds because the individual coding blocks are fixed. This means that for every standard operation, there’s already a completed chunk of code available. You’ll only need to arrange the individual code blocks to personalize the solution you’re looking for.
These solutions are referred to as Excel macros and they make it easy to organize, format, or import data. But before you get started, it’s best to familiarize yourself with the VBA codes in order to put them together in the right way.
Excel VBA tutorial: learn the foundations
It’s a good idea to start with the foundations of Visual Basic before you get into arranging the individual coding blocks in Excel. Our short, step by step VBA tutorial demonstrates how to create a simple Excel macro.
- Launch the VBA Editor from the Developer tab and insert a new module. If the Developer tab is not shown, click on Excel -> Preferences -> Ribbon & Toolbar. In the Macros dialog, select the Developer checkbox and click Save.
- Once the Developer tab has been added to the Excel dashboard (next to the View tab), click on Visual Basic to launch the editor window and Insert -> Module.
- A pop-up window will open. Enter “Option Explicit” and then “Option Base 1.” This will allow you to work with Subs and Functions. Subs are programs made of only commands. There are no return values. Return values are used when entering functions which is why they’re suitable for mathematical calculations.
Adding variables
In Excel Visual Basic, variables are used in the same manner as mathematic equations. They’re not just placeholders for values, but also placeholders for character strings or objects. The use of variables is a good idea if certain elements are activated more than once. In VBA code, you need to declare a variable as a type of data. Use the following DIM command to do so: Dim [Variable] As [Data type].
For example, Dim datStart As Date tells VBA that a variable with the name of “Start” of data type “Date” should be created.
Depending on the content of your Variable, you’ll need to choose the correct type of data in Excel VBA. The most-used data types and declaration symbols are:
- Variant: Can contain arbitrary data, for example, numerical values, character strings, time and date values. A variant is automatically shown where the type of data is not explicitly declared. Symbol: not defined.
- Integer: Used for whole numbers of values between -32,768 to 32,767. Symbol: **%()
- Long: Used for whole numbers between values of -2,147,483.648 to 2,147,483.647. Symbol: (&)
- Double: Encompasses all floating-point numbers from plus/minus 1.79 * 10^308. Symbol: (#)
- Boolean: These are true or false variables displayed as “True” or “False” or #TRUE# or #FALSE#
- String: Strings are character arrangements of variable or fixed length. Symbol: (**$**)
- Date: Used for date and time designations. Times from 00:00:00 (midnight) to 23:59:59.9999999 can be entered. The date format is M/T/JJJ or JJJJ-MM-TT.
The most common VBA commands
Do…Loop Command
Do
[statement]
Loop [While/Until] PrintThe “Do loop” allows you to define commands that are repeated until the loop matches a predefined condition. Using “while” repeats the loop until the condition becomes “False”. Using “Until”, the loop is repeated until “True”.
For…Next Command
For [start value] To [value] Step [value]
[Do1]
Next [Variable]The “For loop” repeats commands as specified. The loop starts as soon as the specified variable is reached and increases by the value specified in “Step” until the final value.
If…Then…Else Command
If [statement1] Then
[Do1]
Elseif [statement2] Then
[Do2]
Else
[Do3]
End ifThe “If-Then” command executes a series of commands depending on whether the statement is matched or not. In the example above, if statement 1 is correct, command “Do1” is automatically executed. If it’s not correct, statement 2 is checked. If statement 2 is correct, command “Do2” is executed. If neither of the statements are correct, command Do3 is executed.
Besides VBA programming, Excel shortcuts are a great way to make working in Excel more efficient.
VBA programming: a practical example
The best way to learn Excel VBA is to practice using examples and exercises. The following shows a simple “If…Then...Else” command:
The following example uses an Excel macro to show whether candidate 142 passed a test or not. To pass, they would need to have scored at least 60 points.
The VBA code above: If Range("b2").Value >= 60 Then Range("c2").Value = "Yes" stipulates the condition that needs to answer the value in cell C2 with “Yes”. When you hit the play button and “Execute” in the next window, VBA programming begins and cell C2 is auto-filled:
The example above is simple so that beginners can get a first impression of Visual Basic in Excel. You’ll find many other examples online that you can use to practice.
The website builder from IONOS
MyWebsite is the turnkey solution for your professional web presence, including a personal consultant!
Related articles
Creating a flowchart in Excel is quick and easy
Flowcharts in Excel allow you to visualize complex processes with simple icons and arrows. Whether you’re working on a complex computer program (and want to understand the algorithm) or you’re trying to improve your team’s workflow, Excel gives you everything you need to create visually appealing flowcharts.
Google Sheets vs. Excel
Microsoft Excel and Google spreadsheet programs differ primarily in how they work. While the more conventional Excel is used locally, Google Sheets is used online. Both have their advantages. In this guide, we will compare Microsoft Excel and Google Sheets and show you what you can expect from these spreadsheet programs.
Rawpixel.comShutterstock Gantt chart in Excel – it’s this easy!
A Gantt chart is a useful tool in project management. Using this graphic, teams are able to quickly gain an overview of the timing of tasks. To be able to create this kind of chart, however, you don’t necessarily need any kind of specialist software. You can also create a Gantt chart in Excel without any difficulties. We’ll explain step-by-step how it works.
How to count characters in Excel using the LEN function: step-by-step instructions
In Excel, it can be useful to count the number of characters in each cell if the big picture gets lost in large amounts of data. To quickly and easily get an overview of the number of characters, you can use the Excel LEN function. In our step-by-step instructions, you will learn how to use this function to count characters in Excel.
