XL97: Returning Data with DAO to a Custom Worksheet Function

      Function DAO_Array() As Variant

          Dim db As Database
          Dim rs As Recordset
          Dim varrecords As Variant
          Dim MyPath As String

          ' This is the path to the Northwind database .dbf files.
          MyPath = "c:\program files\microsoft office\office"

          ' Open the Northwind database.
          Set db = OpenDatabase(MyPath, False, False, "dBase 5.0;")

          ' Select all records in the Customer.dbf table.
          Set rs = db.OpenRecordset("Customer")

          ' Move the pointer to the last record of the retrieved data.
          rs.MoveLast

          ' Count the number of records that are returned.
          x = rs.RecordCount

          ' Move the record pointer to the first record. This is required
          ' to return the number of records specified by the RecordCount
          ' property. If this is not done, the record pointer remains on
          ' the last record and only the last record is returned.
          rs.MoveFirst

          ' Return the records to a Variant variable containing the array.
          varrecords = rs.GetRows(x)

          ' Transpose the data so it is orientated correctly.
          DAO_Array = Application.Transpose(varrecords)

          rs.Close
          db.Close

      End Function