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

  • Thread starter Etienne-Louis Nicolet
  • 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
 
C

Chip Pearson

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)
 
B

Bernie Deitrick

Try using ByRef instead of ByVal...

Public Function DivideBy2(ByRef pDouble As Double) As Double

HTH,
Bernie
MS Excel MVP
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top