Page 1 of 1

Working with the Excel Object Model

#1 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3467
  • View blog
  • Posts: 11,768
  • Joined: 12-December 12

Posted 26 April 2013 - 07:14 PM

This tutorial describes how to work effectively, and efficiently, with the Excel Object Model. This could be within Excel (using VBA) or via .NET and Office Interop. NOTE: I do not describe how to connect to Excel from .NET, as I'm concentrating purely on Excel Objects and the Excel Object Model.

Pre-requisites: You should be reasonably comfortable with Excel and know the basics of a programming language. Specificially: How to use variables and create object variables, what procedures are (called functions in other languages). You should also be familiar with the OOP terms: objects, properties and methods.

The most important tool at your disposal is the Macro Recorder in Excel, which will generate VBA code which you can then edit and improve upon. In Excel 2003 and earlier these options are available from the Tools menu, Macro menu item. It 2007+ it is best to display the Developer Tab in the Ribbon. In Excel 2007 choose the Office Button, Options and click 'Show Developer Tab..'. In 2010, right-click the Ribbon somewhere and choose Customize the Ribbon - there is a checkbox on the right to display the Developer tab.

Choose the option to Record Macro, give it a name, and then perform a few simple Excel actions: enter some text, do some formatting, create a formula, etc.. It doesn't matter too much as the code that is generated will have similarities to that described below. DON'T FORGET to stop the recording when you've finished! NOTE: In Excel 2007+, if you want to save the macros in a file, then you need to explicitly save the file as Macro Enabled, which has the file extension of .xlsm (or .xslb).

Open the Macros dialog (on the left of the Developer tab or via Tools, Macro, Macros in 2003 - or use Alt-F8) and press Edit to open the Visual Basic Editor.

You will discover code similar to the following:

Sub Macro1()
'random comment lines (denoted by an apostrophe in VB)
    ActiveCell.FormulaR1C1 = "Name"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Andy"
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("B2").Select
    ActiveCell.FormulaR1C1 = "4/27/2013"
    Range("B2").Select
    Selection.NumberFormat = "[$-F800]dddd, mmmm dd, yyyy"
    Columns("B:B").EntireColumn.AutoFit
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "10"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "20"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "40"
    Range("A7").Select
    ActiveCell.FormulaR1C1 = "=SUM(R[-3]C:R[-1]C)"
    Range("A8").Select
End Sub

As we will discover shortly, this code is very inefficient and we can greatly improve upon it. Nevertheless, the macro-recorder will remain a useful tool for discovering the names of methods and properties. It is also worth noting that if you record a macro in a slightly different way, perhaps using shortcut-keys or the shortcut-menu, then you may uncover slightly different code. Use this to your advantage.

Recorded code makes extensive use of ActiveCell, Select and Selection. (You will also encounter ActiveSheet, ActiveWorkbook and, for example, ActiveChart.) This is a very inefficient way to work and it is not necessary to activate or select anything in order to work with it.

If you highlight a few cells in Excel then the ActiveCell is the one that is white, Selection refers to the whole highlighted region. If you are working with a single cell then ActiveCell and Selection are the same.

We don't need to activate a cell to put a value into it:

Range("A3").Value = 41    'or
Range("A1").Offset(2, 0).Value = 41    'Offset(rowoffset, coloffset)
Range("A1").Cells(3, 1) = 41    'Value is the default property, which is assumed
Cells(3, 1) = 41    'assumes A1 as the starting point


The recorder uses a cryptic FormulaR1C1 version (I call it "row and column version") of a formula. The above could be replaced with:

Range("A7").Formula = "=SUM(A4:A6)"

When running macros, Excel continually refreshes the screen, causing noticeable flicker and seriously impacting performance. This is largely unnecessary and we can turn this aspect on and off. For most macros I tend to include this line very near the top of the code:

Application.ScreenUpdating = False

You should set this to True at the end of the code, although Excel will update the screen when the procedure finishes anyway. Other features (properties) that you can control are Application.Calculation and Application.DisplayAlerts. Be careful with DisplayAlerts though: if you close a file it won't prompt you to save it if this feature is set to False.

Setting Application.Calculation to xlCalculationManual can improve peformance but (as with .DisplayAlerts) make sure that you set it back to, for example, xlCalculationAutomatic at an appropriate point.

A Template

Here is a basic template that you might adopt, although I prefer not to tinker too much with Application.Calculation if my code involves a lot of work with formulas:

Sub Better()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    dim calc As Integer

    Application.ScreenUpdating = False
    calc = Application.Calculation    'remember users choice
    Application.Calculation = xlCalculationManual

    Set wb = Workbooks.Open("C:\Path to\SomeFile.xlsx")    'the extension is not always necessary
    Set ws = wb.Worksheets("TheOneIWantToWorkWith")

    'working with ranges - see below
    'more code here..

    wb.Save 'or .SaveAs "C:\Some Folder\The file.xlsx"
    wb.Close

    Set rng = Nothing   'in reverse order..
    Set ws = Nothing
    Set wb = Nothing
    Application.Calculation = calc
    Application.ScreenUpdating = True
End Sub


Working with Ranges

Defined Names (formerly Range Names) are extremely useful in both Excel and VBA. Very often we work with tables of data, having column headings. We can name these columns of data according to their headings. Click into your table and press Ctrl-A to select the table, which is referred to as the CurrentRegion in VBA. On the Formulas tab, choose Create from Selection to create defined names based on the column headings (the top row). In Excel 2003- this is the Insert menu, Name and Create. Note that spaces will be replaced with underscores and non-alphanumeric characters will be removed. (Sometimes an additional underscore will also be added to the name at the end or beginning.) You can check, and test, these names by clicking into a cell and pressing F5 (the GoTo dialog). The statement that creates these defined names programmatically is:

Range("A1").CurrentRegion.CreateNames True, False, False, False

The we can make good use of these defined names in our code:

Range("someHeading").Font.Bold = True
'although it is generally preferable to qualify the Range with a 
'reference to the worksheet:
ws.Range("someHeading").Font.Bold = True


Note: It is very important that users don't put odd values in any cell immediately adjacent to the table, otherwise selecting the CurrentRegion will include that (nonsense) column.

We can name individual cells, and other ranges, as well:

Range("A1").Name = "tada"
Range("tada").Value = 54

Range("B1:C20").Name = "myData"
Range("myData").Value = 43    'fill the whole range with a value


So, typical code could be:

Dim ws As Worksheet
Dim rngData As Range

Set ws = Worksheets("Sheet20")
Set rngData = ws.Range("C4").CurrentRegion
rngData.CreateNames True, False, False, False
Range("someHeading").Font.Italic = True
Range("someHeading").Cells(1, 1).Font.Color = vbRed    'the first cell in this area
rngData.Select    'not really necessary

Set rngData = Nothing
Set ws = Nothing

It might be useful to know how many rows there are in a range:

Dim lRows As Long
lRows = Range("someHeading").Rows.Count


If necessary we can use For..Each to efficiently loop through a range:

Dim rng As Range
For Each rng In Range("someHeading")
    rng.Font.Color = vbGreen
Next rng

Some other very useful methods, and properties, when working with ranges are:

UsedRange, Intersect, EntireColumn, EntireRow, Row(s), Column(s), Resize

I encourage you to explore these. (One way to do so is to press F2 in the VB Editor to display the Object Browser - it's in the View menu.) However, if some interest is expressed, I might produce a Part 2 for this tutorial, to demonstrate their use.

Good luck, and I hope that you've found this tutorial useful. Andy.

This post has been edited by andrewsw: 03 October 2013 - 10:27 AM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1