Scope of application:
Microsoft Office Excel 2003
Summary: Looking for a macro that can add additional functions for Excel worksheets. Just exercise slightly, you can extend these programs to make your own application.
This page
Introduction to export text files with commas and quotation marks, computing the cell number of formulas, text, or numbers Use the saved attribute to determine if the workbook has changed the total number of banks in the merge data column array, and the total number of column number conclusions
introduction
This article describes several Microsoft Visual Basic for Applications (VBA) macros, you can use these macros to add additional features for Microsoft Office Excel 2003 workbooks and worksheets. These macros will provide your application with new features or enhance existing features. To read the examples, you should find ways to extend these macros to suit your own situation.
Back to top
Export text file with comma and quotation separator
Excel does not automatically export the data to the menu command for text files, so the exported text files have a comma and quotation marks. For example, there is no command that automatically creates a text file containing the following:
"Text1", "Text2", "Text3"
However, you can use the VBA macro to create this feature in Excel. This file format is a usual format that imports text data in applications such as Microsoft Office Access 2003 and Microsoft Office Word 2003.
You can use the print statement in the VBA macro shown below, export text files with commas and quotation markers. To make the program run normally, you must select a cell that contains data before running the program.
Before using the following example, perform the following steps:
1. Open a new workbook. 2. In the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press the Alt F11 combination key). In the Visual Basic Editor, click the Insert menu and click Module. 3. Type or paste the following sample code into the module: SUB QuoteCommaExport ()
DIM DESTFILE AS STRING
DIM Filenum as in
DIM columncount as integer
DIM ROWCOUNT AS INTEGER
'Tip the user to specify the target file name.
Destfile = INPUTBOX ("Enter The Destination Filename" & _
CHR (10) & ":", _
"Quote-Comma Exporter")
'Get the next available file handle number.
FILENUM = Freefile ()
'Turn off the error check function.
ON Error ResMe next
'Try to open the target file for the output.
Open destfile for Output as #filenum
'If an error occurs, the error is reported and the program is completed.
IF Err <> 0 THEN
Msgbox "Cannot Open FileName" & Destfile
End
END IF
'Open the error check function.
ON Error Goto 0
'Every line of cyclic selection.
For rowcount = 1 to selection.Rows.count
'Each column of cyclic selection.
For columncount = 1 to selection.columns.count 'Write the text in the current cell to the file, the text is enclosed with quotation.
Print #filenum, "" "" & selection.cells (rowcount, _
COLUMNCOUNT) .TEXT & "" "";
'Check if the cell is in the last column.
IF color = selection.columns.count dam
'If so, write an empty line.
Print #filenum,
Else
'Otherwise, a comma is written.
Print #filenum, ",";
END IF
'Start the next iteration of the columnCount loop.
Next columncount
'The next iteration of the ROWCOUNT loop.
Next rowcount
'Close the target file.
Close #filenum
End Sub
4. Before running the macro, select the data you want to export, and then point to Macro in the Tools menu and click Macro. 5. Select the QuoteCommaExport macro and click Run.
Back to top
Calculate the number of cells containing formulas, text or numbers
In Excel, you can calculate the number of cells in the worksheet containing formulas, text, or numbers, and the method is to select a cell using the Positioning Condition dialog box and then run the macro of the number of selected cells. For example, this method may be useful when you need to set a table to determine if each line of the total column contains an formula without manually checking each line.
Select cell
To select a formula, text, or numbers, perform the following steps:
1. In the Edit menu, click Position, and then click Positioning Conditions. 2. In the Positioning Conditions dialog box, you want to select all formulas, click Formula and make sure the "Number", Text, "Logic Values", and "Error" checkboxes. To select a text, select the "Constant" option, then only click and select the Text check box. To select a number, select the "constant" option, then only click and select the "Number" check box.
Calculate VBA code for the number of selected cells
To calculate the number of cells selected and display the calculation result in the 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 program to a command button so that when you click the button, the number of options will be displayed.
Back to top
Determine if the workbook has changed using the Saved property
You can determine if the workbook has changed by checking the Saved property of the workbook. The Saved property will return true or false value based on whether the workbook has changed.
Note: In addition to the "Event" setting Saved properties, users can also set them to True or false via the code. The example macro included in this section describes how to use the saved attribute in both cases.
Various situations in the worksheet (for example, a variable function) may affect the Saved property. Variable functions refer to the function that recalculates each change in the worksheet, regardless of whether or not the changes affect these functions. Some common variable functions include RAND (), now (), Today (), and offset (). If the active workbook contains unsaved changes, the first macro will display the following message:
Sub testforunsavedchanges ()
If ActiveWorkbook.saved = false then
Msgbox "this workbook contains unsaved change". "
END IF
End Sub
The next macro will close the workbook containing the sample code and give all the changes to the workbook:
Sub closewithoutchanges ()
Thisworkbook.saved = true
THISWORKBOOK.CLOSE
End Sub
The following example macro will also turn off the workbook and give up the changes:
Sub closewithoutchanges ()
Thisworkbook.close saveChanges: = false
End Sub
Back to top
Merged data column
In Excel, the macros can be used in two adjacent columns and display results in the right column containing the data, do not need to manually set formulas. This feature can be implemented in this section.
Sub concatcolumn ()
Do While Activecell <> "" 'has been loop until the active cell is empty.
Activecell.offset (0, 1). Formular1c1 = _
Activecell.offset (0, -1) & "& Activecell.offset (0, 0)
Activecell.offset (1, 0) .select
Loop
End Sub
To use a macro, perform the following steps:
1. Open a workbook containing the data. 2. Press the Alt F11 combination key to activate the Visual Basic Editor. 3. In the Insert menu, click Module to insert a module. Type the macro above in the code window of the module. 4. Click "Close and return to Microsoft Excel" in the File menu. 5. Select the worksheet that contains the data you want to merge. 6. Click the first cell of the right data column you want to merge. For example, if cells A1: A100, and B1: B100 contain data, click Cell B1. 7. In the Tools menu, point to Mobilities and click Macro. Select the ConcatColumns macro and click Run. Note: You can use statements Activecell.offset (0, 1) .formula replace statement Activecell.offset (0, 1) .formular1C1. If you use text and numbers (without formulas), then the effects of both statements are the same. The R1C1 used at the end of the first statement represents the first column of the first line, which is the form of most of the examples in the Excel help topics.
Back to top
The total number of banks and total columns in arrays
In Excel, you can use an array to calculate and operate data in a worksheet, and a macro can be used to store values in a range in a range in an array. The example macro code in this section will add a row and a column in a rectangular cell area to include the total number of cells in each row and each column in the region.
The specific step is that the code reads data from the current cell area around the active unit on the active worksheet. Macros store these data in an array, calculate the total number of cells in each row and each column, and then display the output in the worksheet. The size of the array is determined by the number of cells in the current area.
Note: This macro does not add any formulas in a worksheet, so if there is a change in the total number of cells in this range, you must re-run the macro. Before using the following example, perform the following steps:
1. Open a new workbook. 2. In the Tools menu, point to Macro, and then click Visual Basic Editor (or simply press the Alt F11 combination key). In the Insert menu in the Visual Basic Editor, click Module. Type or paste the following sample code into the module: SUB TOTALROWSANDCOLUMNS ()
'This macro leaves you have
'Inside the rectangular area of the total number of cells
'Select a cell or a set of cells. The total number of cells and columns will appear
'The rows below the current area and the columns on the right.
DIM R AS INTEGER
DIM C AS Integer
DIM I as integer
DIM J AS INTEGER
DIM MyArray As Variant
'Storing MyArray as variables will enable arrays to receive
'Set of cells. At this point, the array will be automatically converted to
The array begins the following marrray (1, 1).
'Refers to the area around the previously selected cell.
WITH Selection.currentRegion
r = .rows.count
C = .columns.count
'Recalculate the total number of lines and the total number of colors and store the results into an array.
MyArray = .resize (R 1, C 1)
'In the nested cycle below, the variable I tracks
'Line number, variable J tracking
'List. J is once in the available columns once,
'i increments one, but j
'Returning from one to C once.
For i = 1 TO R
For j = 1 to c
Total number of line I
MyArray (i, c 1) = MyArray (i, c 1) MyArray (i, j)
'Total number of column J
MyArray (R 1, J) = MyArray (R 1, J) MyArray (i, j)
'Total
MyArray (R 1, C 1) = MyArray (R 1, C 1) MyArray (i, j)
NEXT J
Next i
'Return the array to worksheets, and now contain one
'New line and a new column for storage total.
.Resize (R 1, C 1) = MyArray
End with
End Sub
3. Highlight a cell in the requirement and region, in the Tools menu, point to Mobilities and click Macro. 4. Select TotalRowsandColumns macros and click Run. Note: To perform similar operations similar to the calculation in this example, you can modify the macro code. For example, to subtract, multiply or division operations, multiplication or division operations, can change the mathematical operator in the selected range.
Back to top
in conclusion
This article describes a variety of VBA macros, using these macros to reduce the workload required to use worksheets. In addition, you can modify most macros described in this article to extend their features. Always remember to add more macros in the tool or prompt library, you will benefit.
