Create your own Excel formula on Invantive UniversalSQL

Wiki (en)
, , , ,
1

This article describes how you can define your own custom Excel formulas in VBA to exchange data with Invantive UniversalSQL supported platforms.

Instructions

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 UniversalSQL 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:
=MyGetDivisionName(102673)
  • Of course you can replace the formula parameters by range references.
  • Watch the results appear.

image
image786×273 19.5 KB

Of course you will need to make sure the Invantive UniversalSQL 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”:

image