Formatting Techniques for Data Access from Excel VBA - Formatting Techniques with Style
(Page 3 of 5 )

Since this section is about formatting, let’s take a look at how you can apply the currency style to the results of any formula that returns a number. (Remember that formulas can return numbers,Booleanvalues, text, or errors.) We will modify the procedure in the previous example to use currency.

  Public Sub FormulatoCurrency(targetrange As Excel.Range)
  Dim rng As Excel.Range
  Dim xlws As Excel.Worksheet
  Dim rng2 As Excel.Range

  Set xlws = ActiveSheet
  Set rng = targetrange.SpecialCells(xlCellTypeFormulas, xlNumbers)
  For Each rng2 In rng.Cells
    rng2.Style = "Currency"
  Next rng2
  xlws.Range("A1").Select
  Set rng = Nothing
  Set rng2 = Nothing
  Set xlws = Nothing
  End Sub

You can see that we’ve changed the setting of the style. Excel has some predefined styles, which you can refer to more easily than you can write a number format. In addition, you can create a new style and refer to it, as you would one of Excel’s predefined formats, to format cells the way you want. Let’s assume that column D includes numbers, and you want to create a style that is:

1. A number with commas
   
2. A whole number (has no decimal places )
   
3. Blue when positive
   
4. Red when negative
   
5. A bold Arial size 14 font

Formatting that several times in your code or by hand would get repetitive and annoying. Instead, you can create code for a style and apply it from the Format -> Style menu or refer to it in code.

The beauty of creating a style is that you can also modify styles that you’ve already applied using the Format -> Style screen. So, if you wanted to change every cell that has a particular style, rather than having to find each cell on your worksheet, you could simply modify the style you’ve applied. The style dialog box is in Figure 3-2. The next example shows you how to create the style. The following example shows you how to apply that style to column D.

Figure 3-2.  The Excel Style dialog box

  Public Sub CreateStyle()
  ActiveWorkbook.Styles.Add "MySpecialFormat"
  With ActiveWorkbook.Styles("MySpecialFormat")
     .Font.Bold = True
     .NumberFormat = "[Blue]#,##0_);[Red](#,##0)"
     .Font.Name = "Arial"
     .Font.Size = 14
  End With
  End Sub

  Public Sub ApplyStyle()
  ActiveSheet.Columns("D:D").Style = "MySpecialFormat"
  End Sub

Note that all of the boxes are checked in the style dialog box in Figure 3-2. If, for example, you don’t want to apply a style to Protection, uncheck that box. In
addition, you could modify the code in theWithclause to say.IncludeProtection = False. You can also do that with the other options in the figure.

Next: Formatting Techniques Example >>
 

More Database Articles
More By O'Reilly Media

Comments On: Formatting Techniques for Data Access from Excel VBA

· This article is an excerpt from the book "Integrating Excel and Access," published...   >>> Post your comment now!

Buy this book...

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.