VB.NET: How to declare a parameter in a worksheet-function

  • Thread starter Thread starter Etienne-Louis Nicolet
  • Start date Start date
E

Etienne-Louis Nicolet

I'm playing with a sample found on
http://www.cpearson.com/Excel/CreatingNETFunctionLib.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(pType))

End Sub

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

Registry.ClassesRoot.DeleteSubKey(GetSubkeyName(pType), 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)
 
Try using ByRef instead of ByVal...

Public Function DivideBy2(ByRef pDouble As Double) As Double

HTH,
Bernie
MS Excel MVP
 
Back
Top