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

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

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

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

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

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

Etienne,

The only way I could reproduce the problem is by overloading the
function to a second function with a different signature. I don't
think that works as expected with Excel objects. You're not
overloading the function, are you?

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 17:31:05 +0100, "Etienne-Louis Nicolet"
wrote:

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

Chip,

No, the function is not overloaded.

In the meantime I ran the Office Diagnostics which found (and pretended to
fix) one issue, without specifying what it was... Did it a second time,
everything was ok... Then I tried to run my functions again, unfortunately
without success. Installed it on another PC, same result..I have to assume
that the my project has a problem, not my Laptop or Excel....

Since I have Office 2007 installed I referenced the following libraries, I
think that should be ok:
- Microsoft Excel 12.0 Object Library
- Microsoft Office 12.0 Object Library
- Microsoft.Office.Tools.Common.v9.0
- Microsoft.Office.Tools.Excel.v9.0

I'm really in the dark... :-(

Kind regards,
Etienne

"Chip Pearson" wrote in message
...
Etienne,

The only way I could reproduce the problem is by overloading the
function to a second function with a different signature. I don't
think that works as expected with Excel objects. You're not
overloading the function, are you?

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 17:31:05 +0100, "Etienne-Louis Nicolet"
wrote:

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
m...
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

You can email me the project if you want. I'll take a look at it.

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 21:15:11 +0100, "Etienne-Louis Nicolet"
wrote:

Chip,

No, the function is not overloaded.

In the meantime I ran the Office Diagnostics which found (and pretended to
fix) one issue, without specifying what it was... Did it a second time,
everything was ok... Then I tried to run my functions again, unfortunately
without success. Installed it on another PC, same result..I have to assume
that the my project has a problem, not my Laptop or Excel....

Since I have Office 2007 installed I referenced the following libraries, I
think that should be ok:
- Microsoft Excel 12.0 Object Library
- Microsoft Office 12.0 Object Library
- Microsoft.Office.Tools.Common.v9.0
- Microsoft.Office.Tools.Excel.v9.0

I'm really in the dark... :-(

Kind regards,
Etienne

"Chip Pearson" wrote in message
.. .
Etienne,

The only way I could reproduce the problem is by overloading the
function to a second function with a different signature. I don't
think that works as expected with Excel objects. You're not
overloading the function, are you?

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 17:31:05 +0100, "Etienne-Louis Nicolet"
wrote:

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
om...
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