Excel Spreadsheets Help: October 2014

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.

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.

VB Script functions: http://www.w3schools.com/vbscript/vbscript_ref_functions.asp

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.

Download macro examples here: http://excelspreadsheetshelp.blogspot.com/p/downloads.html

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.