Excel Programmer : Record and Read Code

10/10/2010 9:33:40 AM
The best way to learn about Excel objects, properties, and methods is by recording code . It's even better than online Help. Recording will almost always tell you what you need to know if you know how to use it. When Excel records code, it translates your actions into Visual Basic code. This lets you reverse-engineer recorded actions or simply cut and paste recorded actions into other procedures.

For example, suppose that you have a workbook containing multiple sheets of sales data as shown in Figure 1. You want to format the data on each of the sheets and add a chart comparing units sold and revenue. This is a great opportunity to record some code.

Figure 1. An example for recording code

To record your code:

  1. Choose Tools → Macros → Record New Macro. Excel displays the Record Macro dialog (Figure 2).

    Figure 2. Step 1
  2. Select the range A1:C16 and choose Format → AutoFormat. Excel displays the AutoFormat dialog (Figure 3).

    Figure 3. Step 2
  3. Select the Simple format and click OK. Excel formats the range.

  4. Press Shift-Up to deselect the Total row and then choose Insert → Chart. Excel displays the Chart Wizard (Figure 4).

    Figure 4. Step 4
  5. Click Finish to insert a chart on the sheet as shown in Figure 5.

  6. Finally, choose Tools → Macro → Stop Recording to turn off recording.

Now you could repeat this task by selecting one of the other worksheets and run the recorded code by pressing Alt-F8 and running Macro1, but the data would have to be in the same location on the active worksheet and the new chart would appear on the 2002 worksheet, not the active one. Instead, press Alt-F8 and click Edit. Excel starts the Visual Basic Editor (VBE) and displays your recorded code, as shown here:

    Sub Macro1( )     '<---------- Name of procedure.
' Macro1 Macro <----------- Comments describing procedure.
' Macro recorded 5/26/2004 by Jeff

Range("A1:C16").Select '<---- Following lines record what you did.
Selection.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, Font _
:=True, Alignment:=True, Border:=True, Pattern:=True, Width:=True

ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("2002").Range("A1:C15"), PlotBy:= _
xlColumns ' Long lines are continued using an underscore ---------^
ActiveChart.Location Where:=xlLocationAsObject, Name:="2002"
End Sub '<---------- End of procedure.

Figure 5. Step 5

I added some labels in the recorded code to identify its parts:

  • Each procedure in a workbook has a unique name. Excel assigns the names Macro1, Macro2, and so on to recorded code.

  • Anything that appears after ' is a comment. Comments are descriptive text that don't run as code.

  • Lines of text that aren't comments are executable statements . Statements tell Visual Basic what to do in Excel.

  • Lines that are longer than about 80 characters are continued on the next line using the _ character. Excel does that for readability. Actually, Visual Basic allows lines of code to be much longer if you don't mind horizontal scrolling.

  • Procedures always include an End statement to tell Visual Basic where to stop.

  •  Configuring Server Roles in Windows 2008 : New Roles in 2008
  •  Windows Server 2003 : Creating and Configuring Application Directory Partitions
  •  Windows Server 2003 : Configuring Forest and Domain Functional Levels
  •  Windows Server 2003 : Installing and Configuring Domain Controllers
  •  Manage Server Core
  •  Configure Server Core Postinstallation
  •  Install Server Core
  •  Determine Your Need for Server Core
  •  Install Windows Server 2008
  •  Windows Server 2008 : Configure NAP
  •  Incorporate Server Core Changes in Windows Server 2008 R2
  •  Decide What Edition of Windows Server 2008 to Install
  •  Perform Other Pre-Installation Tasks
  •  Developing Windows Azure Services that Use SQL Azure
  •  Creating Windows with Mixed Content
  •  Mixing Windows and Forms
  •  Exploring an Assembly Using ildasm.exe
  •  The Assembly/Namespace/Type Distinction
  •  Communicate Between Two Machines on the Same Network (WCF)
  •  Communicate Between Processes on the Same Machine (WCF)
    Top 10
    Use Preview to Edit On Your Mac (Part 1)
    How fast is Windows 8? (Part 1)
    ASUS Radeon HD 7950 : is not only powerful, but also energy saving
    Safeguarding Confidential Data in SharePoint 2010 : Enabling SQL Database Mirroring
    IIS 7.0 : Managing Administration Extensions
    Gamepad - How It Works (Part 3) - Inside Kinect of Microsoft
    QPad MK-50 Pro Gaming Keyboard
    Just My Type (part 2) - ZAGGfolio,Writer Plus, keyPAD, Professional Workstation
    iPad Therapy (Part 3)
    Home Theatre Pc Software And Operating Systems (Part 7) - Playing Back Blu-rays
    Most View
    Kickstarting A Revolution? (Part 3)
    Beginer's Guide To Sports Photography (Part 3)
    Silverlight Recipes : Networking and Web Service Integration - Accessing Resources over HTTP
    Active Directory Domain Services 2008 : Seize the Schema Master Role, Seize the Domain Naming Master Role
    Windows 7 : Using Desktop Gadgets (part 1) - Using the Calendar gadget
    iPhone 3D Programming : Textures and Image Capture - Creating Textures with the Camera
    Windows Phone 7 Advanced Programming Model : Advanced Data Binding (part 3) - Showing Progress & Lazy Load Images
    SQL Server 2008 : Using the OUTPUT Clause with the MERGE Statement
    Apple's Undiscovered Country : Apple TV
    The Human Touch
    Binding Application Data to the UI objects in Silverlight
    Using the iPhone Simulator
    Microsoft SQL Server 2005 : Report Definition and Design (part 4)
    Samsung Series 5 530U4B
    iPhone Application Developmen : Using the View-Based Application Template (part 3)
    Choosing The Right Parts For Your Build (Part 2) - Choosing the right motherboard
    Windows System Programming : File Locking
    The Android Phone computer (Part 2) - The world of OTG & Big screen entertainment
    Windows Server 2008 : Domain Name System and IPv6 - Understanding DNS Queries
    Windows Phone 7 Development : Debugging Application Exceptions (part 1) - Debugging Page Load Exceptions