Many thanks, Chip, again I learned a lot!

I set a breakpoint at the function. The debugger starts if is use a number
as parameter "=DivideBy2(50)", but it does not start when passing a cell
reference "=DivideBy2($A$1)"... And, yes, I tried several times in order to
ensure that I made no typos ;-)

When using a parameter of type Object, the debugger starts, but the
parameter is of type {System.__ComObject}.

I'm really sorry to bother you again with my problem and I'd like to take
the opportunity to thank you for the time you sacrifice!

Kind regards,
Etienne


"Chip Pearson" wrote in message
...
On Wed, 11 Mar 2009 14:44:59 +0100, "Etienne-Louis Nicolet"
wrote:

To debug the code, close Excel and then open your solution in Visual
Studio. Double-click the "My Project" item to open the project
options and open the Debug tab. There, select "Start external program"
and enter the full path to Excel, something like "C:\Program
Files\Office2003\Office11\excel.exe". Of course, your path will be
different. Put breakpoints at appropriate locations. You might also
want to wrap up the function code in a Try/Catch/Finally block.

Then press F5 to start the program. This will open Excel. In Excel, go
to Tools then Add-Ins and uncheck the NET automation add-in. Click
"yes" if you get a message like "cannot find coree.dll". Then reload
the add-in from the Automation add-ins dialog. Enter a function from
the add-in, e.g., =DivideBy2(A1). That will bring up the debugger in
Visual Studio. There, you can check types and exceptions to see what
the problem is.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)






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