To view a printable version of this article in a PDF viewer,
click here for PDF.
This article describes using virtual mode in a Spread COM control.
This article consists of the following sections:
Throughout this article, we assume that you are familiar with the Microsoft® Visual Basic® development environment and with Spread features, such as adding the control to your toolbox, putting the control on a form, and data binding. We also assume that you have installed the Spread ActiveX control. If you are not familiar with these things, please review the
Spread User's Guide (for any Spread COM product version 3.0 or later) and the tutorial, "Adding Data in Spread COM," available on the FarPoint web site (
This paper was originally written for version 3.0 of the Spread COM product.
For later versions, simply use the VirtualRefresh Method in place of the VRefresh action.
How Does Virtual Mode Work?
Using virtual mode with your Spread control can improve your control's performance considerably. Turning virtual mode on lets the control read into a buffer only the amount of data necessary to display the requested rows. With virtual mode turned off, the control reads in an entire recordset before displaying any records.
For example, if you have a Spread control access a large database with thousands of records and virtual mode is turned off, the control will read in all the records in the database before displaying any records in the control. Reading in so many records may take a long time. Accessing the same database with virtual mode turned on means the control will read in only, for example, a few hundred records before displaying them.
Turning On Virtual Mode
Turn on virtual mode by setting the
VirtualMode property to True.
Virtual Mode Behavior. There will obviously be some behavioral differences resulting from buffering a subset of records rather than reading in the entire set of records. For example, your application will not be able to sort your database or perform similar tasks that require the use of the entire recordset at once. Since virtual mode increases responsiveness by dealing with just a subset of the recordset at a time, then by definition virtual mode is not capable of performing database-wide tasks. Be aware also, that a Spread control using virtual mode cannot print using the SmartPrint option or freeze rows. Refer to the
Spread COM User's Guide when you need more details about the Spread control's behavior when using virtual mode.
Specifying the number of rows
While in virtual mode, the control ignores the setting of the
MaxRows property and uses the number of items specified by the
VirtualMaxRows property. The default setting of the
VirtualMaxRows property is –1, which specifies that the control use however many items are in the recordset, but that the control does not know the exact number.
If you prefer, you can tell the control the exact number of items in the recordset, or you can guess the number of items. If you guess the number of items, overestimate, because the control will read in only the number of items specified by the
For best results, for example, to have the control best reflect where the user is in the recordset, set the
VirtualMaxRows property to the number of items in the recordset.
Changing the recordset at run time. If you set the
VirtualMaxRows property to the number of items in your recordset for a bound database, then bind the Spread control to a different recordset at run time, set
VirtualMaxRows to –1 or to the number of items in the new recordset before you refresh the Data control. If you do not reset the value of the
VirtualMaxRows property, the Spread control might not read in all the records in the new recordset.
What can I do with virtual mode?
You can customize how many records are buffered, along with many other virtual mode features. The topic
How Can I Customize Virtual Mode?describes many of your options for customizing virtual mode. You will see some of these customization options in use in the examples in this white paper.
When virtual mode is turned on and the control requests additional data, the
QueryData event occurs, offering you an opportunity to include your own code to add more data or further customize the control.
How does virtual mode handle property settings?
A spreadsheet can have properties that determine the behaviors of individual cells, rows, columns, and the entire sheet. An application using virtual mode will typically set sheet and column properties in a
Form_Load event. Then, as each subset of rows comes into view, the application will typically set row and cell properties for those rows. The row and cell properties come from an external source (the source of the data, such as a bound database, a file, or an array). The properties may be set automatically, as when bound to a database, or set in the application's code (such as in the
As you work with virtual mode, particularly if you use the
QueryData event, keep in mind these distinctions about how the Spread control is handling property settings.
How Can I Customize Virtual Mode?
As described earlier, virtual mode reads in a certain amount of data, which is then available for viewing or working with. Additional data is not read into the control until it is requested by the user or the application.
The amount of data read into the control is called the
virtual page. You can set the number of records to include in the virtual page. The control also can store records that have been read in memory, for faster control access. You can adjust this buffer size to an optimal size to conserve system resources or to increase control responsiveness, depending on your application's needs.
In virtual mode, the control does not necessarily know how many rows of data there are in the complete set of data. Because the control might not know the number of rows, the scroll bar cannot use the scroll box to accurately represent where the user is in the data. Therefore, you can either tell the control the total number of records or an estimate of the number of records, or you can