C# class library for COM interop

[Guest]

Hello,

I want to create a C# class library which I can use in an Excel VBA macro. I
created a C# class library and set the "Register for COM Interop" option in
Visual Studio to TRUE. Now in Excel I can use this library but the
intellisense does not show any properties or functions of the class. Do I
have to add any keywords or GUID to each public function and property in C#?
Do I have to load the library to the global assembly cache?
Does anyone know a link to a tutorial in which a C# class library is created
to be used in VBA?

Thanks

 

[Willy Denoyette [MVP]]

Make sure your methods are decorated with the ComVisibleAttribute

[ComVisible(true)]
public .... SomeMethod(...)

Willy.

 

[Guest]

[ComVisible(true)] doesn't has any effect. The only thing which helped is

[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyClass :IMyInterface

but when using AutoDual my functions which uses "ref" parameters such as
public string GetUnitStrings(ref int nr)
create an error in VBA

Josef

Make sure your methods are decorated with the ComVisibleAttribute

[ComVisible(true)]
public .... SomeMethod(...)

Willy.

Hello,

I want to create a C# class library which I can use in an Excel VBA macro.
I
created a C# class library and set the "Register for COM Interop" option
in
Visual Studio to TRUE. Now in Excel I can use this library but the
intellisense does not show any properties or functions of the class. Do I
have to add any keywords or GUID to each public function and property in
C#?
Do I have to load the library to the global assembly cache?
Does anyone know a link to a tutorial in which a C# class library is
created
to be used in VBA?

Thanks

 

[Willy Denoyette [MVP]]

Well, [ComVisible(true)] is the default, but I was just guessing you could
have set it to false.

The [ClassInterface(ClassInterfaceType.AutoDispatch)] is default, so no
typelib info is emitted when the typelib is generated so there is no
intellisense support, and the interface can only be used from late-bound
clients.
(ref int nr) should work, please post some more code (C# and VBA) and the
error message in VBA.

Willy.

[ComVisible(true)] doesn't has any effect. The only thing which helped is

[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyClass :IMyInterface

but when using AutoDual my functions which uses "ref" parameters such as
public string GetUnitStrings(ref int nr)
create an error in VBA

Josef

Make sure your methods are decorated with the ComVisibleAttribute

[ComVisible(true)]
public .... SomeMethod(...)

Willy.

Hello,

I want to create a C# class library which I can use in an Excel VBA
macro.
I
created a C# class library and set the "Register for COM Interop"
option
in
Visual Studio to TRUE. Now in Excel I can use this library but the
intellisense does not show any properties or functions of the class. Do
I
have to add any keywords or GUID to each public function and property
in
C#?
Do I have to load the library to the global assembly cache?
Does anyone know a link to a tutorial in which a C# class library is
created
to be used in VBA?

Thanks

 

[Guest]

the c# code look like this:
----------------------
namespace KUnits
{
interface IKUnits
{
string GetLabel(ref int nrOfStrings);
}
[ClassInterface(ClassInterfaceType.AutoDual)] // ???
public class Units :IKUnits
{
public Units()
{
}
public string GetLabel(ref int nrOfStrings)
{
string[] arr = new string[a];
...
nrOfStrings = arr.Length;
return arr;
}
....
}
}
----------------------

VBA Code:
----------------------
Set Units As KUnits.Units
Dim str as String
Dim sizeOf as Integer
str = Units.GetLabel(sizeOf)
....
----------------------

VBA error message at "str = Units.GetLabel(sizeOf)":
"Argument type ByRef incompatible" (I hope this is the right translation to
english)

Thanks
Josef

Well, [ComVisible(true)] is the default, but I was just guessing you could
have set it to false.

The [ClassInterface(ClassInterfaceType.AutoDispatch)] is default, so no
typelib info is emitted when the typelib is generated so there is no
intellisense support, and the interface can only be used from late-bound
clients.
(ref int nr) should work, please post some more code (C# and VBA) and the
error message in VBA.

Willy.

[ComVisible(true)] doesn't has any effect. The only thing which helped is

[ClassInterface(ClassInterfaceType.AutoDual)]
public class MyClass :IMyInterface

but when using AutoDual my functions which uses "ref" parameters such as
public string GetUnitStrings(ref int nr)
create an error in VBA

Josef

Make sure your methods are decorated with the ComVisibleAttribute

[ComVisible(true)]
public .... SomeMethod(...)

Willy.

Hello,

I want to create a C# class library which I can use in an Excel VBA
macro.
I
created a C# class library and set the "Register for COM Interop"
option
in
Visual Studio to TRUE. Now in Excel I can use this library but the
intellisense does not show any properties or functions of the class. Do
I
have to add any keywords or GUID to each public function and property
in
C#?
Do I have to load the library to the global assembly cache?
Does anyone know a link to a tutorial in which a C# class library is
created
to be used in VBA?

Thanks

 

[Willy Denoyette [MVP]]

the c# code look like this:
----------------------

public string GetLabel(ref int nrOfStrings)
{
string[] arr = new string[a];
...
nrOfStrings = arr.Length;
return arr;
}
...

This method returns a string array while the method signature specifies a
string as return type, this can't compile.
Please submit the correct code.

Willy.

 

[Willy Denoyette [MVP]]

the c# code look like this:

public string GetLabel(ref int nrOfStrings)

VBA Code:
----------------------
Set Units As KUnits.Units
Dim str as String
Dim sizeOf as Integer
str = Units.GetLabel(sizeOf)
...
----------------------

VBA error message at "str = Units.GetLabel(sizeOf)":
"Argument type ByRef incompatible" (I hope this is the right translation
to
english)

Dim sizeOf as Long

int in .NET are 32bit entities, Integer in VBA are 16bit, Long is 32 bit.


Willy.

 

[Guest]

Sorry,

string[] GetUnitStrings(string unitGroup, bool useUnicode, ref int
nrOfStrings)
should be the right methode.

Now I resolved the problem. When I use
"Dim sizeOf as Long" in VBA everything works fine

But:
1. Is it the right way using
"[ClassInterface(ClassInterfaceType.AutoDual)]"? I read something that
someone should avoid using "ClassInterfaceType.AutoDual".
2. Do I have to define interfaces as I have done?
3. What do I have to do when I want to use the class library on another
computer (registration, gac, ...)?

Thanks
Josef