VSTO & .NET & Excel

March 29, 2012

VLOOKUP Week; Using VLOOKUP in VB

Filed under: .NET & Excel, COM Add-ins, Excel, VSTO & Excel, XLLs — Dennis M Wallentin @ 3:49 pm

Bill Jelen, MrExcel, has marked this week to honor the VLOOKUP function in MS Excel. Around the online Excel development community You will find contributions, usually blog posts, that gives various aspects for using or not VLOOKUP.

I thought it would be interesting to create a case and see how VLOOKUP can be part of  VB-solutions. Actually, for several years ago I did similar solutions but with classic VB (VB6) for a customer. Imagine that we have a price list for our products. Because of the frequent price updates the company has decided to store it in a MS Excel workbook. To keep it very simple for the sales staff they use a standalone VB-solution where they select products, retrieve the price for each product item and add some additional information manually to get a total price.

As most Excel developers should know, we can acquire data from workbooks in several ways. The most common approach is to treat the workbook as a flat database table. Technically we either use classic ADO or DAO to connect to the database table and using a SQL query to get the wanted records. This is a smooth solution but it assumes that the ADO/DAO library is available on all computers that will use the solutions. To avoid a situation where we must check if the library is installed or not we can use a solution where we automate Excel and execute the VLOOKUP function to get the wanted data. Practical we get a robust solution that is not depended on ADO/DAO and when working with small data sets the performance is fully acceptable.

The following screen shot shows how the price list workbook looks like:

As we can see it’s a simple table that is manually updated.

The next screen shot shows the VB-solution in action. The product list is picked up from a storage system. Sales person starts to select the products he/she wants to calculate the prices for, retrieve the prices, add number of items and then get a total price per product. The total prices including freight etc. is done in another part of the VB-solutions:

As You can see it’s a simple tool to work with. The last piece of the solution is the code I use to retrieve the prices and populate the DatagridView with.

The following code is in use and is executed when clicking on the Get Price button control:

Imports Excel = Microsoft.Office.Interop.Excel
Imports System.Runtime.InteropServices

Public Class Form1

Private Sub btnVLOOKUP_Click(sender As System.Object, e As System.EventArgs) _
Handles btnVLOOKUP.Click

Dim Location As String = Environment.GetEnvironmentVariable("USERPROFILE") _
.ToString() + "\Documents\Product Lists\"

Dim File As String = "'" + Location + "[P_List_2011.xlsx]ProductList'!R1C2:R78C3,2,0"

Dim itemChecked As Object

If Me.CheckedListBox1.CheckedItems.Count = 0 Then
MessageBox.Show("Please select at least one product.")
Exit Sub
End If

Dim xlApp As New Excel.Application

Try

For Each itemChecked In Me.CheckedListBox1.CheckedItems

Dim Price As String = String.Empty

'Here we do the lookup based on the selected product name.
Price = xlApp.ExecuteExcel4Macro("VLOOKUP(""" + itemChecked.ToString() _
+ """," + File + ")")

Dim rowValues As String() = New String() {itemChecked.ToString, Price.ToString}

'Add the row with data to the data grid,
Me.dgwPList.Rows.Add(rowValues)
Me.dgwPList.Columns(0).AutoSizeMode = DataGridViewAutoSizeColumnMode.AllCells

Next

Catch ex As Exception

MessageBox.Show(ex.ToString())

Finally

xlApp.Quit()

End Try

'Cleaning up.
If Not xlApp Is Nothing Then
GC.Collect()
GC.WaitForPendingFinalizers()
GC.Collect()
GC.WaitForPendingFinalizers()
xlApp.Quit()
End If

Marshal.FinalReleaseComObject(xlApp)

End Sub

Edit: I intentionally used the XLM macro program to show that we can still use it and with VB.NET. I thought that someone would point out that it can also be done by using xlApp.WorksheetFunction.VLookup. My friend Ken Puls, ExcelGuru, finally pointed it out.
Kind regards,
Dennis

Advertisements

2 Comments »

  1. Hi Dennis,

    I use ADO late binding to overcome problems with ADO libraries. Apparently it is slightly slower and lacks of intellisense, but for me works OK.

    Thanks

    Comment by Leszek — March 29, 2012 @ 10:10 pm

    • Hi,
      ADO late binding is a good option. OK, it exist some performance penalty compared with early binding. But if early binding is not an option then we should not compare 😉

      Kind regards,
      Dennis

      Comment by Dennis M Wallentin — March 29, 2012 @ 11:59 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: