facebookIcon tracking


Coding Beginner? Get Started with VBA Macros in Excel

Published: February 2, 2015

Author: Oliver Eldredge

Note: This blog post covers basic VBA skills. An intermediate knowledge of Excel and a basic understanding of macros are both required prerequisites for getting the most out of this entry.
Coding is a valuable skill that’s becoming a more prominent aspect of digital marketing. Not so long ago, the only syntax issues that a search marketer would have to understand would be AdWords DKI syntax and figuring out what the params in Bing were. As engines evolve we’re seeing more opportunities to participate in advanced features, if you have a basic coding skillset. Two examples of this are implementing scripts for AdWords and creating modern ads with features like countdowns and product attributes.
But where can you get started if you’ve never coded anything before?
It turns out that Excel is a great place to start. Excel offers you the ability to create and enhance macros with VBA code. This can not only introduce you to the fundamentals of coding, but can also boost your productivity by enabling you to create powerful, time-saving Excel workbooks.
Let’s take a look at a couple of really basic coding skills that can get you started.

Declaring Variables

Variables are used in coding to hold values, and enable various functions and calculations. When you create one, you will have to choose from a list of available data types. The basic syntax for doing this is:
Dim x as _______
Where x is the name of your variable (you can use any name that you want), and underscore is replaced by the desired data type.
A few basic variable types:
Boolean: These variables will be able to have values of True or False. They cannot contain arbitrary numeric or text values.
Date: Holds a date between Janurary 1st, 0100 and December 31st, 9999. This creates an obvious Y10K issue if you’re planning using the same Excel file for the next 7,985 years.
String: Holds a text string. The size limitation is not an issue. They can hold 2 billion characters.
Integers: These represent numbers without decimal places. They can have positive or negative values. These variables take up a limited amount of data and are subsequently limited to values between 32,768 and -32,768.
Decimal: These variables take up more space and allow for more accuracy. Because of this they can be slower, too. They can store numbers that contain approximately 28 number places.
There are many other data types available that you can use for specific purposes such as working with currencies, enhanced variants, and others.
Let’s see what we can do with only integers in a VBA macro.

Assigning Values to Variables

This task can involve slightly more complicated syntax compared with declaring a variable in the first place. Let’s start with a really simple example, though.
If all you want to do is give your variable a fixed value, then you can use the following syntax:
X = 5
This will assign the value of 5 to your variable. A more practical task is to assign the value of a cell to your variable. You can do that using this code:
X = Worksheets(“name of worksheet”).Cells(2,”B”).value
The only trick here is that the syntax for determining which cell you want to pull a value from will use a number for the row reference first, and then a letter for the column reference second. This is backwards compared to a regular cell reference, which starts with a column and then follows up with a row.

Creating a Loop

There are several ways to get an Excel macro to repeat itself. Loops are one of the easier ways to accomplish this.
We will deliberately start with an incomplete example and then fill in the details.
The basic idea here is that we’ll create to separate lines of code that denote what we want to have repeated. The first line is “Do”, and the second is “Loop”. Everything in between these will get repeated over and over. So, for example, the code below will keep adding 1 to the current value of X, forever.
X = X + 1
In order to make this practical, we need an instruction to tell Excel when we want it to stop doing laps on the loop. The two options here are “While”, and “Until”. A “Do While” loop will continue to go through the loop as long as a certain criteria remains true, and a “Do Until” loop will continue to go through the loop until a certain criteria is true. So if you ran the following code, X would be equal to 10 when Excel quit the loop.
X = 0
Do Until X =10
X = X + 1

The Attached Workbook and A Few Macros

Let’s cover a powerful example that can save you tons of time by using the three simple coding techniques covered above.
The example we want to cover involves creating the same keyword list several times to cover several geo-targeted cities. This same technique could be used to plug product names into a generic keyword list too.
The attached Excel file covers this example and has the following 6 worksheets:
Backup Data: Simply included so you can copy and paste it to start the macro over. Running a macro interferes with Ctrl ­­+ Z functionality.
Generic List: This will be used by our macro and will end up being deleted one row at a time.
Current Data: We’ll use the A2 cell in the worksheet to create the keyword list, and our macro will use the value in cell B2 to determine how many rows of data are left in the Generic List worksheet.
Builder: This sheet contains a keyword list that uses cell references that point back to our Current Data worksheet’s A2 cell.
Workspace: We’re just using this as a space for copying and pasting.
Bulksheet: This is where our final product will get kicked out.
This Excel workbook also contains three macros: two smaller ones, and one that puts the previous two and adds some code to it. (Step_1, Step_2, and Process_w_Code)
The macro titled “Step_1” simply copies the keywords from the Builder worksheet that contains formulas, then pastes values into the Workspace worksheet, and then copies the rows from there and inserts them into the Bulksheet workbook above the second row. In plain English, we take the list from the Builder tab and paste values into the Bulksheet tab. This is the function that we want to include in our loop.
In order to make sure that each loop is unique, we have a second macro titled “Step_2”. This macro copies the value from the top of our list in the Generic Data tab, then pastes into cell A2 in our Current Data tab. This basically means that we’re updating our list, and updating the keywords contained in the Builder tab.
So the goal now is to repeat these two steps until the list is exhausted (we’re monitoring that with cell B2 in the Current Data tab). The result is that the Bulksheet tab will contain the keyword list duplicated across all of the cities listed in the original Generic List.

Combining our Macros with VBA Code

Let’s look at this powerful example that can save you tons of time by using these three simple coding techniques.
The first thing we’ll do is jump into the source code and paste the code from our first two macros into a third new macro. The screenshot below gives us a snapshot of where we’re going with this.
The final product:
Then we manually add the declaration of our variable, the assignment of a value to it, and the creation of a loop to our code.
So from a coding perspective, we do the following things in order.

-Declare a variable “X”

-Assign it the value from the Current Data tab that counts the rows in our list

-Start a loop that runs while X > 1 (it’s one and not zero because of the header row)

-Complete step 2

-Complete step 1

-Assign it the value from the Current Data tab that counts the rows in our list

-Then either start the loop over, or leave the loop depending on the value of X

-Shift our view to the Bulksheet tab right before the macro finishes

You can see this macro in action by navigating to the Developer ribbon, clicking on macros, and running the macro titled “Process_w_Code”.
Results from running our macro:
kw list

Taking it to the Next Level

These three simple techniques are barely scratching the surface of what you can accomplish using VBA in Excel. As coding becomes a more prominent skill in digital marketing, these kinds of skills will become more and more valuable. Take advantage of opportunities like this to build your skillset while creating applicable tools for your team to use.

1 S Wacker Drive
Suite 2250

Chicago, IL 60606(650) 539-4124


Want to become a client?

Contact Us decorative arrow

Want to join the team?

View Our Openings decorative arrow

Find us on social media.

Press inquiries.

Email Us decorative arrow

Expert insights for your inbox. Subscribe to our content.

Accept No Limits.

Learn more about 3Q/DEPT READ MORE