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.
The second method is calledSpecialCells. TheSpecialCellsmethod returns a range object based on constants built into Excel. I usexlCellTypeLastCell, a special cell that refers to the last cell being used. Of the others you can use, two that I find particularly useful arexlCellTypeFormulas andxlCellTypeComments. These return a range that includes all of the cells that have either formulas or comments, respectively.
Often, I want to send an Excel Worksheet to someone, but I don’t want the recipient to change something on one sheet that will affect formula results on another. For example, if you show a scenario of net income based on sales assumptions, and you do not want that net income scenario to be changed if the sales assumptions change, you can change all of the formulas to values using the following procedure. This allows you to maintain a history of the results at various stages. For example, if you make a copy of the scenario with one sales assumption and then remove the formulas, you can maintain the results while still having a second worksheet that will change when the sales assumptions change. Note that the following example assumes that your report is in cells A1 to D100. (You could also build that first range dynamically by using thexlCellTypeLastCellmethod used above instead of building it directly into the code if you needed more flexibility or if the size of your spreadsheet will grow larger.)
Public Sub FormulatoConstant() Dim rng As Excel.Range Dim xlws As Excel.Worksheet Dim rng2 As Excel.Range
Set xlws = ActiveSheet Set rng = xlws.Range("A1:D100").SpecialCells(xlCellTypeFormulas) For Each rng2 In rng.Cells rng2.Copy rng2.PasteSpecial xlPasteValues Next rng2 xlws.Range("A1").Select Application.CutCopyMode = False Set rng = Nothing Set rng2 = Nothing Set xlws = Nothing End Sub
This procedure uses aFor Each...Nextloop to loop through every cell in the Range variablerng. Each individual cell is copied and then pasted back onto itself with only the values. When it is done, cell A1 is selected and theCutCopyModeis set toFalse(if you do not set theCutCopyModetoFalse, cell A1 will be selected, but the last cell copied will still have the marquee box around it as if it is being copied). You could select the entire sheet and use Copy and Paste Special Values, or aFor Each...Nextloop can be placed into code and set to a particular range by changing it like this:
Public Sub FormulatoConstant(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) For Each rng2 In rng.Cells rng2.Copy rng2.PasteSpecial xlPasteValues Next rng2 xlws.Range("A1").Select Application.CutCopyMode = False Set rng = Nothing Set rng2 = Nothing Set xlws = Nothing End Sub
UsingFor Each...Nextallows you to target just the range of cells that you want to find formulas in and change them to values. You could take this code, put it into any of your Excel workbooks, and with a simple call to the procedure, perform this action.
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:
A number with commas
A whole number (has no decimal places )
Blue when positive
Red when negative
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.
So far you’ve seen how to pull in a recordset and how to do some formatting in Excel. The first couple of chapters show many examples of how to pull in recordsets because pulling is the primary method for bringing in data.
This example brings formatting and recordset pulling together. There is a query in the Northwind database that is called [Sales by Category], which is in brackets because there are spaces in the name. That data is not updated daily, but assume that you have a query named that on your system that is updated daily (or monthly, weekly, etc.). You also have a user who wants to see an Excel report that shows that data and produces subtotals by category. I will show you several methods to give the user the desired report, and you can pick the one that suits you best.
Getting to the Data
The first choice you have to make is whether to use ADO or DAO. Since Access will be my only source of data, I will use DAO. The next option is to decide where you want to put the data. I am going to default to the active worksheet starting in cell A4. This will give you room to put a title on the report. Once that is done, determine what you want to do with the data. The program offers two options: using the Subtotal feature in Excel or using a PivotTable. You could also use an input box and have the user type in a number, etc. I will use a message box to ask the user whether she wants a PivotTable. If she clicks Yes, it pulls the data and puts in a pivot table. If she clicks No, it pulls the data and uses Subtotals. If she presses cancel, it stops execution. This example also uses a button placed on the worksheet to allow you to run the code.
Your first step is to go into Excel and turn on the Visual Basic toolbar by going to View -> Toolbars and checking Visual Basic. On the Visual Basic toolbar, click on the button that says Control Toolbox when you hover over it to bring up a box with the standard controls that you can place on your Excel Worksheets. We want to use the Command button. When you click the Command button box, your mouse cursor changes to a plus sign. Go to the top of your worksheet and click and drag a box to a size that you want.
Next, right-click on the Command button and select Properties. There are several properties to change so that this worksheet will look and print the way you want it to. First, change the PrintObject property to False so that the button will not show when you print the report. Next, change the Caption property to Pull Data or something similar. You can also change the system name that VBA uses to refer to the Command button to something more meaningful, so let’s call it DataButton. Change the name on the box called Name (the first box on the alphabetical list or under Miscellaneous in the categorized list). You can also change the font, color, etc., but it is not necessary for this example. Once you have finished modifying the button, close the Properties box, right-click on the button again, and select View Code. When you are in the VBA screen, go to Tools -> References and select a reference to Microsoft Data Access Objects. I am using Version 3.6. Put the code in Example 3-5 in for theDataButton_Click Event.
Example 3-5. Summarize data with a PivotTable or SubTotal
Private Sub DataButton_Click() Dim wrk As DAO.Workspace Dim dbconn As DAO.Database Dim rs As DAO.Recordset Dim fld As DAO.Field Dim msgoption As Long Dim x As Integer Dim xlws As Excel.Worksheet Dim xlws2 As Excel.Worksheet Dim xlrng As Excel.Range
Set xlws = ActiveSheet
Set wrk = DAO.CreateWorkspace("myworkspace", "admin", "") Set dbconn = wrk.OpenDatabase("C:\Program Files\Microsoft Office\OFFICE11" & _ "SAMPLES\Northwind.mdb") Set rs = dbconn.OpenRecordset("Select * from [Sales by Category]")
msgoption = MsgBox("Do you want a PivotTable?", vbYesNoCancel, _ "Report Type")
Select Case msgoption Case vbYes Set xlrng = xlws.Cells(4, 1) On Error Resume Next xlrng.RemoveSubtotal x = 1 For Each fld In rs.Fields xlws.Cells(4, x).Value = fld.Name x = x + 1 Next Set xlrng = xlws.Cells(5, 1) xlrng.CopyFromRecordset rs xlws.Columns.AutoFit Set xlrng = xlws.Columns("D:D") xlrng.NumberFormat = "$#,##0.00" Set xlrng = xlws.Range(xlws.Cells(4, 1), _ xlws.Cells(rs.RecordCount + 4, rs.Fields.Count)) Set xlws2 = ActiveWorkbook.Sheets.Add xlws2.Name = "PivotTable"
xlws2.PivotTableWizard xlDatabase, xlrng, xlws2.Cells(3,1),"SalesbyCategory", _ False, True, True, True, False, , True, True, , , True xlws2.Cells(3, 1).Select xlws2.PivotTables("SalesbyCategory").AddFields RowFields:="ProductName", _ ColumnFields:="CategoryName" With xlws2.PivotTables("SalesbyCategory").PivotFields("ProductSales") .Orientation = xlDataField .NumberFormat = "$#,##0.00" End With ActiveWorkbook.ShowPivotTableFieldList = False Set xlws2 = Nothing Case vbNo Set xlrng = xlws.Cells(4, 1) On Error Resume Next xlrng.RemoveSubtotal x = 1 For Each fld In rs.Fields xlws.Cells(4, x).Value = fld.Name x = x + 1 Next Set xlrng = xlws.Cells(5, 1) xlrng.CopyFromRecordset rs xlws.Columns.AutoFit Set xlrng = xlws.Columns("D:D") xlrng.NumberFormat = "$#,##0.00" Set xlrng = xlws.Range(xlws.Cells(4, 1), _ xlws.Cells(rs.RecordCount + 4, rs.Fields.Count)) xlrng.Subtotal 2, xlSum, 4, True, False, xlSummaryAbove xlws.Outline.ShowLevels 2
Case vbCancel GoTo ExitStuff End Select
ExitStuff: Set xlws = Nothing Set xlws2 = Nothing Set xlrng = Nothing rs.Close Set fld = Nothing Set rs = Nothing dbconn.Close Set dbconn = Nothing Set wrk = Nothing End Sub
There is a lot going on in this procedure. First, the code sets up the DAO connection. This example uses the Northwind database and, provided you installed it in the default location, this path should work if you are using Office 2003. If you are using another version of Office, the path will be different. If the given path does not work, search your hard drive to find the correct path. As an aside, I will mention that I find theSelect Case...End Selectstatement much easier to read than multipleIf...Thenstatements. TheSelect Casestatement allows you to write code that will be executed based on the value of your variable.
In the example above, you store the result of the message box in a variable calledmsgoption. The message box returns a value that is most easily deciphered by using the constants provided by Excel. The only options we gave were Yes, No, and Cancel. Excel VBA recognizes these asvbYes,vbNo, andvbCancel.
If the user clicks Yes, saying that she wants a pivot table, the code betweenCase vbYesandCase vbNoruns. The first few lines of both are the same. Remove subtotals if they exist. I put a line that saysOn Error Resume Nextabove this line; it will be in effect for each line after that. I suggest putting this in as the very last thing when you are writing code. This way, you know where your errors are occurring. As a general rule, I like to see all errors while I am writing and testing code. Then, when I put the code into production, I like to useOn Errorstatements to try to avoid having the end user see error messages.
In addition to this command to add error-checking to the Remove Subtotals command, it is useful to have theOn Error Resume Nextline in for the occasion when someone runs the report twice and asks for a PivotTable both times. When this happens, Excel generates an error message that the sheet named “PivotTable” already exists. By having the code bypass that request, Excel simply skips the step of renaming the sheet, and it stays the default sheet. Returning our focus to the data, after the subtotals are removed (or the step is skipped, in the case where there are no subtotals to remove), you need to add the titles of the columns to the sheet by cycling through theFieldscollection of the recordset. Use theCopyFromRecordset method of the range object to pull in the data from the recordset. With the data in place, the procedure formats the data in column D using a dollar sign, commas, and two decimal places. Those steps are the same for bothvbYesandvbNo. InvbYes, the next step is to make the PivotTable.
There are two general methods for creating a PivotTable. The first method uses thePivotCachesmethod of theWorkbookobject. While I find thePivotCachesmethod to be less intuitive, it is the method that you see if you record a macro of making a PivotTable. The second method, used in the code above, is thePivotTableWizardmethod of theWorksheetobject. Prior to this method being called, thexlrngRange Object is set up to reference the data worksheet, and theCells object is used so that the data can be referred to by rows and columns. You could also use R1C1 notation, but this method is a little cleaner to read since we are pulling the values fromrs.Fields.Count, etc. It is important to set up this Range first because you will build the PivotTable on a new worksheet, and the data resides on another sheet; by setting the range reference first, it becomes a simple matter of using that variable for the data range. The other steps are putting on the row, column, and data fields, as well as formatting the data to be like the data on the original worksheet.
For thevbNo option, go through the same steps asvbYesup to the point where column D is formatted. The Subtotal method is very easy to use. First, theRangeobject needs to be set up to refer to the range. Next, theSubtotalmethod of the range object is called by usingRange.Subtotal—VBA helps you write the rest as you type. The first argument asks which column you want to group on. In Example 3-5, column 2 is selected instead of column 1 because it has the actual names. The consolidation function being used isxlSum. You can select from 12 different functions, including Average (xlAverage), Count (xlCount), etc. If you are not sure which one you want to use, you can highlight Subtotal with your mouse and press F1. Microsoft Help describes each function.
The next argument asks what column(s) you want to apply the function to. In this case we are using only one, so we can simply write4. If there were multiple columns, say you wanted 4, 5, and 6, you would writeArray(4,5,6). The next two arguments want to know whether it should replace the subtotals and whether you want page breaks between groups. The final argument asks if you want the totals to be above or below the group. In Figure 3-3, I have selected above the group. If you selectxlSummaryBelow, the grand total appears at the bottom, and all of the details above the subtotals. This is really a user preference. Thinking back to the original reason for this report, the user wanted a summary of the data. It is unlikely that she wants to see all the details by default, but she probably wants to have the option to see the details if she chooses to. Assuming that, theShowLevelmethod of theOutlineproperty of theWorksheet object is being used to set the opening level to 2. In this case, 1 is the grand total, 2 is the total by category, and 3 is the detail. If the user wants to expand any subtotal, Excel places plus signs to the left of the row. Click on the plus sign to expand the subtotal. Also, press 1, 2, or 3 at the top left of the worksheet to get to the subtotal level that you want.
ThevbCancel line simply sends it to theExitStuff marker. In this case, you could have placed nothing in that line. However, since in many cases you will have code running after theSelect Casestatement, I added it. At the end of the procedure all of the objects are closed and/or set to nothing.
This chapter introduced you to the power of integrating Excel and Access. There is still a lot of ground to cover. At this point, my hope is that you understand how to build a recordset by using ADO and DAO. The rest of the chapter discussion and examples were meant to illustrate additional features already built into Excel. Our code just automated it. A side goal is that you are becoming familiar with the objects used in Excel VBA. When you read Chapter 5 on automating Excel with Access VBA, your familiarity with these objects will greatly increase your understanding of that chapter.
The Northwind database will not be used again until we use SQL Server in Chapter 7. For the rest of the examples, you will need to obtain the sample files available at www.oreilly.com. In these sample files, you will find the Excel example above and the other necessary databases and Excel workbooks, although I encourage you to try to enter the code for yourself, as it will help you get a better feel for VBA.
Figure 3-3. The resulting Excel file when the code in Example 3-5 is run showing subtotals instead of a pivot table, as well as details of the beverages category demonstrating how that works with subtotals
Note that there are appendixes at the end of the book that describe the commonly used Excel and Access objects that you use when doing this type of work. If you need help with VBA, there is a very complete language reference available from O’Reilly called VB & VBA in a Nutshell: The Language.
Database 
/ 11 
Data Access from Excel VBA
