This is the first in a series of How To posts that are targeted at Excel users who are unfamiliar with VBA programming. Topics will include
- Using someone else’s macro
- Recording your own macro
- Fixing a recorded macro
- Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
- VBE settings
- VBE features
- Debugging and error proofing
Suppose you need Excel to do something beyond its normal capabilities, or just do something automatically to save you from hours of tedium. The answer is often a VBA procedure, or “macro”. You’re not a programmer, you don’t know a macro from a mackerel, so what do you do? You could ask a colleague, you can Google for help, you can ask on a forum or newsgroup. And the answer is, “Use this macro.” Oh boy. Now what do you do?
Let’s put the macro into your workbook and try it out. We’ll use the following simple macro for this exercise.
Sub TestMacro()
' center alignment
Selection.HorizontalAlignment = xlCenter
' font: arial, 10 point, bold, blue color
With Selection.Font
.Name = "Arial"
.Size = 12
.Bold = True
.ColorIndex = 5
End With
End Sub
The first step to using a VBA procedure is to open the VB Editor (VBE). There are two ways to get there:
- Developer* tab > Visual Basic
- Shortcut key combo Alt+F11
*If you don’t have a Developer tab on your ribbon, right-click between buttons in the ribbon and choose Customize Ribbon. In the list of ribbon tabs on the right of the dialog, check the box in front of Developer.
This opens the Visual Basic Editor window. Find your workbook in the Project Explorer pane. By default, the Project Explorer is docked along the left edge of the VBE window. If you don’t see it, activate it from View menu > Project Explorer.
Note the treeview of the workbook. The top level is the VBAProject, with the workbook name in parentheses. Under that is a folder entitled Microsoft Excel Objects. Within this is an element for the workbook (ThisWorkbook) and for the worksheet, with the sheet name in parentheses. There would be an element in this list for every worksheet and chart sheet in the workbook.
We need to insert a code module. This is easy: Insert menu > Module. Or right-click on any of the elements in the project’s treeview, choose Insert > Module.
A new folder, Modules, has opened under the VBAProject entry in the treeview, and an item called Module1 appears in the folder. A blank code module window has opened in the VB Editor.
Copy the macro code from wherever you found it, and paste it into this window. Red text indicates a syntax error, so you will need to figure out what’s wrong. Often it’s just a problem with line wrapping in email or on a web page, or maybe you need to change curly quotes to straight quotes.
This macro requires a range to be selected, so go back to Excel and select a range, then run the macro. This can be done in a number of ways:
- In Excel
- Developer tab > Macros, select the Macro, and click Run
- View tab > Macros, select the Macro, and click Run
- Shortcut Key combo Alt+F8, select the Macro, and click Run
- In the Visual Basic Editor
- Run Macro button on the Visual Basic toolbar, select the Macro, and click Run
- Click in the macro with the mouse and press F5
This is the first exposure most people have to VBA and the VB Editor. In a series of upcoming posts, I’ll cover the following VB topics:
- Recording your own macro
- Fixing a recorded macro
- Assigning a macro to a button or shape, ActiveX button, or menu or toolbar
- VBE settings
- VBE features
- Debugging and error proofing
Dan says
You know Jon, I’m going to take a tangent on this topic. I am an experienced programmer (in other languages) and it seems like every VBA lesson out there assumes that you have no knowledge whatsoever. Are you aware of some better advanced material (i.e., I know how to program and now I want to know how to connect to a SQL server and a few excel sheets on networked drives and then perform data validation and calculations on the combined data with VBA). Sometimes I think it would be faster to figure out the COM model and just use IronPython :P
Excel’s object model is a bit quirky and it’s implementation of OOP seems to be lacking, where is the material on that stuff?
Thanks
derek says
I look forward to this series. VBA is the frontier I just cannot seem to cross. I can do a lot with “charts and things” without VBA, but usually it means I have to maintain my sreadsheets myself. If I could turn my knowledge into robust simple programs, I could hand more work off to my colleagues :-)
Jon Peltier says
Derek – Charting was the last area of VBA I got into. There was something scary about it. Or that I was losing control. But I got over it, got into the branch of the object model that dealt with charts, and despite some funny business there, realized it’s really no different that the rest of it.
Dan – The advanced work is not readily available as tutorials. The examples in advanced Excel VBA is there, but hard to find. You might find it useful to expand your search to VB6: those resources are still available despite dot-net having taken over. There are a small number of advanced books. Walkenbach’s books range from beginner to more-than-intermediate, while advanced topics and details are found in the Excel VBA Handbook series and the seminal Professional Excel Development (look for authors Bullen, Bovey, and Green together, and occasional others).
Here is a link to a number of books about programming in Excel and VBA:
https://peltiertech.com/Excel/xlbooks.html#ExcelVBA
And here are the most advanced Excel VBA books you will find:
derek says
Just to be clear, I wasn’t saying I’m new to VBA as it applies to charts. I was saying I’m new to VBA, period.
Jon Peltier says
Derek – VBA isn’t too tough. The macro recorder (see the next post) gives you much of what you need, then it’s a matter of fiddling around, or using Google, to make it do wyat you need.
Jorge Camoes says
Jon, I believe Derek is the user Charley Kyd talks about…
Derek, believe me, I am not a programmer, and I don’t go much beyond the examples above, but once you start automating some tasks with recorded macros you will not turn back. There are many things that you simply can’t do without VBA (like stupid and repetitive tasks and user interfaces).
Jon Peltier says
Note to self: blog about how I got started with Excel programming.
Jorge – I’ve heard it said that laziness is the mother of all inventions, which may be true. I know for me, laziness is why I got started programming. Back in the old days of Excel 4, I had this technique I used to model material deformation behavior, and it required iteration between two Solver loops, one for loading and the other for unloading. You had to run alternative Solver simulations on each part until the difference between them was less than some small acceptable error, which meant probably a total of a dozen or so Solver loops, and you had th change all of the constraints and target cells and changing cells each time. Then repeat for a total of a dozen cases. So let’s say 144 Solver loops, each set up manually. This took more than half a day to do by hand, and it was tedious as hell, almost as tedious as this description. Anyway, I spent a couple weeks in Excel with the XLM manual in my lap, and I automated the sucker, and finally it ran in about two minutes. When I got Excel 97, this was my first VBA project, and it was hard, but I got the time down to ten or fifteen seconds. There was no turning back.
Dan says
Thanks for the pointers (no pun intended) Jon.
mathieu imbert says
Hello,
I m in need of urgent help!
I m putting together a large presentation requiring the use of the same layout (including graph)
I m trying to build a blank template with graphs that I can copy & paste several times in order to save time but I m encountering some issues with the source data of the graph
It keeps pulling the information from the original sheet (sheet 1) in order to correct the graph I have to go in
-source date and reselect all the cells for each individual cell.
Is there away to have the graph read only the cells vs sheet + cells?
Your urgent help is appreciated
Thank you
Mathieu Imbert
Jon Peltier says
Following guidelines in Getting Answers For Your Excel Questions, please submit your question to a dedicated Excel forum.
Sajjad says
Dear Jon Peltier
I have a basic question. How to select range before running macro. can you elaborate in a screenshot. I am using one of your macro for exponential axis.
Jon Peltier says
Sajjad –
You can use Application.InputBox to let the user select a range. I have examples in Interactive Chart Creation and Interactive Formatting of a Chart Series with a Cell’s Color.