UDF in VB.NET XLL

Add-in Express™ Support Service
That's what is more important than anything else

UDF in VB.NET XLL
 
Kevin Wornell




Posts: 36
Joined: 2006-08-14
I am trying to move a working excel UDF to an Excel XLL file. The UDF is:

Public Function GetColorIndex(MyCell As Range) As Integer
' returns color index of top left cell in a range
GetColorIndex = Cells(MyCell.Row, MyCell.Column).Interior.ColorIndex
If GetColorIndex < 0 Then
GetColorIndex = 0
End If
End Function

After adding it I get the following error about not recognizing the data type even though I have a reference to excel and have added Imports Excel


Application Domain: C:\Code\WW_XL_UDFs\WW_XL_UDFs\bin\Debug\
Assembly Codebase: file:///C:/WINDOWS/assembly/GAC_MSIL/AddinExpress.MSO.2005/4.0.1902.2005__4416dd98f0861965/AddinExpress.MSO.2005.dll
Assembly Full Name: AddinExpress.MSO.2005, Version=4.0.1902.2005, Culture=neutral, PublicKeyToken=4416dd98f0861965
Assembly Version: 4.0.1902.2005
Assembly Build Date: 03/17/2005 2:06:50 AM

Exception Source: AddinExpress.MSO.2005
Exception Type: System.ApplicationException
Exception Message: Unknown Data Type: Excel.Range
Exception Target Site: SetTypeInfo

---- Stack Trace ----
AddinExpress.MSO.ADXExcelParameterInfo.SetTypeInfo(type As Type, isReturnType As Boolean)
AddinExpress.MSO.2005.dll: N 1736 (0x6C8) IL
AddinExpress.MSO.ADXExcelParameterInfo..ctor(paramInfo As ParameterInfo, params_ As ADXExcelParameterDescriptors)
AddinExpress.MSO.2005.dll: N 0146 (0x92) IL
AddinExpress.MSO.<>c__DisplayClass1.<.ctor>b__0(pi As ParameterInfo)
AddinExpress.MSO.2005.dll: N 0000 (0x0) IL
System.Array.ConvertAll(array As TInput[], converter As Converter`2)
AddinExpress.MSO.2005.dll: N 0041 (0x29) IL
AddinExpress.MSO.ADXExcelMethodInfo..ctor(targetMethod As MethodInfo, functions As List`1, modBuilder As ModuleBuilder)
AddinExpress.MSO.2005.dll: N 0220 (0xDC) IL
AddinExpress.MSO.ADXExcelMethodInfo.ConvertToXlMethodInfos(methodInfos As List`1, functions As List`1)
AddinExpress.MSO.2005.dll: N 0056 (0x38) IL

As usual this is only one of several UDFs I need to develop and package for deployment.

Any help greatly appreciated.




hacking my way through life, one syntax error at a time
Posted 14 May, 2008 21:49:17 Top
az az




Posts: 1
Joined: 2008-05-14
how to get calculated cells and their values after recalculate event fired

reply me at XXXXX@XXXXX.com

thanks


PLEASE DON'T POST YOUR EMAIL ADDRESSES HERE.


Andrei Smolin
Add-in Express Team Leader
Posted 14 May, 2008 22:53:31 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
Joined: 2004-07-05
Hi Kevin.

The fact is that the XLL technology doesn't support types of Excel object model. You can only pass types which are supported in Excel cells (like strings, numbers, arrays and so on).
Please uncomment the AllSupportedExcelTypes function in the XLL module of your project and test it. You will see all possible types.
Moreover you can't call the Excel object model from an XLL function. It is possible to do from a macro function only.

Posted 15 May, 2008 09:04:46 Top
Kevin Wornell




Posts: 36
Joined: 2006-08-14
I was able to overcome the limitations of the XLL file by adding a 'COM Excel Add-in Module' to my project. This got me the functions and also lets me deploy them at the same time as my Toolbars, etc. which makes things overall a bit easier.

Is there a way to register the Functions within the 'COM Excel Add-In Module' so that they display in the function list within Excel?

hacking my way through life, one syntax error at a time
Posted 15 May, 2008 12:29:02 Top
Kevin Wornell




Posts: 36
Joined: 2006-08-14
I added a COM Excel Add-In Module and was able to get the functions to work in Excel 2007.

Is there a way to register the Functions within the COM Excel Add-In Module so they appear in the Function list within Excel?
hacking my way through life, one syntax error at a time
Posted 15 May, 2008 13:03:17 Top
Sergey Grischenko


Add-in Express team


Posts: 7233
Joined: 2004-07-05
Hi Kevin.

The point is that XLL functions are registered in the xlAutoOpen export function of the XLL dll. You can only load XLL dll via a COM add-in using the RegisterXLL method of Excel application.
Posted 16 May, 2008 06:55:53 Top