Home » Articles posted by TylerG

Author Archives: TylerG

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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

 

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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

 

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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

 

 

 

 

Excel Pivot Chart

Now that you know how to create a Basic Chart in Excel, we will now explore how you can create an Excel Pivot Chart using the same data form our prior tutorial.

In our prior tutorial you needed to manually sum up the Hours per Month in order to get the data into a format that would display nicely on a chart. Making a Pivot table of the data instead, can help you produce your chart more quickly, as well as allowing you to change it more easily in the future.

File:
Excel_Pivot_Chart

Making an Excel Pivot Chart

  1. Insert a Pivot Table into cell I1 of the above file. If you need a review of how to create a pivot table, please see our Pivot Table tutorial.
  2. Select Cells A1 through G10 and click OK
  3. Add Month to the Row Labels section
  4. Add Hours per Month to the Values section as a Sum

PivotChart1

  1. Click on your Pivot
  2. Go to your PivotTable Tools – Options tab
  3. Click on PivotChart
  4. Choose 2d and click OK.

You now have a Chart just like the one from the last tutorial except now you can control the chart with a pivot table. Filtering out March from the Pivot will make it filter out from the chart as well.

Note that Pivots sort ascending by default. You may want to change the order of the Months from Feb-Jan-Mar to Jan-Feb-Mar to get your chart in a chronological order.

Please see the below for all steps in making the Pivot Chart and Ordering the Fields.

Excel_PivotChart1gif

[the_ad id=”1391″]


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.

Please also consider making a small donation to help with the upkeep of the site and with the development of future content.

$0.99

Make a Basic Excel Chart

Excel Charts provide a way for you to graph out your numerical data in a visual format that is both easy to make and easy to understand. In this and subsequent tutorials, we will be using the below Excel data to create our charts.

File:
Basic Excel Chart

Creating a Basic Chart in Excel

A chart in Excel can be created from selecting almost any part of your data, going to the insert tab, and choosing a chart. A quick example can be done by:

  1. Select Month and Hours data
  2. Go to the Insert Tab
  3. Choose the Column – 2d Column option

Excel_Chart1gif

ExcelChart1

The resulting graph will show bars of data represented in the same format that our data is. You may be telling yourself, “that graph does not make any sense”, and you are right, it does not. The graph does show us our Hours and it does show Months, but the Months are repeated; just like how they are repeated in our data set for each employee/project.

[the_ad id=”1391″]

 

Making the graph make sense

First lets define our objective with this graph.

Objective: I want to see the total hours billed per Month

To complete this objective, we can either:

  1. Manually rearrange our data to sum the hours for each month and then graph it
  2. Run a pivot on the data that sums for us

Let’s start with option one first.

  1. Copy your Month and Hours column headers and paste them elsewhere in your spreadsheet
  2. Figure out the total hours for each month. You can do this by selecting each Hours cell with the CTRL key, or by writing a SUM or SUMIFS formula. All three options are displayed below.
  3. Once you have your totals for January, February, and March, select your data like you did before and graph it.

Selecting with the Ctrl Key – Verify totals at the end – 145 and 145
Excel_Chart2gif


Writing a SUM formula
Excel_Chart3gif


Writing a SUMIF/SUMIFS formula
Excel_Chart4gif


[the_ad id=”1393″]

 

Final Graph

Your final chart should look like the below. Note that your chart can look entirely different depending on your objective. If you wanted to see employee or project information in your chart then you can certainly add that in to see the breakout. It is all a matter of what you are looking to accomplish.

Next we will go over making charts from Pivot Tables, ways to format your charts, as well as pivot slicers that can control your pivot table and chart at the same time.

ExcelChart2


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.

 

Access Left Outer Joins

In our prior post we reviewed how to make a relationship to “join” two tables together using a query to produce a single data set. This query allowed us to see our employees and their related hours.

The join that we made between these two tables is called an inner join. An inner join takes the common records between the two data sets and displays just those common records. An easy example of this can be shown as:

Table A has employees Tyler and Steve
Table B has employees Bob and Tyler

If we were to join these tables together using an inner join, only the Tyler records would show.

To get around this predicament, we can use a left outer join.

Left Outer Joins

A left outer join allows you to display ALL of the records from table A as well as the records from table B where the join conditions are met. Using the above example, our query would show records for both Tyler and Steve, while still excluding Bob.

Jumping back into our database, take a look at the records we have in our query. We have three records, three employees. Open up the Employee_Data table and notice that we actually have four employees in this table; Taylor Lacky is missing.

This is because Taylor Lacky’s EMPLID, 56789 does not exist in Employee_Hours, so we only show the three matched employees.

 

[the_ad id=”1391″]

 

Changing the Join Type

When we made our join in the last post we made an innner join, Access’ default join type. We clicked on EMPLID in Employee_Data and drag-and-dropped it onto EMPLID in Employee_Hours. This motion established our Left (A) and Right (B) tables. Table A being Employee_Data and table B being Employee_Hours.

To change the inner join we have to a left outer join

  1. Double Click the line that is joining the two tables
  2. Choose the second option
  3. Click OK

You should now see a line with an arrow rather than just a normal line joining the two tables.

Run your query again and you should now see employee data for Lacky,Taylor while the hours information is null (blank).

Next we will go over joining multiple tables together, making unions,  and writing expressions (formulas).


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.

[the_ad id=”1393″]

Access: Making a Relationship Between Two Tables

Microsoft Access is considered a Relational Database system. A Relational Database allows you to establish relationships within your data to tie them together to form a single data set. In our prior Query Introduction post we went over how to…

  • Build a query based on one table
  • Filter via query criteria

In this post we will go over how to make a query that includes two separate tables to make one data set.


 

Files

Below are the tables I am using in my database. I’ve exported them into Excel so that you can import them into your database to follow along is you wish.

File:RelationalDB Tables

Below is what my database looks like once I add in all of the tables. None of my tables have a primary key, it is simply the data from the spreadsheet. Instead, I will be using EMPLID and Country/Country Code as my unique identifiers.

RelationalDB1


 

Building Relationships

Objective 1: Build a query to show employee names and hours
Objective 2: Build a query to show employee names, hours, and country/region descriptions

  1. Make a new query via the query design button on the “Create” tab.
  2. Bring in table Employee_Data and add the three fields to the query.
    Your query should look like the below.

RelationalDB2

Now that we have our Employee_Data, lets now bring in the Employee_Hours data.

  1. Add the table Employee_Hours to our query
  2. Add the fields from Employee_Hours to our query.

Note that you typically would not need another EMPLID field in your query but we are adding it here for the sake of instruction and learning.

  1. Run your query

Notice how your query is a jumbled mess and looks nothing like the data in either table. Joe for example is the only person that has worked 50 hours. What’s happening is that Access has no idea which employee hours goes with which employee. You haven’t told the query that EMPLID 12345, 50 hours, actually belongs to Joe which is causing Access to allocate each set of hours to each employee name.

[the_ad id=”1391″]

RelationalDB3

  1. Go back into the design view of your query.
  2. Click on the EMPLID field in your Employee_Data table and drag-and-drop it on top of the EMPLID in your Employee_Hours table.

You should see a line connecting the two now. You have told access that these two fields are related and have joined them together. Run your query again and you should now see your employee Names and Hours correctly.

RelationalDB4RelationalDB5

Next we will go over joining multiple tables together and then the different types of joins/relationships you can make between your data.


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.

[the_ad id=”1393″]

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′]