Excel can make a great reporting engine if you have a basic understanding of the Excel Object Model.
Excel can be used as a reporting engine in situations where a standard reporting engine is inadequate. Here are some situations:
- when you don't know the number of fields or columns in a report beforehand (e.g., budget schedules);
- when you would like to autofit onto any paper size (report writers confine you to layout a report for a particular page type);
- when you would like sophisticated shading on columns; and
- when you would like to allow user customization (e.g.,
adding totals or what-if analysis on a generated report).
In this article, we'll go over the basics of Excel
Automation with VBA and delve into some neat capabilities. We'll cover setting
cell values, cell formulas, and number formats, and then get into more intricate
things like drawing borders, shading columns, and setting cell tooltips. In a
later article, we will query an Access database and render the results in Excel.
Excel Object Model - Key Objects
The Key Objects in Excel starting from the top of the hierarchy and working down are
- the Workbook Object,
- the Worksheet Object,
- the Chart Object,
- the Range Object (which can be composed of columns, rows, or a named set of cells),
- Borders, and
- the Cell (technically the smallest range you
There are certain objects that are not unique to Excel yet are very important to know for report generation in Excel. These are
- the Application (the Excel application - the starting point for creation of workbooks and sheets),
- the Font Object,
- the Page Setup Object (which allows you to set such things as autofit and landscape/portrait),
- Shape Objects and Tooltips (which allows you to embed tooltips
in Excel cells and manipulate shapes, e.g., the shape of a tooltip
Creating the Excel Spreadsheet in VBA
An Excel spreadsheet can be generated from any VBA-enabled product such as all the Office 97 applications or Visual Basic 5/6. These tools have an intellisense feature (auto code fill) which becomes enabled if you reference the Excel VBA library. Once you reference the VBA library you can create Excel spreadsheets and instances of Excel using the modern "New" keyword approach. Without referencing the Excel VBA library, you lose the auto sense functionality and must resort to using the CreateObject() OLE construct. For simplicity, we will use the more modern reference approach in our examples.
Step 1 - Reference the "Microsoft Excel 8.0 Object Library" when you are in the VBA module design mode. This is usually found under Tools - References.
Step 2 - Create a VBA module and declare a global object as an Excel.Application like so:
Dim appXl As Excel.Application
We declare an Excel application at the global level so that when we create the application in a subroutine or function, it does not lose scope when the subroutine or function is completed.
Step 3 - Create the function or subroutine.
This would look something like this:
Dim currentRow As Integer, currentColumn As Integer
currentRow = 1
currentColumn = 1
Set appXl = New Excel.Application
appXl.Workbooks.Add 'Creates a new workbook
.Cells(currentRow, currentColumn).Value = "THIS IS A TEST EXCEL SHEET"
.Cells(currentRow, currentColumn).Font.Bold = True
.Cells(currentRow, currentColumn).Font.Name = "Times New Roman"
.Cells(currentRow, currentColumn).Font.Size = 11
.Cells(currentRow, currentColumn).HorizontalAlignment = xlCenter
currentRow = 2
.Cells(currentRow, currentColumn).Formula = "=Now()"
The example above creates an instance of an Excel application, creates a workbook object in that application window, sets some properties of Cell A1, and sets the formula of Cell A2 to display the current date and time. Note that we can use Excel specific constants such as xlCenter
. If we had
not first referenced the Excel Object Library, then we would have had to specify
the integer equivalent of the constant, which is -4108 for
. This is another benefit of using library references.
Merging Cells -
To merge cells together into a single cell, use the Merge
method on a range object. For example
would merge cells A1, B1, C1 into a single cell in the active sheet of the appXl application we defined previously.
To draw a border around a cell, use the BorderAround property of a cell object. For example
appXl.ActiveSheet.Cells(1, 1).BorderAround Weight:=xlThin
draws a border around cell A1.
object allows you to control the laying out of an excel spreadsheet. Below is a little snippet of code that sets the Active Sheet to fit on a page 1 page wide and 1 page tall, sets the left, right, top, and bottom margins to 0.25 inches, prints the first 6 rows as the header for each printed page, and sets the print area.
.Orientation = xlLandscape
.Zoom = False 'Force to use fit to page
.FitToPagesWide = 1
.FitToPagesTall = 1
'Do not print the grid lines on the printout
.PrintGridlines = False
'Set left margin to 0.25 inches
.LeftMargin = appXl.InchesToPoints(0.25)
'Set right margin to 0.25 inches
.RightMargin = appXl.InchesToPoints(0.25)
.TopMargin = appXl.InchesToPoints(0.25)
.BottomMargin = appXl.InchesToPoints(0.25)
'Print the first 6 rows as the header on each sheet
.PrintTitleRows = appXl.ActiveSheet.Range("A1:A6").Address
.PrintArea = "$A$1:$D25$"