Although we can create External Data Sets in Excel that pull information from a SQL Server Database there are times when you want to check a value or lookup a value from an underlying SQL Server database directly from an Excel formula.

Using VBA we can do this!

We will look at an example that enables us to lookup a customer’s total revenue value from an Excel Formula.

The VBA To Query a SQL Server Database

The following VBA Function can be used from within an Excel VBA Project. You will, however need to add the ADODB library to the project first.

In this example we use an ADO connection to connect to a SQL Server instance (DBSRV SQL2014):

• The Provider parameter indicates that an OLDEB connection will be established and the Data Source parameter points to the SQL Server Instance.

• The Initial Catalog parameter identifies the database to be queried (AdventureWorks2014)

• The Integrated Security parameter indicates that Windows Authemtication will be used to authenticate with SQL Server.

A RecordSet object (rs) is used to create a record set from a SELECT statement:

The SELECT statement is constructed from a literal string and the value for the variable intID that is passed into the function when it is called.

The If statement at the start checks for an intID value of 0. Integer variables default to a value of zero if not initialised (in other words if no value is provided when the function is called). If no value is passed in to the function a value of 0  is returned as the revenue value.

The second If statement tests for a non numeric value being returned by the SELECT statement. If a customerID passed in to the function is valid, but they have plaved no orders the SUM(TotalDue) expression will return a NULL value. If this happens then the funvtion will return a value of 0 instead.

The NULL value scenario can be seen in the following screen shot.

I placed a breakpoint on the line of VBA code containing the If statement and opened the Locals Window so that I could see all the variable values at that point of execution.

I tested the function by executing it from the Immediate Window in the VBA editor:

With a breakpoint set the code execution automatically stops at the marked line and enables us to view the environment at that point of execution.

The Locals Window in the above screen shot shows the Recordset object variable rs, and specifically the value for the first field from rs, “CustRev”. We can see that it is set to Null. This is because a Customer with CustomerID value 1 has not placed any orders and, therefore, has no resulting revenue value.

The following screnn shot shows that the query returns NULL when run directly on the SQL Server instance:

Assuming that a valid CustomerID is passed into the function and a non NULL value is returned by the SELECT statement, the function will return the total sales revenue for that customer as a currency value.

Calling The VBA Function From An Excel Formula

Calling a VBA function from an Excel Formula is simple. When you construct an Excel Formula by typing it into a cell intellisense will display matching VBA functions as well as built in functions. The following screen shot shows this:

You can see that there are two VBA functions listed above that start with Lo: LookupAWCustomerRevenue and LookupPersonName.

The following example shows the LookupAWCustomerRevemue function being used in an cell to calvulate the total revenue for the CustomerID value sepcified in Column A of the worksheet:

Summary

In this article we have seen a bit of VBA coding, some VBA Error Handling and Debugging techniques, and we have seen how we can call VBA functions from an Excel formula. Feel free to email us at info@ptr.co.uk if you have any questions relating to this article. There is also an Access version of this article.

If you would like to learn more about any of the techniques seen in this article or VBA programming why not take a look at our Excel and Access VBA Training courses.

If you would like to learn more about working with a SQL Server Database then take a look at our SQL Server Training Courses.

You might find that these courses come up on a Late Availability offer from time to time, offering savings of 30%.