Skip to Main Content
Integrating Excel and Access
book

Integrating Excel and Access

by Michael Schmalz
November 2005
Intermediate to advanced content levelIntermediate to advanced
236 pages
6h 32m
English
O'Reilly Media, Inc.
Content preview from Integrating Excel and Access

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 called Columns that is available on a Range object and a ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

Business solutions Automating Microsoft® Access with VBA

Business solutions Automating Microsoft® Access with VBA

Susan Sales Harkins, Mike Gunderloy
Microsoft® Office Excel 2003 Programming Inside Out

Microsoft® Office Excel 2003 Programming Inside Out

Curtis Frye, Wayne S. Freeze, Felicia K. Buckingham

Publisher Resources

ISBN: 0596009739Supplemental ContentErrata Page