Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
VBA function as report triggers (Read 2942 times)
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
VBA function as report triggers
21.10.05 at 13:45:38
Calling functionality in the database was already possible through the use of PL/SQL commands or the SQLProcedure and SQLFunction worksheet functions. The other way around was not possible yet. How can you call Excel macros when returning data from the database? You can by using the 'select as function' feature:
Suppose you have a query for which you want to do some processing on a certain column. As an example I am going to display a message when the salary of an employee is larger than 1000.
First thing to do is to write a VBA function that will act as the report trigger:
In a module in your VBA project (Press Alt-F11 to go to the VB projects) code the following routine:
Public Function mytrigger(Salary as double) as Double
   if Salary > 1000 then
     Msgbox "Earning more than a thousand!"
   end if
   mytrigger = Salary
End Sub
Now run the following query:
select ename, '=mytrigger(sal)' as formula from emp
Executing the query will put the =mytrigger(sal) in the cell as a formula. Excel will evaluate it and run the mytrigger routine. Making the routine mytrigger as a function is required for the formula trick to work and returning the salary as formula result is just done to make it return something. The point is that you can make code wake up when a value enters the sheet.
Please note that I had to make a modification to the select as function code to make the code work well with functions that have commas and spaces in them. The fix will be released in version SQL*XL 4.0.25
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Pages: 1