Microsoft Office Tutorials and References
In Depth Information
VBA Techniques
Table 11-1: Sorting Times (in Seconds) for Four Sort Algorithms Using Randomly Filled Arrays
Array Elements
Excel Worksheet Sort
VBA Bubble Sort
VBA Quick Sort
VBA Counting Sort
100
0.04
0.00
0.00
0.02
500
0.02
0.01
0.00
0.01
1,000
0.03
0.03
0.00
0.00
5,000
0.07
0.84
0.01
0.01
10,000
0.09
3.41
0.01
0.01
50,000
0.43
79.95
0.07
0.02
100,000
0.78
301.90
0.14
0.04
The worksheet sort algorithm is amazingly fast, especially when you consider that the values are
transferred to the sheet, sorted, and then transferred back to the array.
The bubble sort algorithm is reasonably fast with small arrays, but for larger arrays (more than
10,000 elements), forget it. The quick sort and counting sort algorithms are blazingly fast, but
they’re limited to Integer and Long data types.
Processing a series of files
One common use for macros is to perform repetitive tasks. The example in this section
demonstrates how to execute a macro on several different files stored on disk. This example — which
may help you set up your own routine for this type of task — prompts the user for a file
specification and then processes all matching files. In this case, processing consists of importing the file
and entering a series of summary formulas that describe the data in the file.
Sub BatchProcess()
Dim FileSpec As String
Dim i As Integer
Dim FileName As String
Dim FileList() As String
Dim FoundFiles As Integer
‘ Specify path and file spec
FileSpec = ThisWorkbook.Path & “\” & “text??.txt”
FileName = Dir(FileSpec)
‘ Was a file found?
If FileName <> “” Then
FoundFiles = 1
ReDim Preserve FileList(1 To FoundFiles)
FileList(FoundFiles) = FileName
Else
MsgBox “No files were found that match “ & FileSpec
Exit Sub
 
Search JabSto ::




Custom Search