I was working on a function in a customer database that returned a customer name. The function looked in a CSV file that was being selected to look through it and find the name in the CSV data.

There was a new requirement to look up more information from the file. I now needed a number representing a “total payments” amount (which was just a piece of data, nothing I had to sum) and the payment date the file was reporting on.

All of this info was within the first 2 or three lines of the file, but my function was just returning a String. I wanted it to return all the information at once if possible. I knew I could do this if I returned a Variant and could make it an array, or some string that contained a list my return code could split into an array or parse, but that seemed kind of clunky.

Another option would have been to create global variables at the top of the form and populate each one. My routines would have to know which variables were updated and also would have to be concerned about re-initializing them if the customer re-ran the file selection process. It seemed better than the array option, but was still kind of clunky.

I rested on using a User Defined Type as a solution. This allows me to encapsulate multiple named variables into a single object. I don’t need a whole class module to do this (which would have worked) since I’m just using native variable types.

It kind of works like a cross between global variables and a class, and also uses autocomplete when you declare a variable as that type. Here’s some of the code I wrote to give you an idea:

Option Compare Database 
Option Explicit 

Private Type ImportFileInfo 
    Customer As String 
    PaymentTotal As Double 
    PaymentDate As Date 
End Type 

Private Sub cmdSelectFile_Click() 
   Dim strFileType As ImportFileInfo 
   
   Me.cmdImportData.Enabled = False 
   Me.Label10.Caption =  "File Name: Selecting"
   Me.Label11.Caption =  "Customer: Selecting"
   Me.Label12.Caption =  ""
   Me.Label13.Caption =  ""
   Me.Label14.Caption =  ""
   strFileName = RDFfile.dialogOpenFileName( "dx-xf-vv.080",strSelectedFolder, "Pick your file to import", "STX Data Files", "*.*") 
   If strFileName =  "" Then 
      Me.Label10.Caption =  "File Name: None"
      Me.Label11.Caption =  "Customer: None"
      Me.Label12.Caption =  "Payment Total:"
      Me.Label13.Caption =  "Payment Date:"
      Me.Label14.Caption =  ""
   Else 
      strSelectedFolder = Left(strFileName,InStrRev(strFileName, "\")) 
      Me.Label10.Caption =  "File Name: " & RDFfile.GetFilenameFromPath(strFileName) 
      strFileType = DetectFileType(strFileName) 
      Me.Label11.Caption =  "Customer: " & strFileType.Customer 
      Me.Label12.Caption =  "Payment Total: " & strFileType.PaymentTotal 
      Me.Label13.Caption =  "Payment Date: " & strFileType.PaymentDate 
      
End Sub 

Private Function DetectFileType(strFileName As String) As ImportFileInfo 
   Dim fh As Integer 
   Dim InputLine1 As String 
   Dim arrInput() As String 
   Dim Customer As String,PaymentTotal As Double,PaymentDate As Date 
   
   fh = FreeFile 
   Open strFileName For Input As #fh 
   Line Input #fh,InputLine1 
   
   Customer =  "Unknown"
   Me.cmdImportData.Tag =  "CancelImport"
   Me.cmdImportData.Enabled = False 
   If InStr(1,InputLine1, "TMM",vbTextCompare) > 0 Or _ 
      InStr(1,InputLine1, "TMMK",vbTextCompare) > 0 Or _ 
      InStr(1,InputLine1, "NAEXP",vbTextCompare) > 0 Or _ 
      InStr(1,InputLine1, "NUMMI",vbTextCompare) > 0 Then 
         If InStr(1,InputLine1, ",",vbTextCompare) > 0 Then 
            arrInput = Split(InputLine1, ",") 
            If UBound(arrInput) < 2 Then 
               Customer =  "Initial Line does not have expected fields"
               GoTo Exit_Function 
            ElseIf arrInput(1) <>  "820" Then 
               Debug.Print arrInput(1),InputLine1 
               Customer =  "File is not an 820 of the expected format"
               GoTo Exit_Function 
            Else 
               Customer =  "Toyota 820"
               Me.cmdImportData.Tag = strFileName 
               Me.cmdImportData.Enabled = True 
            End If 
         Else 
            Customer =  "Not Comma Delimited"
            GoTo Exit_Function 
         End If 
   End If 
   If Customer =  "" Or Customer =  "Unknown" Then GoTo Exit_Function 
   Line Input #fh,InputLine1 
   arrInput = Split(InputLine1, ",") 
   PaymentTotal = arrInput(1) 
   PaymentDate = CDate(Mid(arrInput(3),5,2) &  "/" & Right(arrInput(3),2) &  "/" & Left(arrInput(3),4)) 
   Line Input #fh,InputLine1 
   arrInput = Split(InputLine1, ",") 
   Customer = arrInput(2) 
Exit_Function: 
   Close #fh 
   DetectFileType.Customer = Customer 
   DetectFileType.PaymentTotal = PaymentTotal 
   DetectFileType.PaymentDate = PaymentDate 
End Function