Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Go Back  Xtreme Visual Basic Talk > > > Use Excel WorksheetFunction in VB.net


Reply
 
Thread Tools Display Modes
  #1  
Old 05-03-2010, 07:20 PM
OmacronTheta OmacronTheta is offline
Newcomer
 
Join Date: May 2010
Posts: 2
Default Use Excel WorksheetFunction in VB.net


Hi,

I'm trying to use common Worksheet functions found in Excel 2003 (average, standard deviation, etc) from within VS 2008. See thinned down sample code below:

Code:
Imports Microsoft.Office.Interop

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        Dim ExcelMath As Excel.WorksheetFunction
        ExcelMath = New Excel.WorksheetFunction

        Dim I As Double
        Dim s As Double

        I = ExcelMath.Average(1, 2, 3, 4, 5)
        s = ExcelMath.StDev(1, 2, 3, 4, 5)

    End Sub

End Class
When ran it returns "New cannot be used on an interface". Removing "New" returns "'WorksheetFunction' is a type in 'Excel' and cannot be used as an expression."

I've added a reference to the Microsoft.Office.Interop.Excel library v11.0.0.0, which I'm using in other places in the code with no complaints.

Many thanks,
OmacronTheta
Reply With Quote
  #2  
Old 05-04-2010, 03:34 AM
Colin Legg's Avatar
Colin Legg Colin Legg is offline
Out Of Office

Retired Moderator
* Expert *
 
Join Date: Mar 2005
Location: London, UK
Posts: 3,402
Default

Welcome to the forum. The New keyword isn't required here:
Code:
    Dim ExcelMath As Excel.WorksheetFunction
    Dim I As Double
    Dim s As Double
    
    ExcelMath = Excel.WorksheetFunction
    
    I = ExcelMath.Average(1, 2, 3, 4, 5)
    s = ExcelMath.StDev(1, 2, 3, 4, 5)
*** untested
Reply With Quote
  #3  
Old 05-04-2010, 03:48 AM
OmacronTheta OmacronTheta is offline
Newcomer
 
Join Date: May 2010
Posts: 2
Default

Hi Colin,

Assigning variable ExcelMath value Excel.WorksheetFunction throws error "'WorksheetFunction' is a type in 'Excel' and cannot be used as an expression."
Reply With Quote
  #4  
Old 05-04-2010, 07:35 AM
Mike Rosenblum's Avatar
Mike Rosenblum Mike Rosenblum is offline
Microsoft Excel MVP

Forum Leader
* Guru *
 
Join Date: Jul 2003
Location: New York, NY, USA
Posts: 7,848
Default

Hi OmacronTheta,

The issue here is that the Excel global values that are available to VBA are not exposed by .NET. Basically, .NET does not know how to handle them, so they simply do not show up in the object model that .NET sees.

What you need to do is create a new 'Excel.Application' instance, and then make use of that instance to expose the 'Excel.WorksheetFunction' class. Using the approach that you are taking, your code could look something like this:

Code:
Imports System.Runtime.InteropServices
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form1
    Dim excelApp As Excel.Application
    Dim excelMath As Excel.WorksheetFunction

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        excelApp = New Excel.Application()
        excelMath = excelApp.WorksheetFunction

        Dim i As Double = excelMath.Average(1, 2, 3, 4, 5)
        Dim s As Double = excelMath.StDev(1, 2, 3, 4, 5)
    End Sub

    Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
        ' Clean up Excel and Memory:
        GC.Collect()
        GC.WaitForPendingFinalizers()

        Marshal.FinalReleaseComObject(excelMath)
        excelApp.Quit()
        Marshal.FinalReleaseComObject(excelApp)
    End Sub
End Class
Give it a try!

You also might want to have a read of the Automating Office Programs with VB.NET tutorial to help learn some of the basics when automating Excel when using .NET.

- Mike
__________________
My Articles:
| Excel from .NET | Excel RibbonX using VBA | Excel from VB6 | CVErr in .NET | MVP |
Avatar by Lebb
Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump

Advertisement:





Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
subscribe
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
subscribe
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..
subscribe
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
 
Use Excel WorksheetFunction in VB.net
Use Excel WorksheetFunction in VB.net
 
-->