Create DLL in Visual Studio 2005 accessible by Excel 2002

4 Aug 2006 5:30 PM

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_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/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 Interoperability in .NET Framework Applications
http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx

And many others...

4 Aug 2006 5:45 PM

admspam

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:
Show quoteHide quote

> 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/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 Interoperability in .NET Framework Applications
> http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
>
> And many others...

4 Aug 2006 6:39 PM

Steve

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.

Show quoteHide quote

"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_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/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 Interoperability in .NET Framework Applications
> > http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
> >
> > And many others...
>
>

4 Aug 2006 6:20 PM

David Browne

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)
Show quoteHide quote

"Steve" <St***@discussions.microsoft.com> wrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@microsoft.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/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 Interoperability in .NET Framework Applications
> http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
>
> And many others...
>

4 Aug 2006 8:56 PM

Steve

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

Show quoteHide quote

"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.com> wrote in message
> news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@microsoft.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/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 Interoperability in .NET Framework Applications
> > http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
> >
> > And many others...
> >
>
>
>

4 Aug 2006 10:44 PM

David Browne

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

Show quoteHide quote

"Steve" <St***@discussions.microsoft.com> wrote in message
news:33B4A840-8982-4E9C-85CC-F487C50DEE1E@microsoft.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.com> wrote in message
>> news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@microsoft.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/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 Interoperability in .NET Framework Applications
>> > http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
>> >
>> > And many others...
>> >
>>
>>
>>

5 Aug 2006 3:12 PM

Steve

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

Show quoteHide quote

"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.com> wrote in message
> news:33B4A840-8982-4E9C-85CC-F487C50DEE1E@microsoft.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.com> wrote in message
> >> news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@microsoft.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/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 Interoperability in .NET Framework Applications
> >> > http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
> >> >
> >> > And many others...
> >> >
> >>
> >>
> >>
>
>
>

5 Aug 2006 4:59 PM

David Browne

"Steve" <St***@discussions.microsoft.com> wrote in message
news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@microsoft.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/Utilities/ProcessExplorer.html

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

David

6 Aug 2006 5:34 AM

Steve

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?

Show quoteHide quote

"David Browne" wrote:

>
> "Steve" <St***@discussions.microsoft.com> wrote in message
> news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@microsoft.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/Utilities/ProcessExplorer.html
>
> See what version of mscorwks.dll is loaded by Excel.exe.
>
>
> David
>
>
>

6 Aug 2006 6:07 AM

Steve

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

Show quoteHide quote

"David Browne" wrote:

>
> "Steve" <St***@discussions.microsoft.com> wrote in message
> news:97B63ACB-382A-4DF6-BB35-4ABF0482342B@microsoft.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/Utilities/ProcessExplorer.html
>
> See what version of mscorwks.dll is loaded by Excel.exe.
>
>
> David
>
>
>

5 Aug 2006 3:55 PM

Grumpy Aero Guy

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

Show quoteHide quote

"Steve" <St***@discussions.microsoft.com> wrote in message
news:CE1B1A36-537F-4C8D-9FF2-B48345AC7A9E@microsoft.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/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 Interoperability in .NET Framework Applications
> http://msdn2.microsoft.com/en-us/library/e7a79b4y.aspx
>
> And many others...
>