none
How I can Use excel funtion such as .Linest on Visual Basic. RRS feed

  • Question

  • Hello, I'm working on web application  with visual basic 2012 ansql server as database. I need perform statistical calculations

    such as intercept and slope. Excel has these functions and I want to use them.

    I have done a routine, but it generates error, someone could share some idea or experience.

    Thank in advance

    error:

    Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))              Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.            

    Exception Details: System.Runtime.InteropServices.COMException: Type mismatch. (Exception from HRESULT: 0x80020005 (DISP_E_TYPEMISMATCH))

    Source Error:
    Line 134:
    Line 135:        With excelApp.WorksheetFunction
    Line 136:            resultados = .LinEst(varY, varX)
    Line 137:
    Line 138

    Code

     Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim excelApp = New Excel.Application

            Dim db As New DBLRRPDataContext

            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final

            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final

            With excelApp.WorksheetFunction
                resultados = .LinEst(varY, varX)

            End With

    Thursday, December 1, 2016 9:38 PM

Answers

  • .ToArray() is only one dimensional. For the Linest function, These are all supposed to be 2-dimensional, base 1 arrays. Input & Output, of type double. However, because of limits in the vb.net, you must declare the 0 base, but don't load your data in it because it is ignored in the worksheet function.

    Dim arr2x(,) as double
    Dim arr2y(,) as double
    Dim resultados(,) as Double     ' resultados(1,1) = slope; resultados(1,2) = intercept. There's more information depending on the options you specify
    '------- edit -----
    ' You might also be encountering the  the 'Array lower bounds can only be 0' constraint
    'so, you could also try:
    'Dim resultados(0 to 3,0 to 6) as Double  ' to force the array to be of sufficient size, and maybe handle the error/constraint.

    ' failing that, you would have to go the latebound route:
    ' Dim resultsados as Object
    '----- end edit ------

    I've always had to manually cycle through each array to convert from a 1 dim to a 2 dimensional array, something like this:

    Dim lngPosition as long
    Redim  arr2x(0 to varX.getupperbound(), 0 to 1)

    For lngPosition = 0 to (varX.getupperbound())
        arr2x(lngPosition + 1, 1) = varX(lngposition)
    next lngPosition
    '... do the same for varY --> arr2y. Upperbound for varX and varY must match

    resultados = .LinEst(arr2y, arr2x)

    ----------------------------------
    Hope this helps.



    • Edited by MainSleuth Saturday, December 3, 2016 2:46 AM Even another idea.
    • Marked as answer by Fredy68 Tuesday, December 6, 2016 6:20 PM
    Saturday, December 3, 2016 2:10 AM

All replies

  • Hi Fredy68,

    Welcome to the MSDN forum.

    Refer to your description, your issue is about the development of Excel. Since our forum is to discuss Visual Studio WPF/SL Designer, Visual Studio Guidance Automation Toolkit, Developer Documentation and Help System, and Visual Studio Editor, I will help you move it to the appropriate forum: https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=exceldev for dedicated information, you will get a more professional support from there, thank you for your understanding.

    Best regards,

    Sara


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, December 2, 2016 5:10 AM
  • Try adding First:

        Dim varX  = (From … Select …).First()
        Dim varY  = (From … Select …).First()

    Friday, December 2, 2016 7:07 AM
  • Hey Oscar,

    Your Type Mismatch error is because varX and varY are instances of IList. They need to be Arrays.

    So create the arrays sizing it with the count of items in your list, then copy the list to the array.

    Here is the code:

    Public Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load
            Dim excelApp = New Excel.Application
    
            Dim db As New DBLRRPDataContext
    
            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final
    
            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final
    
            Dim arrX as new int[varX.count]
            Dim arrY as new int[varY.count]
    
            varX.CopyTo(arrX,0)
            varY.CopyTo(arrY,0)
    
            With excelApp.WorksheetFunction
                resultados = .LinEst(arrY, arrX)
    
            End With

    There may be some semantic errors in that code. Please correct them for me. Its been a thousand years I don't code VB.

    I also wanted to mention another possibility for you to get the statistical work done. Using the Excel library is a bit hackish (sorry!) so maybe you could consider using an cleaner approach.

    There quite a few functions in the System.Math class. Have you cheeked them out?

    System.Math Class

    There is also an open source that is quite respected, Math.NET NUmerics.

    Moving away from the Excel library would probably make your code cleaner and more maintainable, so the developers looking at your code later would be able to understand your code easier.


    Thanks,


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com



    Friday, December 2, 2016 2:07 PM
  • Have you declared the data type of resultados?

    Since Linest() is a worksheet array function, Linest returns a 2-dimensional array, (row, column). Hence, resultados must also be a 2-dimensional array.

    edit: Both are dimensioned base 1, not 0.


    • Edited by MainSleuth Friday, December 2, 2016 3:04 PM More info
    Friday, December 2, 2016 2:36 PM
  • Mauricio Mauricio, Thank You for your response. I am trying to apply your suggestion, but when I try to use  copy the value from varX to arrx show this message : copy to  is not member of system.Linq.Iqueryable (of string).

            Dim resultados As Decimal
            Dim excelApp = New Excel.Application
            Dim db As New DBLRRPDataContext

            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final

            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final

            Dim arrx = New Integer() {varX.Count}
            Dim arry = New Integer() {varY.Count}

            varX.CopyTo(arrx, 0)
            varY.CopyTo(arry, 0)

            With excelApp.WorksheetFunction
                resultados = .LinEst(arry, arrx)
            End With

    Friday, December 2, 2016 5:36 PM
  • Change the lines:

      varX.CopyTo(arrx, 0)
      varY.CopyTo(arry, 0)

    to

      varX.ToList().CopyTo(arrx, 0)
      varY.ToList().CopyTo(arry, 0)

    What happens now?


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Friday, December 2, 2016 5:56 PM
  • I did the change, but new message appear  '1 - dimensional array of integer' cannot be converted to '1 dimensional array of decimal' because integer is not derived from decimal

             varX.ToList().CopyTo(arrx, 0
             varY.ToList().CopyTo(arry, 0)

    Friday, December 2, 2016 6:05 PM
  • Need help with that one? Looks like the array we created have to be decimal, not integer. Please give it  shot and let me know. 

    Thanks!


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com


    Friday, December 2, 2016 6:34 PM
  • array

    Friday, December 2, 2016 6:43 PM
  • try one of these two approaches see if it works better for you. I wish I had a VB environment to play with, but if you don't mind doing it this way, I am ok with it.

    Here are the two approaches:

            Dim resultados As Decimal
            Dim excelApp = New Excel.Application
            Dim db As New DBLRRPDataContext
    
            Dim varX = (From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final).ToArray()
    
            Dim varY = (From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final).ToArray()
    
    
            With excelApp.WorksheetFunction
                resultados = .LinEst(varY, varX)
            End With

    or this:

            Dim resultados As Decimal
            Dim excelApp = New Excel.Application
            Dim db As New DBLRRPDataContext
    
            Dim varX = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.porosity_final > 0
                       Select datainputsamples.porosity_final
    
            Dim varY = From datainputsamples In db.DataInputSamples Where
                       datainputsamples.FileId = 160035 And datainputsamples.PreKmax_Final > 0
                       Select datainputsamples.PreKmax_Final
    
            Dim arrx = varX.ToArray();
            Dim arry = varY.ToArray();
    
            With excelApp.WorksheetFunction
                resultados = .LinEst(arry, arrx)
            End With


    My Technet Articles

    If you like this or another reply, vote it up!
    If you think this or another reply answers the original question, mark it or propose it as an answer.


    Mauricio Feijo
    www.mauriciofeijo.com

    Friday, December 2, 2016 8:28 PM
  • .ToArray() is only one dimensional. For the Linest function, These are all supposed to be 2-dimensional, base 1 arrays. Input & Output, of type double. However, because of limits in the vb.net, you must declare the 0 base, but don't load your data in it because it is ignored in the worksheet function.

    Dim arr2x(,) as double
    Dim arr2y(,) as double
    Dim resultados(,) as Double     ' resultados(1,1) = slope; resultados(1,2) = intercept. There's more information depending on the options you specify
    '------- edit -----
    ' You might also be encountering the  the 'Array lower bounds can only be 0' constraint
    'so, you could also try:
    'Dim resultados(0 to 3,0 to 6) as Double  ' to force the array to be of sufficient size, and maybe handle the error/constraint.

    ' failing that, you would have to go the latebound route:
    ' Dim resultsados as Object
    '----- end edit ------

    I've always had to manually cycle through each array to convert from a 1 dim to a 2 dimensional array, something like this:

    Dim lngPosition as long
    Redim  arr2x(0 to varX.getupperbound(), 0 to 1)

    For lngPosition = 0 to (varX.getupperbound())
        arr2x(lngPosition + 1, 1) = varX(lngposition)
    next lngPosition
    '... do the same for varY --> arr2y. Upperbound for varX and varY must match

    resultados = .LinEst(arr2y, arr2x)

    ----------------------------------
    Hope this helps.



    • Edited by MainSleuth Saturday, December 3, 2016 2:46 AM Even another idea.
    • Marked as answer by Fredy68 Tuesday, December 6, 2016 6:20 PM
    Saturday, December 3, 2016 2:10 AM