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

Excel Pivot Table Data Source

Excel Pivot Tables rely on a Data Source to populate the Pivot table.  We have established this data source in prior posts when we first make our pivots.  The data source can also be changed at any time to include more or fewer rows, and more or fewer columns.

You can find the data source of the pivot table by:

  1. Click anywhere in the pivot table area. This will open up the Pivot Table tools tabs in your menu
  2. Click on the “Change Data Source” button

There are two ways you can select your data, Column Selection and Data Selection, both options have pros and cons for why you might use each method.

Column Selection

Pivotlesson3a

Selecting an entire column such as A:F lets your Pivot Table compensate for more or fewer rows in your data. If my data set is 9 rows today and 20 tomorrow, a simple refresh of my pivot will allow my pivot to display that data automatically. Doing this method will also cause a “blank” record to display in your pivot.  You can filter this out if you want but you will need to keep an eye out on your filter options as new records such as a new employee may not always be automatically included in the pivot filter.

The below GIF demonstrates how refreshing a pivot table with a column based Data Source will automatically add a new record.  The GIF also demonstrates how a filtered Pivot Table will not automatically include new data if records like “blank” are filtered out.

Excel_Pivot3_ColumnDS

[the_ad id=”638″]


 

Data Selection

Pivotlesson3b

Selecting a specific range of data will only show that data in your Pivot Table. If your record set increases, your pivot table will not automatically adjust. You will need to go into the Pivot Table data source and change it to include the new rows, row 10 in the above example.  If the rows decrease, you will want to make sure you adjust your Data Source accordingly otherwise blank records will show.

I personally prefer this method when handing out templates to people.  Most of my templates require the user to export data from a query in our system.  In my opinion, it’s easier to teach people to manage their data source than to manage blank records, filters, adding new items, etc.

[the_ad id=”638″]

Pivot Tables and What to Watch Out for: Unique Records

Excel Pivot tables can be a useful tool when doing any kind of data aggregation; however, it is important to understand your Raw data fully before summarizing it, lest you add your data together incorrectly.


Unique Counts –  Number of employees at company

File: Excel_Unique_Count

Say you have a list of employees that are working for specific projects and customers of yours.  You log the hours each one of them spends working with that customer.
Pivotlesson2A

Count of Employees

Pivotlesson2b

Those less familiar with Pivots and their data may make the mistake of counting the EMPLID field by putting it into the value section which would end up counting all records, reporting eight employees at the company, when there are three.

There are two ways you can count these employees here. Both demonstrated below.

  1. Put ONLY the EMPLID in the row label section of the pivot to get each unique EMPLID.
    1. Select the employee IDs to see a count in the bottom.
  2. Put ONLY the EMPLID in the row label section of the pivot.
    1. Copy the unique IDs into a new column.
    2. Run a pivot on that

Pivotlesson2c

Pivotlesson2d

[the_ad id=”638″]

Unique Records

Pivot tables aggregate data based on the fields you put into row labels box.

I want to count the number of projects my employees are working on For Each customer. Watch below how the numbers change for each field I add into the Row Labels.

Counting projects gives me the same result as counting employees, just a count of the records in my raw data.

Pivotlesson2e

Adding in Employee Name will give me a count of projects/records per employee.

Pivotlesson2f

Adding in Customer Name will give a count of the number of projects per Employee and Per customer.

Pivotlesson2g

This last screenshot could be what I was looking for; though, it’s important to know your data.  Every bit of data will have certain anomalies that come with it.  Look at Bob’s record for example. He billed time twice to the same project and the pivot table is showing he is working on two projects for Customer2 even though he is only working on one.

If you want to get a unique count of project per Employee/Customer, you can do a process similar to the Employee Count we did earlier.

  1. Bring the project field into the row label section so that you get a unique view of each Employee-Customer-Project
  2. I like to setup my fields in tabular form so that each field is it’s own column and I like to remove subtotals for this since I will be copying the data elsewhere.
  3. Copy and Paste the data and then run another pivot on it like we did above.

Excel_Pivot2


In the next post we will go over things to watch out for with a pivot table’s data source.

[the_ad id=”638″]

Intro to Excel Pivot Tables

Excel Pivot Tables provide you with a quick and easy tool to summarize, aggregate, and reorient your data to display your needed results. I tend to use my Pivot tables in a manner similar to some basic queries I might write in Access; using them to Sum the number of Billed Hours, count the number of Employees, or change the representation of the data from my raw data set.

Please use the below file if you would like to follow along through this quick intro to pivot tables. I will be writing more posts on Pivot tables in the near future that go into more detail on everything you can do.

File:
Excel_Pivot_Intro


Make a Pivot Table

Using the attached spreadsheet, let’s start off our pivot by making a new WorkSheet to put our pivot on. This can be done by clicking the “Insert Worksheet” button at the bottom left of our Excel Screen. You also have the option of right clicking on your sheet tab (Sheet1) and choosing Insert.
InsertWS

Now that we are on our new sheet…

  1. Click in Cell A1
  2. Go to the “Insert” Tab
  3. Click the Pivot Table Button

Excel is now going to prompt you for your Pivot Table Range and/or Data Source. With this prompt window still open…

  1. Click on Sheet1 (note that Sheet1! has now appeared in your data source)
  2. Select your data, A1:F11 if you are using the above data.
    1. You can Select this data by using your mouse, or by using one of the Selection methods we learned earlier such as Ctrl+Shift+End
  3. Click OKExcelInsertPivot

Your Pivot table has now been created but is completely blank at the moment.  Notice on the right side you now have a panel that displays the column names (field names) from  your data as well as:

  • Report Filter
  • Column Labels
  • Row Labels
  • Values

Placing a field in the report filter box will allow you to use that field as a filter for the entire pivot table.  I tend to place fields here that I don’t want to see the actual data for but want to give the users of my report the field to filter by.

By default, date and time fields are automatically placed in the Column Label section; however, if you are like me, most date and time fields I work with are in a text format and won’t go here unless you place them here manually.

By default, non-numeric fields are placed in the Row Label section. This section will display your fields in a similar format to how they appear in your raw data.

By default, numeric fields are placed in the Values section and will default to either a Sum or a Count of the field.


Pivot Table 1

Let’s summarize our data by showing the number of hours our employees have worked.

  1. Add the “Name” field to Row Labels by either checking the box next to Name or by dragging and dropping the field into the Row Labels section.
  2. Add the “Hours” field to Values by either checking the box next to Hours or by dragging and dropping the field into the Values section.

You should now see a quick summary of hours by employee.
HoursByEmployee.png


Pivot Table 2

It’s likely that Pivot Table 1 does not give you the data you are looking for.  All we are showing right now are the total hours our employees have billed.  Perhaps you would like the hours billed to be represented within a specific time frame.

Luckily, we also have a Date/Time field called “Month” in our Data.
Check the box next to Month to add it to our Pivot Table.
HoursByMonthEmployee

Now we can see our Employees Billed Hours by Month. You might notice that the months are out of order. That is because Field sorting will default to an Ascending order (F>J>M).

We can place January 2017 above February 20017 by:

  1. Click on a Jan2017 cell.
  2. Place your cursor on the edge of the cell until the cursor changes to a + sign with directional arrows
  3. Drag Upwards until your see a grey placement line above Feb2017
  4. Let go of your mouse button to place the selection

ExcelPivotItemOrder


Pivot Table 3

Let’s say that my data set is getting too long, making it hard to compare Greg’s, Lilly’s, and Holly’s hours to each other. I can Pivot this information by placing the Month field into the Column Label Section, allowing me to see the data as a trend over time.

This can be done by dragging the Month Field from the Row Label Section to the Column Label Section.

Pivot3 Ex


Stay Tuned as we continue to dive more into pivots in future posts.

Please like and share if you found this content helpful. Thanks!

[the_ad id=”638″]

Query Introduction and Making a Simple Select Query

Making a query in MS Access allows you to do a multitude of things with your data and database. The most common purposes of a query are to:

  • Search and filter your data to find something specific
  • Aggregate and Summarize data
  • Automate processes

The simplest of queries to make is the Select Query. The select query, as it’s name indicates, allows you to select/retrieve data from your table(s). You can select specific fields, and specific records that meet your filters criteria that you define.

In our prior posts, we were working with tables that contained Employee Hours and Employee Addresses. Let’s make a simple Select query on the Employee Hours table.

Steps:

  1. Go to the Create Tab
  2. Choose Query Design
  3. Select the table tbl_employee_hours from the Tables Tab
  4. Click Add
  5. Click Close

AccessCreateSelectQry

You can see our table is now shown within our query as well as the field names within the table; however, trying to run the query right now will result in an error from Access because we haven’t told the query what to select yet. All we have told it so far is that we want “it” to be from this employee hours table.

To add all of these fields to the table, double click on the Asterisk at the top.  The asterisk at the top means “All” in Access and in SQL programming language. Adding the asterisk to your query will add all fields within this table to your query.

Click the view or Run icons in the top left to run your query on this table.

AccessSelectQ1

AccessSelectQ2

What’s the point, it’s just our table

You might be asking yourself what the point of this query is and honestly, in it’s current state, none. The query provides the exact same data results as what is in our table so let’s change that.

Go back into design view on your query and remove the field tbl_employee_hours.*
You can do this by hovering your mouse cursor just barely above the field name until it shows as a black down arrow. Left clicking will select the field and pressing the delete key will remove it.

AccessSelectRemoveField

This time, I want to add only the fields Name, Hours, and EMPLID.

Steps:

  1. Double Click on Name
  2. Double Click on Hours
  3. Click and drag-and-drop the field EMPLID into the first field/column slot. I want EMPLID to be the first field in my table and doing this will bump the other two fields over for me.
  4. View/Run your query.

Now we have only the data we want to see.

Lets go back to design mode.

Notice the row descriptions for the fields we added
We have:

  • Field – Displays the field name from the table. Can also display expressions (formulas)
  • Table – Displays the Table the field is from
  • Sort –  Allows you to sort the field
  • Show – Allows you to show or hide a field or expression
  • Criteria/Or – Allows you to set filters for your Data

AccessSelectQ3

Where Criteria:

For the last bit of this Select Query, let’s add in a filter to only see the record for John Doe. John’s employee ID is 12345. Add that to the Criteria for the EMPLID field and run your query.

Our query is now only showing John’s record.

This concludes the introduction to Queries and Select Queries. We will continue to dive into further detail on Select Queries and all facets of MS Access very soon.

[the_ad id=”638″]

MS Access: Data Types Summary

Now that we have our first tables created in our database, it’s now time to talk about Data Types. Every field in a table has a data type which defines how Access will handle that field, as well as the type of data that can go into it.

You can easily see these data types by going into design view on one of your tables.  In the address table, for example, we have Number, Text, and Date data types.
AccessDataTypeExample

Please visit this page to view a list of all primary Access Data Types.


Data Type Limits

To show how these data types control the behavior and type of data that can go into each field…

  1. Go into “View” mode on your table
  2. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  3. Hit Enter to exit the field

AccessDataType01AccessDataType02

The zero is now gone. Zeros prior to a set of numbers are called leading zeros and the data type of “Number” does not support this format; which, technically makes sense, right? Numbers are shown as 12345, 1000, 9999.


I need leading zeros

Let’s say that you are working with data that has leading zeros, or maybe you wanted to add them to your five digit EMPLID to make the ID seven digits long. This can be done by first changing the data type of your EMPLID to Text.

Text is a data type that is used for alpha characters (ABCD)  as well as a combination of alpha and numeric characters. The Text data type also only supports up to 255 characters.

  1. Go into design mode on your Employee_Addresses Table
  2. Choose “Text” from the EMPLID data type drop-down
  3. Go into “View” mode on your table
  4. Add a zero prior to any of the EMPLID numbers like I have for Lilly.
  5. Hit Enter to exit the field

AccessDataType03
AccessDataType04

We now have our leading zero in our EMPLID!


To Note:

It’s important to note that the data in these tables came from Excel.  Access auto-identified the data types when we pasted it into the table. Even if we had pre-formatted our EMPLID field to be Text in Excel, Access would still have made this field a number.

MS Access: Creating a Table

In this tutorial we will learn how to create a small table and fill it with data. These tables will be filled in manually by us and will be maintained within the Access Database.

The two tables will be:

  • Employee Hours Billed
  • Employee Addresses


Excel Files:
Employee Hours Billed
Employee Addresses

 


Steps:

  1. Open the blank database you created earlier
  2. Go to the Create tab
  3. Click on Table

    Notice that a screen has popped up that resembles an Excel spreadsheet with a row and column grid. There is also a “Table1” in the left “Access Objects” Panel

  4. You can type in the values manually or copy and paste them. Lets do the latter to make things a bit easier
  5. Use the attached Employee_Hours_Billed spreadsheet and select the range A1:E5
  6. Copy the data
  7. Go to your Access Database
  8. Right click in the “Click to Add” column and choose “Paste as Fields”
    1. If you get a warning that the field name you provided is a reserved word, click “OK”
  9. Access will notify you on how many records you are about to add to the table (4)
  10. Press “Yes”
  11. You will now see your data in the Access table just as it was in the Excel spreadsheet.

Access_Paste_Tbl_data

You might notice that there is an ID column and an EMPLID column in our table. When you make a table in this manner, Access tries to establish it’s own unique identifier. The default is an ascending number for each row and since we already have a unique ID for our employees, we can delete the default from our table as it is not needed.

Access terms this “Unique ID” as the “Primary Key”

  1. Go to design view (far left icon) for our table, you will be prompted to save
  2. Save the Table as “tbl_Employee_Hours
  3. Select the “ID” field
  4. Right click
  5. Choose delete rows
  6. Access will double check and make sure you want to delete the row, choose “Yes”
  7. Access will warn you that you are deleting the “Primary Key”, choose “Yes”
  8. Click the “View” button (far left icon) to view our data
  9. You will be prompted for a save, choose “Yes”

Access_remove_PKey


Congratulations! You have now created your first table in Access!
Repeat these steps for the Employee Address information

MS Access: Getting Started

In this MS Access series we will be using a 2010 version of Access. Please keep in mind that versions 2003, 2007, and 2013 are very similar to 2010 but some of the controls may look different or be located in different places.


Summary:

In this post we will go over:

  • How to open MS Access
  • Create a blank database (DB) and save it
  • The general controls available to you that we will use in the next few tutorials

  1. Open Microsoft Access and you will start on a screen that looks like the below.
  2. Click on New in the left panel if your screen does not look like this
  3. Click Blank database if it is not already selected
  4. On the right panel, choose the name you want for your database and where you want to save it to.
  5. Click Create

AccessOpen


Your database may open with a table called Table1 already created. Go on ahead and close this by either right clicking on the tab and selecting close, or click on the gray “X” on the far right.

AccessOpen1

Home Tab:

Your Home tab is your default tab in Access. If you have used any other Microsoft programs before, you will likely recognize most of the options here. All options on the home tab are currently grayed out but will be usable once you start making objects (tables, queries, forms) in the database.

The controls on the home tab allow you to:

  • Change the view type of the table, query, or form you have selected
  • Copy and paste data or database objects
  • Sort and filter your data
  • Refresh the tables and queries in your database
  • Format the data or controls in your tables and user forms

AccessHomeTab

Create Tab:

The create tab is where you will generally go to make anything in your database.

Avoiding specifics for now, this tab allows you to create:

  • Tables – Houses your information
  • Queries – Selects, filters, and/or combines information from your tables
  • Forms – Provides a user interface to interact with your database
  • Reports – Similar to queries in that they select and summarize information but Reports tend to run off of the work a query has already performed
  • Macros – A set of Access commands or VBA code that run via user input to perform a series of processes automatically

AccessCreateTab

External Data Tab:

The external data tab allows you to import and link your database with other sources of information. For example, if you had a list of information in an Excel spreadsheet, you could link or import information and work with it within Access. The possibilities for what you can connect to here are really endless as it can range from an Excel Spreadsheet, a Corporate data warehouse, or a website.

Types of Data Connections:

Linking allows you to continually have the most up-to-date information in your database. If you link to a list on a SharePoint site and then go to that site to make a change to the data, that change will be reflected in your Access Database.

Importing provides a static copy of the information at the time it was imported.

AccessEDTab

Database Tools Tab:

This tab provides a series of tools to analyze your database’s efficiency, clean and repair the database data and structure, and move information to other databases and data warehouses. This tab will not be used much during this series and can be disregarded for now.


 

Up Next we will go over how to create a table, fill it with data, and run a query on it.

MS Access: Overview

Overview

Microsoft Access is a Database Management System (DBMS) that provides tools to create relationships between information and provides a user interface that is easy to understand, but flexible enough that it can house the most complex of processes.

Microsoft Access provides its own data storage services as well, which in my opinion, puts it in the forefront of programs one should learn when trying to grow their technical skillset. Other DBMS’ for example, like Microsoft SQL Server, or Oracle may store data, but they then require some form of programming knowledge in order to retrieve it.

Before we dive further into what MS Access is, what it looks like, and how to work in it, I would first like to go over a few terms that we will be using later on.

Terms:

Table:
A table is a place that you store information in a database; think of it like a Microsoft Excel Spreadsheet. You have your columns, columns headers, your rows, and your data.

Query:
A query allows you to pull and review a table(s) information. It can be used to Select only columns you want to see, to filter out information, to add information together, or to relate two tables of data together to combine them.

Field:
A field is a column in a table or query. Whereas in Excel you have columns A, B, and C, in Access, the field is typically represented by a field name. In an Employee Table for example, you may have Fields called “EmployeeID”, “Name”, “Country”, “Zipcode”, and “Address”.

Note that fields can also be referred to as a number, starting with the number 0. In the above example, Field 0 would be EmployeeID and Field 3 would be “Zipcode”

Record:
A record is a row of data in your table. Similar to Excel’s rows 1, 2, 3…

Unique Identifier:
Most tables you make or use will have have a Unique Identifier (UID). A Unique ID is something that distinguishes a record from all other records. An example would be “EmployeeID” or “Social Security Number”. No other record in that table should have that number.

EffectiveDate or AsOf_Dates:
As a table is used throughout time, you may want to make use of an Effective Date field. And effective date allows you to make multiple records with the same Unique ID, but have them dated to a period of time. For example:
On 10/1/2005 John Doe lived as 1234 Park Place.
On 5/1/2012 John Doe moved to 1111 New Address Road.
Having a history of data is useful if it is ever needed; however, if I wanted to select the most recent/current Address, I would want to query on the most recent EffectiveDate for John Doe.

Criteria and Where Clauses:
Criteria, or “where” clauses, are placed in a query to filter for the data that you want to see.  The employee table for example could have ten thousand records in it for ten thousand Employees, but if I only want to find the employee information for John Doe, I would place his Name, or better yet, his EmployeeID in the query criteria to only pull his record(s).

Relationships:
In a database, a relationship is something you make between two tables. Think of our Excel VLOOKUP for this one. We have two tables (two data sets). One table is Employee Names, the other is Employee Addresses. Both Tables have EmployeeID, our unique identifier. We make a relationship, or “Join”, the two tables by EmployeeID in order to tie the data together.

How to use the SUMIF Function

We have gone over the SUM formula to total and aggregate data and we have gone over the IF formula to find data based on defined criteria/logic.
Now, lets combine both of these learned lessons to write and handle a SUMIF function, allowing you to SUM only the data that matches your specific criteria.

Before we begin, let’s do a quick review.

SUM Review:

A sum function is one of the basic Excel math functions that you can use to add data together to get a total. You can add together specific cells and ranges of cells.

=SUM(D:D) for example, will SUM everything in Column D
=SUM(D3,D4,D6,D7,D9) for example, will SUM only those specific cells

IF Review:

The IF statement allows you to setup a logical comparison for your data and identify two possible outcomes.  The two examples below involve a real world logic scenario one might evaluate in order to decide what to wear based on the weather.

If it is raining outside, wear a raincoat, otherwise wear a t-shirt
=IF( raining , wear raincoat , wear t-shirt )

If it is raining outside , wear a raincoat, IF it is snowing outside, wear a Heavy Coat, otherwise wear a t-shirt
=IF( raining, wear raincoat, IF( snowing , wear heavy coat, wear t-shirt ))

Use the file below if you would like to follow along:
SUMIF


SUMIF

A SUMIF combines the summing of numbers and the logic of an IF statement; summing only numbers that meet your defined logic or criteria.

Using the attached document, a standard AutoSUM on Column D would give us 215
SUM IF SUM

This information doesn’t really help us too much though. It’s good to see the total number of clothing we have sold, but what I really want to know is:

Items sold when number is 5?
Items sold when number is greater than 25?

How many RainCoats have we sold?

Let’s adjust the SUM formula to be a SUMIF formula.

Formula Breakdown:

=SUMIF( Range and typically your SUM Range , Criteria that defines what to SUM, Optional Cells you may want to SUM instead of the original range)

Application:

Question: Total Items sold when number is 5?

Type =SUMIF( into D12 to start your formula
Choose D2:D11 as your SUM Range and add a comma ,
Type 5 and close your formula with a parenthesis
=SUMIF(D2:D11,5)

Our formula result is 15, which is correct as there are 3 instances where we sold 5 items, 5+5+5=15.


Question: Total Items sold when number is greater than 25?

Type =SUMIF( into D12 to start your formula
Choose D2:D11 as your SUM Range and add a comma ,
Type “>25” and close your formula with a parenthesis
=SUMIF(D2:D11,>25)

Our formula result is 130, which is correct as there are 2 instances where we sold more than 25 items, 50+80=130.

Note that D4, 25, was not included in the SUM because our criteria was greater than 25, (>25), not greater than or equal to 25, (>=25).


Question: How many RainCoats have we sold

This one is going to switch up the formula a bit as we will be adding in the 3rd SUM_RANGE option.

Type =SUMIF( into D12 to start your formula
Choose C2:C11 as your Range and add a comma ,
Type "RainCoat" and add a comma ,
Type D2:D11 to select the range of cells you want to SUM
=SUMIF(C2:C11,"RainCoat",D2:D11)

Our formula result is 40, which is correct as there are 3 instances where we sold RainCoats, 5+25+10=40