What is vba in excel 2013

What is Excel VBA? : Excel VBA Basics 001

Visual Basic for Applications (VBA) in Excel, is a powerful and sophisticated built-in programming language that allows you to write your own functions or commands in an Excel spreadsheet.

These custom functions or commands, can help to ease your tasks and thus by using Excel VBA you can do almost any imaginable thing in Excel.

Now, before we dive deep into Excel VBA, let’s first try to understand what VBA is.

Table of Contents

What is VBA?

In most simple terms you can also say that, VBA is the language that Office Applications like Excel, Word, PowerPoint, etc. understand. So, all the programming that we are going to do in Excel or in other office application has to be done in VBA.

But technically, VBA is an implementation of Microsoft’s event-driven programming language i.e. VB6 and its associated integrated development environment (IDE).

VBA is an extensible programming language that is made up of a core set of commands and extended on a per-application basis to be able to work directly with objects in that application. And because of this it is sometimes also called as a hosted language.

Visual Basic for Applications is bundled with office applications (host application) so that it can be used as medium for accessing and interacting with the host applications object model. This means that, Excel VBA, knows about things like workbooks, worksheets, cells and charts, but Word VBA doesn’t knows about such things. Instead Word VBA understand terms like documents, bookmarks etc., things that are more related to Microsoft Word.

Is there any difference between a VBA Program and Macro?

I have seen many people confusing between VBA and Macro language. But a Macro language is very much different from a VBA. Macros were used with the older versions of office applications and they could only be used for automating instructions.

In fact, the first spreadsheet macro programs were just shortcuts for the user interface commands. For example, if in the user interface you typed R (for “Range”), N (for “Name”), and C (for “Create”), you would enter RNC into the macro to automate the process.

This approach was intuitive, but it also had integral weaknesses. Because of such weaknesses, VBA later in 1993 replaced this Macro Language, and hence there is still a misconception among people to call VBA as macro language.

Visual Basic for Applications when compared to traditional Macro Language is more powerful and sophisticated and hence it was an instant success.

Why learning Excel VBA is so important?

As you become familiar with Excel, you will notice that although Excel provides you large number of tools, but still it doesn’t have everything that you need to perform your daily tasks. Such tasks may include creating custom functions, repetitive instructions, automating tasks etc.

And hence Microsoft has provided VBA as a gap filler; that allows users to write their own methods or commands to perform their tasks.

After having enough experience with VBA programming in Excel you will never ever get stuck during your tasks due to a lack of built-in tools. Using Visual Basic for Applications you will be able to write your own function and commands whenever you feel the need to write one.

What all you can do with Excel VBA?

With Excel VBA you can do a lot of things in Excel. I have enlisted few of them below:

• You can automate the tasks that you have to do frequently.
• You can create a custom command in excel.
• You can repeat a set of instructions multiple times.
• You can create custom function popularly called as User Defined Function (UDF)
• You can create a custom add in in Excel.

Subscribe and be a part of our 15,000+ member family!

Now subscribe to Excel Trick and get a free copy of our ebook «200+ Excel Shortcuts» (printable format) to catapult your productivity.

Источник

What is VBA? The Excel Macro Language

If you’ve ever used macros in Excel, you’ve used Visual Basic for Applications (VBA). VBA is human-readable (and editable) programming code that gets generated when you record a macro. When you run a macro it’s this code that Excel reads to replay your actions.

The following is a series of frequently asked questions about VBA, with answers for both newcomers to Excel macros and seasoned programmers.

What is VBA used for in Excel?

VBA is used to write macros, which automate simple and complex tasks in Excel.

Users of Microsoft Excel will often find themselves repeating the same series of tasks over and over again. Sometimes these are minor tasks like copy-pasting subsets of data to different sheets, and sometimes the tasks are more complex like uploading data to a website. With VBA, macros can be programmed to automate tasks like these, turning a series of manual operations into a single button click.

How do I start with VBA in Excel?

The easiest way to get started with macros is to record them using the Macro Recorder built into Excel. The macro recorder generates VBA code which you can read and edit, giving you a stepping-stone to learn how to code your own macros from scratch.

For more information, see the article “How to Record a Macro”.

How can I access VBA code?

The VBA editor can be opened at any time by pressing the ALT+F11 keyboard shortcut. From here, you can access the code for all open workbooks.

You can double-click any workbook, sheet, or module object in the Project window to show the VBA code contained in it. Recorded macros will always appear in modules, which is where you’ll typically want to write your own macros too.

For more information about editing macros, see the article “Editing Excel VBA Macros“.

What can VBA do?

First of all: if there’s something you can do in Excel, you can write VBA code to do the same thing. If you learn the commands (using the macro recorder or online resources), you can string together several actions together in a single macro.

Second: you can use VBA to write user-defined functions for use in worksheet formulas – much like the “=SUM()” function, for example. Although Excel already packs a large variety of functions, if you find yourself wanting a function that’s more specific to your industry or workplace, you can add it with VBA.

Third: you can use VBA to add logic (If statements) and loops (repeat actions) to your macros. This means that a macro can be more than a recording of actions – it can be a real program with smart repetition and decision-making.

Fourth: VBA allows you to build user interfaces, giving users an easier way to interact with your macros. These interfaces can be simple buttons on a worksheet, or complex UserForms with rich controls like TreeViews and FlexGrids.

Fifth but not finally: VBA allows you to harness almost any resource your computer or the internet by using external DLL libraries. You can automate a lot more than Excel this way, like accessing web services and databases, parsing XML files, interoperating with other Microsoft Office applications, and much, much more.

Are VB and VBA the same?

The VBA language is identical to Visual Basic 6.0. If you have experience writing VB6 code, you can write VBA.

The VB Editor in Excel is a stripped-down version of the VB6 editor, with similar debugging capabilities and components like the ‘Project’ and ‘Properties’ windows. The Form editor is also very similar, although it has some limitations (like the inability to create control arrays).

Less similar to VBA is Visual Basic .NET. Although these languages share many of the same keywords and syntaxes, the code between these languages is less interoperable. You wouldn’t have to re-write much (if anything) to port a procedure from VB6 to VBA, but you couldn’t do the same from VB.NET.

What is the difference between a macro and VBA?

VBA is just the language that macros are written with. If your macro is a story, VBA is the grammar and dictionary it’s written with.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro — A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!

What is the difference between a macro and a script?

Scripts use a limited subset of the Visual Basic language and are used for different purposes.

Macros are stored inside Excel workbook files and can only be run from inside Excel. They’re typically used to automate various Excel functions and add extra functionality to workbooks.

Scripts are stored in text files using the .VBS extension and can be run from inside Windows or executed by other programs. Sysadmins will use scripts to automate certain administrative tasks on a computer or network.

What does a VBA Developer do?

Often, people who create macros aren’t developers – they’re analysts, traders, accountants, or scientists, who have had training in programming and use VBA on more of an ad-hoc basis.

People who focus on VBA development tend to create more refined tools – they create Add-ins, user interfaces with Forms, interactive dashboards, report generators, and more.

Why is VBA important?

VBA is included as standard in all Microsoft Office applications, including Excel. Excel is one of the most popular programs in the world and finds use in a wide variety of fields and workplaces, all with their own unique workflows and use-cases.

By not using automation, countless work-hours are wasted every week performing boring, repetitive tasks at a human speed. Automation with VBA can perform the same tasks at the computer’s lightning-quick pace, letting users focus on the important aspects of their work, being more productive – and maybe getting out of the office a little earlier!

Is VBA a good programming language?

VBA has the typical programming elements like variables, arrays, functions, decisions and loops. It has a syntax that’s easy to read, and can harness a lot of power from Windows and other services.

On the downside, error-handling isn’t very elegant in VBA. While other languages use “try-catch-finally” code blocks, VBA uses “On Error” statements to modify how macros behave when an error occurs.

Additionally, VBA can’t be used to create standalone programs, web sites or services, and it can’t interface with newer programming APIs like the .NET platform.

Is VBA object-oriented?

VBA has some limited support for some object-oriented concepts:

• Encapsulation: VBA supports data hiding using classes
• Polymorphism: the Implements keyword allows programmers to use other classes as interfaces

In the strict sense though, VBA is not object-oriented. It notably lacks the concept of inheritance, which severely limits the ability to extend the functionality of existing types.

Источник

MS Excel 2013: Open the Visual Basic Editor

This Excel tutorial explains how to open the Visual Basic Editor in Excel 2013 (with screenshots and step-by-step instructions).

See solution in other versions of Excel :

How to open the VBA environment

You can access the VBA environment in Excel 2013 by opening the Microsoft Visual Basic for Applications window.

First, be sure that the DEVELOPER tab is visible in the toolbar in Excel.

The DEVELOPER tab is the toolbar that has the buttons to open the VBA editor and create Form/ActiveX Controls like buttons, checkboxes, etc.

To display the DEVELOPER tab, click on FILE in the menu bar and select Options from the drop down menu.

When the Excel Options window appears, click on the Customize Ribbon option on the left. Click on the Developer checkbox under the list of Main Tabs on the right. Then click on the OK button.

Select the DEVELOPER tab from the toolbar at the top of the screen. Then click on the Visual Basic option in the Code group.

Now the Microsoft Visual Basic for Applications editor should appear and you can view your VBA code.

Источник

Excel VBA Tutorial – How to Write Code in a Spreadsheet Using Visual Basic

Introduction

This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA).

Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said «Think about a world without Excel. That’s just impossible for me.” Well, maybe the world can’t think without Excel.

• In 1996, there were over 30 million users of Microsoft Excel (source).
• Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996.

We’re one big happy family!

In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic.

Prerequisites

You don’t need any prior programming experience to understand this tutorial. However, you will need:

• Basic to intermediate familiarity with Microsoft Excel
• If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine.
• A willingness to try new things

Learning Objectives

Over the course of this article, you will learn:

1. What VBA is
2. Why you would use VBA
3. How to get set up in Excel to write VBA
4. How to solve some real-world problems with VBA

Important Concepts

Here are some important concepts that you should be familiar with to fully understand this tutorial.

Objects: Excel is object-oriented, which means everything is an object — the Excel window, the workbook, a sheet, a chart, a cell. VBA allows users to manipulate and perform actions with objects in Excel.

If you don’t have any experience with object-oriented programming and this is a brand new concept, take a second to let that sink in!

Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a task. Sometimes, this is also referred to as a macro (more on macros below). There are two types of procedures:

• Subroutines: a group of VBA statements that performs one or more actions
• Functions: a group of VBA statements that performs one or more actions and returns one or more values

Note: you can have functions operating inside of subroutines. You’ll see later.

Macros: If you’ve spent any time learning more advanced Excel functionality, you’ve probably encountered the concept of a “macro.” Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to accomplish repetitive tasks. Recorded macros generate VBA code, which you can then examine. It’s actually quite fun to record a simple macro and then look at the VBA code.

Please keep in mind that sometimes it may be easier and faster to record a macro rather than hand-code a VBA procedure.

For example, maybe you work in project management. Once a week, you have to turn a raw exported report from your project management system into a beautifully formatted, clean report for leadership. You need to format the names of the over-budget projects in bold red text. You could record the formatting changes as a macro and run that whenever you need to make the change.

What is VBA?

Visual Basic for Applications is a programming language developed by Microsoft. Each software program in the Microsoft Office suite is bundled with the VBA language at no extra cost. VBA allows Microsoft Office users to create small programs that operate within Microsoft Office software programs.

Think of VBA like a pizza oven within a restaurant. Excel is the restaurant. The kitchen comes with standard commercial appliances, like large refrigerators, stoves, and regular ole’ ovens — those are all of Excel’s standard features.

But what if you want to make wood-fired pizza? Can’t do that in a standard commercial baking oven. VBA is the pizza oven.

Why use VBA in Excel?

Because wood-fired pizza is the best!

A lot of people spend a lot of time in Excel as a part of their jobs. Time in Excel moves differently, too. Depending on the circumstances, 10 minutes in Excel can feel like eternity if you’re not able to do what you need, or 10 hours can go by very quickly if everything is going great. Which is when you should ask yourself, why on earth am I spending 10 hours in Excel?

Sometimes, those days are inevitable. But if you’re spending 8-10 hours everyday in Excel doing repetitive tasks, repeating a lot of the same processes, trying to clean up after other users of the file, or even updating other files after changes are made to the Excel file, a VBA procedure just might be the solution for you.

You should consider using VBA if you need to:

• Automate repetitive tasks
• Create easy ways for users to interact with your spreadsheets
• Manipulate large amounts of data

Getting Set Up to Write VBA in Excel

Developer Tab

To write VBA, you’ll need to add the Developer tab to the ribbon, so you’ll see the ribbon like this.

To add the Developer tab to the ribbon:

1. On the File tab, go to Options > Customize Ribbon.
2. Under Customize the Ribbon and under Main Tabs, select the Developer check box.

After you show the tab, the Developer tab stays visible, unless you clear the check box or have to reinstall Excel. For more information, see Microsoft help documentation.

VBA Editor

Navigate to the Developer Tab, and click the Visual Basic button. A new window will pop up — this is the Visual Basic Editor. For the purposes of this tutorial, you just need to be familiar with the Project Explorer pane and the Property Properties pane.

Excel VBA Examples

First, let’s create a file for us to play around in.

1. Open a new Excel file
2. Save it as a macro-enabled workbook (. xlsm)
3. Select the Developer tab
4. Open the VBA Editor

Let’s rock and roll with some easy examples to get you writing code in a spreadsheet using Visual Basic.

Example #1: Display a Message when Users Open the Excel Workbook

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub Auto_Open()
MsgBox («Welcome to the XYZ Workbook.»)
End Sub

Save, close the workbook, and reopen the workbook. This dialog should display.

How is it doing that?

Depending on your familiarity with programming, you may have some guesses. It’s not particularly complex, but there’s quite a lot going on:

• Sub (short for “Subroutine): remember from the beginning, “a group of VBA statements that performs one or more actions.”
• Auto_Open: this is the specific subroutine. It automatically runs your code when the Excel file opens — this is the event that triggers the procedure. Auto_Open will only run when the workbook is opened manually; it will not run if the workbook is opened via code from another workbook (Workbook_Open will do that, learn more about the difference between the two).
• By default, a subroutine’s access is public. This means any other module can use this subroutine. All examples in this tutorial will be public subroutines. If needed, you can declare subroutines as private. This may be needed in some situations. Learn more about subroutine access modifiers.
• msgBox: this is a function — a group of VBA statements that performs one or more actions and returns a value. The returned value is the message “Welcome to the XYZ Workbook.”

In short, this is a simple subroutine that contains a function.

When could I use this?

Maybe you have a very important file that is accessed infrequently (say, once a quarter), but automatically updated daily by another VBA procedure. When it is accessed, it’s by many people in multiple departments, all across the company.

• Problem: Most of the time when users access the file, they are confused about the purpose of this file (why it exists), how it is updated so often, who maintains it, and how they should interact with it. New hires always have tons of questions, and you have to field these questions over and over and over again.
• Solution: create a user message that contains a concise answer to each of these frequently answered questions.

Real World Examples

• Use the MsgBox function to display a message when there is any event: user closes an Excel workbook, user prints, a new sheet is added to the workbook, etc.
• Use the MsgBox function to display a message when a user needs to fulfill a condition before closing an Excel workbook
• Use the InputBox function to get information from the user

Example #2: Allow User to Execute another Procedure

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub UserReportQuery()
Dim UserInput As Long
Dim Answer As Integer
UserInput = vbYesNo
Answer = MsgBox(«Process the XYZ Report?», UserInput)
If Answer = vbYes Then ProcessReport
End Sub

Sub ProcessReport()
MsgBox («Thanks for processing the XYZ Report.»)
End Sub

Save and navigate back to the Developer tab of Excel and select the “Button” option. Click on a cell and assign the UserReportQuery macro to the button.

Now click the button. This message should display:

Click “yes” or hit Enter.

Once again, tada!

Please note that the secondary subroutine, ProcessReport, could be anything. I’ll demonstrate more possibilities in example #3. But first.

How is it doing that?

This example builds on the previous example and has quite a few new elements. Let’s go over the new stuff:

• Dim UserInput As Long: Dim is short for “dimension” and allows you to declare variable names. In this case, UserInput is the variable name and Long is the data type. In plain English, this line means “Here’s a variable called “UserInput”, and it’s a Long variable type.”
• Dim Answer As Integer: declares another variable called “Answer,” with a data type of Integer. Learn more about data types here.
• UserInput = vbYesNo: assigns a value to the variable. In this case, vbYesNo, which displays Yes and No buttons. There are many button types, learn more here.
• Answer = MsgBox(“Process the XYZ Report?”, UserInput): assigns the value of the variable Answer to be a MsgBox function and the UserInput variable. Yes, a variable within a variable.
• If Answer = vbYes Then ProcessReport: this is an “If statement,” a conditional statement, which allows us to say if x is true, then do y. In this case, if the user has selected “Yes,” then execute the ProcessReport subroutine.

When could I use this?

This could be used in many, many ways. The value and versatility of this functionality is more so defined by what the secondary subroutine does.

For example, maybe you have a file that is used to generate 3 different weekly reports. These reports are formatted in dramatically different ways.

• Problem: Each time one of these reports needs to be generated, a user opens the file and changes formatting and charts; so on and so forth. This file is being edited extensively at least 3 times per week, and it takes at least 30 minutes each time it’s edited.
• Solution: create 1 button per report type, which automatically reformats the necessary components of the reports and generates the necessary charts.

Real World Examples

• Create a dialog box for user to automatically populate certain information across multiple sheets
• Use the InputBox function to get information from the user, which is then populated across multiple sheets

Example #3: Add Numbers to a Range with a For-Next Loop

For loops are very useful if you need to perform repetitive tasks on a specific range of values — arrays or cell ranges. In plain English, a loop says “for each x, do y.”

In the VBA Editor, select Insert -> New Module

Write this code in the Module window (don’t paste!):

Sub LoopExample()
Dim X As Integer
For X = 1 To 100
Range(«A» & X).Value = X
Next X
End Sub

Save and navigate back to the Developer tab of Excel and select the Macros button. Run the LoopExample macro.

This should happen:

Etc, until the 100th row.

How is it doing that?

• Dim X As Integer: declares the variable X as a data type of Integer.
• For X = 1 To 100: this is the start of the For loop. Simply put, it tells the loop to keep repeating until X = 100. X is the counter. The loop will keep executing until X = 100, execute one last time, and then stop.
• Range(«A» & X).Value = X: this declares the range of the loop and what to put in that range. Since X = 1 initially, the first cell will be A1, at which point the loop will put X into that cell.
• Next X: this tells the loop to run again

When could I use this?

The For-Next loop is one of the most powerful functionalities of VBA; there are numerous potential use cases. This is a more complex example that would require multiple layers of logic, but it communicates the world of possibilities in For-Next loops.

Maybe you have a list of all products sold at your bakery in Column A, the type of product in Column B (cakes, donuts, or muffins), the cost of ingredients in Column C, and the market average cost of each product type in another sheet.

You need to figure out what should be the retail price of each product. You’re thinking it should be the cost of ingredients plus 20%, but also 1.2% under market average if possible. A For-Next loop would allow you to do this type of calculation.

Real World Examples

• Use a loop with a nested if statement to add specific values to a separate array only if they meet certain conditions
• Perform mathematical calculations on each value in a range, e.g. calculate additional charges and add them to the value
• Loop through each character in a string and extract all numbers
• Randomly select a number of values from an array

Conclusion

Now that we’ve talked about pizza and muffins and oh-yeah, how to write VBA code in Excel spreadsheets, let’s do a learning check. See if you can answer these questions.

• What is VBA?
• How do I get set up to start using VBA in Excel?
• Why and when would you use VBA?
• What are some problems I could solve with VBA?

If you have a fair idea of how to you could answer these questions, then this was successful.

Whether you’re an occasional user or a power user, I hope this tutorial provided useful information about what can be accomplished with just a bit of code in your Excel spreadsheets.

Источник