My Blog

Calling Excel Worksheet Formulas from VB.Net

In continuation from here

What are Worksheet Formulas?

A formula is an equation that analyzes data on a worksheet. They perform operations such as addition, multiplication, division, concatenating values etc. The formulas can refer to other cells on the same worksheet, cells on other sheets in the same workbook, or cells on sheets in other workbooks.

Why do we need to use Worksheet Formulas from VB.Net?

There is no need per se but the worksheet formulas can make your life easier if you are trying to automate Excel and performing various actions. Let’s take an example. Let’s say we have an Excel file Sample.xlsx at C:\. (See image below).

I have taken a vary basic example. In the above sheet we can see the sales figures for over 13 years. If we wanted to find say

1. The total number of years that we have in our data or
2. The maximum sales ever achieved or
3. The minimum sales ever achieved

We would be writing code in VB.Net to store the entire data in an array and then looping through the array to do our comparisons to deduce the above data. However there is another way to find what we are looking for without writing huge lines of code. We let Excel do the dirty work!

So to find the total count of years we will have this formula in an excel sheet.


and to find the maximum number of sales ever achieved, we will have this


Similarly for the minimum sales


Now let’s try the same in

Create a form and put a button on the form. Set the reference to Microsoft Excel Library. If you do not know how to do that then see this.

Your form should look like this

Use this code

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim xlApp As New Excel.Application
        Dim xlWb As Excel.Workbook
        Dim xlsheet As Excel.Worksheet
        Dim lCol As Long = 0

        With xlApp
            .Visible = True

            '~~> Open workbook
            xlWb = .Workbooks.Open("c:\Sample.xlsx")

            '~~> Set it to the relevant sheet
            xlsheet = xlWb.Sheets("Sheet1")

            With xlsheet
                '~~> This will give you the count of years

                '~~> This will give you the maximum sales ever achieved

                '~~> This will give you the minimum sales ever achieved
            End With

            '~~> Close workbook and quit Excel

            '~~> Clean Up

        End With
    End Sub

    Private Sub releaseObject(ByVal obj As Object)
            obj = Nothing
        Catch ex As Exception
            obj = Nothing
        End Try
    End Sub

If you noticed that we used only three lines of code to find what we wanted.


So isn’t it simple letting the Excel do our dirty work? ;)

BTW if you do not want to use the .WorksheetFunction, you can also use .Evaluate. Simply replace those three lines with these


When you are using .Evaluate, simply insert the actual formula between the double quotes.