VBA Samples for Working with Excel 2003 Worksheets

  • Article

 

Frank Rice
Microsoft Corporation

August 2004

Applies to:
    Microsoft Office Excel 2003

Summary: Discover macros that you can use to add additional functionality to your Excel worksheets. With a little practice, you can extend these procedures to fit your own applications. (8 printed pages)

Contents

Introduction
Export a Text File with Comma and Quote Delimiters
Count Cells That Contain Formulas, Text, or Numbers
Use Saved Property to Determine If Workbook Has Changed
Concatenate Columns of Data
Total Rows and Columns in an Array
Conclusion

Introduction   

This article presents a number of Microsoft Visual Basic for Applications (VBA) macros that you can use to add additional functionality to your Microsoft Office Excel 2003 workbooks and worksheets. These macros offer new functionality or enhance existing functionality for your applications. While reading through the examples, you should look for ways to extend the macros to suit your own situation.

Export a Text File with Comma and Quote Delimiters

Excel does not have a menu command to export data automatically to a text file such that the text file is exported with both quotation marks and commas as delimiters. For example, there is no command to automatically create a text file that contains the following:

"Text1","Text2","Text3"

However, you can create this functionality in Excel by using a VBA macro. This file format is commonly seen when importing text data in such applications as Microsoft Office Access 2003 and Microsoft Office Word 2003.

You can use the Print statement in a VBA macro, such as the following one, to export a text file with both quotation marks and commas as the delimiters. For the procedure to function properly, you must select the cells that contain your data before you run it.

Before working with the example below, follow these steps:

  1. Open a new workbook.

  2. On the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press ALT+F11). In the Visual Basic Editor, click the Insert menu and then Module.

  3. Type or paste the following sample code into the module:

    Sub QuoteCommaExport()
    Dim DestFile As String
    Dim FileNum As Integer
    Dim ColumnCount As Integer
    Dim RowCount As Integer

    ' Prompt user for destination file name.
    DestFile = InputBox("Enter the destination filename" & _
      Chr(10) & "(with complete path and extension):", _
      "Quote-Comma Exporter")
    ' Obtain next free file handle number.
    FileNum = FreeFile()

    ' Turn error checking off.
    On Error Resume Next

    ' Attempt to open destination file for output.
    Open DestFile For Output As #FileNum
    ' If an error occurs report it and end.
    If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
    End If

    ' Turn error checking on.
    On Error GoTo 0

    ' Loop for each row in selection.
    For RowCount = 1 To Selection.Rows.Count
      ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

         ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";
         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
    ' Start next iteration of RowCount loop.
    Next RowCount

    ' Close destination file.
    Close #FileNum
End Sub

  4. Before running the macro, select the data that you want to export, and then point to Macros on the Tools menu and click Macro.

  5. Select the QuoteCommaExport macro, and click Run.

Count Cells That Contain Formulas, Text, or Numbers

In Excel, you can count the number of cells in a worksheet that contain formulas, text, or numbers, by using the Go To Special dialog box to select the cells and then running a macro that counts the number of selected cells. For example, it might be helpful when setting up a table to determine if each row in a summary column contains a formula instead of manually examining each row.

Selecting the Cells

To select formulas, text, or numbers, follow these steps:

  1. On the Edit menu, click Go To, and then click Special.
  2. In the Go To Special dialog box, to select all formulas, click Formulas and then ensure that the Numbers, Text, Logicals, and Errors check boxes are selected. To select text, select the Constants option and then click to select only the Text check box. To select numbers, select the Constants option and click to select only the Numbers check box.

VBA Code to Count the Selected Cells

To count the number of cells in the selection and display the result in a message box, use the following procedure:

Sub Count_Selection()
    Dim cell As Object
    Dim count As Integer
    count = 0
    For Each cell In Selection
        count = count + 1
    Next cell
    MsgBox count & " item(s) selected"
End Sub

You can assign this procedure to a command button on the worksheet in order to display the number of items selected when you click the button.

Use Saved Property to Determine If Workbook Has Changed

You can determine if changes were made to a workbook by checking the Saved property of the workbook. The Saved property returns a True or False value depending on whether changes were made to the workbook.

Note   It is possible to set the Saved property to True or False in code in addition to having the property set by the user from an "event." This section contains sample macros that demonstrate the use of the Saved property in both circumstances.

Various conditions in your worksheet, such as the presence of volatile functions, may affect the Saved property. Volatile functions are those functions that are recalculated each time something changes in the worksheet regardless of whether the change affects the function or not. Some of the more common volatile functions are RAND(), NOW(), TODAY(), and OFFSET().

The first macro displays a message if the active workbook has unsaved changes:

Sub TestForUnsavedChanges()
    If ActiveWorkbook.Saved = False Then
        MsgBox "This workbook contains unsaved changes."
    End If
End Sub

The next macro closes the workbook that contains the sample code and discards any changes to the workbook:

Sub CloseWithoutChanges()
    ThisWorkbook.Saved = True
    ThisWorkbook.Close
End Sub

The next example is another macro that closes the workbook and discards the changes:

Sub CloseWithoutChanges()
    ThisWorkbook.Close SaveChanges:=False
End Sub

Concatenate Columns of Data

In Excel, you can use a macro to concatenate the data in two adjacent columns and display the result in the column to the right of the columns that contain your data, all without manually setting up a formula. This section contains a sample macro to accomplish this.

Sub ConcatColumns()
    Do While ActiveCell <> ""  'Loops until the active cell is blank.

        ActiveCell.Offset(0, 1).FormulaR1C1 = _
           ActiveCell.Offset(0, -1) & " " & ActiveCell.Offset(0, 0)
        ActiveCell.Offset(1, 0).Select
    Loop
End Sub

To use the macro:

  1. Open the workbook which contains the data.

  2. Press ALT+F11 to activate the Visual Basic Editor.

  3. Click Module on the Insert menu to insert a module. Type the macro above in the module's code window.

  4. Click Close and Return to Microsoft Excel on the File menu.

  5. Select the worksheet that contains the data that you want to concatenate.

  6. Click the top cell in the right-hand column of data that you want to concatenate. For example, if cells A1:A100 and B1:B100 contain data, click cell B1.

  7. Point to Macros on the Tools menu and click Macro. Select the ConcatColumns macro, and click Run.

    Note   You can replace the statement ActiveCell.Offset(0, 1).FormulaR1C1 with the statement ActiveCell.Offset(0, 1).Formula. You can use them with equal success if you are using text and numbers only (not with formulas). The R1C1 used at the end of the first statement refers to row one, column one and is the form used in most examples in the Excel Help topics.

Total Rows and Columns in an Array

In Excel, you can use arrays to calculate and manipulate data in a worksheet. You can also use macros to store the values from a range of cells into an array. The sample macro code in this section adds an additional column and row to a rectangular region of cells that contain totals for each row and column in that region.

Specifically, the code reads data from the current region of cells surrounding the active cell on the active worksheet. The macro stores the data within an array, summing each row and column, and then places the output on the worksheet. The size of the array is determined by the number of cells in the current region.

Note   This macro does not add any formulas to your worksheet, so if the numbers in the range being totaled change, you must run the macro again.

Before working with the example below, follow these steps:

  1. Open a new workbook.

  2. On the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press ALT+F11). In the Visual Basic Editor, on the Insert menu, click Module.

    Type or paste the following sample code into the module:

    Sub TotalRowsAndColumns()
    ' This macro assumes that you have selected any cell or group of
    ' cells within a rectangular region of cells that you would 
    ' like to have totaled. The totals will appear in the row 
    ' below and the column to the right of the current region.

    Dim r As Integer
    Dim c As Integer
    Dim i As Integer
    Dim j As Integer
    Dim myArray As Variant

    ' Declaring myArray as a Variant prepares it to receive a 
    ' range of cells. At that point it is transformed automatically 
    ' into an array with beginning subscript myArray(1,1).

    'Refer to the region surrounding the current selection.
    With Selection.CurrentRegion
        r = .Rows.Count
        c = .Columns.Count
        'Resize for totals row and column and place into array.
        myArray = .Resize(r + 1, c + 1)

        ' In the following nested loop, the variable i keeps 
        ' track of the row number, while j keeps track of the 
        ' column number. Every time j cycles through the 
        ' available columns, i gets incremented by one and j 
        ' starts the cycle from one to c all over again.
        For i = 1 To r
            For j = 1 To c
              'total for row i
              myArray(i, c + 1) = myArray(i, c + 1) + myArray(i, j)
              'total for column j
              myArray(r + 1, j) = myArray(r + 1, j) + myArray(i, j)
              'grand total
              myArray(r + 1, c + 1) = myArray(r + 1, c + 1) + myArray(i, j)
            Next j
        Next i

        ' Return the array, which now contains an extra row 
        ' and column for the totals, to the worksheet.
        .Resize(r + 1, c + 1) = myArray
    End With
End Sub

  3. Highlight a cell within the region you want to sum, on the Tools menu, point to Macro, and then click Macros.

  4. Select the TotalRowsAndColumns macro, and then click Run.

    Note   To perform a similar operation to the one performed in this sample, you can modify the macro code. For example, to subtract, multiply, or divide the values contained in the selected range of cells, you change the mathematical operator.

Conclusion

In this article, we reviewed various VBA macros that you can use to reduce the amount of work required to use your worksheets. In addition, you can modify most of these macros to extend their utility. It is worthwhile to be always on the alert for additional macros that you can add to your arsenal of tools and tips.