blog.vbmacros.com

Two is better than ... three?

2008-02-13T08:54:00.001-06:00

In my previous post (Out of the Gates) we modified a recorded macro to perform steps with locations relative to the starting point rather than absolute locations on the spreadsheet. The modified macro allowed us to start at any location in the spreadsheet and the actions would perform in locations relative to our starting point. However, our modified macro still included 3 lines of code like our original.

* By the way, the default mode during recording is to record absolute addresses. But here is an article describing a step to make your macro record with relative addresses (http://support.microsoft.com/kb/213740).

Sometimes when recording a macro, extra steps are recorded that are not necessary to accomplish the desired result. As in our example from the previous post we pressed the right arrow, entered a 1, and then pressed the left arrow. This seems like 3 logical steps. But the desired result is to put a 1 in the cell to the right of our starting cell and move the selection to the cell immediately below our starting cell. That can be done in 2 steps.

The first step is to put a 1 in the cell to the adjacent cell to our right. So, we use this command:

ActiveCell.Offset(0,1).FormulaR1C1 = "1"

The above command is basically a shortcut to complete steps 1 and 2 of our previous macro. Instead of moving our selection, we just tell the macro to place a 1 in the referenced cell. Start with the current cell, offset 1 to the right, and set the formula to = 1.

The second step is just moving the cursor to finish out the macro. This leaves our final macro code as:

Sub Macro1()
'
' Macro1 Macro
' Two is better than ... three?
'
' Keyboard Shortcut: Ctrl+g
'
    ActiveCell.Offset(0, 1).FormulaR1C1 = "1"
    ActiveCell.Offset(1, 0).Select
End Sub

Notice that our second line of code (previously line 3) has changed also. Because we did not move our cursor position in step 1 we only need to move the cursor down 1 row as opposed to before when we moved down 1 row and left 1 column.

In this example we reduced our code to 2 lines from 3. You probably will not notice a speed increase from doing so. But imagine if we had a macro of 300 lines that we reduced to 200 lines. Most computers I deal with will process 200 lines faster than 300. ;)

Out of the Gates

2008-02-12T12:20:00.001-06:00

In my previous post (Getting Started) I demonstrated how to record a macro to do a simple task. I also showed how to open the VB Editor and examine the recorded macro which is VB code.

In this post, I want to take that macro a step further. In the former post, we started the macro recorder with A1 as our selected cell. Next we pressed our right arrow key and then typed a 1 (one). Next we pressed enter and then pressed the left arrow and stopped the recorder. The result was that we had a macro that moves to cell B1 and places a 1 in the cell and then moves to cell A2.

This time we want to make the locations relative to our starting point and not the absolute addresses. So let's get started. Go to the previous post and follow those steps to get the macro to the point where we left off in Getting Started.

Ready? Great! Open your VB Editor and pull up the Module1 window. You should now be viewing Macro1() in the code window. You can change the comment from getting started to "out of the gates" if you wish but as I said before... comments are for informational (and documentation) purposes only. The macro is not affected either way.

Instead of giving an absolute location like B1 we want to tell the macro to go right 1 cell. For that, I use the command:

activecell.offset(0,1).select

What we are telling the macro is to start at the current selection and move an offset of 0 rows and 1 column (that is 1 cell to the right). Then we select it. That will move the selected cell 1 position to the right. So replace the first line of code [Range("B1").Select] with the new code above.

The second line of code is responsible for placing the 1 in the cell. We'll keep that like it is.

Can you guess what the third line of code will change to? We don't have to just use positive numbers in our offset -- we can use negative numbers as well. So our final result of code will be:

Sub Macro1()
'
' Macro1 Macro
' Out of the Gates
'
' Keyboard Shortcut: Ctrl+g
'
    ActiveCell.Offset(0, 1).Select
    ActiveCell.FormulaR1C1 = "1"
    ActiveCell.Offset(1, -1).Select
End Sub

Did you catch that both numbers needed to change? After our first line of code we were positioned 1 cell to the right of our starting point. The second line of code changed the contents but did not move down a row like pressing enter manually. So our third line of code needs to move the selection down 1 row and back to the left 1 row. That is why we have (1,-1). The first number refers to the row offset (positive is down) and the second number is the column offset (positive moves right).

Now go back to the spreadsheet and press Ctrl-g to run the macro. Press it again. Change your starting position to cell C3 and press Ctrl-g again. Notice that no matter where we start our macro, that a 1 is placed immediately to the right of the starting cell and the macro finishes by selecting the cell just below our starting cell.

Now you're out of the gates with vb macros, so to speak. Nothing holding you back... You can have a macro move the selection all around. Check back later and I'll show you how to improve this macro with 3 lines of code and reduce it to just 2 lines.

Getting Started

2008-02-11T10:09:00.001-06:00

How do I get started writing vb macros? The best way I know is to just jump right in. So let's get started in Excel.

Open MS Excel. You should have a blank worksheet sitting before you with the selected cell of A1. We will actually record a macro first and then tweak it to do what we want.

[For Office 2007 users] Click the Developer tab on the ribbon bar. Next, click the Record Macro menu item.

[For Office 2003 users] In the menu bar, click Tools, then Macros, then Record Macro.

Now you have a dialog box requesting some information before we actually start recording. It asks for a macro name which is Macro1 by default. It asks for a shortcut key which will be combined with the control key to allow us to quickly start the macro. It asks where we want to store the macro. Finally, it asks for an optional description for the macro.

We will leave the macro name the default of Macro1. Let's assign the letter "g" as our shortcut for GO. We will keep the macro in the current workbook and for the description we will use "getting started". Next, click OK.

It looks like nothing has happened. But it has. We are now in recording mode. All the steps you take, the macro will keep a record and be able to play them back later. In Office 2003, you probably saw a new toolbar appear (possibly somewhere in your spreadsheet) with a red square to stop the macro. In Office 2007, the menu item that formerly read Record Macro now reads Stop Recording.

Press your right arrow key to move the selection to cell B1. Type 1 and then press enter. Press your left arrow key to move to cell A2. Now press the Stop Recording button.

To test our new macro, delete the contents of B1 and click on cell A1. Now press Ctrl-g. You should now see the 1 again in cell B1. Not very exciting, I know, but we're just getting started.

Let's take a look at the vb code to perform such an act. Press Alt-F11 to bring up your Visual Basic Editor. Expand the Modules folder by clicking the + to the left of the folder. Double click Module1. You now see the vb code that drives Macro1. A comment begins with an apostrophe (') and continues to the end of the line. The VB Editor colors it green. A comment is ignored by the macro and is for informational purposes only.

Sub Macro1()
'
' Macro1 Macro
' getting started
'
' Keyboard Shortcut: Ctrl+g
'
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "1"
    Range("A2").Select
End Sub

Notice our first code line inside the macro says Range("B1").Select . The Range command identifies a single location or range of locations. The .Select following the range tells what operation to perform on that Range. We pressed our right arrow but Excel viewed that as selecting cell B1.

The second statement reads ActiveCell.FormulaR1C1 = "1". Here the macro is given the instruction to take the active cell (which is now B1) and set it's formula to equal 1 (which is what we typed).

The third and final statement reads Range("A2").Select. We pressed our left arrow but Excel interpreted that as a selection of cell A2. And then our macro ends.

I wanted to use this example for several reasons. I wanted to show an easy way to get familiar with the vb code needed to perform common tasks. If you don't know how to write the vb code to do something, then first record a macro to do it and examine the code. Also, this example shows that what you think you told the recorder might not be what is actually getting recorded. For instance, we pressed our arrow keys but the recorder captured absolute locations on the spreadsheet. If we needed our macro to always move 1 cell to the right and the after pressing enter move 1 cell to the left, then this macro would not work for us. No matter where our current selection is when we start the macro, the macro will always go to cell B1, put a 1, and then return to cell A2.

Check back and I'll show you how to take this same macro and make it work with relative positions.

au·to·ma·tion (noun)

2008-02-08T14:41:00.001-06:00

Dictionary.com defines automation as the technique, method, or system of operating or controlling a process by highly automatic means, as by electronic devices, reducing human intervention to a minimum.

I will do my best to provide short, clear techniques to automate everyday processes by using vb macros. Microsoft Office has been using VBA (Visual Basic for Applications) since 1993. Before that time, macros in Excel were a series of keystrokes or shortcut commands. With VBA you have much wider options in dealing with automation such as using IF...THEN to compare situations or DO...WHILE to perform iterative loops.

Future posts will include code snippets that perform specific tasks. And I'll probably throw in some Excel formula examples from time to time. Automation is all about doing something faster or without the use of human control. Sometimes a well thought out formula is all you need. Other times we can write a User Defined Function (UDF) in VB and let the code do all the work and pass us back the answer.

The point is you'll want to check back here often for helpful tips on automating your office. You can expect to see tips for much of the Microsoft Office Suite including MS Excel, MS Word, MS Access, and occasionally MS Outlook.