Creating a "Hello world" com add-in

[Charles]

Hello

I am trying to create a Hello World com add-in that I can call from
VBA in Office 2003. I found various step by step guides on google, but
none really works.

I am using Visual Studio Express 2008.
I created a new Class Library project, went into Assembly information,
and made it "COM Visible". I then went into Signing and clicked Sin
the assembly, created as "password.pfx" file with a 6+ letters
password.

I have only one class, which looks like this:

Option Compare Text
Option Strict Off
Imports System
Imports System.Runtime.InteropServices
Imports Microsoft.Win32

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)> _
Public Class TestClass
Public Sub HelloWorld()
MsgBox("Hello World!")
End Sub

<ComRegisterFunctionAttribute()> _
Public Shared Sub RegisterFunction(ByVal type As Type)
Registry.ClassesRoot.CreateSubKey(GetSubkeyName(type))
End Sub

<ComUnregisterFunctionAttribute()> _
Public Shared Sub UnregisterFunction(ByVal type As Type)
Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(type), False)
End Sub

Private Shared Function GetSubkeyName(ByVal type As Type) As String
Dim S As New System.Text.StringBuilder()
S.Append("CLSID\{")
S.Append(type.GUID.ToString().ToUpper())
S.Append("}\Programmable")
Return S.ToString()
End Function
End Class


I then compile. I go into Excel, VB Editor, create a module, go into
Tools/References, click on Browse and pick the DLL in the Release
folder. At this point it says it cannot add the reference to the
specified file.

Would someone be kind enough to indicate where I missed a step?
Alternatively if you know a good step by step guide (or sample project
code) that I could use, it would be much appreciated!

thanks in advance!
Charles

 

[Michel Posseth [MCP]]

Hello Charles


I have written a few dozen examples of enabling COM in a VB.Net project ,
and also described how to use it i tested all of my examples and they worked
with VBS , VBA , VB6 and even in PHP

http://groups.google.com/group/micr...l+posseth+COM+VB.Net&rnum=28#ca996d74d250974e

some general pitfalls


1. your class must have a public sub new ( i am missing that in your
code )
2. a .Net dll must be registred with regasm.exe and NOT with regsvr32.exe


HTH

Michel

 

[Charles]

Thanks. For the records, I registered the dll using regasm located in
this folder:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727
using the command:
regasm /TLB /Codebase "D:\Projects\Visual Basic\tmp\testdll\testdll\bin
\Release\testdll.dll"
With this, the dll is available to VBA and I can call it from there. I
still get an automation error everytime I try running it (error code
-2146233082 not sure of what it means precisely).

according to this link:
http://support.microsoft.com/kb/817248/EN-US/
I have to create some constants that contain various GUID. (they are
not created automatically in Visual Studio Express). If I go in the
assembly section of the project properties, there is one GUID. But the
sample code shows 3 different GUIDs.
Does the project GUID correspond to one of them? Can I make a GUID
myself or does it have to come from somewhere? How can I know the GUID
of a class/interface/event?

Charles

 

[Charles]

To be precise, this is my new code (I created the 3 GUID myself using
the command Guid.NewGuid.ToString().ToUpper on another project)

Imports System
Imports System.Runtime.InteropServices
Imports Microsoft.Win32
<ComClass(TestClass.ClassId, TestClass.InterfaceId, TestClass.EventsId)

_

Public Class TestClass

#Region "COM GUIDs"
' These GUIDs provide the COM identity for this class
' and its COM interfaces. If you change them, existing
' clients will no longer be able to access the class.
Public Const ClassId As String = "41E0E909-0F20-41C2-8B97-
DD87CA213F84"
Public Const InterfaceId As String = "00C6A39E-
D17D-4450-8B8F-27B8DBDB2C3A"
Public Const EventsId As String =
"0F379866-6A95-482D-81C6-05CB6A774E40"
#End Region

' A creatable COM class must have a Public Sub New()
' without parameters. Otherwise, the class will not be
' registered in the COM registry and cannot be created
' through CreateObject.
Public Sub New()
MyBase.New()

End Sub
Public Function GetHelloWorld() As String
Return "Hello World!"
End Function
End Class


The function GetHellowWorld does appear in VBA but the following code
in VBA:

Public Sub dedeed()
Dim E As New testdll.TestClass
MsgBox E.GetHelloWorld
End Sub

returns the following error:
Run-time error '-2146233082(80131506)'
Automation error

 

[Charles]

Found the solution! I installed the SP3 for Office and it now works.