VBA vs Macros

| 7 Comments | 0 TrackBacks

A couple of months ago, Microsoft asked me to take a new, student grades template for teachers and enhance it with a little VBA so that it could print an entire class of student progress reports in a batch.

The existing template was built by fellow MVP, Beth Melton. It's a good example of some of Excel 2010's features, including Excel Tables.

It only took a few minutes to enhance the workbook with the VBA required to print each progress report in a batch.  It was very well received by Microsoft and they asked me if I'd be interested in penning an article for their Excel Blog specifically about how the code works.

The process of having an article published on their site is fascinating. A lot goes into it from their end.  And the experience is very different from what I'm used to with my little blog.

But I am happy to report that they published my article a couple of days ago!

Here it is, my very first article on Microsoft's Excel Blog:

If you liked this article, please share it!

Your Ad Here

No TrackBacks

TrackBack URL: http://www.excelhero.com/cgi-bin/mt/mt-tb.cgi/148


Since when can you refer to named ranges/tables with [NameHere]?

I've always used Range("NameHere").

Hi Dave,

The square brackets are used as shortcut notation for the Application.Evaluate method. This style of notation only supports constants between the square brackets, whereas the full evaluate method allows for variables.

But, the text can be the name of a named formula.

The square bracket notation originated with Excel Version 5 in 1994.

I like to use this notation as it shortens range references from code. Like everything else, there are pros and cons. I write about this in my blog entry here:


Congratulations.. good solid article.

interesting that you only made a passing reference to error handling though.

On Error Resume next often causes more problems than it fixes. People click on a macro and it executes without bringing up the debug box and they assume that everything is ok.

have you done a follow -up on error trapping?



Excel Addin

Hello EA.

I agree with your sentiment in general.

As I mentioned in the article:

"Error handling is a large subject... For many circumstances this would not be sufficient, but for our little program, this works well."

This is because the batch of reports will either print or they will not.

But the article was already a little on the long side considering the target audience. Adding and discussing a robust error trapping mechanism was beyond the scope of the article.

My students learn proper error handling in the Excel Hero Academy.

Hi Daniel,

Excellent article. Elucidating.

I was wondering what "screen recording" software you used for the little screen animation you have? It looks really good.


Hi Stefan.

I use Camtasia Studio for all of my videos.

For this particular article, I created an Animated GIF image from the screen recording. Camtasia offers a wide selection of output formats.

Animated GIFs are good for short clips and just about any browser can display them natively. But for longer clips, their file sizes get too large, and their other problem is that they are limited to 256 colors total in the entire clip. If you look closely you'll notice that some of the gradients of the Excel interface hard clipped. This is because of the 256 color limitation.

The Flash format is a better option for longer clips, but this reduces the number of visitors that can view the clip.

There's always choices.

Thanks, I was looking for information and your blog really helped me.
bulb flat steel

Leave a comment

About this Entry

This page contains a single entry by Daniel Ferry published on November 25, 2011 8:10 AM.

Awesome Formula Challenge! was the previous entry in this blog.

Excel Universal Calendar Template is the next entry in this blog.

Find recent content on the main index or look in the archives to find all content.