problem when i compiled the file together,couldn't find the defined functions in excel

Jun 5, 2013 at 9:12 AM
Edited Jun 5, 2013 at 9:13 AM
Hi_ guys,
I have met a problem when using the Excel DNA v0.30 to compile my vb files together. My vb project is a class-library which contains 3 classes. Brent is a class where i define a numerical method to find root for equations. Rewrite is a must inherit class which define methode my functions as type Func(Of Double,Double) . Weir_Bazin_Rect is a class contain functions that i want to solve who inherit class Rewrite and overrides methods in Rewrite and call function in class Brent.This is my idea.
However when i compile the files together i find a problem that i couldn't find the functions in excel.
I couldn't find the problem so i'd like ask for your help.


these are my codes:

Brent.vb:
Namespace Hydraulic
Public Class Brent
    Public Shared Function BrentsMethodSolve(ByVal [function] As Func(Of Double, Double), ByVal lowerLimit As Double, ByVal upperLimit As Double, ByVal errorTol As Double) As Double

   blablabla

  End Function
  End Class
  End Namespace
Rewrite.vb

Imports System
Namespace Hydraulic
Public MustInherit Class Rewrite

    Public Overridable Function rewrite4(ByVal a As Double, ByVal b As Double, ByVal c As Double, ByVal d As Double) As Func(Of Double, Double)
        Dim f As Func(Of Double, Double) = rewrite4(a, b, c, d)
        Return f
    End Function

End Class
End Namespace
Weir.vb
'Imports System
'Imports ExcelDna.Integration
Namespace Hydraulic
Public Class Weir
    Inherits Rewrite
    Dim u As Double

<ExcelFunction(Description:="Returns the water Hight (in m). (author: Degremont)", Category:="Hydraulic")> _
    Function Weir_Rect_Bazin_h( _
            <ExcelArgument(Description:=" Flow rate", Name:=" Flot rate (q) m3/s")> ByVal q, _
            <ExcelArgument(Description:=" Weir width in m", Name:=" width (w) m")> ByVal w, _
            <ExcelArgument(Description:=" Weir upstream height in m", Name:=" upstream height (a) m")> ByVal a, _
            <ExcelArgument(Description:=" Downstream submergence in m", Name:=" submergence (s) m")> ByVal s, _
            <ExcelArgument(Description:=" lower bound for the intervall where we want to find the result in m", Name:=" Lower bound (l) m")> ByVal l, _
            <ExcelArgument(Description:=" upper bound for the intervall where we want to find the result in m", Name:=" Upper bound (m) m")> ByVal m, _
            <ExcelArgument(Description:=" tolerence in m", Name:=" tolerence (e) m")> ByVal e) As Double
        Dim h As Double
        If q < 0 Or w <= 0 Or a <= 0 Then
            Return "Error on input"
        End If


        If s < 0 Then
            s = 0
        End If

        h = s


        If q > 0 Then

            Dim f As Func(Of Double, Double) = rewrite4(q, a, w, s)
            h = Brent.BrentsMethodSolve(f, l, m, e)

        End If
        Return h
    End Function



    Public Overrides Function rewrite4(ByVal q As Double, ByVal a As Double, ByVal w As Double, ByVal s As Double) As Func(Of Double, Double)
        Return Function(h As Double) As Double
                   Dim g As Double = 9.81
                   u = (0.405 + 0.003 / h) * (1 + 0.55 * (h / (h + a)) ^ 2)
                   Return ((2 * g) ^ 0.5 * u * w * h ^ (3 / 2)) * (1 - (s / h) ^ 1.5) ^ 0.385 - q
               End Function
    End Function

End Class
End Namespace
Coordinator
Jun 5, 2013 at 9:42 PM
Hi,

Excel-DNA is not registering your functions because they are instance methods in a class. For Excel-DNA to register a function it must be either:
  • in a Public Module, or
  • a Public Shared method in a Public Class.
In your case, it's not clear that you need to derive from the Rewrite class. Simplest would be to put your code in a Module (not that the u variable is now declared in each function).

You can try this code for Weir.vb:
Namespace Hydraulic
    Public Module Weir_Rect_Bazin

        ' THIS MIGHT BE A PROBLEM
        ' I've put the declarations for  
        ' Dim u As Double

        <ExcelFunction(Description:="Returns the flow rate (in m3/s). (author: Degremont)", Category:="Hydraulic")> _
        Function Weir_Rect_Bazin_Q( _
                                                <ExcelArgument(Description:=" Water height in m", Name:=" water height (h) m")> ByVal h, _
                                                <ExcelArgument(Description:=" Weir width in m", Name:=" width (w) m")> ByVal w, _
                                             <ExcelArgument(Description:=" Weir upstream height in m", Name:=" upstream height (a) m")> ByVal a, _
                                     <ExcelArgument(Description:=" Downstream submergence in m", Name:=" submergence (s) m")> ByVal s)

            'for information see reference file

            Dim u As Double
            Dim g As Double
            Dim q As Double


            If a < 0 Or w < 0 Or a <= 0 Then
                Return "Error on input"
            End If

            If s < 0 Then
                s = 0
            End If

            If h <= 0 Or w = 0 Or h < s Then
                Weir_Rect_Bazin_Q = 0
                ' THIS MIGHT BE AN ERROR 
                ' - you might mean to return from here, else the assignment makes no sense....
                ' - like this:   Return 0
            End If

            g = 9.81

            u = (0.405 + 0.003 / h) * (1 + 0.55 * (h / (h + a)) ^ 2)
            q = ((2 * g) ^ 0.5 * u * w * h ^ (3 / 2)) * (1 - (s / h) ^ 1.5) ^ 0.385

            Weir_Rect_Bazin_Q = q

        End Function
        <ExcelFunction(Description:="Returns the water Hight (in m). (author: Degremont)", Category:="Hydraulic")> _
        Function Weir_Rect_Bazin_h( _
                <ExcelArgument(Description:=" Flow rate", Name:=" Flot rate (q) m3/s")> ByVal q, _
                <ExcelArgument(Description:=" Weir width in m", Name:=" width (w) m")> ByVal w, _
                <ExcelArgument(Description:=" Weir upstream height in m", Name:=" upstream height (a) m")> ByVal a, _
                <ExcelArgument(Description:=" Downstream submergence in m", Name:=" submergence (s) m")> ByVal s, _
                <ExcelArgument(Description:=" lower bound for the intervall where we want to find the result in m", Name:=" Lower bound (l) m")> ByVal l, _
                <ExcelArgument(Description:=" upper bound for the intervall where we want to find the result in m", Name:=" Upper bound (m) m")> ByVal m, _
                <ExcelArgument(Description:=" tolerence in m", Name:=" tolerence (e) m")> ByVal e) As Double
            Dim h As Double
            If q < 0 Or w <= 0 Or a <= 0 Then
                Return "Error on input"
            End If


            If s < 0 Then
                s = 0
            End If

            h = s


            If q > 0 Then

                Dim f As Func(Of Double, Double) = Rewrite4(q, a, w, s)
                h = Brent.BrentsMethodSolve(f, l, m, e)

            End If
            Return h
        End Function

        Function Rewrite4(ByVal q As Double, ByVal a As Double, ByVal w As Double, ByVal s As Double) As Func(Of Double, Double)
            Return Function(h As Double) As Double
                       Dim u As Double
                       Dim g As Double = 9.81
                       u = (0.405 + 0.003 / h) * (1 + 0.55 * (h / (h + a)) ^ 2)
                       Return ((2 * g) ^ 0.5 * u * w * h ^ (3 / 2)) * (1 - (s / h) ^ 1.5) ^ 0.385 - q
                   End Function
        End Function

    End Module
End Namespace
Regards,
Govert