Windows Tools, Help & Guides

IF you are to really learn just one Office application, then you should choose Excel. Because it is the most versatile and powerful of all the Office applications.  With Excel you can almost get rid of the other Office applications all together. A side from the normal calculations you can write letters, create databases with powerful reports and analyze tools, create useful forms for everyday chores, and program powerful small applets. Within certain limits of course.

Using Advanced Macros

Most users have come across the word MACRO, one time or another using Excel. A macro is a recording of sorts, that stores your actions, step by step. Macros are used to eliminate the need to repeat the steps of common tasks over and over. Tasks such as adding or removing rows and columns, selecting a range of cells, or changing textcolor, fonts etc. In Excel, macros are written in Visual Basic for Applications (VBA). For those who cannot write VBA code, Excel allows you to record a series of steps – using keyboard and mouse – that Excel then converts into a macro.
In a few upcoming articles I am going to show you how you can create powerful and effective macros that hopefully will take your Excel experience to another level. How about letting Excel Copying Sheets, renaming them, Choose filenames, choosing between file locations and save the document as PDF sound ?

Sounds interesting ?

Great, let’s get started…

For most users, macros are an unvisited territory. Some try to record a macro and never see the full potential in it – or the usage for it. And some, create macros of tasks they do repeatedly, over and over. Like formatting cells, copying formulas etc. And it ends there. Not so any more.

Built into the MS Office Platform you will find something called VBA (Visual Basic for Applications). This is a light version of the programming language adjusted to work within each application (and across). And this is where we can harness the power of Excel. Imagine being able to Export/Import data between Excel and Word, creating automatic reports and letters. Programming Office to your needs.

Programming ???

Don’t despair – it is not as hard as it sounds. By combining the Macro Record functionality, with some small coding efforts you will come a long way. Learning as you go. Before we get into that, lets record a Macro.

Record a Macro

I am using the new Office 2010 in these examples, but you may use any Excel version you choose. However, not every icon or menu will have the same name and/or appearance, though the functionality will remain mostly the same.

I use the Excel cell references like this:

• [B2] = Click on this specific Cell
• [B2:C3] = Highlight these Cells

Open a blank Excel Document and click on the VIEW-Ribbon. All the way to the left is the Macro Button. Click on the little black arrow underneath the Macro Icon, and choose: “Use Relative References”. This setting allows us to use the macro anywhere we like, as opposed to a set area in the worksheet.

NOTE!
If you have Excel PRE 2007, you will not have the “Relative References” choice. You must instead, select the cells BEFORE recording the macro to avoid it locking to specific Cells.

Click the little black arrow (under the macro icon) again. This time, choose “Record Macro”. Call it “First”, Click OK.

• Now, Click on a Cell [B2], and type: “This is My first Macro Recording”.  (Normally, you won’t be typing text when recording macros but, in this example we are).
• Select a sample of cells around the text you wrote [B2:D3]
  (if you don’t use 2007/2010, hold down the SHIFT key and create the selection using the arrows, right and down).
• Click on the HOME-Ribbon, and set text color to RED and Background Color to Yellow

Click the little black arrow once more, choose “Stop Recording” (In Pre 2007/2010 Excel you will have a small floating window, containing the “Stop Recording”-button).

Highlight a new cell [H5]. Click on the Macro Icon, showing a list of available macros. Highlight the Macro we just created and Click RUN.

What you see is that Excel now replicates our steps, on a new set of cells. Choose a different Cell, and try it again to see that it works.

What the code looks like

To see what the code looks like, click the Macro Icon again, choose the macro and click EDIT.  This will open the VBA editor and show your macro:


Sub First()
'
' First Macro
''
ActiveCell.FormulaR1C1 = "This is my first Macro Recording"
ActiveCell.Select
Selection.Font.Bold = True
With Selection.Font
.Color = -16776961
.TintAndShade = 0
End With
ActiveCell.Range("A1:D3").Select
With Selection.Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
.PatternTintAndShade = 0
End With
End Sub

What you see is the VBA-Code necessary to do the task you recorded (it may differ from this example). We will get back to the code later. But if you want to play around with this, try changing these settings (marked RED in the example):

• .Color = -16776961 -> .Color = -16776981
• .Color = 65535 -> .Color = 255

Close the VBA Editor and run the Macro again.

Next Part

Read next part

In the next parts I will teach you how to write a Macro from Scratch and how to target specific cells and sheets.  We are also going to create a macro that Export documents to PDF, choosing filenames, folder and open the finished file in Acrobat Reader.

Free PC tips by email