Edit: The real solution to what I wanted to do can be found on this post here. I just wanted to expose some compiled functions to excel. This proved to be very easy using the Excel DNA nuget package. You just add a class library, add the nuget package, and copy paste the code found in the readme. Click F5 and it launches excel with the add-in already loaded. If you want your functions to be persisted you just need to manually add the add-in to the excel file through the "developer" ribbon section.
Original Post: I was following the instructions from this microsoft post on how to create an automation add-in. Code compiles fine and I can access the functions from within Excel. However the functions do not work. I almost always get a #value or a #ref error when I try to assign to a cell the result of a function call. To be more specific:
The following function that is provided by Microsoft does not work. It shows me a #value error in the cell where I try to use it. I select using the mouse a random cell range as a parameter for the function.
Public Function NumberOfCells(ByVal range As Object) As Double
Dim r As Excel.Range = TryCast(range, Excel.Range)
Return CDbl(r.get_Cells.get_Count)
End Function
The following function that I created does not work. I get a #ref error. I called it by passing either directly integers ( Add1(1,2) ) or cells that contain numbers.
Public Function Add1(ByVal i1 As Integer, ByVal i2 As Integer) As Integer
return i1+i2
End Function
The following function that I created works(?!?):
Public Function Add1(ByVal i1 As Integer, ByVal i2 As Integer) As Integer
return 222
End Function
I am quite experienced in c# but not at all in vb.net, however for this add-in I need to use vb.net. I suspect that there is something simple that I am missing here but I have no idea what it is. It is also strange that the code provided by Microsoft doesn't work.
Edit: I also copy pasted the function presented here and I get the same #Value error inside excel. I did not follow the tutorial from this post from the beginning but I will during the day.
Edit 2: I figured out that the code from Microsoft doesn't work for some reason whenever you add a number in the function name. If I renamed Add1 on the sample code above to Addqweqew it would work!
MSDN Ref: http://blogs.msdn.com/b/andreww/archive/2008/01/23/managed-automation-add-ins.aspx
It has to do with a locale ID (LCID) issue. This is a known issue when developing Excel solutions in a mixed culture environment. For more information, see here: http://support.microsoft.com/kb/246501/.
VSTO solves this problem via its LCID Proxy. Although you can only use this with VSTO solutions, its worth reading the documentation so you can understand the problem: http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.excellocale1033proxy.aspx and http://msdn.microsoft.com/en-us/library/microsoft.office.tools.excel.excellocale1033attribute.aspx.
I got the same problem #Value results, I mucked around a bit and got this working (obviously it could be cleared up - but this code definitely works for me while keeping my PC set to my Australian locale ID. I'm not sure which part of the world you live but I am guessing not the United States as that's the locale where it works by default)
Public Function AddNumbers1(ByVal num1 As Double, _
ByVal num2 As Double) As Double
Dim oldCI As CultureInfo = Thread.CurrentThread.CurrentCulture
Dim english As System.Globalization.CultureInfo = System.Globalization.CultureInfo.GetCultureInfo("en-US")
System.Threading.Thread.CurrentThread.CurrentCulture = english
System.Threading.Thread.CurrentThread.CurrentUICulture = english
Dim valresult As Double = num1 + num2
Thread.CurrentThread.CurrentCulture = oldCI
Return valresult
End Function