Adit Cookbook Pages

We are going to take advantage of the fact that classes have properties and that properties can have separate Get and Let procedures.

The relevant procedure is called depending upon how the property is addressed - the Let is called if the properties is on the left hand end of an equals (=) sign and the Get if it is on the right. This makes substituting a property name for an array name very simple as all of the arguments can remain unaltered.

Instead of myValue = myArray(99) we can have myValue = myProperty(99) and hide the code needed to store and retrieve the data in the property Let and Get procedures.

If your array is made up of one of the standard data types then you can just go ahead and add a class module to your project and define the Get and Let properties as you will see below. However, assuming that you would not be in this mess without a large defined type on your hands the next step needs a little thought. If the subs or functions accessing your data can all be located within a single form then you can use the fact that a form is a class - if not then you will need to add an ActiveX DLL to handle this otherwise simple process to get over the problem of scope for your defined type - please see the notes at the bottom of the page for help with this.

Assuming that the scope of your Virtual Array can be confined within a single VB Form module then implement the code below. The assumptions are that the defined type is called myType and that your array starts at element 0 and that all of the element numbers are positive. If not, then you will have to add a little bit of code to make the file record numbers positive. If you give the property the same name as your array then this is just about all you have to do - alternately do a search and replace to swap the new property name for your original array name. Please remember to call the procedure to create the temporary file before you first address the now virtual array and do call the kill function to delete the temporary file when you have finished.

Private VirtFile as String
Private VFileNumber as Long

Private Property Get myVirtArray(ByVal VElement As Long) As myType
   Dim workType As myType
   If VElement <= (LOF(VFileNumber) / Len(myType)) - 1 and VElement >= 0 then
       Get #VFileNumber, (VElement + 1), workType
       myVirtArray = workType
   Else
       Err.Raise Number:=vbObjectError + 9, _
         Description:="Subscript Out Of Range"
   End If
End Property

Private Property Let myVirtArray(ByVal VElement As Long, ByRef vNewvalue As myType)
   If VElement >= 0 then ‘you could also apply an upper limit here
       Put #VFileNumber, (VElement + 1), vNewValue
   Else
       Err.Raise Number:=vbObjectError + 9, _
         Description:="Subscript Out Of Range"
   End If
End Property
‘you will note that the type has to be passed by reference to the Let procedure (sigh)

Private Sub MakeVirtArray()
   Dim Reclen as Long

   recLen = Len(myType)
   VirtFile = GetTempFile()
   VFileNumber = FreeFile
   Open VirtFile For Random As VFileNumber Len = RecLen
End Sub

Private Sub KillVirtArray()
   Kill VirtFile
End Sub

Private Function GetTempFile() As String
   Dim PathString As String, FileName As String
   Dim RetLength As Long, RetValue As Long
  
   On Error Resume Next
   PathString = Space$(260)
   FileName = Space$(340)

   RetLength = GetTempPath(260, PathString)
   If RetLength > 0 Then
       'the returned value is the length of the Path to the temporary directory
       'not including the null terminating character
       PathString = Left$(PathString, RetLength)
       RetValue = GetTempFileName(PathString, "tmp", 0, FileName)
       If RetValue <> 0 Then
           RetLength = InStr(FileName, vbNullChar) - 1
           FileName = Left$(FileName, RetLength)
       Else
           FileName = ""
       End If
   Else
       FileName = ""
   End If
   GetTempFile = FileName
End Function

Can’t get everything into a single Form module?

You will need to add another project to the current project and make that an ActiveX DLL type. This creates a public code module within which you should define your type. You can now create your class and add the relevant bits of code - adjusting the Private declarations to Public ones.