Create DLL in Visual Studio 2005 accessible by Excel 2002

[Steve]

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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

[admspam@yahoo.com]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

>
Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

>
COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

>
And many others...

[David Browne]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002



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

using System;
using System.Collecti ons.Generic;
using System.Text;
using System.Runtime. InteropServices ;
using System.Reflecti on;

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

namespace ExcelFunctions
{

[ClassInterface( ClassInterfaceT ype.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_Selec tionChange(ByVa l Target As Range)

Dim f As ExcelFunctions. Functions
Set f = CreateObject("E xcelFunctions.F unctions")

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("M yComponent.MyCl ass)
"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@mi crosoft.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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

>
Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

>
COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

>
And many others...
>

[Steve]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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

Dim o As MyCOMAddin.Conn ect

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

"admspam@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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

>
>

[Steve]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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("E xcelFunctions.F unctions")

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.Collecti ons.Generic;
using System.Text;
using System.Runtime. InteropServices ;
using System.Reflecti on;
>
[assembly: ComVisible(true )]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion ("1.0.0.0")]
[assembly: AssemblyFileVer sion("1.0.0.0")]
>
namespace ExcelFunctions
{
>
[ClassInterface( ClassInterfaceT ype.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_Selec tionChange(ByVa l Target As Range)
>
Dim f As ExcelFunctions. Functions
Set f = CreateObject("E xcelFunctions.F unctions")
>
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("M yComponent.MyCl ass)
"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@mi crosoft.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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

>
>
>

[David Browne]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

If you save these two lines

Set f = CreateObject("E xcelFunctions.F unctions")
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_RO OT\ExcelFunctio ns.Functions
exit?

David

"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:33B4A840-8982-4E9C-85CC-F487C50DEE1E@mi crosoft.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("E xcelFunctions.F unctions")
>
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.Collecti ons.Generic;
>using System.Text;
>using System.Runtime. InteropServices ;
>using System.Reflecti on;
>>
>[assembly: ComVisible(true )]
>[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
>[assembly: AssemblyVersion ("1.0.0.0")]
>[assembly: AssemblyFileVer sion("1.0.0.0")]
>>
>namespace ExcelFunctions
>{
>>
> [ClassInterface( ClassInterfaceT ype.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_Selec tionChange(ByVa l Target As Range)
>>
> Dim f As ExcelFunctions. Functions
> Set f = CreateObject("E xcelFunctions.F unctions")
>>
> 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.MyC lass)
>"Steve" <Steve@discussi ons.microsoft.c omwrote in message
>news:CE1B1A3 6-537F-4C8D-9FF2-B48345AC7A9E@mi crosoft.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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

>
Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

>
COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

>
And many others...
>

>>
>>
>>

[Steve]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

David,

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

HKEY_CLASSES_RO OT\ExcelFunctio ns.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("E xcelFunctions.F unctions")
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_RO OT\ExcelFunctio ns.Functions
exit?
>
David
>
"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:33B4A840-8982-4E9C-85CC-F487C50DEE1E@mi crosoft.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("E xcelFunctions.F unctions")

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.Collecti ons.Generic;
using System.Text;
using System.Runtime. InteropServices ;
using System.Reflecti on;
>
[assembly: ComVisible(true )]
[assembly: Guid("37496b5c-462a-4547-b57e-d9063256e443")]
[assembly: AssemblyVersion ("1.0.0.0")]
[assembly: AssemblyFileVer sion("1.0.0.0")]
>
namespace ExcelFunctions
{
>
[ClassInterface( ClassInterfaceT ype.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_Selec tionChange(ByVa l Target As Range)
>
Dim f As ExcelFunctions. Functions
Set f = CreateObject("E xcelFunctions.F unctions")
>
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("M yComponent.MyCl ass)
"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@mi crosoft.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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

>
>
>

>
>
>

[Grumpy Aero Guy]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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" <Steve@discussi ons.microsoft.c omwrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@mi crosoft.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_U SER\Software\Mi crosoft\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/default.asp?url=/library/en-us/dnoxpta/html/odc_piaissues.asp

>
Walkthrough: Creating COM Objects with Visual Basic 2005

http://msdn2.microsoft.com/en-us/library/x66s8zcd.aspx

>
COM Interoperabilit y in .NET Framework Applications

http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

>
And many others...
>

[David Browne]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002


"Steve" <Steve@discussi ons.microsoft.c omwrote in message
news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@mi crosoft.com...

David,
>
The "test.vbs" script works fine. Also, the registry key
>
HKEY_CLASSES_RO OT\ExcelFunctio ns.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/Utilities/ProcessExplorer.html

See what version of mscorwks.dll is loaded by Excel.exe.


David

[Steve]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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" <Steve@discussi ons.microsoft.c omwrote in message
news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@mi crosoft.com...

David,

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

HKEY_CLASSES_RO OT\ExcelFunctio ns.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/Utilities/ProcessExplorer.html

>
See what version of mscorwks.dll is loaded by Excel.exe.
>
>
David
>
>
>

[Steve]

Re: Create DLL in Visual Studio 2005 accessible by Excel 2002

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" <Steve@discussi ons.microsoft.c omwrote in message
news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@mi crosoft.com...

David,

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

HKEY_CLASSES_RO OT\ExcelFunctio ns.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/Utilities/ProcessExplorer.html

>
See what version of mscorwks.dll is loaded by Excel.exe.
>
>
David
>
>
>