Questions regarding a VB.NET Shared Add-In for Excel

[Luis Sim?es]

Hi,

I think this questions maybe very usefull for me and lot of other
users trying to make an office add-in.
This are just a few questions about funcionality and problems
encountered during the creating of the addin.

1. How can i create an addin to use commandbars and UDF's(User
defined Function) all in the same DLL? I managed that by using the
AutoDual option but i don't know if it is the best choice or if exists
another one...
I also noticed that AutoDual exposes some functions to the excel that
i don't want to...

2. How can i create an UDF function to accept an array of parameters
from Excel like ( =function(1;2;3;...) )

3. How can i specify descriptions and help for each UDF?


This are just a few questions. Please try to support this thread.

Best Regards,
Luis Simões

 

[Fredrik Wahlgren]

Hi,

I think this questions maybe very usefull for me and lot of other
users trying to make an office add-in.
This are just a few questions about funcionality and problems
encountered during the creating of the addin.

1. How can i create an addin to use commandbars and UDF's(User
defined Function) all in the same DLL? I managed that by using the
AutoDual option but i don't know if it is the best choice or if exists
another one...
I also noticed that AutoDual exposes some functions to the excel that
i don't want to...

2. How can i create an UDF function to accept an array of parameters
from Excel like ( =function(1;2;3;...) )

3. How can i specify descriptions and help for each UDF?


This are just a few questions. Please try to support this thread.

Best Regards,
Luis Simões

1) You have to create an add-in that is both a COM add-in and an automation
add-in. Consequently, it will have two interfaces. You can create the add-in
in sucha away that it re´gisters the functions whenever you add the COM
add-in to Excel.
http://support.microsoft.com/default.aspx?kbid=291392

2) You can create a function that takes a Range as a parameter.

3) Not sure. I think it's possible to specify help items in an idl file.
This requires C++. I guess this could be done with a shim dll.

Have a look at the thread "Excel Add-in - Function Wizard shows strange
functions".

Best Regards,
Fredrik

 

[Luis Sim?es]

1) You have to create an add-in that is both a COM add-in and an automation
add-in. Consequently, it will have two interfaces. You can create the add-in
in sucha away that it re´gisters the functions whenever you add the COM
add-in to Excel.
http://support.microsoft.com/default.aspx?kbid=291392

2) You can create a function that takes a Range as a parameter.

3) Not sure. I think it's possible to specify help items in an idl file.
This requires C++. I guess this could be done with a shim dll.

Have a look at the thread "Excel Add-in - Function Wizard shows strange
functions".

Best Regards,
Fredrik

1 - Well i think the AutoDual makes it for the Addin / Automation.

2 - I want to receive a reference to a range or to specific values
like strings or numeric data. ;/ My functions are all working right
except the ones that have varying arguments. I really really really
need this functionality... IN VBA the ParamArray works fine. If i call
the function from inside the VB.NET it works fine but from excel the
formula don't get called... ;/ I think it is because the excel uses by
reference and in vb.net i can only use byval paramarray ;/ how can i
go trought this problem?

3 - I don't know how to use c++ to create the IDL can you get some
examples?


Best Regards,
Luis Simões

 

[Fredrik Wahlgren]

"Fredrik Wahlgren" <[email protected]> wrote in



1 - Well i think the AutoDual makes it for the Addin / Automation.

2 - I want to receive a reference to a range or to specific values
like strings or numeric data. ;/ My functions are all working right
except the ones that have varying arguments. I really really really
need this functionality... IN VBA the ParamArray works fine. If i call
the function from inside the VB.NET it works fine but from excel the
formula don't get called... ;/ I think it is because the excel uses by
reference and in vb.net i can only use byval paramarray ;/ how can i
go trought this problem?

3 - I don't know how to use c++ to create the IDL can you get some
examples?


Best Regards,
Luis Simões

Hmm.. I will need to have a look at ParamArray function. I have found few
references that may be useful.
http://support.microsoft.com/kb/158222/EN-US/
http://support.microsoft.com/kb/151582/EN-US/
http://support.microsoft.com/kb/158355/EN-US/
http://www.dicks-blog.com/archives/2004/05/04/

As for the idl, I can show you how to add a function and the generated idl
file. The problem is, the project can't be attributed. To make things
easier, I may create something from scratch. Do you have a chm file that can
be used? Adding help is something that I have wanted to try but I haven't
tried it out. I will be busy tomorrow but I'll come back later.

Best Regards,
Fredrik

 

[Luis Sim?es]

1) You have to create an add-in that is both a COM add-in and an automation
add-in. Consequently, it will have two interfaces. You can create the add-in
in sucha away that it re´gisters the functions whenever you add the COM
add-in to Excel.
http://support.microsoft.com/default.aspx?kbid=291392

2) You can create a function that takes a Range as a parameter.

3) Not sure. I think it's possible to specify help items in an idl file.
This requires C++. I guess this could be done with a shim dll.

Have a look at the thread "Excel Add-in - Function Wizard shows strange
functions".

Best Regards,
Fredrik

Sorry i could not answer after your latest post but it didn't allowed
me to respond directly to your latest post.

As for the ParamArray issue i have tried to use the paramarray with
vb6 and it worked just fine, so i think it must be because of the
Vartype or the lack of ByRef

this is my function in .net:
Public Function teste(ByVal ParamArray parametros() As Object) As
Object
...
End Function

Well the Visual Studio just replaces Variant by Object and adds the
ByVal always because it doens't have ByRef with paramarray.

If i use this function inside the addin class it works just fine but
excel seems not to recognize it.

I hope someone can help me here. I'm stuck for along time now and i
really need the ParamArray type of usage for dinamic parameters usage.

Best Regards,
Luis Simões

 

[Fredrik Wahlgren]

Hi Luis

Unfortunately, I have been busy. I wanted to create asimple UDF using ATL
so that you could have a project with an idl file to see if you could
provide help. I should be able to create something simple with some
screenshots so you see the process.

I'm not sure I understand this:

"> this is my function in .net:

Public Function teste(ByVal ParamArray parametros() As Object) As
Object
...
End Function

Well the Visual Studio just replaces Variant by Object and adds the
ByVal always because it doens't have ByRef with paramarray.

"
It seems as if you have converted a VB6 project as there are no variants in
vb.net. I haven't had time to check ParamArray. Is it something similar to a
safearray?

Your name is Portuguese, right? I have relatives there.

Best Fegards,
Fredrik

 

[Luis Sim?es]

Hi Fredrik,

I didn't use Vb6. I created the function in .NET and for what i have
read all over i must use the ParamArray to receive an Array of
parameters right?

So the function works great like it is if i call it from inside the
add-in class, but the from excel the function don't get called.

In vb6 i created i tested a replica of that function but with ByRef
instead of ByVal and Variant types instead of Object ones, and guess
what? It worked in Excel!

So i think it must be somekind of bug in the ParamArray or it must be
exposed to excel some other way...The odd thing is that the functions
with know number of parameters works just fine what really makes think
that its a bug...

What do you mean by SafeArray? I have read about it but i didn't
understood what they are and what they are used to...

PS: Yes i am Portuguese, working in Lisbon! Where are you from and
where do you have relatives?

Best Regards,
Luis Simões

 

[Luis Sim?es]

Sorry i could not answer after your latest post but it didn't allowed
me to respond directly to your latest post.

As for the ParamArray issue i have tried to use the paramarray with
vb6 and it worked just fine, so i think it must be because of the
Vartype or the lack of ByRef

this is my function in .net:
Public Function teste(ByVal ParamArray parametros() As Object) As
Object
...
End Function

Well the Visual Studio just replaces Variant by Object and adds the
ByVal always because it doens't have ByRef with paramarray.

If i use this function inside the addin class it works just fine but
excel seems not to recognize it.

I hope someone can help me here. I'm stuck for along time now and i
really need the ParamArray type of usage for dinamic parameters usage.

Best Regards,
Luis Simões

 

[Luis Sim?es]

No one used paramarray within vb.net??

Damn this tech is really not up to work with either...

If anyone has any experience creating an excel add-in with toolbars,
formulas etc... i would really apreciate some knowhow sharing

I have read almost all articles about this type of thing on the web
but they all speak about the same... not too good!


Regards,
Luis Simões