Try using ByRef instead of ByVal...

Public Function DivideBy2(ByRef pDouble As Double) As Double

HTH,
Bernie
MS Excel MVP


"Etienne-Louis Nicolet" wrote in message
...
I'm playing with a sample found on http://www.cpearson.com/Excel/Creati...nctionLib.aspx . My
idea is to create a class library in VB.NET 2008 that I can use as automation add-in in Excel.

In the following code snippet the function 'DivideBy2' has a parameter of type Double.
- When passing a number as parameter e.g. "=DivideBy2(50)", I get a correct result.
- If I pass a cell reference as paramter e.g. "=DivideBy2($A$1)", I get a "#Value" error ("A value
used in the formula is of the wrong type"

Could anybody give me a hint how to solve this problem?

Many thanks for your suggestions,
Etienne

Here's the code:

<ClassInterface(ClassInterfaceType.AutoDual), ComVisible(True)Public Class SampleFunctions

Public Function DivideBy2(ByVal pDouble As Double) As Double

Return pDouble / 2

End Function

<ComRegisterFunctionAttribute()Public Shared Sub RegisterFunction(ByVal pType As Type)

Registry.ClassesRoot.CreateSubKey(GetSubkeyName(pT ype))

End Sub

<ComUnregisterFunctionAttribute()Public Shared Sub UnregisterFunction(ByVal pType As Type)

Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(pT ype), False)

End Sub

Private Shared Function GetSubkeyName(ByVal pType As Type) As String

Dim S As New System.Text.StringBuilder()

S.Append("CLSID\{")

S.Append(pType.GUID.ToString().ToUpper())

S.Append("}\Programmable")

Return S.ToString()

End Function

End Class