goolz.blogg.se

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. The NULL value scenario can be seen in the following screen shot. If Not IsNumeric(rs.Fields("CustRev").Value) Then If this happens then the funvtion will return a value of 0 instead. 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. The second If statement tests for a non numeric value being returned by the SELECT statement. If no value is passed in to the function a value of 0 is returned as the revenue value. Integer variables default to a value of zero if not initialised (in other words if no value is provided when the function is called). The If statement at the start checks for an intID value of 0. 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 Integrated Security parameter indicates that Windows Authemtication will be used to authenticate with SQL Server.Ī RecordSet object (rs) is used to create a record set from a SELECT statement: Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID).The Initial Catalog parameter identifies the database to be queried (AdventureWorks2014).The Provider parameter indicates that an OLDEB connection will be established and the Data Source parameter points to the SQL Server Instance.In this example we use an ADO connection to connect to a SQL Server instance (DBSRV\SQL2014): strConnString = "Provider=SQLOLEDB Data Source=DBSRV\SQL2014 " _ LookupAWCustomerRevenue = rs.Fields("CustRev").Value Set rs = conn.Execute("SELECT SUM(TotalDue) AS CustRev FROM Sales.SalesOrderHeader WHERE CustomerID = " & intID)

& "Initial Catalog=AdventureWorks2014 Integrated Security=SSPI " StrConnString = "Provider=SQLOLEDB Data Source=W10NBMJD\SQL2014 " _ Public Function LookupAWCustomerRevenue(intID As Long) As Currency You will, however need to add the ADODB library to the project first. The following VBA Function can be used from within an Excel VBA Project. We will look at an example that enables us to lookup a customer’s total revenue value from an Excel Formula.