Angus Walker Angus Walker - 1 month ago 31
Vb.net Question

How do i 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.

Answer

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.