Visual Basic Q&A

Symptoms
This article describes how to create the basic framework of an Add-in forVisual Basic 5.0 or 6.0. An Add-in utilizes the Visual Basic Extensibilityobject model to customize and extend the Visual Basic environment.
Resolution
In Visual Basic 5.0 or 6.0, you can create the code for a minimal Add-in project with a single step. You simply double-click on the Add-in icon when you start a new Visual Basic Project.
A Visual Basic 5.0 or 6.0 Add-in can be either an ActiveX DLL or ActiveXEXE, depending upon the project type setting in the Project Propertiesdialog.
By default, an ActiveX EXE is created by the Add-in Wizard for Visual Basic5.0 and an ActiveX DLL is created by default for Visual Basic 6.0. It isgiven the name MyAddin.vbp. In Visual Basic 5.0, three files are added tothis project, connect.cls, frmAddin.frm, and Addin.bas. The contents offiles are listed below with additional comments.
Visual Basic 5.0
Connect.cls

Option Explicit’The IDTExtensibility is used to gain access to the’necessary events in the extensibility model. These four’interface methods must be contained in this class’module and must contain at least one line of code. Even’if the line of code is a comment.”+ OnConnection’+ OnDisconnection’+ OnStartUpComplete’+ OnAddInsUpdate”Implements IDTExtensibility’FormDisplayed keeps track of whether your form is displayedPublic FormDisplayed As Boolean’VBInstance is used to identify which Visual Basic IDE the’Addin belongs to. Because you can have multiple’IDEs open, this identifies the correct IDE.Public VBInstance As VBIDE.VBE’mcbMenuCommandBar is a reference to the new menu item in the Addins’Menu.Dim mcbMenuCommandBar As Office.CommandBarControl’mfrmAddIn is used to reference the addins form.Dim mfrmAddIn As New frmAddIn’MenuHandler is the command bar event handler that gives’access to the command bar events that is used to notify the’addin that a menuitem was selected.Public WithEvents MenuHandler As CommandBarEvents’Hides the Addin Form’Sub Hide()On Error Resume NextFormDisplayed = FalsemfrmAddIn.HideEnd Sub’Shows the Addin Form’Sub Show()On Error Resume NextIf mfrmAddIn Is Nothing ThenSet mfrmAddIn = New frmAddInEnd If’Sets the forms Public VBInstance variable to the instance of Visual’Basic that the addin is being run under.Set mfrmAddIn.VBInstance = VBInstance’Sets the Forms Connect Variable to the instance of this class.Set mfrmAddIn.Connect = MeFormDisplayed = TruemfrmAddIn.ShowEnd Sub’This method adds the Add-In to VB.’Private Sub IDTExtensibility_OnConnection(ByVal VBInst As Object, _ByVal ConnectMode As vbext_ConnectMode, _ByVal AddInInst As VBIDE.AddIn, custom() As Variant)On Error GoTo error_handler’save the vb instanceSet VBInstance = VBInst’This is a good place to set a breakpoint and’test various addin objects, properties and methods.Debug.Print VBInst.FullNameIf ConnectMode = vbext_cm_External Then’Used by the wizard toolbar to start this wizard.Me.ShowElse’Create the Menu Item in the AddinMenu and’return a reference to it in mcbMenuCommandBar.’(See AddToAddInCommandBar function below.)’Set mcbMenuCommandBar = AddToAddInCommandBar(“My AddIn”)’Sets this Classes MenuHandler Event to receive events from the’Menu Item that was just added to the AddInCommandBar’(mcbMenuCommandBar).Set Me.MenuHandler = _VBInst.Events.CommandBarEvents(mcbMenuCommandBar)End If’ vbext_cm_AfterStartup indicates Addin is connected after IDE’ startup.’If ConnectMode = vbext_cm_AfterStartup Then’Checks in the Registry to see if it needs’to show the Addin when it connects.If GetSetting(App.Title, “Settings”, “DisplayOnConnect”, “0″) = _”1″ Then’Set this to display the form on connect.Me.ShowEnd IfEnd IfExit Suberror_handler:MsgBox Err.DescriptionEnd Sub’This method removes the Add-In from VB.’Private Sub IDTExtensibility_OnDisconnection(ByVal RemoveMode As _vbext_DisconnectMode, custom() As Variant)On Error Resume Next’Remove the Menu Item for the Add-inmcbMenuCommandBar.Delete’ Shut down the Add-In and Save the Visible state of the’ addin form for the next time the addin is loaded.’If FormDisplayed ThenSaveSetting App.Title, “Settings”, “DisplayOnConnect”, “1″FormDisplayed = FalseElseSaveSetting App.Title, “Settings”, “DisplayOnConnect”, “0″End IfUnload mfrmAddInSet mfrmAddIn = NothingEnd SubPrivate Sub IDTExtensibility_OnStartupComplete(custom() As Variant)If GetSetting(App.Title, “Settings”, “DisplayOnConnect”, “0″) = _”1″ Then’Set this to display the form on connect.Me.ShowEnd IfEnd Sub’As it has been mentioned above, all four interfaces must be’implemented and must contain at least one line of code for your add-in’to function properly. If you don’t have any particular code that you’want to put in these procedures, just insert a comment. If the’procedure is empty, it will be removed by the compiler.Private Sub IDTExtensibility_OnAddInsUpdate(custom() As Variant)’End Sub’This event fires when the menu is clicked in the IDE.Private Sub MenuHandler_Click(ByVal CommandBarControl As Object, _handled As Boolean, CancelDefault As Boolean)Me.ShowEnd SubFunction AddToAddInCommandBar(sCaption As String) As _Office.CommandBarControlDim cbMenuCommandBar As Office.CommandBarControl’command bar objectDim cbMenu As ObjectOn Error GoTo AddToAddInCommandBarErr’See if the Add-Ins menu can be found.Set cbMenu = VBInstance.CommandBars(“Add-Ins”)If cbMenu Is Nothing Then’Add-Ins menu is not available so you fail.Exit FunctionEnd If’Create a new menu item in the Add-Ins menu.’Set cbMenuCommandBar = cbMenu.Controls.Add(1)’Set the menu caption.’cbMenuCommandBar.Caption = sCaption’ Return a Reference to the New Menu Item.’Set AddToAddInCommandBar = cbMenuCommandBarExit FunctionAddToAddInCommandBarErr:End Function
frmAddin.frm

Public VBInstance As VBIDE.VBEPublic Connect As ConnectOption ExplicitPrivate Sub CancelButton_Click()’ Hide the Form’Connect.HideEnd SubPrivate Sub OKButton_Click()’ Place your specific Add-in code here’MsgBox “AddIn operation on: ” & VBInstance.FullNameEnd Sub
Addin.bas

Option ExplicitDeclare Function WritePrivateProfileString& Lib “Kernel32″ Alias”WritePrivateProfileStringA” (ByVal AppName$, ByVal KeyName$, _ByVal keydefault$, ByVal FileName$)’====================================================================’This sub should be executed from the Immediate window.’In order to get this app added to the VBADDIN.INI file,’you must change the name in the second argument to reflect’the correct name of your project.’====================================================================Sub AddToINI()Dim ErrCode As LongErrCode = WritePrivateProfileString(“Add-Ins32″,”MyAddIn.Connect”,”0″, “vbaddin.ini”)End Sub
Visual Basic 6.0Although the Add-in project code in Visual Basic 6.0 is very similar to theVisual Basic 5.0 code, only two files are added to the MyAddin project; thefrmAddin.frm and an ActiveX Designer (connect.dsr). The ActiveX Designer isan ActiveX component that has a programmable interface and a visualinterface. The Designer allows users to customize the Add-in for run-timeuse. The designer also contains the necessary AddinInstance Interface,rather than needing to implement the IDTExtensibility Interface.
On the General tab, the designer lets you customize some basic informationfor the Add-in such as the Display Name, Description, Host application andversion, Initial Load Behavior, and whether the Add-in contains a UserInterface.
On the Advanced tab, you have the option of selecting a Satellite DLL forLocalization and a Registry key to save additional Add-in specific data.
You can add code to the add-in through code by editing the designer codeand by adding code to frmAddin.
How to Run the Basic Add-in Using Visual Basic 5.0 or Visual Basic 6.0Start a new project and select the AddIn icon.If you are using Visual Basic 5.0, press CTRL+G to open the Immediate Window. Type addtoini, and press return.(NOTE: This step is not required in Visual Basic 6.0.)Press F5 to run the Add-In. This loads and registers the Add-In.Create a new Standard EXE in a second instance of Visual Basic.From the Add-Ins menu, select Add-in Manager.In Visual Basic 5.0, select the My Addin check box.
In Visual Basic 6.0, select My Add-In from the list, and select the Loaded/Unloaded check box. Click OK.From the Add-Ins menu, select My AddIn.Click OK to run your specific Add-In code in frmAddin. (A messagebox appears by default.) Click Cancel to close the form.

Symptoms
When compiling an ActiveX DLL or ActiveX EXE with Binary Compatibility, thefollowing error occurs:

Compile Error : User defined type not defined
Resolution
Turn off Binary Compatibility and compile the project. Visual Basic willhighlight the line of code that contains the User Defined Type that is notdefined. After resolving the problem, Binary Compatibility can be turnedback on.

Symptoms
When compiling an ActiveX DLL or ActiveX EXE with Binary Compatibility, thefollowing error occurs:

Compile Error : User defined type not defined
Resolution
Turn off Binary Compatibility and compile the project. Visual Basic willhighlight the line of code that contains the User Defined Type that is notdefined. After resolving the problem, Binary Compatibility can be turnedback on.

Symptoms
You have a Visual Basic ActiveX DLL that has a method that takes an array of Scripting Dictionary Objects as an argument. It may work fine when your Visual Basic client is run in the Visual Basic integrated development environment (IDE) by using this method, but when you run it as a compiled application, you get the following run-time error message:

Run-time error ‘-2147417848 (80010108)’:
Method ‘~’ of object ‘~’ failedThis only occurs when you use late binding to call the method.
Resolution
Use early binding to work around the problem.

Symptoms
When you try to use Microsoft Decision Support Objects (DSO) from an ASP.NET application to perform administrative tasks on a server that is running Analysis Services, you may receive an error similar to the following:
When connecting to the local computer that is running Analysis Services:

Cannot connect to the Analysis server on computer ‘MyServer’. Connection to the server is lost
When connecting to a remote computer that is running Analysis Services:

Cannot open connection to Analysis server ‘MyRemoteServer’. Error in data [Possible data corruption]
Resolution
This behavior occurs for two separate reasons. One part of the issue involves the start permissions of the DSO ActiveX DLL. By default, the ActiveX DLLs are carried out in the ASP.NET worker process (Aspnet_wp.exe) under the ASPNET account, when called from an ASPX page.
The other part of the issue is, Aspnet_wp.exe uses a Multi-Threaded Apartment (MTA) model, while DSO uses a Single-Threaded Apartment (STA) model. With the ASP.NET application, the impersonation token is on one of the applications MTA threads and the STA COM component is accessed by a different thread (the single thread in its STA). Because the MTA threads impersonation token is not passed to the STA thread, the STA thread carries out under the security token associated with the Aspnet_wp.exe process.

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>