This article describes how you can define your own custom Excel formulas in VBA to exchange data with Invantive SQL supported platforms.
Perform the following steps to add your custom Excel formula to an Invantive Control spreadsheet using VBA:
- Open the spreadsheet attached.
- Or: create a new spreadsheet using File → New.
- Log on to your desired database, such as Exact Online.
- Activate the Invantive Control for Excel repository using the utmost left slider in the Modeler ribbon.
- Go to the Developer ribbon.
- Create a module in your spreadsheet.
- Paste the following code in the code block:
Option Explicit ' ' Retrieve the name of an Exact Online company using a custom Excel formula. ' Illustrates the use of Invantive SQL from within VBA. ' Public Function MyGetDivisionName(division As String) As String On Error GoTo Catch MyGetDivisionName = I_SQL_SELECT_SCALAR("Description", "SystemDivisions", "Code = " + division) Finally: Exit Function Catch: HandleError "MyGetDivisionName" End Function
- Adapt where necessary.
- Go to the Excel sheet again.
- Enter an Excel formula in a cell:
- Of course you can replace the formula parameters by range references.
- Watch the results appear.
Of course you will need to make sure the Invantive SQL constructed is valid and not sensitive for SQL injection, but these are general engineering issues.
Excel function returns “#N/A” (Dutch: “#N/B”)
When your Excel function returns “#N/A” (Dutch: “#N/B”) instead of a value, please execute the following steps:
- Disconnect and log on again.
- Open the Tools menu and select “Re-register VBA Integration”: