Programming Excel with VBA and .NET

Random Access

Random-access files are read or written one record at a time. In this case, record usually means a fixed-size data structure identified by a user-defined type. Because Visual Basic knows the length of each record, you can jump to any record in the file using the Seek statement (that’s what makes the access random).

In order to use random access , you must first define the structure of your record with a Type statement. You then declare a variable with that type and use it to read and/or write records to the file. I’m not going to show you how to do all that, because XML files and databases both provide a much better approach for storing and retrieving structured data. I cover those topics in Chapters 12 and 15.

Why is random access not such a great approach? A few reasons:

• The records are fixed-length by definition, which means names, addresses, and other variable-length data must be stored in fixed-length strings. You have to correctly guess the maximum size of those items during design.

• Changes to your data structure, such as adding a field, means you have to convert all of your existing datafiles. You have to write code to open, convert, and save files using the new structure. (In programming circles this is called tying your data structure to your implementation, and it’s a bad thing.)

• You’re programming in Excel! You already have better tools for doing these types of tasks.