How does vba work

To find out what happens when you use single quotes instead, return to the Visual Basic Editor and replace the "Wow! If you choose the next line, the Visual Basic Editor reacts. The error "Compile error: Expected: expression" is not that helpful, but the line that generates the error turns red to tell you that you have a syntax error in that line and as a result, this program will not run.

Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it. For example, open the Visual Basic Editor and change the Value property name to ValueX in your Macro, deliberately introducing a runtime error since the Range object does not have a property called ValueX. Go back to the Excel document, open the Macros dialog box and run Macro1 again.

You should see a Visual Basic message box that explains the run-time error with the text, "Object doesn't support this property of method. When you return to the Visual Basic Editor, it is in a special debug mode that uses a yellow highlight to show you the line of code that failed. As expected, the line that includes the ValueX property is highlighted. You can make changes to VBA code that is running, so change ValueX back to Value and choose the little green play button underneath the Debug menu.

The program should run normally again. It is a good idea to learn how to use the debugger more deliberately for longer, more complex programs. At a minimum, learn a how to set break-points to stop execution at a point where you want to take a look at the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line.

These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts. To open the Developer Reference that is built into Office Help, open the Help reference from any Office application by choosing the question mark in the ribbon or by pressing F1. Then, to the right of the Search button, choose the dropdown arrow to filter the contents.

Choose Developer Reference. If you do not see the table of contents in the left panel, choose the little book icon to open it, and then expand the Object Model Reference from there. Time spent browsing the Object Model reference pays off. After you understand the basics of VBA syntax and the object model for the Office application that you are working with, you advance from guesswork to methodical programming. Of course the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. Fortunately, the Internet has fostered a community of developers who help each other solve programming problems.

Any search on the Web for "office developer forum" reveals several discussion groups. You can search on "office development" or a description of your problem to discover forums, blog posts, and articles as well.

If you have done everything that you can to solve a problem, do not be afraid to post your question to a developers forum. These forums welcome posts from newer programmers and many of the experienced developers are glad to help. Before you post, look on the site for an FAQ or for guidelines that members want you to follow. Ensure that you post content that is consistent with those guidelines and in the correct section of the forum. Include a clear and complete code sample, and consider editing your code to clarify it for others if it is part of a longer section of code.

Describe your problem clearly and concisely, and summarize any steps that you have taken to solve the problem. Take the time to write your post as well as you can, especially if you are flustered or in a hurry.

Present the situation in a way that will make sense to readers the first time that they read the problem statement. Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started.

In the simple examples in this article you manipulated objects that the application had already created. You might want to create your own objects to store values or references to other objects for temporary use in your application. These are called variables.

You then set its value and use it to set other variables or properties. The simple programs in this article execute one line at a time, from the top down. The real power in programming comes from the options that you have to determine which lines of code to execute, based on one or more conditions that you specify. You can extend those capabilities even further when you can repeat an operation many times.

For example, the following code extends Macro1. Type or paste the code into the Visual Basic Editor and then run it. Follow the directions in the message box that appears and change the text in cell A1 from Wow! This code snippet demonstrates variables, branching and looping. Read it carefully after you see it in action and try to determine what happens as each line executes. Be aware that there are situations in which you might want to automate email in Outlook; you can use templates as well.

Be aware that you can select a column of cells and run this macro to delete all rows in the selected column that have a blank cell. Be aware that this code loops through all of the slides and deletes all text boxes that do not have any text. The count variable decrements instead of increments because each time the code deletes an object, it removes that object from the collection, which reduces the count.

Be aware that this code copies the currently open contact in Outlook into the open Word document. This code only works if there is a contact currently open for inspection in Outlook. Have questions or feedback about Office VBA or this documentation? Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback. Feedback will be sent to Microsoft: By pressing the submit button, your feedback will be used to improve Microsoft products and services.

Privacy policy. Skip to main content. This browser is no longer supported. You use scalar variables when working with a single item. In these cases, you use arrays. Arrays are sets of indexed elements that share the same data type and have a logical relationship between them. The function is substantially the same as that of a variable: holding values. The main difference is that arrays can store several values while scalar variables can hold only one value.

When you use an array, you refer to the different elements of the array using the common name and distinguish among them with a number called a subscript or index.

For example, if you had a group of 10 horses numbered 1 through 10, you can refer to them as horses 1 , horses 2 , horses 3 and so on until horses A condition is a statement or expression that evaluates to either true or false. Then, depending on whether the statement has evaluated to true or false, Excel executes or doesn't execute a group of statements.

In other words: If the condition is met true , something happens. Can you think of a way you can apply a conditional statement in the VBA application you are developing to keep track of how many sugar cubes are given to your horse by the caretakers?

More precisely:. If you're an Excel user, you may have noticed that conditions are not exclusive to VBA programming. For example, several Excel functions such as the IF function allow you to check whether a condition is true or not and, based on the result, do one thing or another. There are several ways to structure conditional statements in Visual Basic Applications.

You now know how to create the variables that store the number of sugar cubes given to the horse by each caretaker and the identification number of each caretaker. You also know how to get Excel to remind a caretaker of the rule that states they should give 1 or 2 sugar cubes per day to the horse in case they haven't done so.

How do you get Excel to ask each of the 5 caretakers how many sugar cubes he has given to the horse? In other words, a loop is a particular statement that makes a group of instructions be followed multiple times.

Just as conditional statements, there are several ways in which you can structure loops. This statement asks Excel to execute a group of statements repeatedly for each of the components of a particular group.

In practice, this looks roughly as follows. However, what is more relevant for purposes of this section is the general structure of a For Each…Next statement, so let's take a look at it. To summarize, the opening and closing lines of the For Each…Next statement tell Excel that it should run the instructions that are inside the loop for each of the 5 cells where the number of sugar cubes given to the horse by each caretaker are to be recorded.

Let's go through the main items of this macro while making some general comments to further illustrate each of the terms covered in this tutorial:. The code that appears in the screenshot above is an example of VBA code. The terms macro, VBA code, Sub procedure, routine and procedure are sometimes used interchangeably.

A Sub procedure is the series of statements that are between the Sub and the End Sub statements and, more precisely, is a part of a computer program that performs an action.

The other main type of procedure in VBA are Function procedures, which carry out calculations and return a particular value. Once Excel executes this line, the macro stops running. In Visual Basic for Applications, variables are usually declared using the Dim statement. After this, you can determine the name of the variable and its characteristics. The computer allocates a storage location to the variable and, then, you can use the declared variable as a placeholder to represent a particular value.

In the example above, 2 variables are declared. This line is an assignment statement which assigns the value 1 to the variable caretakerNumber. A For Each…Next statement asks Excel to execute a group of statements repeatedly for each member of a group. This type of statement is one of the simplest ways to implement a loop, a statement that makes a particular group of instructions be repeated several times.

Let's take a look at the body of the For Each…Next statement to understand the set of instructions that is repeated:. By now, based on what you have learned on this beginners guide, you know the meanings of at least 16 essential terms you need to know to learn VBA programming.

You're also able to understand how some of these terms are sometimes used interchangeably and some of the discussions regarding their use. Additionally, you've seen how these concepts come together to form a macro, and you know how some of the VBA components that have been explained in this Excel VBA tutorial for beginners can be implemented in practice. I hope that this Excel tutorial for beginners has proved that the most essential terms you need to know to learn VBA programming are not that complicated to understand.

You're likely to find the terms that have been covered in this particular guide for beginners many times during your journey to becoming a VBA expert so you may want to bookmark this post and come back as required during your future Visual Basic for Applications studies. Here are my favorite Excel Training Resources:. However… Being able to create a basic macro in Excel is only the beginning in the process to become a really efficient and productive user of macros and VBA.

The bad news at least for some of you is that using VBA requires that you learn programming. If this doesn't sound that convincing, wait… In order to help you during the process of learning Visual Basic for Applications, I have created this Excel VBA tutorial for beginners where I explain, in detail, 16 actually you'll probably learn even more essential terms you need to know in order to learn VBA programming.

Why VBA? Just like in excel, you can use. Logical operators — The concept of logical operators covered in the earlier tutorials also apply when working with VBA.

These include. How to Enable the Developer Tab Below is the step by step process on how to enable the developer tab in Excel: Create a new workbook Click on the ribbon start button Select options Click on customize ribbon Select the developer checkbox as shown in the image below Click OK.

Report a Bug. Previous Prev. Next Continue. Home Testing Expand child menu Expand. SAP Expand child menu Expand. Web Expand child menu Expand. Must Learn Expand child menu Expand. Big Data Expand child menu Expand. Live Project Expand child menu Expand. Private registration. See more. How does VBA work? In the Macros dialog, select the Developer checkbox and click Save. A pop-up window will open.

Subs are programs made of only commands. There are no return values. Adding variables In Excel Visual Basic, variables are used in the same manner as mathematic equations. 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 , to 32, Times from midnight to The most common VBA commands. Table with example values.