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