Home » VBA

Category Archives: VBA

Excel VBA Loops Practice 1 – Nested Loops

Multiple loops can be written within each other to assist in processing multiple series of information. These are called Nested Loops. After the first loop starts, the code then enters another loop. The first loop will not get to process anything further until the second, nested loop has been completed.

In our last tutorial we worked with the For-Each-Next Loop to run down a series of cells to multiple each cell by two. We are now going to add onto now and instead of multiplying the cell by two, we will enter into another loop to add a sheet to our workbook.

The Problem

I have the data in my workbook organized by week number. I want a macro that can copy each week’s data to it’s own sheet. Each sheet is named after the week of the year. If we have a new week of data then that sheet needs to be created.

There is of course the possibility that I open the spreadsheet more than once in a week, so I do not want the macro to create the new sheet if it already exists.

My Cells in column A include a few week numbers. They are:

[the_ad id=”1391″]

Application

The below will setup the first loop, going down each cell in column A.
A message box will appear for each cell and tell you it’s value, 49, 50, 51, and 52.

Sub loop_through_ws()

Dim cell As Range, rnge As Range
Set rnge = Range("A2:A5")

For Each cell In rnge

    MsgBox cell.Value
    
Next
End Sub

What we want to do now is to tell the code to check each sheet in our workbook for our current cell value and see if it exists. If it does not, make a new sheet.
Cell value of 49, check sheet numbers.
Cell value of 50, check sheet numbers etc

The below will loop through each cell in my range and then loop through each sheet in my workbook. As is the nature of loops, the sheet loop will not exit until it has processed  through each sheet in your workbook. The order of the loop is:

cell value 49 -> sheet Template, sheet 50, sheet 51, sheet 52
cell value 50 -> sheet Template, sheet 50, sheet 51, sheet 52

Sub loop_through_ws()

Dim cell As range, rnge As range
Dim ws As Worksheet
Set rnge = range("A2:A5")

For Each cell In rnge


    For Each ws In ActiveWorkbook.Worksheets
    
    MsgBox ws.Name
    
    Next
    
Next

End Sub

What we need to do now is add in some conditional logic to check and see if our sheet exists or not. If it does not, add a new sheet and name it for the week number. We also want to make sure the sheet “Template” is being ignored in our loop. Once I process through all of my sheets, I want to store the max week (sheet) number I have in a variable. I am going to reset this variable to 0 for each cell loop so that I always know its value, though this isn’t technically needed here.

Sub loop_through_ws()

Dim cell As Range, rnge As Range
Dim ws As Worksheet
Set rnge = Range("A2:A5")

For Each cell In rnge

Maxweek = 0
    For Each ws In ActiveWorkbook.Worksheets
    If ws.Name = "Template" Then GoTo A
    If ws.Name > Maxweek Then Maxweek = CInt(ws.Name)
A:
    Next
If cell.Value > Maxweek Then
    ActiveWorkbook.Sheets.Add
    ActiveSheet.Name = cell.Value
End If
    
Next

End Sub

 

[the_ad id=”1393″]

Wrap Up

The idea here is that as we are looping through each cell in our range, we are also checking the name of each sheet. We want to ignore the sheet where our data is (Template). We also want to add in a flag to see if our sheet name is greater than or less than our cell value. Worksheet names are stored as text, so we convert that data format to an integer.
During our worksheet loop, we determine what our highest worksheet number is.
If our cell value is greater than our max worksheet name, then we create a new worksheet with the name of of the cell value, 52.

The entire loop process goes through these values, only creating a sheet when the cell value is greater than the Maxweek (highest sheet name).


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

Excel VBA and The For Each Next Loop

In our prior tutorial, we covered how to do a standard Do loop. Now, we will continue with another form of loop, the For-Each-Next Loop.  For-Each-Next allows you to specify a series or range of cells that you would like to loop through.

The general setup involves

Sub For_Each_Loop()
'set loop range
For Each 'range to loop through
'code to process in loop
Next
End Sub

Looping Through a Range of Cells

Last time we looped through a range of cells using our Activecell and offsetting it row by row for each loop iteration. In this example we will do something similar by specifying a range of cells as a variable, and looping through that range.

I have the below values in column A, cells A2:A7

Suppose I wanted to loop through them and multiply each of them by two. To do so I would do the below.
The general logic to make the below is that:

  • I have a range of cells.
  • I am telling Excel that my range of cells is A2:A6.
  • Assign the range to a variable.
  • State that For Each cell in this range, take the cell value and multiply it by 2.
  • Once this is done I move on the the Next cell
Sub For_Each_Loop()

Dim cell As range, rnge As range
Set rnge = range("A2:A6")

For Each cell In rnge
cell.Value = cell.Value * 2

Next

End Sub

 [the_ad id=”1391″]

Looping Through Worksheets

You can also loop through a series of worksheets, columns, or objects like pictures and graphs.

Sub loop_through_ws()


Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
    
    MsgBox ws.Name
    
    Next

End Sub

The above will loop through every sheet in my workbook, displaying each sheet name in a Message box.

[the_ad id=”1393″]


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

Excel VBA and the Do Loop continued – Do While Loop

In our prior tutorial, we covered how to make a standard Do loop. As we adjusted our code throughout the tutorial we ended up telling our loop to stop at our first empty cell.

Do Until IsEmpty(ActiveCell)

This loop is considered a Do Until Loop. The setup of the loop continues to process our series of statements in a loop until a set condition is met, at which point the loop ends.

There is one other version of the Do Loop which we will be covering in this tutorial, the Do While Loop. The Do While Loop allows you specify a certain condition to be met, and continue looping as long as that condition is true. As soon as the condition is false, the loop ends.

 

Application

An easy way to demonstrate this is to start with the integer 1 and increment it by 1 for each loop. Let’s say I want to only loop while my integer is less than 10.

  1. Create our Sub called Do_While_Loop().
  2. Define you variable as an integer (1,2,3,4 etc) I use “i” in this case
  3. Start your integer off at the value of 0
  4. Create your “Do While i is less than 10” Loop
  5. Inside of the loop, increment i by 1

You code will now loop continuously until i = 10

[the_ad id=”1391″]

Sub Do_While_Loop()
Dim i As Integer

i = 0
Do While i < 10
i = i + 1
Loop

End Sub

[the_ad id=”1393″]


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

 

Excel VBA Looping With Do Loop

Looping in VBA can be one of the most powerful things you can do as it allows you to process an entire range of information with only a few lines of code.

The standard setup for a loop is the below.

Do
'insert code to loop through here
Loop

VBA processes code from top to bottom.  When your code is processing, it will hit the Do line first and continue on as normal. Once it hits the loop however, it will go back up to the Do and process that code all over again. The code will continue doing this until told otherwise.

A warning in advance, if you have not told your code to exit the loop at some point, it will continue looping until you manually stop/break the code processing. This can typically be done by pressing the esc key or ctrl+shift+end.

Application

Lets use the below for example to show a basic loop. With this data, I want to know if the value in column A is greater than 10. If the value is greater than 10, tell me ‘Yes’ in column B or tell me ‘No’ if it is not. Use F8 to step through your code line by line to see how it is processing.

Sub loop_training()

Range("A2").Select
Do

If ActiveCell.Value > 10 Then
    ActiveCell.Offset(0, 1).Value = "Yes"
Else
    ActiveCell.Offset(0, 1).Value = "No"
End If

Loop

End Sub

My code starts off by selecting column A. The code then enters my loop where I am using an If-Then-Else statement to evaluate the value of my current cell, A2. Five is not greater than ten so it logs No in cell B2. Keep pressing F8 and you will notice something. Nothing else is happening. We keep going to the top of our loop, reevaluating our If statement for the same cell and then doing it all over again.

[the_ad id=”1391″]

 

Telling the loop to move down our column

All we need to do to our loop to cause it to run down our list of cells is to offset by one row for each loop iteration. This can be done by adding
ActiveCell.Offset(1, 0).Select

The  below GIF shows how this has effected our loop. We now run down the list values; however, we are still stuck in a continuous loop. We need to tell our loop when to stop looping.

 

Telling the loop when to stop looping

We can tell our loop to stop in numerous ways; I am going to highlight two that I use pretty frequently when looping using an Active Cell. The first is to stop the loop when my active cell is empty (null) and the second is to stop the loop when I have reached the end of my data.

The first option can be accomplished by changing our Do statement to
Do Until IsEmpty(ActiveCell)

This will have the below effect on our code.

Notice how our code did not finish running through all of our cells. This is because we have a blank value in our column. Typically, this option will be fine as most sets of data do not have a blank cell until the end of the data set; however, in this case, looping until we encounter our first blank cell is not the option we want.

The second option can be accomplished by adding a variable to our code before the loop. Variable lastrow will tell us what the last row in our data is.
lastrow = Range("A" & Rows.Count).End(xlUp).Row

You can always test your variables by stepping through the code with F8 and hovering your mouse over the variable.

We also want to remove our Do Until IsEmpty(ActiveCell)and change it to Do Until ActiveCell.Row > lastrow. Since our last row is 10, we want the code to stop once our Activecell is on row 11.

[the_ad id=”1393″]

 

You can now run the macro without stepping through with F8 in order to process everything in milliseconds.


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

 

Excel VBA Variables

A Variable in VBA allows you to store information to be referenced at a later time in the code. Variables in VBA are not too different than variables like x and y in basic algebra except that you can assign almost anything to a variable in VBA.

The first thing you want to do when making a variable is to declare it’s data type. This isn’t always required, as Excel knows for the most part what type of data you are assigning to the variable, but it is a good practice to do when starting off as it will help you in the long run.

Now, before you math haters run off, check out the below example of a variable to see how easy assigning variables can be.

Sub Learning_About_Variables()
x = 1
MsgBox x
End Sub

The above code assigns the value of 1 to variable x and then displays what x is in a message box.

If you are having trouble getting the above code to work, please see this brief intro to macros to get the developer tab added to your Excel ribbon. You will then want to insert a module and paste the above code into that module. You can step through the code line by line by clicking inside of the Sub/End sub and pressing the F8 button.

 

Declaring Variables

In our above example, we assigned 1 to variable x. The number 1 is an integer.
The simplest way to declare a variable in VBA is to use Dim.

Dim x as Integer
Dim y as Integer

[the_ad id=”1391″]

 

Using Variables to complete a calculation

Add y as an integer to your code. You can assign any number you wish to it; I have given it the value of 25 below. Running your code again will show the sum of x and y, 26, in the message box.

Sub Learning_About_Variables()
Dim x As Integer
Dim y As Integer
x = 1
y = 25
MsgBox x + y
End Sub

[the_ad id=”1393″]

 

Variables are not just numbers

You can make almost any value a variable, numbers, text strings, dates, currencies, and more. In the below for example, I am using a variable combined with an If-Then statement to tell me if x+y is greater than or less than than 30.

Sub Learning_About_Variables()
Dim x As Integer
Dim y As Integer

x = 1
y = 25

If x + y > 30 Then IsTheSum30 = "This equation is over 30"
If x + y < 30 Then IsTheSum30 = "This equation is below 30"

MsgBox IsTheSum30

End Sub

Note I did not declare the variable “IsTheSum30”. Not doing so causes the variable to be variant, which VBA automatically assigns a data type to. If I had assigned a data type to it, I would have declared Dim IsTheSum30 As String

 


If you liked this content and found it helpful, please like the page and leave a comment below. You can also ask questions on the Forum to get personalized help on your issues and questions.

 

 

 

 

Why you Should Not Rely on Recorded Macros

Recording Macros is great, right? It allows you to automate a series of steps in a process to make them more efficient.  Though while recording Macros is a great place to start learning about Macros and the VBA code they’re built upon, recordings should not be relied on solely.  After all, data will change, needs change, and processes change.  When that happens, your macros will break.

In our Intro to Macros, we learned how to record an Excel Macro that will type a value into a cell, and copy and paste it. Those two cells were A5 and A10. What happens when needs change and we need to instead type in A6? Our macro would no longer work.  We could always re-record the Macro as this one was rather short, but would it not be easier to just change the small bit of code, A5 to A6? What if our Macro was One Thousand lines of code long for a 20 minute process? Would you want to re-record that?

All of these questions you should ask yourself when making a Macro.  Recording is a good place to start, but learning how to type the VBA code, and adding in variables an loops to make the code dynamic is where you really want to be at; all of which we will continue to get into in greater detail later on.

[the_ad id=’1391′]

 

Editing Your Macro

Our Macro in our Intro to Macros had the below steps in it:

  1. Click in Cell A5
  2. Type “Hello World” into cell A5
  3. Copy A5
  4. Paste it into A10
  5. Bold A10
  6. Highlight A10 as Yellow

Which resulted in the below code being recorded:

Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("A5").Select
Selection.Copy
Range("A10").Select
ActiveSheet.Paste
Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

We can change our A5 references to A6 and our Macro will now meet our new needs. No recording required.

Range("A6").Select
ActiveCell.FormulaR1C1 = "Hello World"
Range("A6").Select

 

Streamlining the Macro

Recording a Macro will cause Excel to code anything you do, including inadvertent clicks, spelling errors, process errors, etc. VBA code is also processed step-by-step from top to bottom.  Having errors and un-needed clicks in your code will thus slow your macro down.

We can streamline our above macro a bit by removing the “Selects” of the cells.  It is not always required to have a cell selected in order to work with it.

Range("A6").Value = "Hello World"
Range("A6").Copy
Range("A10").Paste

Note how the code that bolds and colors the cell requires a selected cell.  No cell range is specified here, instead it is bolding whatever the currently selected cell is. To make sure we are doing this to cell A10, we can either add code to Range("A10").Select, or change Selection to Range("A10"). Both will work the same.

Selection.Font.Bold = True
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

If you found this content helpful, like and share and feel free to leave a comment below and we will respond as quickly as possible. If you have a question or concern, please feel free to email me via the contact form.

Thank you!

[the_ad id=’1393′]

Intro to Excel Macros and Visual Basic Programming

I am sure that many of you work in routine processes and work with Macros, or have at least heard of Macros. The most common type of Macro is an Excel based one, but you can use Macros in any application in the Microsoft Office Suite.

A Macro is defined by Microsoft as an action, or a set of actions that you can run whenever you want. Macros can be created by recording them or by coding them directly into your application.  The code behind the Macro is commonly referred to as VB or VBA.


Getting Started

Before getting started with Macros we will want to add the “Developer” tab to your excel Ribbon. To do this, go to:

  1. File
  2. Options
  3. Customize Ribbon

On the right Panel you will see “Developer“. Click the checkbox for it and hit OK


Recording a Macro

The easiest way to understand Macros and the code behind them is to record one yourself.  You can find the option to record a macro under your developer tab, labeled “Record Macro

Excel_Record_Macro

For this Macro Recording, we are going to:

  1. Click in Cell A5
  2. Type “Hello World” into cell A5
  3. Copy A5
  4. Paste it into A10
  5. Bold A10
  6. Highlight A10 as Yellow

Open your Excel File and Turn on “Record Macro“.
Click “OK” to start recording and perform the above steps.

When you have performed the above steps, click “Stop Recording”

 

[the_ad id=’1391′]

 


Running the Macro

To see how our macro works, delete all of Column A so that we have a blank spreadsheet. On the Developer Tab, click on “Macros” and run your Macro.  You should hopefully see the steps you did earlier, automatically recreated for you.

The below GIF shows a recreation of all of these steps.
Excel_Record_Macro1


Coding Behind it all

If you wanted to see the code behind your Macro, choose “Step Into” instead of “Run“. This will bring up a small code window showing you the steps that were recorded in your macro.

You can press F8 to step through each individual step in your Macro to see how it works.
Most of the programming here should make sense as you read it. VB/VBA is considered a “Logic Based” coding system and the understanding of each line of code can be pretty easily derived as you read it.

For example,  the below is selecting cell A5 and setting the cell value to "Hello World"
Range("A5").Select
ActiveCell.FormulaR1C1 = "Hello World"


This concludes our introduction into Visual Basic and Excel Macros.  We will be including more detail on Excel and Access VBA programming very soon.

[the_ad id=”1393″]