Excel VBA Macro Basics (Part 1)

The following is a brief guide to VBA Macros in Excel, how to use them, and in my opinion, what functions you should know about. This is one of my first reference pages, so this page might be updated frequently.

Requirements: At least Excel 2007. Other versions of excel might have ways of accessing / running macros differently.

Note: I'm assuming that don't know anything about macros in Excel prior to reading this.

Setup:

Activate Developer Tab: In Excel 2007 Options under "Popular" submenu. In Excel 2010 you'll have to go to the Customize Ribbon and on the right you'll have the option to check the "developer" menu to enable it.

Access Visual Basic: Using the developer tab, click the button that says "Visual Basic".

Create a Module: Modules are used to run functions / subcommands and store code for any macros or button functions. If you record a macro, a module will automatically be created.

Though it is entirely possible to place functions inside a sheet and run it as a macro, it's typically better to store them in separate modules in the document, because if you happen to delete the sheet containing macro code, you'll lose it. Code related to buttons and other controls on the current sheet have to be placed inside their own sheet code.

Selecting Cells and Writing Data

There are several ways to write data to a cell. Replace "Text Here" with the text that you want in the cell enclosed in quotes. Assuming the cell you wish to write is B2 on Sheet1:

• Worksheets("Sheet1").Cells(2,2) = "Text here"
• Cells(2,2) = "Text here" (When Sheet1 is active)
• ActiveSheet.Cells(2,2) = "Text here" (When Sheet1 is active)
• Range("B2") = "test" (When Sheet1 is active)
• Range("B2").Select
  Selection = "test" (When Sheet1 is active)
• ActiveCell = "test" (When Sheet1 is active on cell B2)

Referencing Sheets, Cells and Variables

• ActiveSheet refers to the currently active sheet when that line of code is run. Similarly, ActiveCell refers to the current cell that was selected.
• Worksheets("Sheet1") calls a specific sheet when selecting cells or other commands. Note if a sheet with that name doesn't exist, then an error will be thrown.
• Cells (2,2) refers to the coordinates of the cell in column, row coordinates, with 1 being row A. You can use variables to determine the cell being referenced. If you want the address of the cell referenced in Letter/number notation, use Cells().Address
• Range() can be used to select a single range (such as "B2:C4"), or a series of ranges separated by commas (such as "B2, C2, D5").
  • Alternatively, just enclose the range in brackets. Ex: [B2] = "" will fill in cell B2 appropriately.
• Variables can be defined / saved using a variety of variable types: Integer, Float, Range, Date, etc.

Aesthetic Stuff

• Column widths can be set with Columns("G").ColumnWidth = 10, where G is the column you wish to have selected.
• For a range, use .Interior.Color = RGB(Red:=a, Green:=b, Blue:=C) to change cell color, where a, b, and c are values between 0 and 255 (following the RGB color scheme). To change the font color use Font.Color = ... instead. Examples: Range("F12").Interior.Color = RGB(Red:=255, Green:=0, Blue:=0) shades the background blue.