Chip, Bernie,
Many thanks for the input. I tried both using double paentheses and using
ByRef. The result remains. the same.
Just for fun I created a new function with the parameter defined as object
to see what the type of parameter the function receives:
Public Function MyTest(ByVal MyNumber As Object) as Double
Dim n As Double = 0
Try
n = DirectCast(MyNumber, Double)
Catch ex As Exception
MsgBox("Type=" & MyNumber.GetType.ToString()
End Try
Return n / 2
End Function
It turns out that MyNumber is of type "System.__ComObject"...
I'm really puzzled...
What is the best way to debug in this case? As I understand I have to
compile the solution, install it and then load it in Excel in order to
use/test it, so I see no way to get into debug mode...
Thanks again & kind regards,
Etienne
"Chip Pearson" wrote in message
...
I can't reproduce the problem. I created a NET Class Library exactly
as described on the web page and the Divide functions work with both
static values =DivideBy2(100) or with cell references
=DivideBy2($A$1). Have you tried debugging the code in VS? Just out
of curiosity, try calling the functions as
=DivideBy2(($A$1))
The inner parentheses should cause Excel to evaluate A1 before passing
it to the Divide2 function.
Beyond that, I don't know why it would return an error.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Wed, 11 Mar 2009 13:08:29 +0100, "Etienne-Louis Nicolet"
wrote:
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