By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,140 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Create DLL in Visual Studio 2005 accessible by Excel 2002

P: n/a
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains
custom functions. I believe I need to use COM interop to allow VBA code in
Excel 2002 to access it. I've studied everything I can find on COM Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in Access.

I don't even care about the commandbar buttons for my purposes; I just want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

Aug 4 '06 #1
Share this Question
Share on Google+
10 Replies


P: n/a
You have done more research into this than I have, but let's start with
the basics.

Are you able to add a reference to your COM object using the "Add
Reference" dialog in the Excel/Visual Basic editor's Tools menu?

adm

Steve wrote:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains
custom functions. I believe I need to use COM interop to allow VBA code in
Excel 2002 to access it. I've studied everything I can find on COM Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in Access.

I don't even care about the commandbar buttons for my purposes; I just want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...
Aug 4 '06 #2

P: n/a


Ok. Super simplistically, here's a complete C# COM-visible class library

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;

[assembly: ComVisible(true)]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

namespace ExcelFunctions
{

[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public double Add(double a, double b)
{
return a + b;
}
}
}

Create a DLL project in VS2005, paste this code, sign it,build it, GAC it
and then register it for COM interop with regasm.exe.
Then from an Excel macro set a reference to the library (this gives you
intellisense) and use it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim f As ExcelFunctions.Functions
Set f = CreateObject("ExcelFunctions.Functions")

MsgBox f.Add(1, 2)

End Sub
This same VBScript can be used to test the COM library from a .vbs file too.

David

CreateObject("MyComponent.MyClass)
"Steve" <St***@discussions.microsoft.comwrote in message
news:CE**********************************@microsof t.com...
>I am trying to create a DLL in Visual Studio 2005-Visual Basic that
contains
custom functions. I believe I need to use COM interop to allow VBA code
in
Excel 2002 to access it. I've studied everything I can find on COM
Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even
these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using
Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in
Access.

I don't even care about the commandbar buttons for my purposes; I just
want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

Aug 4 '06 #3

P: n/a
Yes, I can add a reference to it. If I do that then define a new object like:

Dim o As MyCOMAddin.Connect

The VBA Editor recognizes the object and 'intellisenses' the class. None of
the Public Subs are visible, though.

"ad*****@yahoo.com" wrote:
You have done more research into this than I have, but let's start with
the basics.

Are you able to add a reference to your COM object using the "Add
Reference" dialog in the Excel/Visual Basic editor's Tools menu?

adm

Steve wrote:
I am trying to create a DLL in Visual Studio 2005-Visual Basic that contains
custom functions. I believe I need to use COM interop to allow VBA code in
Excel 2002 to access it. I've studied everything I can find on COM Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in Access.

I don't even care about the commandbar buttons for my purposes; I just want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

Aug 4 '06 #4

P: n/a
Thank you for your reply, David.

After following the steps from your post, upon changing the selection in
Excel I get the following error message box (which is the error I've seen in
my other attempts):

Run-time error '-2147014894 (80070002)'

File or assembly name ExcelFunctions, or one of its dependencies, was not
found.

If I click the debug button, the

Set f = CreateObject("ExcelFunctions.Functions")

line is highlighted.

To verify that I followed your post correctly, here are the steps I took:

1) Started Visual Studio 2005 and started a New Visual C# Class Library
Project. I assigned it the name ExcelFunctions.

2) in the Class1.cs file I replaced all the auto-generated code with a copy
& paste of the code in your post.

3) I removed the '[assembly' portions from the Class1.cs file and updated
the entries in the AssemblyInfo.cs file with those from your post, except I
retained the Guid generated by Visual Studio.

4) On the 'Signing' tab of the the Project Properties page, I checked the
"Sign the assembly" box and named the key file "ExcelFunctions.snk"

5) I built the project.

6) From the project directory where the .dll was located, I ran
"gacutil -i ExcelFunctions.dll"

7) From the project directory where the .dll was located, I ran
"regasm ExcelFunctions.dll /tlb"

8) I started Excel 2002 with a new blank workbook.

9) In the VBA Editor, I added a reference to "ExcelFunctions". I then copy
and pasted your code into Book1 - Sheet1 (Code)

10) On selecting a new cell in Excel, the error appears.

After this, I uninstalled the .dll from the GAC, re-ran 'regasm' because I
thought it was supposed to be added to the registry first, then re-ran
'gacutil' with the same result in Excel.

Please comment on any corrections you have or any insight you can provide.

Thank you,

Steve
"David Browne" wrote:
>

Ok. Super simplistically, here's a complete C# COM-visible class library

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;

[assembly: ComVisible(true)]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

namespace ExcelFunctions
{

[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public double Add(double a, double b)
{
return a + b;
}
}
}

Create a DLL project in VS2005, paste this code, sign it,build it, GAC it
and then register it for COM interop with regasm.exe.
Then from an Excel macro set a reference to the library (this gives you
intellisense) and use it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim f As ExcelFunctions.Functions
Set f = CreateObject("ExcelFunctions.Functions")

MsgBox f.Add(1, 2)

End Sub
This same VBScript can be used to test the COM library from a .vbs file too.

David

CreateObject("MyComponent.MyClass)
"Steve" <St***@discussions.microsoft.comwrote in message
news:CE**********************************@microsof t.com...
I am trying to create a DLL in Visual Studio 2005-Visual Basic that
contains
custom functions. I believe I need to use COM interop to allow VBA code
in
Excel 2002 to access it. I've studied everything I can find on COM
Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even
these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using
Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in
Access.

I don't even care about the commandbar buttons for my purposes; I just
want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...


Aug 4 '06 #5

P: n/a
If you save these two lines

Set f = CreateObject("ExcelFunctions.Functions")
MsgBox f.Add(1, 2)

into a file called test.vbs and run it, does it work?

You see the reference in the Excel Macro box, so you're really close.

Does the reg key
HKEY_CLASSES_ROOT\ExcelFunctions.Functions
exit?

David

"Steve" <St***@discussions.microsoft.comwrote in message
news:33**********************************@microsof t.com...
Thank you for your reply, David.

After following the steps from your post, upon changing the selection in
Excel I get the following error message box (which is the error I've seen
in
my other attempts):

Run-time error '-2147014894 (80070002)'

File or assembly name ExcelFunctions, or one of its dependencies, was not
found.

If I click the debug button, the

Set f = CreateObject("ExcelFunctions.Functions")

line is highlighted.

To verify that I followed your post correctly, here are the steps I took:

1) Started Visual Studio 2005 and started a New Visual C# Class Library
Project. I assigned it the name ExcelFunctions.

2) in the Class1.cs file I replaced all the auto-generated code with a
copy
& paste of the code in your post.

3) I removed the '[assembly' portions from the Class1.cs file and updated
the entries in the AssemblyInfo.cs file with those from your post, except
I
retained the Guid generated by Visual Studio.

4) On the 'Signing' tab of the the Project Properties page, I checked the
"Sign the assembly" box and named the key file "ExcelFunctions.snk"

5) I built the project.

6) From the project directory where the .dll was located, I ran
"gacutil -i ExcelFunctions.dll"

7) From the project directory where the .dll was located, I ran
"regasm ExcelFunctions.dll /tlb"

8) I started Excel 2002 with a new blank workbook.

9) In the VBA Editor, I added a reference to "ExcelFunctions". I then
copy
and pasted your code into Book1 - Sheet1 (Code)

10) On selecting a new cell in Excel, the error appears.

After this, I uninstalled the .dll from the GAC, re-ran 'regasm' because I
thought it was supposed to be added to the registry first, then re-ran
'gacutil' with the same result in Excel.

Please comment on any corrections you have or any insight you can provide.

Thank you,

Steve
"David Browne" wrote:
>>

Ok. Super simplistically, here's a complete C# COM-visible class library

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;

[assembly: ComVisible(true)]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

namespace ExcelFunctions
{

[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public double Add(double a, double b)
{
return a + b;
}
}
}

Create a DLL project in VS2005, paste this code, sign it,build it, GAC it
and then register it for COM interop with regasm.exe.
Then from an Excel macro set a reference to the library (this gives you
intellisense) and use it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim f As ExcelFunctions.Functions
Set f = CreateObject("ExcelFunctions.Functions")

MsgBox f.Add(1, 2)

End Sub
This same VBScript can be used to test the COM library from a .vbs file
too.

David

CreateObject("MyComponent.MyClass)
"Steve" <St***@discussions.microsoft.comwrote in message
news:CE**********************************@microso ft.com...
>I am trying to create a DLL in Visual Studio 2005-Visual Basic that
contains
custom functions. I believe I need to use COM interop to allow VBA
code
in
Excel 2002 to access it. I've studied everything I can find on COM
Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even
these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using
Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint
2002,
however so I believe the Add-in is being correctly registered in the
GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or
Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in
Access.

I don't even care about the commandbar buttons for my purposes; I just
want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...



Aug 4 '06 #6

P: n/a
David,

The "test.vbs" script works fine. Also, the registry key

HKEY_CLASSES_ROOT\ExcelFunctions.Functions

does exist.

Are there other references I need to add in the Excel VBA Editor?

Again, thank you for your help.

Steve

"David Browne" wrote:
If you save these two lines

Set f = CreateObject("ExcelFunctions.Functions")
MsgBox f.Add(1, 2)

into a file called test.vbs and run it, does it work?

You see the reference in the Excel Macro box, so you're really close.

Does the reg key
HKEY_CLASSES_ROOT\ExcelFunctions.Functions
exit?

David

"Steve" <St***@discussions.microsoft.comwrote in message
news:33**********************************@microsof t.com...
Thank you for your reply, David.

After following the steps from your post, upon changing the selection in
Excel I get the following error message box (which is the error I've seen
in
my other attempts):

Run-time error '-2147014894 (80070002)'

File or assembly name ExcelFunctions, or one of its dependencies, was not
found.

If I click the debug button, the

Set f = CreateObject("ExcelFunctions.Functions")

line is highlighted.

To verify that I followed your post correctly, here are the steps I took:

1) Started Visual Studio 2005 and started a New Visual C# Class Library
Project. I assigned it the name ExcelFunctions.

2) in the Class1.cs file I replaced all the auto-generated code with a
copy
& paste of the code in your post.

3) I removed the '[assembly' portions from the Class1.cs file and updated
the entries in the AssemblyInfo.cs file with those from your post, except
I
retained the Guid generated by Visual Studio.

4) On the 'Signing' tab of the the Project Properties page, I checked the
"Sign the assembly" box and named the key file "ExcelFunctions.snk"

5) I built the project.

6) From the project directory where the .dll was located, I ran
"gacutil -i ExcelFunctions.dll"

7) From the project directory where the .dll was located, I ran
"regasm ExcelFunctions.dll /tlb"

8) I started Excel 2002 with a new blank workbook.

9) In the VBA Editor, I added a reference to "ExcelFunctions". I then
copy
and pasted your code into Book1 - Sheet1 (Code)

10) On selecting a new cell in Excel, the error appears.

After this, I uninstalled the .dll from the GAC, re-ran 'regasm' because I
thought it was supposed to be added to the registry first, then re-ran
'gacutil' with the same result in Excel.

Please comment on any corrections you have or any insight you can provide.

Thank you,

Steve
"David Browne" wrote:
>

Ok. Super simplistically, here's a complete C# COM-visible class library

using System;
using System.Collections.Generic;
using System.Text;
using System.Runtime.InteropServices;
using System.Reflection;

[assembly: ComVisible(true)]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion("1.0.0.0")]
[assembly: AssemblyFileVersion("1.0.0.0")]

namespace ExcelFunctions
{

[ClassInterface(ClassInterfaceType.AutoDual)]
public class Functions
{
public double Add(double a, double b)
{
return a + b;
}
}
}

Create a DLL project in VS2005, paste this code, sign it,build it, GAC it
and then register it for COM interop with regasm.exe.
Then from an Excel macro set a reference to the library (this gives you
intellisense) and use it:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim f As ExcelFunctions.Functions
Set f = CreateObject("ExcelFunctions.Functions")

MsgBox f.Add(1, 2)

End Sub
This same VBScript can be used to test the COM library from a .vbs file
too.

David

CreateObject("MyComponent.MyClass)
"Steve" <St***@discussions.microsoft.comwrote in message
news:CE**********************************@microsof t.com...
I am trying to create a DLL in Visual Studio 2005-Visual Basic that
contains
custom functions. I believe I need to use COM interop to allow VBA
code
in
Excel 2002 to access it. I've studied everything I can find on COM
Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even
these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using
Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint
2002,
however so I believe the Add-in is being correctly registered in the
GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or
Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in
Access.

I don't even care about the commandbar buttons for my purposes; I just
want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...



Aug 5 '06 #7

P: n/a
Steve... this is NOT a straight forward issue at all.

I have a sample VB (2003) app with written step-by-step documentation to do
this. Credit for this does not reside with me, but a sharp software support
guy at MS.

If you wish, e-mail me at

fbachman "at" landenshra dot com,

and I can send you what I have. It is involved enough that I wouldn't even
start by trying to post the communication here. I am happy to share what I
have. Please note, however, it os for VS.Net 2003, but I am SURE that
porting this to 2005 wouldn't involve a lot of pain.

--
Grumpy Aero Guy

"Steve" <St***@discussions.microsoft.comwrote in message
news:CE**********************************@microsof t.com...
>I am trying to create a DLL in Visual Studio 2005-Visual Basic that
contains
custom functions. I believe I need to use COM interop to allow VBA code
in
Excel 2002 to access it. I've studied everything I can find on COM
Interop
and .NET. I've also tried many of the 'Walkthroughs' on the MSDN site
relating to COM add-ins, .NET and Office XP but am unable to get even
these
working in Excel 2002 or Word 2002.

I've installed the Office XP PIA's; I have Office XP SP3 installed; I'm
developing on a Windows XP Pro SP2 system.

Specifically the article, "How To Build an Office COM Add-in by Using
Visual
Basic .NET" located at http://support.microsoft.com/kb/302896/, doesn't
function in Excel 2002. It does work in Access 2002 and PowerPoint 2002,
however so I believe the Add-in is being correctly registered in the GAC.

When I start Excel, I get no 'connect' message and no button but excel
starts without error. I get no 'disconnect' message on closing Excel,
either. The behavior is the same in Word. Also, After the Word or Excel
starts, the
"HKEY_CURRENT_USER\Software\Microsoft\Office\[OfficeApp]\Addins\[ProgID]\LoadBehavior"
registry entry changes from a 0x03 to a 0x02. This doesn't happen in
Access.

I don't even care about the commandbar buttons for my purposes; I just
want
to have access to my custom functions.

Any suggestions?

PS Some of the references I've researched are:

INFO: Develop Microsoft Office solutions with Visual Studio .NET
http://support.microsoft.com/kb/311452/EN-US/

Office XP Primary Interop Assemblies Known Issues
http://msdn.microsoft.com/library/de..._piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005
http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

Aug 5 '06 #8

P: n/a

"Steve" <St***@discussions.microsoft.comwrote in message
news:97**********************************@microsof t.com...
David,

The "test.vbs" script works fine. Also, the registry key

HKEY_CLASSES_ROOT\ExcelFunctions.Functions

does exist.

Are there other references I need to add in the Excel VBA Editor?
No. Youv'e done everything else right.

My current hypothesis is that the 1.1 framework has been loaded by Excel
somehow.

To help diagnose assembly loading problems.

Assembly Binding Log Viewer (Fuslogvw.exe)
http://msdn2.microsoft.com/en-us/library/e74a18c4.aspx

Use Process Explorer to investigate the loaded dll's
http://www.sysinternals.com/Utilitie...sExplorer.html

See what version of mscorwks.dll is loaded by Excel.exe.
David
Aug 5 '06 #9

P: n/a
Well... how does version 1.1.4322 sound?

And, as I mentioned early on, Access and PowerPoint worked with the Add-in
samples. I just tried your DLL from a new form in Access and it works.
Would you believe the .NET Runtime version for Access is 2.0.50727?

What do you think?

"David Browne" wrote:
>
"Steve" <St***@discussions.microsoft.comwrote in message
news:97**********************************@microsof t.com...
David,

The "test.vbs" script works fine. Also, the registry key

HKEY_CLASSES_ROOT\ExcelFunctions.Functions

does exist.

Are there other references I need to add in the Excel VBA Editor?

No. Youv'e done everything else right.

My current hypothesis is that the 1.1 framework has been loaded by Excel
somehow.

To help diagnose assembly loading problems.

Assembly Binding Log Viewer (Fuslogvw.exe)
http://msdn2.microsoft.com/en-us/library/e74a18c4.aspx

Use Process Explorer to investigate the loaded dll's
http://www.sysinternals.com/Utilitie...sExplorer.html

See what version of mscorwks.dll is loaded by Excel.exe.
David
Aug 6 '06 #10

P: n/a
Thank you for your efforts! With a little more searching on the MSDN site
after your post I came across this page:

"Add-ins, smart documents, or smart tags that you create by using Microsoft
Visual Studio 2005 do not run in Office"
http://support.microsoft.com/kb/908002/

After installing the fix, your DLL works and Process Explorer reports
version 2.00.50727.0042.

Again, thank you for you help!

Steve

"David Browne" wrote:
>
"Steve" <St***@discussions.microsoft.comwrote in message
news:97**********************************@microsof t.com...
David,

The "test.vbs" script works fine. Also, the registry key

HKEY_CLASSES_ROOT\ExcelFunctions.Functions

does exist.

Are there other references I need to add in the Excel VBA Editor?

No. Youv'e done everything else right.

My current hypothesis is that the 1.1 framework has been loaded by Excel
somehow.

To help diagnose assembly loading problems.

Assembly Binding Log Viewer (Fuslogvw.exe)
http://msdn2.microsoft.com/en-us/library/e74a18c4.aspx

Use Process Explorer to investigate the loaded dll's
http://www.sysinternals.com/Utilitie...sExplorer.html

See what version of mscorwks.dll is loaded by Excel.exe.
David
Aug 6 '06 #11

This discussion thread is closed

Replies have been disabled for this discussion.