|
Hi all, newbie here, need some serious help! Followed all the instruction created the NAddIn, built, and saw it on the Tool->Add-In, but not shown on the Insert -> Functions. Nothing!!! I used some suggested code to resolve the mscoree.dll problem already.
Using Excel 2003 and MicroSoft Visual c# 2005 Express Edition.
Any help is really appreciated.
|
|
|
|
|
Hi,
i just want to make a DLL that refer to an object which contains a lot of functions...
the code will be like this :
dim l_objTest as MyClass
public property Test as Myclass
get
return l_objTest
end get
set
l_objTest = Value
end set
When i compile this part of the code with the rest of you NaddIn, i have this :
I can use the Add2 function but i can't see the functions that are implemented in my object...
Is there anyway to do what i want to do ?
Regards
Shurka
|
|
|
|
|
Hi Shurka,
I'm not sure from your question what functions you expect to be exported to Excel. Properties are not likely to appear in Excel as user-defined functions, and there is nothing that would automatically make the functions in your Myclass appear.
I suggest you also have a look at my ExcelDna project(http://exceldna.typepad.com). However, I currently support only exporting of static functions (apart from the limited experimental support for Excel Services using the UdfClass and UdfMethod attributes). So for now you might need a wrapper class containing static functions that correspond to your class methods.
Regards,
Govert van Drimmelen
|
|
|
|
|
Hi,
thank you for answering me so quickly.
My problem is that i want to show in Excel functions that i have made in an another class that the one in the DLL. Let me explain this :
I have a class in a solution called MyClass1:
Myclass1
{
function1()
function2()
}
and in my dll class i have
MyDLLclass
{
private l_test as new MyClass1
public property test() as MyClass1
get
return l_test
end get
set(byval Value as MyClass1)
l_test = value
end set
end property
public function Add1(byval v1 as double, byval v2 as double) as double
return v1+v2
end function
}
When i compile this (i haven't copied the Com functions) it shows me in Excel the function Add1 and the Test... but not the functions function1 and function2... is there a way for me to get them ? Or is it impossible to made?
I hope i am clear enough
|
|
|
|
|
hi,maybe you can add Public void before them
www.artistsoft.com
|
|
|
|
|
Would you be kind enough to provide instructions on embedding the type library generated by regasm (I noticed you removed those instructions in July)?
Currently, as an automation add-in or a pure assembly, you can generate the type lib using the regasm /tlb. You cannot access the automation UDFs in cells directly without that tlb file in the same directory as the DLL file itself. This makes it annoying to have two files for redistribution (compared with just one in say ATL COM, where the tlb is embedded).
Is there anyway to do this with automation/.NET?
|
|
|
|
|
I developed a function add-in for excel that takes data from internet. When I try to refresh (in external data toolbar) the data in excel worksheet, no function is called. So, I have to refresh all the instances of that function selecting each cell and refreshing them by hand, that takes a lot of time.
Is there any way to refresh the whole excel worksheet when using .Net add-ins?
|
|
|
|
|
I can see this is an old question but the solution may be relevant for others.
There are in fact two solutions:
1) instead of pressing F9 / Shift+F9 you can force every cell to recalculate by pressing Ctrl+Alt+F9 or Shift+Ctrl+Alt+F9
2) append the following to your worksheet function: "+NOW()*0"
Hope that helps
|
|
|
|
|
Hello,
Has anyone tried this in C++/CLI. I created a class library and added the following code:
using namespace System;
using namespace System::Runtime::InteropServices;
namespace ExcelFunction2 {
[ClassInterface(ClassInterfaceType::AutoDual)]
public ref class Functions
{
public:
Functions(){}
double AddC(double v1, double v2)
{
return v1 + v2;
}
[ComRegisterFunctionAttribute]
static void RegisterFunction(Type t)
{
Microsoft::Win32::RegistryKey^ root = Microsoft::Win32::Registry::ClassesRoot;
root->CreateSubKey( "CLSID\\{" + t.GUID.ToString()->ToUpper() + "}\\Programmable");
}
[ComUnregisterFunctionAttribute]
static void UnregisterFunction(Type t)
{
Microsoft::Win32::RegistryKey^ root = Microsoft::Win32::Registry::ClassesRoot;
root->DeleteSubKey( "CLSID\\{" + t.GUID.ToString()->ToUpper() + "}\\Programmable");
}
};
}
but it doesn't work. I got a little bit further by adding the following to the assembly info:
[assembly:ComVisible(true)];
This makes ExcelFunction2.Functions appear in the list of automation objects in excel, but the function AddC is invisible.
Yours
David.
|
|
|
|
|
David,
I was wondering you you managed to workout the logic for this. If so would you please suggest how I might get around the problem. I am using Visual Studio 2005 and Excel 2003. After compiling I am able to add the DLL as an Automation Add-in. However, the Insert Function dialog does not list the function.
I would really appreciate some help.
Arnab
|
|
|
|
|
Hi Guys,
First of all, thanks for the great article!
The plugin works perfectly on the local machine, but when I relocate it to others it breaks down. I do register it with regasm, after that I do see it in the excel addin list, but when I am trying to reference it from spreadsheet - the functions are not in the list anymore.
Also, from VBA - I can see the namespace and classes, but when I try to reference them I get 'Automation Error'...
Your assistance is greately appreciated.
-igor
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
OK I found the answer myself. Microsoft have a patch solution posted back in december that works for Office XP, but has not been included in the office xp update.
http://support.microsoft.com/kb/908002/en-us
I think the latest updates to office 2003 include it already so it is only needed for office XP only users using .net framework 2.0 add-ins.
Well it works for me anyway...what a pity clickonce deploy packages pre-requisite as a setup.exe and .msi which i don;t allow to download form my site...which was the whole point of choosing the .deploy/manifest solution check box in the click once options anyway..GO FIGURE.
Jeltz
|
|
|
|
|
I've just stumbled upon this solution myself and was about to post. Getting the latest office SP2 works just as well...
Have you tried achieving the same with vs03? I have the suspicion it will work on earlier patches.
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
I don't know about you but it took me about 18 hours to find any reference to KB908002 anywhere; trouble was no error message and #NAME in excel is sooo generic. complicated by the fact my development machine is dual install office 10,11 and SP2 Office 11 automagically updates office 10 as well (go figure, thats why office XP clients will still need a manual patch!)
Even though supplied as VS2005 extra, in fact it is an office patch, so no reason you can;t distribute it from VS2003 (may have to do manually though, since the KB908002 download looks for VS2005 I think.
Much better just to advise Office 2003 users to update to SP2 from Microsoft office update, but alas NO alternative to redistrbute prerequisite yet for Office XP users as SP3 + latest extras show no sign of it.
Jeltz
|
|
|
|
|
Hi,
I had the same problem when developing the ExcelDna library that enables Excel add-ins and user-defined functions to be created using managed code.
The final version of .Net 2.0 added a registry entry the forces .Net 1.1 to be loaded into the Excel process, even when the first managed code is a .Net 2.0 assembly. The registry entry seems to suggest this only works for some versions of Excel, but I had problems in all versions I tried. I think they tried to add some backward compatibility for their VSTO or SiteServer stuff at the last minute, but it was a very, very bad idea. And it turns out to be a breaking change that is still (to my knowledge) completely undocumented.
The patch referred to above, or SP2, fixes it, or you can remove the registry entry, or you can create an excel.exe.config file that forces the .Net 2.0 runtime to be loaded. All these options are described in the ExcelDna 'Getting Started' at http://exceldna.typepad.com, together with a pointer to the newsgroup thread which told me all I know about the issue.
I have found deployment of the Automation Add-Ins to be pretty tricky, especially compared to ExcelDna Add-Ins (these work using the .xll glue).
Anyway, I hope this provides a bit more info.
Regards,
Govert
|
|
|
|
|
When i built the project and run excel i can't see the class there
|
|
|
|
|
indeed i have the same problem. it might be a new issue with .net 2.0.
i'm using visual studio 2005 express with .net 2.0.50727 on winxp.
since i'm a newbee i would appreciate any help solving the problem.
PS: while compiling i got the following messages in my output window:
warning MSB3391: ......NAddIn.dll" does not contain any types that can be unregistered for COM Interop.
warning MSB3214: ......NAddIn.dll" does not contain any types that can be registered for COM Interop.
|
|
|
|
|
i have found the problem. it's a simple option in preferences menu of the project.
on solution explorer sidebar right click the project and select properties, then choose 'Application', click the button "Assembly Information..." and check the box where it says "Make assembly COM-Visible".
recompile it and it works just fine.
|
|
|
|
|
Hello
The plugin works perfectly on the local machine, but when I relocate it to others it breaks down. I do register it with regasm, after that I do see it in the excel addin list, but when I am trying to reference it from spreadsheet - the functions are not in the list anymore.
Also, from VBA - I can see the namespace and classes, but when I try to reference them I get 'Automation Error'...
Do you know what I might be missing?
Your assistance is greately appreciated.
Thanks
-igor
---------------------------
Igor R. Manassypov, M.Eng.,
imanassypov@ci.com
Network Architect
CI Investments
Toronto, Canada
---------------------------
|
|
|
|
|
Ohhhh yes I have the same problem .. a pain in the....
No solution yet but my experience is (all win xp pro):
1) I am having this prob even with com visible (never works without it OF COURSE!)
2) It works fine and registers on any remote machine with VS2005 installed.
3) It works fine and registers on a non-development machine (No VS2005) but with office 2003 installed
4) It works fine in Excel XP on another development machine with dual install Office XP and office 2003 (also works Excel 2003 same machine)
5) BUT on a machine with only Office XP, Excel XP will register the addin fine in Tools->Add-ins->Automation, but the functions just do not show up in the function list (so #NAME? shows on formula entry).
All latest versions and latest updates to office XP, VS2005 and .Net Framework 2.0 runtime.
Am currently investigating if it is Office 2003 or VS2005 installed that allowed Excel XP to work on (4) above.
Here I was , excel XP and Excel 2003 project all tested, debugged and ready to go...and no go when it gets to the end user. What a crock.
Jeltz
|
|
|
|
|
It worked perfectly by activating "Make assembly COM-Visible".
Thanks a lot.
CSmood
|
|
|
|
|
OK I found the answer myself. Microsoft have a patch solution posted back in december that works for Office XP, but has not been included in the office xp update.
http://support.microsoft.com/kb/908002/en-us
I think the latest updates to office 2003 include it already so it is only needed for office XP only users using .net framework 2.0 add-ins.
Well it works for me anyway...what a pity clickonce deploy packages pre-requisite as a setup.exe and .msi which i don;t allow to download form my site...which was the whole point of choosing the .deploy/manifest solution check box in the click once options anyway..GO FIGURE.
Jeltz
|
|
|
|
|
I have been working on ExcelDna, an open-source project to integrate .NET into Excel. This gives another approach to creating user-defined functions and macros in .NET, using an integration library (.xll). User code can be in .dlls or text-based script files.
|
|
|
|
|
I would like to read a vector or matrix from excel..
Something like:
//public double GetSum(double[,] v1)
public double GetSum(double[] v1)
{
double val = v[1]+ v[2];
return val;
}
But It doesn't work. I tried also using Range Object. But not able to access the [r,c] the value in the range..
public double NumberOfCells(object Range)
{
Excel.Range r = Range as Excel.Range;
//double a = r[1,1].Value ??
//return a;
return r.Cells.Count;
}
Have you got any ideas?
Regards Germoz
|
|
|
|
|
Hi Germoz -- Eric Carter's write-up, linked in the 'Extended Article' message below, gives an example using a Range object. If you are able to use the .NET 2.0 Beta 2, and have some patience to test new stuff, please contact me at govert@icon.co.za - I might have a better answer ...
|
|
|
|