Visual Basic Q&A

Symptoms
When compiling code that has user-defined types containing arrays ofvariable-length strings you may get this error:

“Fixed or static data can’t be larger than 64K”
Resolution
You exceeded the data limitations of a user-defined type. As stated in theProgrammer’s Guide for Microsoft Visual Basic version 4.0:
No variable of a user-defined type can exceed 64K, though the sum ofvariable-length strings in a user-defined type may exceed 64K(variable-length strings occupy only 4 bytes in the user-defined type;the actual contents of a string are stored separately. User-definedtypes can be defined in terms of other user-defined types, but the totalaggregate size of the types cannot exceed 64K.

Symptoms
This article discusses numerous methods for transferring data to Microsoft Excel from your Microsoft Visual Basic application. This article also presents the advantages and the disadvantages for each method so that you can choose the solution that works best for you.
Resolution
The approach most commonly used to transfer data to an Excel workbook is Automation. Automation gives you the greatest flexibility for specifying the location of your data in the workbook as well as the ability to format the workbook and make various settings at run time. With Automation, you can use several approaches for transferring your data: Transfer data cell by cellTransfer data in an array to a range of cellsTransfer data in an ADO recordset to a range of cells using the CopyFromRecordset methodCreate a QueryTable on an Excel worksheet that contains the result of a query on an ODBC or OLEDB data sourceTransfer data to the clipboard and then paste the clipboard contents into an Excel worksheet There are also methods that you can use to transfer data to Excel that do not necessarily require Automation. If you are running an application server-side, this can be a good approach for taking the bulk of processing the data away from your clients. The following methods can be used to transfer your data without Automation: Transfer your data to a tab- or comma-delimited text file that Excel can later parse into cells on a worksheetTransfer your data to a worksheet using ADOTransfer data to Excel using Dynamic Data Exchange (DDE) The following sections provide more detail on each of these solutions.
Note When you use Microsoft Office Excel 2007, you can use the new Excel 2007 Workbook (*.xlsx) file format when you save the workbooks. To do this, locate the following line of code in the following code examples:

oBook.SaveAs “C:\Book1.xls”Replace this code withwith the following line of code:

oBook.SaveAs “C:\Book1.xlsx”Additionally, the Northwind database is not included in Office 2007 by default. However, you can download the Northwind database from Microsoft Office Online.Use Automation to transfer data cell by cell With Automation, you can transfer data to a worksheet one cell at a time:

Dim oExcel As ObjectDim oBook As ObjectDim oSheet As Object’Start a new workbook in ExcelSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Add data to cells of the first worksheet in the new workbookSet oSheet = oBook.Worksheets(1)oSheet.Range(“A1″).Value = “Last Name”oSheet.Range(“B1″).Value = “First Name”oSheet.Range(“A1:B1″).Font.Bold = TrueoSheet.Range(“A2″).Value = “Doe”oSheet.Range(“B2″).Value = “John”‘Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit Transferring data cell by cell can be a perfectly acceptable approach if the amount of data is small. You have the flexibility to place data anywhere in the workbook and can format the cells conditionally at run time. However, this approach is not recommended if you have a large amount of data to transfer to an Excel workbook. Each Range object that you acquire at run time results in an interface request so that transferring data in this manner can be slow. Additionally, Microsoft Windows 95 and Windows 98 have a 64K limitation on interface requests. If you reach or exceed this 64k limit on interface requests, the Automation server (Excel) might stop responding or you might receive errors indicating low memory. This limitation for Windows 95 and Windows 98 is discussed in the following Knowledge Base article:
216400?(http://support.microsoft.com/kb/216400/) Cross-process COM automation can hang client application on Win 95/98 Once more, transferring data cell by cell is acceptable only for small amounts of data. If you need to transfer large data sets to Excel, you should consider one of the solutions presented later.
For more sample code for Automating Excel, please see the following article in the Microsoft Knowledge Base:
219151?(http://support.microsoft.com/kb/219151/) How to automate Microsoft Excel from Visual BasicUse automation to transfer an array of data to a range on a worksheet An array of data can be transferred to a range of multiple cells at once:

Dim oExcel As ObjectDim oBook As ObjectDim oSheet As Object’Start a new workbook in ExcelSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Create an array with 3 columns and 100 rowsDim DataArray(1 To 100, 1 To 3) As VariantDim r As IntegerFor r = 1 To 100DataArray(r, 1) = “ORD” & Format(r, “0000″)DataArray(r, 2) = Rnd() * 1000DataArray(r, 3) = DataArray(r, 2) * 0.7Next’Add headers to the worksheet on row 1Set oSheet = oBook.Worksheets(1)oSheet.Range(“A1:C1″).Value = Array(“Order ID”, “Amount”, “Tax”)’Transfer the array to the worksheet starting at cell A2oSheet.Range(“A2″).Resize(100, 3).Value = DataArray’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit If you transfer your data using an array rather than cell by cell, you can realize an enormous performance gain with a large amount of data. Consider this line from the code above that transfers data to 300 cells in the worksheet:

oSheet.Range(“A2″).Resize(100, 3).Value = DataArray This line represents two interface requests (one for the Range object that the Range method returns and another for the Range object that the Resize method returns). On the other hand, transferring the data cell by cell would require requests for 300 interfaces to Range objects. Whenever possible, you can benefit from transferring your data in bulk and reducing the number of interface requests you make.Use automation to transfer an ADO recordset to a worksheet range Excel 2000 introduced the CopyFromRecordset method that allows you to transfer an ADO (or DAO) recordset to a range on a worksheet. The following code illustrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 and transfer the contents of the Orders table in the Northwind Sample Database using the CopyFromRecordset method.

‘Create a Recordset from all the records in the Orders tableDim sNWind As StringDim conn As New ADODB.ConnectionDim rs As ADODB.RecordsetsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”conn.CursorLocation = adUseClientSet rs = conn.Execute(“Orders”, , adCmdTable)’Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.AddSet oSheet = oBook.Worksheets(1)’Transfer the data to ExceloSheet.Range(“A1″).CopyFromRecordset rs’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.Quit’Close the connectionrs.Closeconn.CloseNoteIf you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:

conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _ sNWind & “;” Replace this line of code with the following line of code:

conn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & _ sNWind & “;” Excel 97 also provides a CopyFromRecordset method but you can use it only with a DAO recordset. CopyFromRecordset with Excel 97 does not support ADO.
For more information about using ADO and the CopyFromRecordset method, please see the following article in the Microsoft Knowledge Base:
246335?(http://support.microsoft.com/kb/246335/) How to transfer data from an ADO recordset to Excel with automationUse automation to create a QueryTable on a worksheet A QueryTable object represents a table built from data returned from an external data source. While automating Microsoft Excel, you can create a QueryTable by simply providing a connection string to an OLEDB or an ODBC data source along with an SQL string. Excel assumes the responsibility for generating the recordset and inserting it into the worksheet at the location you specify. Using QueryTables offers several advantages over the CopyFromRecordset method: Excel handles the creation of the recordset and its placement into the worksheet.The query can be saved with the QueryTable so that it can be refreshed at a later time to obtain an updated recordset.When a new QueryTable is added to your worksheet, you can specify that data already existing in cells on the worksheet be shifted to accommodate the new data (see the RefreshStyle property for details). The following code demonstrates how you could automate Excel 2000, Excel 2002, or Office Excel 2003 to create a new QueryTable in an Excel worksheet using data from the Northwind Sample Database:

‘Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.AddSet oSheet = oBook.Worksheets(1)’Create the QueryTableDim sNWind As StringsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”Dim oQryTable As ObjectSet oQryTable = oSheet.QueryTables.Add( _”OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”, oSheet.Range(“A1″), “Select * from Orders”)oQryTable.RefreshStyle = xlInsertEntireRowsoQryTable.Refresh False’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.QuitUse the clipboard The Windows Clipboard can also be used as a mechanism for transferring data to a worksheet. To paste data into multiple cells on a worksheet, you can copy a string where columns are delimited by tab characters and rows are delimited by carriage returns. The following code illustrates how Visual Basic can use its Clipboard object to transfer data to Excel:

‘Copy a string to the clipboardDim sData As StringsData = “FirstName” & vbTab & “LastName” & vbTab & “Birthdate” & vbCr _& “Bill” & vbTab & “Brown” & vbTab & “2/5/85″ & vbCr _& “Joe” & vbTab & “Thomas” & vbTab & “1/1/91″Clipboard.ClearClipboard.SetText sData’Create a new workbook in ExcelDim oExcel As ObjectDim oBook As ObjectSet oExcel = CreateObject(“Excel.Application”)Set oBook = oExcel.Workbooks.Add’Paste the dataoBook.Worksheets(1).Range(“A1″).SelectoBook.Worksheets(1).Paste’Save the Workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”oExcel.QuitCreate a delimited text file that Excel can parse into rows and columns Excel can open tab- or comma-delimited files and correctly parse the data into cells. You can take advantage of this feature when you want to transfer a large amount of data to a worksheet while using little, if any, Automation. This might be a good approach for a client-server application because the text file can be generated server-side. You can then open the text file at the client, using Automation where it is appropriate.
The following code illustrates how you can create a comma-delimited text file from an ADO recordset:

‘Create a Recordset from all the records in the Orders tableDim sNWind As StringDim conn As New ADODB.ConnectionDim rs As ADODB.RecordsetDim sData As StringsNWind = _”C:\Program Files\Microsoft Office\Office\Samples\Northwind.mdb”conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”conn.CursorLocation = adUseClientSet rs = conn.Execute(“Orders”, , adCmdTable)’Save the recordset as a tab-delimited filesData = rs.GetString(adClipString, , vbTab, vbCr, vbNullString)Open “C:\Test.txt” For Output As #1Print #1, sDataClose #1′Close the connectionrs.Closeconn.Close’Open the new text file in ExcelShell “C:\Program Files\Microsoft Office\Office\Excel.exe ” & _Chr(34) & “C:\Test.txt” & Chr(34), vbMaximizedFocusNoteIf you use the Office 2007 version of the Northwind database, you must replace the following line of code in the code example:

conn.Open “Provider=Microsoft.Jet.OLEDB.4.0;Data Source=” & _sNWind & “;”Replace this line of code with the following line of code:

conn.Open “Provider=Microsoft.ACE.OLEDB.12.0;Data Source=” & _sNWind & “;” If your text file has a .CSV extension, Excel opens the file without displaying the Text Import Wizard and automatically assumes that the file is comma-delimited. Similarly, if your file has a .TXT extension, Excel automatically parse the file using tab delimiters.
In the previous code sample, Excel was launched using the Shell statement and the name of the file was used as a command line argument. No Automation was used in the previous sample. However, if so desired, you could use a minimal amount of Automation to open the text file and save it in the Excel workbook format:

‘Create a new instance of ExcelDim oExcel As ObjectDim oBook As ObjectDim oSheet As ObjectSet oExcel = CreateObject(“Excel.Application”)’Open the text fileSet oBook = oExcel.Workbooks.Open(“C:\Test.txt”)’Save as Excel workbook and Quit ExceloBook.SaveAs “C:\Book1.xls”, xlWorkbookNormaloExcel.Quit For more information about using File I/O from your Visual Basic application, please see the following article in the Microsoft Knowledge Base:
172267?(http://support.microsoft.com/kb/172267/) RECEDIT.VBP demonstrates file I/O in Visual BasicTransfer data to a worksheet by using ADO Using the Microsoft Jet OLE DB Provider, you can add records to a table in an existing Excel workbook. A “table” in Excel is merely a range with a defined name. The first row of the range must contain the headers (or field names) and all subsequent rows contain the records. The following steps illustrate how you can create a workbook with an empty table named MyTable.Excel 97, Excel 2000, and Excel 2003Start a new workbook in Excel.Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastNameFormat cell B1 as right-aligned.Select A1:B1.On the Insert menu, choose Names and then select Define. Enter the name MyTable and click OK.Save the new workbook as C:\Book1.xls and quit Excel. To add records to MyTable using ADO, you can use code similar to the following:

‘Create a new connection object for Book1.xlsDim conn As New ADODB.Connectionconn.Open “Provider=Microsoft.Jet.OLEDB.4.0;” & _”Data Source=C:\Book1.xls;Extended Properties=Excel 8.0;”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Bill’, ‘Brown’)”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Joe’, ‘Thomas’)”conn.CloseExcel 2007In Excel 2007, start a new workbook.Add the following headers to cells A1:B1 of Sheet1:
A1: FirstName B1: LastNameFormat cell B1 as right-aligned.Select A1:B1.On the Ribbon, click the Formulas tab, and then click Define Name.Type the name MyTable, and then click OK.Save the new workbook as C:\Book1.xlsx, and then quit Excel. To add records to the MyTable table by using ADO, use code that resembles the following code example.

‘Create a new connection object for Book1.xlsDim conn As New ADODB.Connectionconn.Open “Provider=Microsoft.ACE.OLEDB.12.0;” & _”Data Source=C:\Book1.xlsx;Extended Properties=Excel 12.0;”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Scott’, ‘Brown’)”conn.Execute “Insert into MyTable (FirstName, LastName)” & _” values (‘Jane’, ‘Dow’)”conn.Close When you add records to the table in this manner, the formatting in the workbook is maintained. In the previous example, new fields added to column B are formatted with right alignment. Each record that is added to a row borrows the format from the row above it.
You should note that when a record is added to a cell or cells in the worksheet, it overwrites any data previously in those cells; in other words, rows in the worksheet are not “pushed down” when new records are added. You should keep this in mind when designing the layout of data on your worksheets.
Note The method to update data in an Excel worksheet by using ADO or by using DAO does not work in Visual Basic for Application environment within Access after you install Office 2003 Service Pack 2 (SP2) or after you install the update for Access 2002 that is included in Microsoft Knowledge Base article 904018. The method works well in Visual Basic for Application environment from other Office applications, such as Word, Excel, and Outlook. For more information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
904953?(http://support.microsoft.com/kb/904953/) You cannot change, add, or delete data in tables that are linked to an Excel workbook in Office Access 2003 or in Access 2002
904018?(http://support.microsoft.com/kb/904018/) Description of the update for Access 2002: October 18, 2005
For additional information on using ADO to access an Excel workbook, please see the following articles in the Microsoft Knowledge Base:
195951?(http://support.microsoft.com/kb/195951/) How to query and update Excel data using ADO from ASPUse DDE to transfer data to Excel DDE is an alternative to Automation as a means for communicating with Excel and transferring data; however, with the advent of Automation and COM, DDE is no longer the preferred method for communicating with other applications and should only be used when there is no other solution available to you.
To transfer data to Excel using DDE, you can: Use the LinkPoke method to poke data to a specific range of cell(s),
-or-Use the LinkExecute method to send commands that Excel will execute. The following code example illustrates how to establish a DDE conversation with Excel so that you can poke data to cells on a worksheet and execute commands. Using this sample, for a DDE conversation to be successfully established to the LinkTopic Excel|MyBook.xls, a workbook with the name MyBook.xls must already be opened in a running instance of Excel.
Note When you use Excel 2007, you can use the new .xlsx file format to save the workbooks. Make sure that you update the file name in the following code example.
Note In this example, Text1 represents a Text Box control on a Visual Basic form:

‘Initiate a DDE communication with ExcelText1.LinkMode = 0Text1.LinkTopic = “Excel|MyBook.xls”Text1.LinkItem = “R1C1:R2C3″Text1.LinkMode = 1′Poke the text in Text1 to the R1C1:R2C3 in MyBook.xlsText1.Text = “one” & vbTab & “two” & vbTab & “three” & vbCr & _”four” & vbTab & “five” & vbTab & “six”Text1.LinkPoke’Execute commands to select cell A1 (same as R1C1) and change the font’formatText1.LinkExecute “[SELECT(""R1C1"")]“Text1.LinkExecute “[FONT.PROPERTIES(""Times New Roman"",""Bold"",10)]“‘Terminate the DDE communicationText1.LinkMode = 0 When using LinkPoke with Excel, you specify the range in row-column (R1C1) notation for the LinkItem. If you are poking data to multiple cells, you can use a string where the columns are delimited by tabs and rows are delimited by carriage returns.
When you use LinkExecute to ask Excel to carry out a command, you must give Excel the command in the syntax of the Excel Macro Language (XLM). The XLM documentation is not included with Excel versions 97 and later. For more information on how you can obtain the XLM documentation, please see the following article in the Microsoft Knowledge Base:
143466?(http://support.microsoft.com/kb/143466/) Macro97.exe file available on online services DDE is not a recommended solution for communicating with Excel. Automation provides the greatest flexibility and gives you more access to the new features that Excel has to offer.

Symptoms
The information below includes the documentation and workarounds for VisualBasic 6.0. This information can also be found in the README.htm file thatships with Visual Basic 6.0 on the Visual Basic 6.0 CD-ROM. Please see theREFERENCES section of this article for a list of the Microsoft KnowledgeBase articles relating to the Visual Basic 6.0 readme.
Following is a list of all parts of the readme file:
Part 1.Important Issues – Please Read First!
Part 2.Data Access Issues and DataBinding Tips
Part 3.Control Issues
Part 4.Language Issues
Part 5.Samples Issues
Part 6.Wizard Issues
Part 7.Error Message Issues
Part 8.WebClass Designer Issues
Part 9.DHTML Page Designer Issues
Part 10. Extensibility issues
Part 11. Miscellaneous Issues
Part 12. Microsoft Transaction Server (MTS) Issues
Part 13. Dictionary Object
Part 14. Visual Component Manager
Part 15. Application Performance Manager
Resolution
Lightweight Controls Must Be BorderlessWhen creating a lightweight User control by setting the Windowless propertyto True, the BorderStyle property is invalidated. By definition alightweight control has no border.
If you first set the BorderStyle property to anything other than 0 – Noneand subsequently change the Windowless property to True, you will receivean error message “Windowless UserControls only support BorderStyle = None”.Run Time Error 711: Compiled .Exe Doesn’t Contain Information AboutUnreferenced Control Causing Controls.Add to Fail
Problem:
Create a new Standard EXE. Form1 is created by default.Add a user control to the project.Add the following code:

Dim WithEvents x as VBControlExtenderPrivate Sub Form_Load ()Set x = Controls.Add (“Project1.Usercontrol1″, “XX”)x.Visible = TrueEnd Sub On the File menu, click Make Project1.exe (Don’t run the project.)Run the EXE.
Result: You get an error (711) stating that Project1.Usercontrol1 is aninvalid ProgID since no info about it can be found in the exe.
Solution: Before compiling the project, under the Project menu, clickProject1 Properties. On the Make tab, clear the “Remove informationabout unused ActiveX controls” check box.
Remarks
By default, ActiveX controls that are referenced but not placed on any typeof form at design time are not available for Controls.Add at runtime or inan executable.
Hierarchical FlexGrid Control: ColWordWrapOption, ColWordWrapOptionBand,
ColWordWrapOptionFixed, ColWordWrapOptionHeader PropertiesThe following properties are part of the Hierarchical FlexGrid control’sfeature set but are not documented in the control’s help:
ColWordWrapOption, ColWordWrapOptionBand, ColWordWrapOptionFixed,ColWordWrapOptionHeader. Descriptions and syntaxes for these properties arefound below. Settings for all properties are the same, and can be found atthe bottom of the topic:
ColWordWrapOption Property
Returns or sets a value that specifies how text is wrapped in a specifiedcolumn.
Syntax

object.ColWordWrapOption (Index) = integer
The ColWordWrapOption property syntax has these parts:

PartDescription—————————————————-objectAn object expression that evaluates to a HierarchicalFlexGrid control.IndexLong. The number of the column to get or set word wrapon. The value must be in the range of -1 to Cols – 1.Setting this value to -1 selects all columns.integerA numeric expression that determines how words will wrap,as shown in settings.
ColWordWrapOptionBand Property
Returns or sets a value that specifies how text is wrapped in a specifiedband.
Syntax

object.ColWordWrapOptionBand (BandNumber, BandColIndex) = integer
The ColWordWrapOption property syntax has these parts:

PartDescription—————————————————-objectAn object expression that evaluates to a HierarchicalFlexGrid control.BandNumberLong. The number of the band to get or set word wrap on.The value must be in the range of 0 to Bands – 1.BandColIndexLong. The number of the column to get or set word wrapon. This optional parameter defaults to -1, indicatingall columns in the band. Valid values are -1 to Cols -1.integerA numeric expression that determines how words will wrap,as shown in settings.
ColWordWrapOptionBand Property
Returns or sets a value that specifies how text is wrapped in a specifiedband.
Syntax

object.ColWordWrapOptionBand (BandNumber, BandColIndex) = integer
The ColWordWrapOption property syntax has these parts:

PartDescription—————————————————————objectAn object expression that evaluates to aHierarchical FlexGrid control.BandNumberLong. The number of the band to get or setword wrap on. The value must be in the rangeof 0 to Bands – 1.BandColIndexLong. The number of the column to get or setword wrap on. This optional parameterdefaults to -1, indicating all columns in theband. Valid values are -1 to Cols -1.integerA numeric expression that determines howwords will wrap, as shown in settings.
ColWordWrapOptionFixed Property
Returns or sets a value that specifies how text is wrapped in a specifiedfixed column.
Syntax

object.ColWordWrapOptionFixed(index) = integer
The ColWordWrapOptionFixed property syntax has these parts:

PartDescription—————————————————-objectAn object expression that evaluates to a HierarchicalFlexGrid control.indexLong. The number of the column to get/set word wrap on.This optional parameter defaults to -1. Valid values are-1 to Cols -1.IntegerA numeric expression that determines how words will wrap,as shown in settings.
ColWordWrapOptionFixed Property
Returns or sets a value that specifies how text is wrapped in a specifiedfixed column.
Syntax

object.ColWordWrapOptionFixed(index) = integer The ColWordWrapOptionFixed property syntax has these parts:

PartDescription——————————————————————objectAn object expression that evaluates to aHierarchical FlexGrid control.indexLong. The number of the column to get/setword wrap on. This optional parameterdefaults to -1. Valid values are -1 to Cols-1.integerA numeric expression that determines howwords will wrap, as shown in settings.
ColWordWrapOptionHeader Property
Returns or sets a value that specifies how text is wrapped in columnheaders.
Syntax

object.ColWordWrapOptionHeader(BandNumber, BandColIndex) = integer The ColWordWrapOptionHeader property syntax has these parts:

PartDescription——————————————————————–objectAn object expression that evaluates to aHierarchical FlexGrid control.BandNumberLong. The number of the band to get/set wordwrap on. The value must be in the range of 0to Bands – 1.BandColIndexLong. The number of the column to get/setword wrap on. This optional parameterdefaults to -1 indicating all column headersin the band. Valid values are -1 to Cols -1.integerA numeric expression that determines howwords will wrap, as shown in settings.
Settings
The settings for integer are:

ConstantValueDescription————————————————————flexSingleLine0(Default) Displays text on asingle line only.flexWordBreak1The lines are automaticallybroken between words.flexWordEllipsis2Truncates text that does notfit in the rectangle and addsellipsis.flexWordBreakEllip3Breaks words between lines andsisadds ellipsis if text doesn’tfit in the rectangle.
Hierarchical FlexGrid Control: ColIsVisible and RowIsVisible Properties are
Read OnlyThe ColIsVisible and RowIsVisible properties are read-only properties andcannot be set programmatically. You can use the properties to test whethera column or row is visible, and hide the column or row, if appropriate, asshow below:

With MSHFlexGrid1If .ColIsVisible(1) Then .ColWidth(1) = 0If .RowIsVisible(1) Then .RowHeight(1) = 0End With
Hierarchical FlexGrid Control: Additional Settings for GridLines,GridLinesBand, GridLinesFixed, GridLinesHeader, GridLinesIndent, and
GridLinesUnpopulated PropertiesTwo additional settings are possible for the following properties:
ridLines, GridLinesBand, GridLinesFixed, GridLinesHeader,GridLinesIndent, GridLinesUnpopulated Properties. The possible settingsare show in the table below:

ConstantValueDescription—————————————————————flexGridDashes4Dashed Lines. Sets line stylebetween cells to dashed lines.flexGridDots5Dotted Lines. Sets line stylebetween cells to dotted lines.
Remarks:
These settings can be used in addition to flexGridNone, flexGridFlat,flexGridInset and flexGridRaised.
DataRepeater Control: Setting Public Properties Affect only the Current
ControlWhen creating a user control to be used in a DataRepeater control, be awarethat public properties of the control will be set only on the currentcontrol (the “live” control with the focus). For example, if you expose theFont property of a user control, at run time, resetting that property (asshown in the example code below) will only affect the current control inthe DataRepeater control. The font of repeated controls will not beaffected.

Private Sub Command1_Click()’ Only the current control’s Font will be affected.DataRepeater1.RepeatedControl.FontName = “Courier”End Sub
The corresponding code in the user control would resemble the following:

Public Property Get FontName() As StringFontName = txtProductName.Font.NameEnd PropertyPublic Property Let FontName(ByVal newFontName As String)txtProductName.Font.Name = newFontNameEnd Property
TabStrip Control: Separators show only when the Style property =
TabFlatButtonsSeparators will only appear on a TabStrip control when the Style propertyis set to TabFlatButtons. An example is shown below:

Private Sub Form_Load()With TabStrip1.Style = tabFlatButtons.Separators = TrueEnd WithEnd Sub
Data Report Designer: Error in Event Handling CodeIn the topic titled Data Report Events, there is an error in code thatshows how to handle asynchronous errors. For more information, searchonline, with Search titles only selected, for “Data Report Events” in theMSDN Library Visual Studio 6.0 documentation.
The code is found under the heading “Error Events’ for Asynchronous Events.”The code omits a “Select Case ErrObj.ErrorNumber” statement. The correctedcode is:

Private Sub DataReport_Error(ByVal JobType As _MSDataReportLib.AsyncTypeConstants, ByVal Cookie As Long, _ByVal ErrObj As MSDataReportLib.RptError, ShowError As Boolean)Select Case ErrObj.ErrorNumberCase rptErrPrinterInfo ‘ 8555MsgBox “A printing error has occurred. ” & _”You may not have a Printer installed.”ShowError = FalseExit SubCase Else ‘ handle other cases here.ShowError = TrueEnd SelectEnd Sub
RichTextBox Control: SelPrint Method Has New ArgumentThe SelPrint method now features a second, optional argument. The syntaxand part descriptions are shown below:
Syntax

object.SelPrint(lHDC As Long, [vStartDoc])
The SelPrint method syntax has these parts:

PartDescription—————————————————–objectAn object expression that evaluates to aRichTextbox control.lHDCLong. The device context of the device youplan to use to print the contents of thecontrol.vStartDocBoolean. Specifies the behavior of thecontrol regarding startdoc and enddoc printercontrol operations, as shown in settings.
SettingsThe settings for vStartDoc are:

ConstantValueDescription—————————————————————True-1(Default) The control retains itsoriginal behavior and sends startdocand enddoc commands to the printer.False0The control doesn’t send startdoc andenddoc commands, but sends onlystartpage and endpage commands to theprinter.
Remarks:
The argument was added to remedy situations when printers do not print withthe default behavior. When the SelPrint method is invoked, both VisualBasic and the RichTextBox control send startdoc and enddoc commands to theprinter resulting in a nested pair of startdoc/enddoc commands. Someprinters respond only to the first pair of commands and thereby becomedisabled when the RichTextbox control sends the second pair. In that case,setting the vStartDoc argument to False prevents the second pair ofcommands from being sent.
Visual Basic 5 Version of MSChart Control Available in Tools DirectoryFor pre-release users of Visual Basic only:
A Visual Basic 5.0x version of the MSChart control is now included withVisual Basic. If you need a Visual Basic 5 version of the Chart control,and you have installed the pre-release version of the MSChart control,please overwrite the pre-release version with the version contained in theTools directory of the Visual Basic CD.
Toolbar Control: Style Property Settings ChangedThe Style property settings for the Toolbar control have been changed. Thehelp topic for the property lists tbrTransparent and tbrRight as possiblesettings, however these are not implemented in the current version. Theactual possible settings and descriptions are shown below:

ConstantValueDescription————————————————————–tbrStandard0(Default) Standard toolbar.tbrFlat1Flat. The borders of a buttondynamically appear when the cursorhovers over the button.
Visual Basic Run-Time Error 720: Attempting to Add Anything Except a
Control to Controls Collection Causes Run-Time ErrorAttempting to add an object that is not a control to the Controlscollection causes run-time error 720. You can only add Visual Basicintrinsic controls or ActiveX controls to the collection.To reproduce:
Create a new Standard Exe. Form1 is created by default.Add the following code:

Private Sub Form_Load ()Controls.Add “Excel.Application”, “MyExcelApp”)End Sub Run the exe.Result: You get an error (720): Invalid class string.
Hierarchical FlexGrid Control: Correcting Errors Binding a Recordset to the
HFlexGridIf you receive the following error when trying to bind the HierarchicalFlexGrid to an ADO Recordset object, “DataSource settings may beincorrect”, try changing some of the behavioral properties associated withthe ADO Recordset Object or Command. For example, change the CursorLocationproperty to adUseNone or adUseClient.
Hierarchical FlexGrid Control: How to Change the Font of Individual BandsSince the same font object is used for the entire grid object, you mustcreate a new font object to change the fonts of individual bands, ratherthan changing the font directly.
For example, this way will not change the font for the individual band:

MSHFlexGrid1.FontBand(1).Name = “Arial”
Since you are directly modifying the font object, this will change thefonts in all of the bands to Arial.
To change an individual band, first create a new Font object, then assignthat Font object to the FontBand property:

Dim ft As New StdFontft.Name = “Arial”Set MSHFlexGrid1.FontBand(1) = ft
This will change just the band’s font to Arial.
Hierarchical FlexGrid Control: Avoiding the display of duplicate headersBy default, the Hierarchical FlexGrid control uses the first FixedRow inthe Hierarchical FlexGrid as a set of headers (which means it displays thenames of the fields bound to each column in this row). Since, by default,the HFlexGrid control displays one FixedRow, if you enable the display ofheaders on Band 0, it will appear as though the header is being duplicatedtwice. To avoid this, set the FixedRow property to 0, or clear out the textvalues in the first FixedRow using code.
ADO Data Control: FetchProgress and FetchComplete Events Not ImplementedAlthough the reference topic for the ADO Data Control includes links to theFetchProgress and FetchComplete events, the events are not implemented forthe control.
DataGrid: SizeMode and Size Properties Do Not Accept Value of 2
(dbgNumberOfColumns)The reference topics for the Split object’s SizeMode and Size propertiesrefer to a non-existent property value of 2 (dbgNumberOfColumns). Pleaseignore this value.
Controls: ImageList Control on Page DesignerWhen using the ImageList control on a DHTML Page designer, images cannot beadded at design time. If you try to use the following code in an uncompiled.dll project, you will get the run-time error: -2147418113 (8000ffff),”Method ‘Add’ of object images failed”.

Private Sub DHTMLPage_Load()ImageList1.ListImages.Add , , LoadPicture(“C:\Winnt\winnt.bmp”)End Sub
However, the code will work when the .dll project is compiled.
MSComm Control: EOFEnable Property Doesn’t Stop Data InputThe EOFEnable property determines if the OnComm event occurs when an EOFcharacter is detected. Contrary to the documentation for the property,however, input does not stop.
Treeview Control: Node Object’s Visible Property is Read-OnlyThe Visible property of the Treeview control’s Node object is a read-onlyproperty. If the node is not visible, you can use the EnsureVisible methodto make it visible, as shown in the example:

Private Sub Command1_Click()If Not TreeView1.Nodes(10).Visible ThenTreeView1.Nodes(10).EnsureVisibleEnd IfEnd Sub
SysInfo Control: Constants Not SupportedThe reference topics for the following events have lists of constants thatidentify devices and device data.:
DeviceArrival EventDeviceOtherEvent EventDeviceQueryRemove EventDeviceQueryRemoveFailed EventDeviceRemoveComplete EventDeviceRemovePending EventContrary to the documentation, however, these constants are not supportedby the events or the SysInfo control. The values associated with theconstants listed in the help topics are valid, but the constant names arenot.
User Control: Binary Persistence of PropertyBag Data Causes Page Designer
to FailThe PropertyBag saves data in binary format. Due to a known problem withbinary persistence and the DHTML page designer, however, such data causesthe page designer and Visual Basic to fail. See Page Designer: BinaryPersistence Issue for more information.

Symptoms
The information below includes the documentation and workarounds for VisualBasic 6.0. This information can also be found in the README.htm file thatships with Visual Basic 6.0 on the Visual Basic 6.0 CD-ROM. Please see theREFERENCES section of this article for a list of the Microsoft KnowledgeBase articles relating to the Visual Basic 6.0 readme.
Following is a list of all parts of the readme file:
Part 1.Important Issues – Please Read First!
Part 2.Data Access Issues and DataBinding Tips
Part 3.Control Issues
Part 4.Language Issues
Part 5.Samples Issues
Part 6.Wizard Issues
Part 7.Error Message Issues
Part 8.WebClass Designer Issues
Part 9.DHTML Page Designer Issues
Part 10. Extensibility issues
Part 11. Miscellaneous Issues
Part 12. Microsoft Transaction Server (MTS) Issues
Part 13. Dictionary Object
Part 14. Visual Component Manager
Part 15. Application Performance Manager
Resolution
Passing User-Defined Types to ProceduresWith Visual Basic 6.0 it is possible to pass a user defined type (UDT) asan argument to a procedure or function. However, there is a restriction.Passing a UDT to a procedure in an out-of-process component or acrossthreads in a multi-threaded component requires an updated version of DCOMfor Windows 95 and Windows 98, or Service Pack 4 for Windows NT 4.0. Thisupdate is required on your development computer as well as on any computerthat will run your application. A run-time error will occur if the requiredfiles are not installed.
The above does not apply to passing UDTs within a single-threadedapplication; this will work without updating. The Package and DeploymentWizard will not determine the dependencies for the necessary components -it is up to you to make sure that the files are on the end user’s computer.You can test for the existence of the components by trapping for run-timeerror 458 – “Variable uses an Automation type not supported in VisualBasic”. If this error occurs, the DCOM or Service Pack components must beupdated; the update procedure differs depending on the operating system:
Windows 95 or Windows 98
DCOM98.EXE is a self-extracting executable that installs the updatedDCOM components for Windows 95 or Windows 98. It can be found in theDCOM98 directory of the Visual Basic 6.0 CD. This file may be freelydistributed with your Visual Basic application.Windows NT 4.0
The updated DCOM components are automatically installed with ServicePack 4 (SP4). When it is released, you can download the Service Packfrom the Microsoft Web site.
Searching Online by Topic TitleTo search for a topic when you have the title:
In the navigation pane of the MSDN window, click the Search tab and thentype or paste the title of the topic you want to find. Enclose thesearch string in quotation marks.Click Search Titles Only.Click List Topics. (If your search returns more than one hit, you cansort the topic list by clicking the Title or Location column heading.)Select the title of the topic you want and then click Display.
To find where a topic is located in the table of contents, click theLocate button on the toolbar. The table of contents will synchronizewith the topic you are viewing.
NOTE: The Locate button is unavailable for the topics in the Referencenode of the Visual Basic documentation.Cross References to Internet Client SDK
Refer to the Internet/Intranet/Extranet SDKIn the Building Internet Applications book within the Component ToolsGuide, multiple cross references are made to a part of MSDN referred to asthe “Internet Client SDK.” The correct name for this SDK is the”Internet/Intranet/Extranet SDK.” When searching for an Internet Client SDKreference in MSDN, please look in this section.
Context-Sensitive HelpTo use Help buttons and the F1 key to access Help without having the MSDNCD in your CD drive, you must choose the Custom install option during setupof the MSDN Library. Check the boxes labeled “VB Documentation,” “VBProduct Samples,” and “VS Shared Documentation.” You may also want to check”VSS Documentation” if you are using Visual SourceSafe.
Sample Code Sometimes Does Not Cut and Paste ProperlyLine breaks and formatting information may not copy correctly when you copyand paste sample code from the MSDN Library Visual Studio documentation toyour code editor. To work around this issue, do one of the following:
Manually edit the line breaks after you copy the code.View the sample code source, copy the entire code sample, including the<pre> and </pre> tags, paste it to your code editor, and then delete theunwanted sections from the pasted version.
Locate Button Disabled for Reference TopicsWhen you find a language reference topic in MSDN through the Search tab,you cannot use the Locate button to find where the topic is located in theMSDN Table of Contents tree.

Symptoms
This article shows by example how to extract the bitmap photos in theMicrosoft Access 97 Nwind.mdb database, and view them from a Webbrowser using Active Server Pages (ASP). In order to accomplish this task,an ActiveX DLL must be created that strips the Access and OLE headers fromthe field. This article shows how to create this ActiveX DLL, and how toimplement it.
Resolution
WARNING: ANY USE BY YOU OF THE CODE PROVIDED IN THIS ARTICLE IS AT YOUR OWNRISK. Microsoft provides this code “as is” without warranty of any kind,either express or implied, including but not limited to the impliedwarranties of merchantability and/or fitness for a particular purpose.
This article demonstrates how to use Visual Basic to retrieve a bitmapstored in an OLE Object field. Because the definition of OLE object storageis not documented, the following code searches the object’s OLE header forcharacters consistent with the start of the graphic. This method may notwork in all circumstances.
Be aware that Internet Explorer 3.0 is unable to display true colorbitmaps. For this reason, the bitmaps stored in the Access database shouldbe no higher than 256 colors.
Step-by-Step Example to Extract the PhotosCreate a new project in Visual Basic and make the project an ActiveXDLL.Add a reference to ActiveX Data Objects (ADO) by clicking the Projectmenu and selecting References. Select “Microsoft OLE DB ActiveX DataObjects 1.0 Library” and click OK.Add a new module to the project by selecting the Project menu andclicking Add Module. Select Module and click Open.Place the following code in the (general) (declarations) section ofMODULE1.BAS:

‘ Enter the following Declare statement as one single line:Public Declare Sub CopyMemory Lib “kernel32″ Alias “RtlMoveMemory”(lpvDest As Any, lpvSource As Any, ByVal cbCopy As Long)Type PTWidth As IntegerHeight As IntegerEnd TypeType OBJECTHEADERSignature As IntegerHeaderSize As IntegerObjectType As LongNameLen As IntegerClassLen As IntegerNameOffset As IntegerClassOFfset As IntegerObjectSize As PTOleInfo As String * 256End Type Place the following code in the (general) (declarations) section ofCLASS1.CLS:

Function DisplayBitmap(ByVal OleField As Variant)Dim Arr() As ByteDim ObjHeader As OBJECTHEADERDim Buffer As StringDim ObjectOffset As LongDim BitmapOffset As LongDim BitmapHeaderOffset As IntegerDim ArrBmp() As ByteDim i As Long’Resize the array, then fill it with’the entire contents of the fieldReDim Arr(OleField.ActualSize)Arr() = OleField.GetChunk(OleField.ActualSize)’Copy the first 19 bytes into a variable’of the OBJECTHEADER user defined type.CopyMemory ObjHeader, Arr(0), 19′Determine where the Access Header ends.ObjectOffset = ObjHeader.HeaderSize + 1′Grab enough bytes after the OLE header to get the bitmap header.Buffer = “”For i = ObjectOffset To ObjectOffset + 512Buffer = Buffer & Chr(Arr(i))Next i’Make sure the class of the object is a Paint Brush objectIf Mid(Buffer, 12, 6) = “PBrush” ThenBitmapHeaderOffset = InStr(Buffer, “BM”)If BitmapHeaderOffset > 0 Then’Calculate the beginning of the bitmapBitmapOffset = ObjectOffset + BitmapHeaderOffset – 1′Move the bitmap into its own arrayReDim ArrBmp(UBound(Arr) – BitmapOffset)CopyMemory ArrBmp(0), Arr(BitmapOffset), UBound(Arr) -BitmapOffset + 1′Return the bitmapDisplayBitmap = ArrBmpEnd IfEnd IfEnd Function Rename the Project by selecting the Project menu, and clicking on”Project1 Properties” and type your new name in the “Project Name”field. This example assumes that you named the project “MyProject” andwill refer to that name in future steps.Select the”Unattended Execution” check box. Click OK.Rename the Class in the Property Pane. This example assumes that younamed the class “MyClass” and refers to that name in future steps.Compile the DLL by clicking the File menu and selecting “MakeMyProject.dll.”Create an ASP page named “bitmap.asp” that contains thefollowing code:

<%@ LANGUAGE=”VBSCRIPT” %><%’You need to set up a System DSN named ‘NWind’ that points to’the Northwind.mdb databaseSet DataConn = Server.CreateObject(“ADODB.Connection”)DataConn.Open “DSN=NWind”, “admin”, “”Set cmdTemp = Server.CreateObject(“ADODB.Command”)Set RS = Server.CreateObject(“ADODB.Recordset”)cmdTemp.CommandText = “SELECT Photo FROM EmployeesWHERE EmployeeID = 1″cmdTemp.CommandType = 1Set cmdTemp.ActiveConnection = DataConnRS.Open cmdTemp, , 0, 1Response.ContentType = “image/bmp”Set Bitmap = Server.CreateObject(“MyProject.MyClass”)Response.BinaryWrite Bitmap.DisplayBitmap(RS(“Photo”))RS.Close%> Create an HTML page named “BitmapTest.htm” that containsthe following code:

<HTML><HEAD><TITLE>Bitmap Test</TITLE></HEAD><BODY><HR><img src=”Bitmap.asp”><HR></BODY></HTML>

Symptoms
Microsoft Visual Basic ships with an add-in utility known as the Visual Data Manager (VISDATA) for which source code is also provided in the form of a sample project named Visdata.vbp. Visdata.vbp is installed with other Visual Basic samples as part of the MSDN Library installation.
This article describes how to modify the source code for the Visual Data Manager add-in in order to use the add-in with Microsoft Access 2000 format database files.

Resolution
Adapting VISDATA to work with Microsoft Access 2000 format databases requires changing only one project reference, adding one menu item, and adding one line of code to process the new menu item.
Update the DAO Reference from DAO 3.51 to DAO 3.60. (This step alone is sufficient to allow you to open and work with Access 2000 files.)Open Visdata.vbp in the Visual Basic design environment.On the Project menu, open the list of References.Deselect the existing reference to Microsoft Data Access Objects 3.51, and then locate and select Microsoft Data Access Objects 3.60. If you are using Visual Basic 6.0, be sure to move the DAO 3.6 reference above the ADO library reference in the list.Add a new menu item and code to allow creating a new Access 2000 database.Open the main form, frmMDI, in the design environment.From the Tools menu, open the Menu Editor.In the Menu Editor window, scroll down in the existing menu items to File / New / Microsoft Access.In order to insert the new menu item after the existing entry for Access Version 7.0 MDB files, select the following entry: dBase. Click the Insert button, and then click the RIGHT ARROW button to make the new, blank entry a subitem under the Microsoft Access heading. Enter Access 2000 as the Caption and mnuDBNMDB2K as the Name. Close the Menu Editor by clicking OK.Open the frmMDI code module and select the mnuDBNMDB2K_Click event procedure in the drop-down list. Enter the following line of code:

NewMDB dbVersion40In Visual Basic 6.0 only, update ADO connection strings to support Access 2000.VisData uses both DAO and ADO. Use the Search function on the Visual Basic Edit menu to locate all references in the project to the Microsoft Jet OLE DB Provider version 3.51 and update 3.51 to 4.0.In Visual Basic 6.0 only, add code that will enable the Grid toolbar button, which displays a table of records in a DBGrid control rather than one record per form.Open the module modVisData and find the OpenTable procedure.After the lines:

If gsDataType = gsMSACCESS Then.ConnectionString = “PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=” & gsDBName insert the additional line:

.CursorLocation = adUseClient Then open frmDataGrid and find the Form_Load event procedure.After the line:

Set datDataCtl.Recordset = mrsFormRecordset insert the additional lines:

datDataCtl.RefreshSet grdDataGrid.DataSource = datDataCtlgrdDataGrid.Refresh If you were to omit this step, you might see an empty grid; or the grid may display with the very first cell empty.Recompile the VISDATA project and copy the compiled executable to the Visual Basic directory (which is \Program Files\Microsoft Visual Studio\Vb98 by default in Visual Basic 6.0).