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.
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.
- Stackoverflow: http://stackoverflow.com/search?q=catia
- Mr Excel Forum: http://www.mrexcel.com/forum/
What tools do you use when programming Excel macros?
No comments:
Post a Comment
I'd love to hear from you!
-Nick