Example: A CSV File Class

The example presented in this section defines an object class called CSVFileClass . This class has two properties and two methods :

• Properties:

  • ExportRange : (Read/write) A worksheet range to be exported as a CSV file.

  • ImportRange : (Read/write) The range into which a CSV file will be imported.

• Methods:

  • Import : Imports the CSV file represented by the CSVFileName argument into the range represented by the ImportRange property.

  • Export : Exports the range represented by the ExportRange property to a CSV file represented by the CSVFileName argument.

CD-ROM  

The example in this section is available on the companion CD-ROM. The filename is ˜csv class.xlsm .

Class module “level variables for the CSVFileClass

A class module must maintain its own private variables that mirror the property settings for the class. The CSVFileClass class module uses two variables to keep track of the two property settings. These variables are declared at the top of the class module:

 Private RangeToExport As Range Private ImportToCell As Range 

RangeToExport is a Range object that represents the range to be exported. ImportToCell is a Range object that represents the upper-left cell of the range into which the file will be imported. These variables are assigned values by the Property Get and Property Let procedures listed in the next section.

Property procedures for the CSVFileClass

The property procedures for the CSVFileClass class module follow. The Property Get procedures return the value of a variable, and the Property Let procedures set the value of a variable.

 Property Get ExportRange() As Range     Set ExportRange = RangeToExport End Property Property Let ExportRange(rng As Range)     Set RangeToExport = rng End Property Property Get ImportRange() As Range     Set ImportRange = ImportToCell End Property Property Let ImportRange(rng As Range)     Set ImportToCell = rng End Property 

Method procedures for the CSVFileClass

The CSVFileClass class module contains two procedures that represent the two methods. These are listed and discussed in the sections that follow.

THE EXPORT PROCEDURE

The Export procedure is called when the Export method is executed. It takes one argument: the full name of the file receiving the exported range. The procedure provides some basic error handling. For example, it ensures that the ExportRange property has been set by checking the RangeToExport variable. The procedure sets up an error handler to trap other errors.

 Sub Export(CSVFileName) '   Exports a range to CSV file     If RangeToExport Is Nothing Then         MsgBox "ExportRange not specified"         Exit Sub     End If     On Error GoTo ErrHandle     Application.ScreenUpdating = False     Set ExpBook = Workbooks.Add(xlWorksheet)     RangeToExport.Copy      Application.DisplayAlerts = False     With ExpBook         .Sheets(1).Paste         .SaveAs FileName:=CSVFileName, FileFormat:=xlCSV         .Close SaveChanges:=False     End With     Application.CutCopyMode = False     Application.ScreenUpdating = True     Application.DisplayAlerts = True     Exit Sub ErrHandle:     ExpBook.Close SaveChanges:=False     Application.CutCopyMode = False     Application.ScreenUpdating = True     Application.DisplayAlerts = True     MsgBox "Error " & Err & vbCrLf & vbCrLf & Error(Err), _       vbCritical, "Export Method Error" End Sub 

The Export procedure works by copying the range specified by the RangeToExport variable to a new temporary workbook, saving the workbook as a CSV text file, and closing the file. Because screen updating is turned off, the user does not see this happening. If an error occurs - for example, an invalid filename is specified - the procedure jumps to the ErrHandle section and displays a message box that contains the error number and description.

THE IMPORT PROCEDURE

The Import procedure imports a CSV file specified by the CSVFileName argument and copies its contents to a range specified by the ImportToCell variable, which maintains the ImportRange property. The file is then closed. Again, screen updating is turned off, so the user does not see the file being opened. Like the Export procedure, the Import procedure incorporates some basic error handling.

 Sub Import(CSVFileName) '   Imports a CSV file to a range     If ImportToCell Is Nothing Then         MsgBox "ImportRange not specified"         Exit Sub     End If     If CSVFileName = "" Then         MsgBox "Import FileName not specified"         Exit Sub     End If     On Error GoTo ErrHandle      Application.ScreenUpdating = False     Application.DisplayAlerts = False     Workbooks.Open CSVFileName     Set CSVFile = ActiveWorkbook     ActiveSheet.UsedRange.Copy Destination:=ImportToCell     CSVFile.Close SaveChanges:=False     Application.ScreenUpdating = True     Application.DisplayAlerts = True     Exit Sub ErrHandle:     CSVFile.Close SaveChanges:=False     Application.ScreenUpdating = True     Application.DisplayAlerts = True     MsgBox "Error " & Err & vbCrLf & vbCrLf & Error(Err), _       vbCritical, "Import Method Error" End Sub 

Using the CSVFileClass object

To create an instance of a CSVFileClass object in your code, start by declaring a variable as type CSVFileClass . Here's an example:

 Dim CSVFile As New CSVFileClass 

You might prefer to declare the object variable first and then create the object when needed. This requires a Dim statement and a Set statement:

 Dim CSVFile As CSVFileClass ' other code may go here Set CSVFile = New CSVFileClass 

The advantage of using both a Dim and a Set statement is that the object isn't actually created until the Set statement is executed. You might want to use this technique to save memory by not creating an object if it's not needed. For example, your code might contain logic that determines whether the object is actually created. In addition, using the Set command enables you to create multiple instances of an object.

After creating an instance of the object, you can write other instructions to access the properties and methods defined in the class module.

As you can see in Figure 29-2, the VBE Auto List Members feature works just like any other object. After you type the variable name and a dot, you see a list of properties and methods for the object.

Figure 29-2: The Auto List Members feature displays the available properties and methods.

The following procedure demonstrates how to save the current range selection to a CSV file named temp.csv , which is stored in the same directory as the current workbook:

 Sub ExportARange()     Dim CSVFile As New CSVFileClass     With CSVFile         .ExportRange = ActiveWindow.RangeSelection         .Export CSVFileName:=ThisWorkbook.Path & "\temp.csv"     End With End Sub 

Using the With-End With structure isn't mandatory. For example, the procedure could be written as follows :

 Sub ExportARange()     Dim CSVFile As New CSVFileClass     CSVFile.ExportRange = ActiveWindow.RangeSelection     CSVFile.Export CSVFileName:=ThisWorkbook.Path & "\temp.csv" End Sub 

The following procedure demonstrates how to import a CSV file, beginning at the active cell:

 Sub ImportAFile()     Dim CSVFile As New CSVFileClass     With CSVFile     On Error Resume Next      .ImportRange = ActiveCell     .Import CSVFileName:=ThisWorkbook.Path & "\temp.csv" End With If Err <> 0 Then _   MsgBox "Cannot import " & ThisWorkbook.Path & "\temp.csv" End Sub 

Your code can work with more than one instance of an object. The following code, for example, creates an array of three CSVFileClass objects:

 Sub Export3Files()     Dim CSVFile(1 To 3) As New CSVFileClass     CSVFile(1).ExportRange = Range("A1:A20")     CSVFile(2).ExportRange = Range("B1:B20")     CSVFile(3).ExportRange = Range("C1:C20")     For i = 1 To 3         CSVFile(i).Export CSVFileName:="File" & i & ".csv"     Next i End Sub