Visual Basic Code Examples For Excel Pdf

VBA Coding Examples for Excel! Searchable list of ready-to-use VBA Macros / Scripts for Excel. Search the list below for free Excel VBA code examples complete with explanations. Below you will find a list of basic macro examples for common Excel automation tasks. Copy and Paste a. I am trying to write a vb code to convert an excel sheet to pdf. I am writing the code as part of a macro in excel. Saturday, August 25, 2007 4:54 AM. Copy this code and paste in the module of the Visual Basic Editor to open visual basic editor press Alt + F11. When we run this code by pressing the F5 key, we get the following option. You can see that your excel file is now converted into PDF. VBA Code Example #2: PDF To Excel Using Able2Extract. The sample macro (pdfToExcelAble2Extract) that appears further below follows roughly the same as the previous one (pdfToExcelAdobe) with one major difference: It relies on Able2Extract instead of Adobe Reader for purposes of opening the PDF file and copying the data. Visual Basic 6 Samples Code comprises 290 pages of captivating content and 48 fascinating sample codes. All the examples are explained in great details using easy-to-understand language and illustrated with gorgeous Images. By reading the book and using the sample source codes, you will master Visual Basic programming effortlessly!

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.

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.

Yum.

Because wood-fired pizza is the best!

But seriously.

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

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.

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

Visual Basic Code Examples For Excel Pdf Document

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

Ta da!

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

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.

Happy coding!

Learning Resources

  • Excel VBA Programming for Dummies, John Walkenbach

A bit about me

I'm Chloe Tucker, an artist and developer in Portland, Oregon. As a former educator, I'm continuously searching for the intersection of learning and teaching, or technology and art. Reach out to me on Twitter @_chloetucker and check out my website at chloe.dev.

If you’ve done a lot of work in Excel, you’ve probably heard about Macros orVBA. Excel VBA can help you do your work much, much faster. It can also help youexpand the true power of Excel in ways you never thought possible. Ranging fromprocessing data in a worksheet all the way to scraping web pages, VBA is quitethe beast. In this post, I’d like to discuss Excel VBA for beginners. We’llcover:

  • What is VBA?
  • When should you use VBA?
  • Getting started with the Visual Basic Editor
  • Your first useful program

What is VBA and why should I care?

VBA stands forVisual Basic for Applications.It is an implementation of Microsoft’sVisual Basic 6 programminglanguage specific to Office applications in order to give you access to eachapplication’s features like cells, charts, and more. Each Office application hasits own VBA Object Model (an Object Model is sort of like a map of the featuresyou have access to with VBA). So while the VBA concepts are the same for eachOffice application, there is still a learning curve for each one.

Ok, enough with the boring stuff. Why should you care about Excel VBA?

Simple: it makes your life easier.

Pdf

(You laugh - this really happened to me)

You can write programs that will do tasks for you lightning fast - leaving youmore time to do other things that are more important. This is especially usefulfor when you have to do a repetitive, tedious task to perform. For example, youcould use Excel VBA for:

  • Copying and modifying data
  • Apply formatting automatically
  • Finding unique values
  • Data checking before Saving
  • Go beyond Sheet Protection
  • Create forms to control data entry
  • And so much more (seriously, way more)

If this article helps solve your problem, please consider supporting me because it takes a lot of effort (and coffee!) to provide this content.

👇 There's a special gift for you in return for your support.

Enjoy the post!

When Should You Use VBA?

When you have a logical, repeatable process that you do over and over, thenit’s time to consider automating it. However, if you’re just doing a one-offthing that will take you 10 minutes to do and will probably never do it again,it will probably not be worth your time making a macro to get the job done. Justrough through it. Trust me on this.

Excel VBA for Beginners - Start with the Visual Basic Editor

The first place to get started would be the Visual Basic Editor. PressALT+F11. You will have a new window pop up.

This is called the Visual Basic Editor and this is where you’ll be doing allyour magic. Consider this your workbench, or your garage, or your desk -whatever place you get your work done.

I’m sure you’re itching to get some coding done, and we will! But it’s importantto understand where the tools are so you understand your surroundings. So, let’squickly breakdown these components:

Excel VBA Project Window

Located at the top left of the VBE window, the Excel VBA Project window willshow you all the places where your VBA code can potentially live. Think of it asa quick reference like Windows folders in Explorer. In this section, you canfind Worksheet code, Workbook code, Userform code, regular Modules, and ClassModules. Each one of these could be considered a Project Object. All ofthese Project Objects can hold code, but each one has it’s own special uses(which I’ll cover in a later post).

Properties Window

Just below the Project Window is the Properties Window. This window holds theproperties of each Project Object that is currently selected. I haven’t foundmany times where I needed to use this, but feel free to poke around to getfamiliar with changing those properties.

Toolbar

Just your standard toolbar. We’ll dig more into this as we move along withfuture posts.

Okay. Enough of that. Let’s get to the real meat.

Writing Your First Useful Program

Many “intro to programming in X language” articles usually start with a “HelloWorld” program. What that basically means is that the article teaches you how todisplay “Hello World” to the user. But these kinds of intros leave a lot to bedesired. So, let’s forget”Hello, world!” and get someREAL work done.

Before Beginning

DANGER

Please realize that you cannot Undo any changes that Excel VBA code will maketo your spreadsheet.

Sorry, but there’s no Control+Z’ing your way out of a VBA mistake!

That said, please Save before moving executing any Excel VBA code in case somethinggoes wrong. I personally suggest that you use a test workbook when learning VBA.

For our first program, let’s delete entire rows of data if column A has aspecific value in it. For example, say we have this data:

We want to delete any row with “Delete” in it and leave the other rows alone.So, we have 4 “Keep” cells to leave alone and to delete 3 “Delete” cells. If youwere to do this, how would you begin?

Your first inclination might be to loop through the cells, search for “Delete”and, if found, delete the entire row. Yes? Cool, let’s try that.

Go to the VBE window and create a new module by right-clicking on the workbookname in the Project Window, hover over Insert, and click on Module, like so:

Add this code to the module (we’ll go over what this means in a minute):

In order to run this code:

  1. Go back to the workbook and select the cells A1:A8 (this is important toremember)
  2. Bring up the Macro Window by pressing ALT+F8
  3. Select “deleteText”

Click Run. Let’s take a look at the data.

…huh. There’s still one cell that says Delete.

C’mon man. How is this a useful program? It didn’t even work right!

Alright, alright. Calm down. This kind of thing is something you’ll always runinto when coding. No matter how good you get, there will always be somethingthat snags you. I figured it was a good idea to get you used to this type ofthing early on. All this means is that we have a bug in the code. So let’s”debug” it.

Debugging Code

There are a lot of debugging features to cover, but this post has already goneon and on pretty long and I want to try to hold your attention for just aliiiittle bit longer. So let’s cover the code step by step to see if we canuncover what went wrong.

The first line of code is just a variable declaration, so there’s nothing wrongwith that. The only other part of this code is the For loop, so let’s see what’sgoing on there.

This line tells VBA to process all the cells in your selection one-by-one. Inorder to do anything with the cell being processed for each time the loop runsits course, we tell VBA to assign each cell to the variable r. This is the waywe access each cell’s information as we loop through all the cells in theselection.

The text inside the cell is what we’re trying to check - and this is how wecheck it. r.Value will return whatever text is within the cell that we’reinvestigating. If the cell’s value is 'Delete' then we will move on to thenext statement, which is:

So, if the cell’s text reads “Delete” then we want to delete the entire rowthat that cell is in. Simple, right?

Well, not exactly.

When we tell VBA “hey, loop through these cells, please,” it will take thatrange of cells, and store it internally so it can loop through it. The way Exceldoes this is by using a counter to keep track of where it is within the range.So, let’s look at the original data again:

The first row has “Data” in the cell, so we skip this one. The second row has“Delete” in it, so we delete it, along with the entire row. This affects theentire range that we’re working with. So, before we deleted the row, VBA wasat row 2:

Then we delete the row, and VBA is still at row “2” (which was row 3, but isnow row 2).

What’s next in the code?

Excel Visual Basic Tutorial Pdf

This is a way to “close off” the If statement. So only the items inside theIf statement will be executed if the If statement evaluates to True.What’s next?

This tells Excel to move to the next row down, which is row 3. So, technically,it skipped the “Keep” that was in row 3, but is now in row 2.

Confusing? Sometimes that’s just how code is :)

Think you know where the bug is? If you realized that the second “Delete” cellwas skipped because we modified the range while VBA was looping through it, youguessed right.

Ok, so how do we fix this?

Let’s pick something easy. Let’s find the cells that have “Delete” in them, andinstead of deleting the entire row, let’s just make the text blank. We’ll changer.EntireRow.Delete to r.ClearContents.

Visual Basic Code Examples For Excel Pdf Template

But that only makes the cells blank, and you want to remove the entire row forall of those. So we’ll add another line of code:

After all the “Delete” cells have had their text cleared, we do a search on theoriginal selection for blank cells, and delete the entire row for only thosecells. Here’s our new code:

Try running that code again. Notice anything different?

Awesome. Now we have working code.

Excel Vba Basics Pdf

Where to Go From Here

There is a lot of power in Excel VBA. When I started learning about it, I wantedto automate everything - and I practically did. I became so good at my job because Iwas getting things done faster than everyone else. This got me noticed, which was good.Then I started sharing the tools I created with my teammates and then we became fasteras a team. This got me a raise, which was better.

If you continue on learning how to harness the power of VBA, it will help you outtremendously. So what are you waiting for? Let’s move on to the next item:

Comments are closed.