Using the Macros On the ExcelTips Website

On the ExcelTips sites, I try to include a variety of tips which can help you improve how you use Excel. Many of the tips I provide include suggestions on using macros to solve a problem or accomplish a task.

If you are not familiar with how to use macros, you may feel a little bit lost when the instructions or discussion moves toward macros. This is understandable, and it is doubtful that your disorientation will subside until you take the time to start learning how to work with macros, in general.

For absolute beginners, the best way to learn how to use macros is to either read a book or take a course. If you take the book approach, you'll want to look for a beginner's tutorial on the subject of macros. For example, here is one that I wrote:

     Microsoft Excel VBA Guidebook

You aren't limited to my book, however; there are many that are on the market. Here are a few more that I picked, at random, from Amazon:

     Excel 2016 VBA and Macros
     Writing Excel Macros with VBA
     Excel VBA Programming For Dummies

Note that one of those above specifically mentions Excel 2016. Don't worry—if you are using a different version of Excel, that book should still work for you. You see, the programming language used for macros is VBA (Visual Basic for Applications), and it has been essentially the same ever since the days of Excel 97, and is still going strong in Excel 2016.

If you choose to go the route of taking a course, there are a number of them available. I offer one a few times each year; you can find additional information on the course here:

     Excel Macros Master Class

Just like with books, mine isn't the only course out there—if you do some searching and poking about, you'll be able to find one to fit the way you learn.

When you decide to start using macros with Excel, there are two ways you can do it: You can either record a macro (using Excel's built-in macro recorder) or you can create a macro from scratch. The following two tips will help you out with both of these approaches:

     Recording a Macro
     Writing a Macro from Scratch

If you want to use any complete macros found on the ExcelTips sites, it is best to use the "from scratch" approach. You can tell if a macro is "complete" by whether or not it has the beginning "Sub" line and the ending "End Sub" line (in the case of a subroutine) or the beginning "Function" line and the ending "End Function" line (in the case of a function). For example, the following is a complete macro:

Function ToNum(X as Variant) as String
    Dim A as String

    A = Trim(Str(X))
    ToNum = A
End Function

To use such a macro, you would copy all the lines from the beginning "Function" line through the ending "End Function" line and paste it into the Visual Basic Editor.

If any of this still sounds confusing to you, then you really need to get ahold of a VBA tutorial (or a course) and start learning about macros. Remember—learning about macros takes time and patience, just like learning any other skill. Once you learn it, however, the way you use Excel will never be the same again and you'll get much more out of the program.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.