Monday, October 6, 2014

7 Resources for Excel Macro Programmers

Here’s a list of seven resources I use almost everyday when programming macros in Microsoft Excel:

1. Built in Help Features

The Microsoft Visual Basic Help Documentation can be accessed by going to Help > Microsoft Visual Basic Help or pressing F1 while inside the Visual Basic Editor. The best part about it is the search feature. I use this all the time!
Another built in feature that can help you solve your problems is the object browser and is a great tool to use when you’re stuck and don’t know what to do next. While in the VBE go to View>Object Browser (or simply hit F2) and use the search bar.
7 resources for excel macro programmers
 

2. Developer tab

You should display the Developer tab or run in developer mode when you want to write macros, run macros that you previously recorded, or create applications to use with Microsoft Office programs.



To show the developer tab: Click the Microsoft Office Button and then click Excel Options, PowerPoint Options, or Word Options. Click Popular, and then select the Show Developer tab in the Ribbon check box.

 
3. Flowchart Process Diagrams
When you’re programming a complicated macro it’s often a good idea to visualize how it’s going to work before you start typing away.  For these large problems I like to create flowcharts to get an overall idea of how the code will flow. It also helps to think about the steps the macro will need to perform, including all the user inputs. I often write out a flowchart, decision tree, or quickly sketch a process map. My favorite tool for this task is Draw.IO because it’s free and it lets you create flowcharts right in your internet browser and you can save your charts directly into Google Drive, DropBox, etc.
Begin sketching flowcharts here: https://www.draw.io/
Another option is to use something simple like Excel, Word, PowerPoint, or Snagit, like this flowchart:
 
4. VB Script Functions
No, I haven’t memorized every single function that can be used in VB. Instead, I prefer to know where that information is and simply look it up really fast. This page contains all the built-in VBScript functions, like Date, Array, IsEmpty, etc.
5. Pre-written Excel marcos
It’s easier to start with code you know works rather than writing everything from scratch. I almost always start a new macro by taking snippets of code from macros I’ve written earlier. If you’re new to programming and don’t have a pool of macros to pull from you can use some of mine. I’m compiling a list of Excel macros that can be downloaded from my website. Especially useful is how to combine workbooks.
6. Notepad++
Sometimes you don’t necessarily need Excel to write your code, just a good notepad tool to help you get the job done. You could use Notepad, Wordpad, or Microsoft word, but instead I recommend and absolutely love Notepad++. I use it all the time for tasks like: looking through other programmer’s code, record macros, compare text files for differences, view/edit files. It’s much better than using plain ole Notepad. It’s also where I compile all my examples and tutorials before copying them to my website or book pages.
Download for free here: http://notepad-plus-plus.org/
 


7. Eng Tips / Stackoverflow / Mr Excel Forum
When I’m completely stumped by a programming problem I turn to forums where I can get potential solutions from over programmers. The forums listed are all simple to setup an account at and you can start posting questions right away. Often times, other programmers have had the exact same question and you can find the solution by searching the forums. So if you’ve gone throw tons of Google search pages and are still pulling your hair out, got to one of these forums and get the answer you need.
What tools do you use when programming Excel macros?

Monday, September 8, 2014

Getting started with Microsoft Excel Macros

I’ve had many readers contact me explaining how they want to start creating macros for Excel to automate their work but they just don’t know where or how to begin. In order to help out I’ve created a short PowerPoint presentation called “Getting started with Microsoft Excel Macros.” I uploaded the ppt to SlideShare where you can download it for free and it’s embedded below.



This introduction to macros quickly covers topics such as why use macros, programming basics, how to create your first macro, the drawbacks of using the macro recorder, troubleshooting, and more.

I’d love to get your feedback on this. Would you like to see more PowerPoint presentations like this or not? Do you have any questions about getting started with Microsoft Excel macros?

Thursday, August 14, 2014

Quick Excel Tips: Dynamic Lookup

Today, I’m going to show you one of my favorite Excel tips: Dynamic Lookup or Dynamic Searching by using a combination of VLOOKUP and MATCH functions. Bascially, this function combo makes it so that the column that you pull the data from is dynamic based on the header making it more flexible than VLOOKUP by itself because you don’t have to rely on knowing the index column number.

In my example (that you can download below), in cell B2 I have this formula that combines VLOOKUP and MATCH:

=VLOOKUP($A2, $D$2:$G$14, MATCH($B$1,$D$1:$G$1,0),FALSE)

excel dynamic lookup formula


The column header in B matches one of the column headers in D, E, F, G - it doesn’t matter which one, you can change it and the values update automatically, which is the beauty of this formula. This makes it easy to add or remove columns without having to update your formulas. Not only is it more dynamic, the index column need not be on the left. Try it yourself!

Download my Dynamic Lookup formula example spreadsheet here.

Monday, August 4, 2014

Personal Finance Tracking Template

I think I've talked about posting this before (and never did) but after a reader’s request I've finally made my personal finance tracking spreadsheet available to download. Since becoming a new parent it’s even more important than ever for me to track my finances due to all the additional expenses, like doctor visits, buying diapers, more groceries, daycare (holy crap it’s expensive!!) and more. You don’t need to buy any expensive personal finance software, just use a simple Excel spreadsheet and I'll show you how.


The first sheet in the workbook is the Categories page where I list what I want to categorize each of my bills as: gas, electric, mortgage, cable, etc.


Then there is an Income page and an Expenses page with drop down lists driven by what is entered on the Categories page. Finally, a Totals page shows you how much you’re bringing in and how much you’re spending each month. I left a few rows filled in on the template so you can easily see how it works (data is made up and not real).




Pie charts are a great way to visualize your monthly expenses to see what you’re spending the most money on. Sometimes it's quite eye opening, like "wow, we spend a LOT of money eating out! Maybe we should start to make more home cooked meals."
 



I use my spreadsheet to see how much we’re spending on food, gas, entertainment, etc. and you can use it as a free budget planner tool too! You can set a montly goal on the Totals page and see if your spending stays below it.


Personal Finance Tracking Spreadsheet.xls download



This template uses:

  • Data Validation
  • Name Manager
  • Pie charts
  • Line Graphs
  • =Month()
  • =Average()
  • =Max()
  • =Min()
  • =Sumif()
 





As you can see on the example income page, I'm a big believer that in this day and age everyone should try to have more than one source of income, even if your secondary income is much smaller than your primary income stream.
 



This can be accomplished by anyone who has access to the internet, because there are multiple ways to make a little extra in your spare time: online survey taker, freelancer, writer, affiliate marketer, social media manager, graphic designer, Fiverr gigs, blogging, Amazon buyback program, etc. All it takes is a little extra effort.

Tuesday, July 8, 2014

2014 NFL Helmet Schedule

Last week I noticed the start of the 2014 college football season is only two months away, which means the 2014 NFL season is not far behind (and actually preseason games start in less than a month). I’ve updated the 2014 schedule spreadsheet that includes all NFL helmets from every team. It’s a fun way to look at your favorite team’s NFL schedules 2014.

2014 nfl helmet schedule

Even though basing a team's supposed “ease of schedule” on the previous year's record is a faulty premise, it's still fun to do and happens all the time. For instance,  the Indianapolis Colts have the easiest schedule based on 2013 winning percentages. Don’t be surprised if the Steelers are back on top of the AFC North this year due to what should be an easier schedule than last year. Statistically, the Raiders have the toughest schedule in the NFL this year, based on 2013 records.

Download the helmet schedule using the link below and let me know what you think about your team’s chances this year:


Yes, it takes quite a long time to assemble all the NFL helmet logos each and every season but its a fun way to look at the schedule and all NFL helmets at the same time. The NFL doesn't see as much change year to year as college football does but in the near future it looks like we could have a team in Toronto, LA, and London, as well as a new logo in Washington.

Wednesday, July 2, 2014

NCAA 2014 College Football Helmet Schedule

It's hard to believe but at the time of writing this post at the beginning of July the college football season kicks off in less than two months! Ohio State fans like myself can finally move on after those two devastating losses to end the season. This college football helmet schedule spreadsheet is not a lot of fun to update because there are so many helmets to move around and to make things more difficult the teams in the college football conferences are never the same, like with the introduction of Rutgers and Maryland to the Big Ten for example.


This 2014 college football helmet schedule in Excel includes every team from all ten conferences plus independents. Every game is listed as either home, away, or neutral site (denotated at the bottom of each sheet).  A college football helmet schedule spreadsheet may be available on other websites but, to my knowlegde, this is the only downloadable Excel version and unlike some of the others is 100% FREE! Download it today using the link below:

Download the 2014 NCAA College Football Helmet Schedule.xls

In the future I think I will look for a way to make the spreadsheet easier to update and more maintainable. At least maybe figure out how to make the football helmets automatically populate. Maybe there is also a way to import the schedules from ESPN's website. Any ideas how to make the yearly update go quicker?

Sunday, June 15, 2014

2014 World Cup Bracket in Excel

I’m a pretty big sports fan but I have to admit I’ve never really followed the World Cup before. I had many readers asking if I’ve ever made a World Cup bracket in Excel so this year I decided to do a little research to learn more about the world’s most popular sporting event. If you're an ignorant American like me you can use this template to learn more about the World Cup format. Here's what the bracket looks like, then I'll explain how it works:


2014 world cup bracket in excel


The FIFA World Cup tournament begins with 32 teams from six continents and takes place once every four years. The teams are initially placed into a group of four. The team groupings are shown below:

2014 fifa world cup team groupings


The host country, Brazil, has won five World Cups and is one of the favorites to win this year. After all group matches are finished, the top two team of each groups will advance to the round of sixteen. Here's the format of the remaining rounds:

world cup format


That's how it works! The spreadsheet also includes the FIFA World Cup schedule, TV stations you can catch the games on, and where each game is going to be played. Few are giving Team USA a chance to advance past that stage, let alone make a run toward the final.

World Cup time means it's time for that weird guy at work to really shine because he knows all the special rules. In the spreadsheet I even included a sheet on World Cup terminology:  it’s not a uniform, it’s a “kit”, they’re not shoes; they’re boots, it’s not soccer, it’s football! Now you won’t be clueless when watching the games in a bar throughout the next month.