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