CODEWITHSUNDEEP
.netexcelvb.networksheet-function
Angus Walker
Angus Walker

Reputation: 398

How to use an Excel function in VB.NET?

I am actually trying to use the Gamma_Inv function but to minimize the complexity of having lots of variables I will use the pi function instead as it illustrates my problem more simply.

My code is:

Imports Microsoft.Office.Interop.Excel

Public Class LoadData

    Shared Sub Main(ByVal cmdArgs() As String)
    
        Dim fred As WorksheetFunction
    
        MsgBox(fred.Pi)

    End Sub

End Class

The error I am getting is:

Variable 'fred' is used before it is assigned a value.

This sort of makes sense, but what should I set it to?

It won't let me use:

MsgBox(WorksheetFunction.Pi)

I am guessing it wants me to open a spreadsheet but I only want to use the function and not Excel itself.

Upvotes: 2

Views: 4227

Answers (2)

Angus Walker
Angus Walker

Reputation: 398

You need to declare the application:

Dim value As New Application

then you can use the functions within it:

MsgBox(value.WorksheetFunction.Pi)

Upvotes: 1

Slai
Slai

Reputation: 22896

Yes, unfortunately you need a running Excel Application to use the Worksheet Functions. 

    Dim app = New Microsoft.Office.Interop.Excel.Application
    Dim wf = app.WorksheetFunction

    MsgBox(wf.Pi)
    MsgBox(app.Evaluate("PI()"))

    app.Visible = True ' optional
    app.Quit()         ' don't forget to close it when done !

The Evaluate method is a bit slower but can evaluate almost everything that can be used in the Excel Formula or Address Bar.

Upvotes: 4

Related Questions