Formatting Techniques for Data Access from Excel VBA (Page 1 of 5 )
In this article, the second of two parts, you will learn about the two formatting methods you can use when dealing with Excel, Access, and VBA. It is excerpted from chapter three of the book
Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Copyright © 2005 O'Reilly Media, Inc. All rights reserved. Used with permission from the publisher. Available from booksellers or direct from O'Reilly Media.
Formatting Techniques
There are essentially two formatting methods, which are on a worksheet using VBA. The first method is to learn where all of the formatting options are and write the code from scratch. This method is most effective when doing the same thing many times (formatting numbers as currency, for example); however, trying to remember how to perform every type of formatting becomes a daunting task. So, this brings us to the second method: using the Macro Recorder.
To access the Macro Recorder from the Excel user interface, go to Tools -> Macro -> Record New Macro. If all you need is the formatting, pick one cell for each type of formatting that you need to perform. When you are done doing the formatting, press the Stop button to stop recording, or go to Tools -> Macro -> Stop Recording. Go to the Visual Basic Editor to view the code that the Excel Macro Recorder wrote to perform the formatting. This gives you the syntax needed for your code. If you always have the same number of rows, etc., you can just do all the formatting and then save the macro to run in the future. If you need to customize it, the next couple of examples show you how.
Determining how to refer to your range becomes tricky. For example, let’s assume that you want to format a column as currency ($0.00). If you are certain that you want every cell in that column to be currency, you can set your range to be that column. In Excel, there is a property calledColumns that is available on aRangeobject and aWorksheetobject. If your range is already defined in a variable calledxlrng, and you want to perform formatting on the third column, refer to this column asxlrng.columns(3). If you do this, I suggest having an additional range object that you can refer to. If you have a range object calledxlrng2, you can write a line that saysset xlrng2 = xlrng.columns(3). I like to do this so that the range properties and methods will be available as I write the code.
To explain that a little, if you have a worksheet that is currently active that you refer to as ActiveSheet, you can writeActiveSheet.Range("A1").Value=100, and it will put 100 in cell A1. However, as you type, VBA will not help you. If you sayset xlws = ActiveSheetand typexlws., as soon as you type the . you will see all of the properties and methods available. Using this technique for your objects will make coding much easier. If you have ever tried to script an Excel worksheet in VB script, you can appreciate how useful the help is while typing.
In addition to using theRANGEobject, you can also use theWorksheet object to refer to a column. So, if you have a variablexlwsthat is defined as theActiveSheet, you can refer to column B by writingxlws.columns(2)orxlws.columns("B:B"). You can also refer to columns B through D by writingxlws.columns("B:D"). If you do not want to refer to entire columns and the range with your data has not been defined yet, you need a method to refer to this set of data. The following example will show you how to select from cell A1 to the end of the worksheet that has data in it.
Public Sub SelActiveRange()
Dim rng As Excel.Range
Dim xlws As Excel.Worksheet
Set xlws = ActiveSheet
Set rng = xlws.Range(xlws.Range("A1"), xlws.Cells.SpecialCells(xlCellTypeLastCell)) rng.Select
Set rng = Nothing
Set xlws = Nothing
End Sub
The first thing you will notice is that you can refer to a range by using the A1 notation. So, to refer to cell A1, you can writexlws.Range("A1"). You could also do this by writingxlws.cells(1,1)—thecells collection also returns a range object that refers to a single cell.
Next: Formatting Techniques Continued >>
More Database Articles
More By O'Reilly Media
|
 This article is excerpted from chapter three of the book Integrating Excel and Access, written by Michael Schmalz (O'Reilly, 2005; ISBN: 0596009739). Check it out today at your favorite bookstore. Buy this book now.
|
|
/ 9 
