# 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.

```=COUNT(A2:A14)
```

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

```=MAX(B2:B14)
```

Similarly for the minimum sales

```=MIN(B2:B14)
```

Now let’s try the same in VB.net

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
MessageBox.Show(xlApp.WorksheetFunction.Count(.Range("A2:A14")))

'~~> This will give you the maximum sales ever achieved
MessageBox.Show(xlApp.WorksheetFunction.Max(.Range("B2:B14")))

'~~> This will give you the minimum sales ever achieved
MessageBox.Show(xlApp.WorksheetFunction.Min(.Range("B2:B14")))
End With

'~~> Close workbook and quit Excel
xlWb.Close(False)
xlApp.Quit()

'~~> Clean Up
releaseObject(xlsheet)
releaseObject(xlWb)
releaseObject(xlApp)

End With
End Sub

Private Sub releaseObject(ByVal obj As Object)
Try
System.Runtime.InteropServices.Marshal.ReleaseComObject(obj)
obj = Nothing
Catch ex As Exception
obj = Nothing
Finally
GC.Collect()
End Try
End Sub
```

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

```    MessageBox.Show(xlApp.WorksheetFunction.Count(.Range("A2:A14")))
MessageBox.Show(xlApp.WorksheetFunction.Max(.Range("B2:B14")))
MessageBox.Show(xlApp.WorksheetFunction.Min(.Range("B2:B14")))
```

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

```    MessageBox.Show(xlApp.Evaluate("=COUNT(Sheet1!A2:A14)"))
MessageBox.Show(xlApp.Evaluate("=MAX(Sheet1!B2:B14)"))
MessageBox.Show(xlApp.Evaluate("=MIN(Sheet1!B2:B14)"))
```

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