Microsoft Excel 2002 Visual Basic for Applications Step by Step (Step by Step (Microsoft))

2017-07-07 02:10:07

An Excel table is basically a list. But it’s a powerful list that lets you sort, filter, total, and calculate. Data for an Excel table comes from basically two sources: either you get it from an existing worksheet, or you use some kind of connection to get it from an outside source. You can think of these as internal and external data sources.

In this section, you will create macros to import data into an Excel table from an Excel worksheet and from an Access database. This will allow you to see the difference between an internal and an external source. Then you’ll learn how to use macros to perform basic manipulations on a table.

Create a New File from an Existing Worksheet

The purpose of this first macro is to create a copy of the Orders worksheet from the Orders.xlsx file into a new, unsaved file. Excel will not allow you to move the only visible worksheet in a file directly into a new file. You can get around the restriction by first moving the worksheet into the Chapter05 workbook, and then moving it from there into a new file. First you’ll use the macro recorder to create the macro and learn the appropriate methods and properties. Then you’ll use clues from the recorded macro to create your own macro, getting additional guidance whenever possible from the Auto Lists feature as you enter each statement. You will add the letter z as a prefix for each macro you record, so you can keep the recorded macros separate from the ones you create.

Start recording a macro with the name zOpenOrdersFile.

Click the Microsoft Office Button, and click Open. Navigate to the folder that contains the practice files for this book, select Orders.xlsx, and click Open.

Microsoft Office Button

Right-click the Orders tab, click Move Or Copy, select Chaper05.xlsm in the To book list, and then click OK.

In the Chapter05 workbook, right-click the Orders tab, click Move Or Copy, select (new book) in the To book list, and click OK. Then stop the recorder.

On the View tab of the Ribbon, click the Macros button, select the Chapter05.xlsm!zOpenOrderFile macro, and click Edit to review the macro.

Important

The exercises in this chapter use shaded code blocks to identify lines from recorded macros. This is so that you can look at the recorded lines as you create the replacement macro without confusing the two. The complete recorded macros-along with the rewritten versions-are included in the Chapter05.xlsm file in the Finished folder on the companion CD.

ChDir "C:\MSP\ExcelVBA07SBS" Workbooks.Open Filename:= " C:\MSP\ExcelVBA07SBS\Orders.xlsx"

The ChDir statement will be in the macro only if you actually had to change folders, but it is redundant anyway, because the Filename argument includes the full path. The problem with including a full path is that if you move the project to a new location, the path will be wrong. A workbook has a Path property that will give you the current full path for that workbook. By prefixing the Path property to the file name, your macro will work even if you move everything to a new location.

Because there are times when the active workbook is not the one that contains the macros, there is a special ThisWorkbook property that returns the macro workbook. Thus, the expression ThisWorkbook.Path returns the full path of the folder that contains the macro workbook, regardless of what the current directory is, or what workbook is currently active. Because the data file is one folder up from the macro workbook, you need to insert a backslash two dots and another backslash (\..\) in front of the file name.

In the Immediate window, type Workbooks.Open ThisWorkbook.Path & "\..\ Orders.xlsx" and press Enter.

This opens the Orders workbook, and it will work provided that the macro file is in a subfolder of the data folder-without regard to the current folder or the current location in the file structure.

Sheets("Orders").Move Before:=Workbooks("Chapter05.xlsm").Sheets(1)

In the Immediate window, type ActiveSheet.Move ThisWorkbook.Sheets(1) and press Enter.

This moves the active sheet to the macro’s workbook. It doesn’t matter whether it goes before or after the first sheet, because you will move it again anyway.

Sheets("Orders").Move

In the Immediate window, type ActiveSheet.Move and press Enter to move the Orders sheet to its own workbook.

In a Visual Basic module, create a new macro with the name OpenOrdersFile. Insert the three statements from the Immediate window into the macro. Test the macro by pressing F5 or F8.

This macro gives you a simple data file to use as an internal source, without the risk of damaging your original Orders.xlsx workbook. The file is not saved, so you can freely discard it, which is particularly useful while you are creating and testing a macro. You used the macro recorder to learn important syntax clues, but then you made the macro your own.

Create a Table from an Internal Source

Now that you have a worksheet that contains a list of data, you can convert the list intoa table. This process is very simple, but you can still generalize and simplify the recorded macro, and you will be able to compare this process to creating a table from an external source. When you record the macro, it will be stored in the new workbook, so if you want to keep it, be sure to copy it into the Chapter05 workbook module before closing the file.

Start with a workbook that contains the Orders sheet. Run the OpenOrdersFile macro if necessary.

Start recording a macro named zTableFromInternal.

On the Insert tab of the Ribbon, click Table. In the Create Table dialog box, leave the default options, and click OK.

This converts the list to an Excel table, as you can tell by the arrows next to each of the column headings.

Stop the recorder, and edit the macro so that you can use it as a reference as you create your own macro.

The macro you recorded is stored in the new workbook that contains the Orders sheet. When you close that temporary workbook, the recorded macro will be lost. Because this is a temporary macro, losing it does not matter. But when you create your permanent macro, you want to be sure to store it in the permanent Chapter05 workbook. Visual Basic has a tool to help you navigate macros that are stored in multiple workbooks.

On the Visual Basic View menu, click Project Explorer.

The Project Explorer shows that the active module is stored in a temporary workbook, but you can also see the Module1 macro sheet in the Chapter05 workbook.

In the Project Explorer, double-click Module1 in the Chapter05 workbook. Then, in the Immediate window, type OpenOrdersFile and press Enter to create a new source list file.

Create a new macro shell that looks like the following, and press F8 twice to initialize the variable, so that you can get Auto List help with properties of the ActiveSheet.

Sub TableFromInternal() Dim mySheet As Worksheet Set mySheet = ActiveSheet End Sub
Now using the recorded macro as a guide, you can begin to write the new macro.

ActiveSheet.ListObjects.Add(xlSrcRange, _ Range("$A$1:$G$3266"), , xlYes).Name = "Table1"

In the Immediate window, type mySheet.ListObjects.Add( and look at the argument list.

All the arguments are optional. The default value for the first argument is xlSourceRange, which is what the recorder inserted. In fact, the default values for all the arguments are suitable.

Delete the opening parenthesis. Rename mySheet to ActiveSheet.

The statement ActiveSheet.ListObjects.Add is all you need in the macro.

Press F5 to stop the macro. Then delete the current body of the macro, and copy in the Add statement from the Immediate window.

Run the OpenOrdersFile macro, and then test the TableFromInternal macro by pressing F5 or F8.

More than anything, this macro shows you how easy it is to create a table from an Excel list. The hardest part was getting the list into a separate file (the OpenOrdersFile macro). Later, you will create macros to manipulate the basic list, but first create a version of the same table by using an external data connection.

Create a Table from an External Source

Excel data lists are very convenient sources for a table, but they are limited to a million rows and cannot retrieve data from a shared database. In this section, you will use Microsoft Office Access as an external data connection, but the same techniques will work with very minor changes for a Microsoft SQL Server database or other supported data source.

Start recording a macro with the name zTableFromExternal.

Press Ctrl+N to create a new workbook. On the Data tab of the Ribbon, clickthe From Access button. Navigate to the folder containing the files for this book, select Orders.accdb, and click Open. In the Import Data dialog box, leave the default options, and click OK. Then stop the recorder.

Edit the zTableFromExternal macro.

This macro looks very convoluted, but most of the complexity is in the connection string, which you can simplify greatly. The other part that makes the macro look imposing is the With structure that surrounds everything. Here’s what’s happening: The Add method returns an instance of the ListObject that it created. That is not difficult-the Add method for most collections returns the new instance that it creates. Then the recorder uses that reference to access the ListObject’s QueryTable property. (The QueryTable object contains the external data link for the ListObject.) But because the QueryTable object has several properties and a method,the recorder puts the QueryTable into a With structure, and the With statement includes the entire Add method. That’s a lot to do in a single structure.

If you add variables for the ListObject and the QueryTable object, you can separate the parts into separate, more manageable chunks.

Clear the contents of the Immediate window, create the shell of a macro that looks like the following, and press F8 twice to step to the End Sub statement, initializing the variables.

Sub TableFromExternal() Dim l As ListObject Dim q as QueryTable Dim s as String End Sub

Tip

It is generally a good idea to give descriptive names to variables, but sometimes while developing a macro, short names are convenient. You can use short names while you create the macro and then convert them to descriptive names by using the Replace command from the Edit menu. Be sure to select the Current Procedure, Find Whole Word Only, and Match Case options when doing the replace.

Workbooks.Add

In the Immediate window, type Workbooks.Add and press Enter.

A new, blank worksheet appears in Excel.

With ActiveSheet.ListObjects.Add( _ SourceType:=0, _ Source:=Array( _ "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;", _ "Data Source=C:\MSP\ExcelVBA07SBS\Orders.accdb;", _
The Source value for the Add method appears very complicated. For an external data source, this string can be quite long, so the Add method allows it to be broken into substrings by using the Array function. The Add method then just concatenates all the pieces from the Array function. The recorder arbitrarily chops the string every 128 characters. Most of the Source string consists of default values-the only parts that are required are data source type, the Provider, and the Data Source. If the whole string is fewer than 128 characters, you don’t need an Array function at all.

Type (on one line) s = ThisWorkbook.Path & "\..\Orders.accdb" and press Enter. Then put the mouse pointer over the s variable to confirm that the full path name for the database file is correct. For example, if the practice files were in the C:\Practice Files folder, the string would be C:\Practice Files\New\..\Orders.accdb.

Type (on one line) s = "OLEDB;Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & s and press Enter. This string is too long to show up in a tool tip, so type ?s and press Enter to see the string in the Immediate window. Then delete the two rows used to make the confirmation.

Type (on one line) Set l = ActiveSheet.ListObjects.Add(xlSrcExternal, s, , xlYes, ActiveCell) and press Enter.

These few lines replace the very long statement from the recorded macro. The next thing the macro does in this long statement is to assign the connection to the list object.

Destination:=Range("$A$1")).QueryTable .CommandType = xlCmdTable .CommandText = Array("Orders") … .Refresh BackgroundQuery:=False End With
Most of the property setting for the QueryTable object are optional as well-the only critical properties are the CommandType and CommandText. Even the argument to the Refresh method is the default.

Type the following four statements in the Immediate window, pressing Enter after each.

Set q = l.QueryTable q.CommandType = xlCmdTable q.CommandText = "Orders" q.Refresh
Assigning the QueryTable object to a variable takes the place of the With structure in the recorded macro. After you execute the Refresh method, the worksheet fills with data.

Press F5 to stop the running macro, and copy the statements from the Immediate window into the TableFromExternal macro. Then test the TableFromExternal macro by pressing F5 or F8.

Surprisingly, this new, short macro has all the essential functionality of the one the recorder created. When dealing with external data sources, the recorder creates macros with huge strings and lots of properties. When you are creating a sophisticated application working with a large, complex, remote, highly-secured corporate database, those options become very valuable. But when you are creating simple macros to access a department database, you can eliminate most of the complexity.

You may notice that when you originally created the table, the Date column was formatted as dates. However, when you run the macro-either the modified version or the original- the dates are not formatted. It appears that Excel does an extra step that isn’t included in the macro. Later, you’ll learn how to refer to parts of the table so that you can easily add formatting to the Date column.

Record a Macro to Manipulate a Table

A table sits on a worksheet, so it has many properties in common with a range, but it also has special behaviors of its own. For example, the way you refer to regions within the table is unique to a table. And the way you create formulas that refer to other cells within the table is unique. Tables also allow you to add structured totals, and you can filter the table based on the values in the columns. Recording a macro can help you learn about how to manipulate a table, but if you apply your understanding of typical collections objects-and make a few trial-and-error guesses-you can significantly simplify and improve a macro that manipulates a table.

Run the OpenOrdersFile and the TableFromInternal macros to get a fresh simple table. Then start recording a macro named zTableManipulate.

One thing you can do with a table is to insert a new column into the middle of it. For example, suppose that you want to insert a column named Year that contains just the year of the order date. Ideally, the new column should be adjacent to the Date column.

Right-click cell B1, point to Insert, and click Table Columns To The Left. In cell B1, replace Column1 with Year.

In cell B2, type =Year(, click cell A2, type ), and press Enter to fill the column with years.

Move the mouse over the top of the Year label until the pointer turns into a solid black downward arrow, and click to select the cells in the body of the column. Right click cell B2 and click Format Cells. On the Number tab, select Number with no decimal places and no thousands separator, and click OK. Press Ctrl+A to select the entire table.

This recorded statement will serve as a marker in the macro that you’ve finished a section.

In cell I1-just to the right of the column headings-type Avg Price and press Enter.

In the new Avg Price column, you need a formula that divides the Net column by the Units column. If you just point at the cells to create the formula, you get the formula =Table_ExternalData_1[[#This Row],[Net]]/Table_ExternalData_1[[#This Row],[Units]]. The only essential part of the formula is =[Net]/[Units]. As you type the short version of the formula into the cell, note that Excel helps you with the name of the columns. As with the Auto Lists in Visual Basic, you can select a column name and press Tab to insert the name into the formula.

In cell I2, type =[Net]/[Units] and press Enter.

Move the mouse pointer over the Avg Price label until the pointer turns into a solid black downward arrow, and then click to select the cells in the body of the column. Right-click any cell in the column, and click Format Cells. On the Number Format tab, select Currency, and click OK. Press Ctrl+A to select the entire table and insert a marker into the macro.

On the Table Tools Design tab of the Ribbon, click the Total Row check box.

Click the Total cell at the bottom of the Units column, click the arrow, and select Sum. Repeat for the Net column.

In the Total cell for the Avg Price column, type an equal sign, click the total cell for Net, type a division sign ( / ), click the total cell for Units, and then press Enter. Press Ctrl+A to select the current region.

This inserts a marker into the macro so that you can easily differentiate separate sections of the macro.

Click the Avg Price header cell arrow, point to Number Filters, and click Top 10. Select Bottom from the first list, and click OK.

This selects the 10 rows with the lowest average price.

Select the Avg Price header cell. Then on the Home tab of the Ribbon, click Sort & Filter, and then click Clear.

Stop the recorder, and edit the recorded macro.

Every time you see Range("Table1").Select followed by a statement to activate a cell, delete the two statements and replace them with a blank line. These breaks help you see the major sections of the recorded macro.

Tip
The complete recorded macro is available in the Chapter05 macro workbook in the Finished folder.

Now you’re ready to convert the recorded macro into a more elegant one. You can use clues from the recorded macro, but don’t limit yourself to methods and properties that are actually found there.

Manipulate Table Columns

You are already familiar with the ListObject for a table. Assigning the table to a variable will simplify the statements and give you Auto List assistance. The recorded macro also frequently manipulates columns within the table. You can use a ListColumn object as well.

In the Visual Basic Project Explorer, double-click Module1 in the Chapter05 workbook if it’s not already active. Then, run the OpenOrdersFile and TableFromInternal macros. Then, create the shell of a macro that looks like this, and press F8 three times to initialize the variables:

Sub TableManipulate() Dim l as ListObject Dim lc as ListColumn Set l = ActiveCell.ListObject End Sub
You’re now ready to start entering the statements for the body of the macro.

Selection.ListObject.ListColumns.Add Position:=2

In the Immediate window, type Set lc = l.ListColumns.Add(2) and press Enter.

The recorder uses the ListColumns collection to create a new column, but it doesn’t store the new ListColumn object in a variable. By storing the created object in a variable, you can take advantage of Auto List help.

ActiveCell.FormulaR1C1 = "Year"

Type lc.Name = "Year" and press Enter.

The macro recorder simply puts the label into the active cell. By using a property of the ListColumn object, you don’t have to worry about where the active cell happens to be when the macro runs.

ActiveCell.FormulaR1C1 = "=YEAR(Table1[[#This Row],[Date]])"

Type lc.DataBodyRange="=Year([Date])" and press Enter.

The recorder puts a complete structured reference into the formula. If the cell that contains the formula is inside the same table-and is on the same row-all you really need is the column identifier. Note that a table uses square brackets to identify named regions within the table. This is to avoid confusion with Excel named ranges.

Range("Table1[Year]").Select Selection.NumberFormat = "0"

Type lc.DataBodyRange.NumberFormat = "0" and press Enter.

The recorder uses a string expression to reference the Year column, but if you create a table with a different name, the recorded macro will break. By using the Auto List to look at the methods and properties available for a ListColumn object, you can find DataBodyRange, which is a more robust way to access the same range.

Tip

In the earlier section titled "Create a Table from an External Source," you saw that the date formats are not imported from an external data source. To apply the number format for the Date column, you can use the statement l.ListColumns("Date")). DataBodyRange.NumberFormat = "mmm-yy".

ActiveCell.FormulaR1C1 = "Avg Price"

Type Set lc = l.ListColumns.Add and press Enter.

This assigns a different ListColumn object to the same object variable, replacingthe old one. The recorder created a new column by putting a label adjacent to the table. But in your macro, you don’t want to be dependent on specific cell addresses. You’ve already seen that you can use the Add method of the ListColumns collection to insert a column. By using the Auto List help as you type the statement, you can see that the Position argument to the Add method is optional. Leaving it out adds a new column to the right side of the table. This approach is easy to read and can be used multiple times to add multiple columns.

Type lc.Name = "Avg Price" and press Enter.

This adds the caption to the column. When you recorded the macro, you typed the label, which created a list column. When you build your own macro, you create the column and then add the caption to the object.

ActiveCell.FormulaR1C1 = "=[Net]/[Units]"

Type lc.DataBodyRange = "=[Net]/[Units]" and press Enter.

By carefully crafting the formula in the user interface, you were able to get a simple formula in the recorded macro. If you had simply pointed at the referenced cells, the formula would include the table name and [#This Row] as qualifiers. Even if you recorded the macro with the complex names, you could simplify the formula when you create your own macro.

Range("Table1[Avg Price]").Select Selection.NumberFormat = "$#,##0.00"

Type lc.DataBodyRange.NumberFormat = "$#,##0.00" and press Enter.

When you use the object model, you don’t have to go through the step of selecting the range.

A ListObject-that is, an Excel table-has an extensive object model, with great collections for the columns and the rows. Even though the macro recorder doesn’t take advantage of those collections, if you understand how collections typically work, you can make effective guesses about how the collections in a ListObject work. As you type statements, the Auto List gives you excellent clues about what methods and properties are available.

Manipulate Table Totals and Filters

When you recorded the macro, you also recorded statements to modify the totals and filters of the Excel table. For the most part, converting these statements into your own consists of straightforward simplifications. Sometimes-as in the case of putting a formula into the Total row-you may need to do some trial-and-error experiments to find out what will work and what won’t.

ActiveSheet.ListObjects("Table1").ShowTotals = True

In the Immediate window, type l.ShowTotals = True and press Enter.

A Total row appears at the bottom of the table, but you can’t see it yet. This statement simply converts the recorded statement to use the object reference variable.

Range("Table1[[#Totals],[Units]]").Select

Type l.TotalsRowRange.Select and press Enter.

This statement makes it possible to see the total row. You can omit it from the final macro if you want.

ActiveSheet.ListObjects("Table1").ListColumns("Units") _ .TotalsCalculation = xlTotalsCalculationSum Range("Table1[[#Totals],[Net]]").Select ActiveSheet.ListObjects("Table1").ListColumns("Net") _ .TotalsCalculation = xlTotalsCalculationSum

Type the following two statements, pressing Enter after each one.

l.ListColumns("Units").TotalsCalculation = xlTotalsCalculationSum l.ListColumns("Net").TotalsCalculation = xlTotalsCalculationSum

On The CD-Important

Before executing each step in this procedure, you should have stepped through the declaration statements of the TableManipulate macro from the preceding procedure and you should refer to statements from the recorded macro in the earlier section titled “Record a Macro to Manipulate a Table.”

These two statements are essentially identical to the recorded statements. They add Sum as the total to the respective columns. Note that they use the TotalsCalculation property. This property puts a formula into the total cell, but the formula must be one of the predefined formulas from the list.

Range("Table1[[#Totals],[Avg Price]]").Select ActiveCell.FormulaR1C1 = _ "=Table1[[#Totals],[Net]]/Table1[[#Totals],[Units]]"

Type l.ListColumns("Avg Price").Total.Formula = "=[[#Totals],[Net]]/ [[#Totals],[Units]]" and press Enter.

When you refer to cells in the body of the table, you can use just the column name. When you need to refer to cells in the total row, you must add the special keyword [#Total]. In the reference [[#Totals],[Net]], the [#Totals] part identifies the row and the [Net] part identifies the column. You separate them by a comma, and then you have to include the whole reference in another pair of brackets. The recorder added the table name as well. Because the reference is inside the table, you don’t need the table name part. This structured reference is logical, if a little cumbersome. But it will work regardless of where the table may be on the work-sheet, and it automatically adjusts when you add or remove rows from the table.

Type l.HeaderRowRange.Select and press Enter.

This statement just gets you to the top of the table without having to switch back to Excel. You can leave it out of the final macro if you want.

ActiveSheet.ListObjects("Table1").Range.AutoFilter _ Field:=9, Criteria1:="10", Operator:=xlBottom10Items

Type l.Range.AutoFilter l.ListColumns("Avg Price").Index, 10, xlBottom10Items and press Enter.

The recorder refers to the column by number. You would prefer to use the column name, but the argument requires the number. Many items from a collection have an Index property that tells the position of the item within the collection. You can use the Index property to convert the name to the required number. The macro recorder included the argument names. You can include them or omit them as you wish.

ActiveSheet.ShowAllData

Type l.AutoFilter.ShowAllData and press Enter.

The recorder used a ShowAllData method from the active sheet. Because you used the AutoFilter property to create the filter, you can look to see if the AutoFilter has its own ShowAllData method. It does. By using the ShowAllData method from the AutoFilter object rather than the sheet, you can have different filter states for different tables on the same worksheet.

Press F5 to stop the macro. Copy the statements from the Immediate window into the TableManipulate macro. Run the OpenOrdersFile and TableFromInternal macros, and then run the TableManipulate macro to test it.

Sometimes converting information from a recorded macro into your own macro requires some trial and error. For example, when looking at the total formula that the recorder generated for the Avg Price column, you might try converting it to =[Net]/[Units], because that simple version worked for the other rows. But it doesn’t work. So you need to try adding back a little more of the recorded formula until it does work. Of course, you could just leave the formula the way the recorder created it, but then the macro would break if you ran it on a table that happened to have a different table name. If you do leave the table name in a reference in a macro, you should explicitly set the table name earlier in the macro .