paxintelligence.blogg.se

I tested the function by executing it from the Immediate Window in the VBA editor: ?LookupAWCustomerRevenue(1) 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. 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”. 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:Īssuming 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. LookupAWCustomerRevenue = rs.Fields("CustRev").ValueĬalling The VBA Function From An Excel FormulaĬalling 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 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: You can see that there are two VBA functions listed above that start with Lo: LookupAWCustomerRevenue and LookupPersonName. #VBA SQL SERVER CONNECTION STRING WINDOWS#.#VBA SQL SERVER CONNECTION STRING CODE#.