Accessing global in-memory DB via UDF

Add-in Express™ Support Service
That's what is more important than anything else

Accessing global in-memory DB via UDF
Delphi UDF DB database 
Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last 
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
How can one set up a "global variable object" (basically a large in-memory database) for an Excel UDF to access? IOW, the UDF should not access the DB on disk (that would be too slow), but rather by calls to methods of a DB object that is global to the add-in. The object exists; it's how to declare it and load it in the add-in that is the question.

Thank you.
Posted 09 May, 2011 16:40:25 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Larry,

You can use global variables that you can initialize in the OnAddInInitialize event handler. Please see the sample code below: 

interface

//...

type
  TADX_UDFSample = class(TadxAddin, IADX_UDFSample)
  protected
    function MyTestFunction: OleVariant; safecall;
  end;

  TAddInModule = class(TadxCOMAddInModule)
    procedure adxCOMAddInModuleAddInInitialize(Sender: TObject);
  private
  protected
  public
  end;

var
  GlobalData: Integer = -1;

implementation

{$R *.dfm}

procedure TAddInModule.adxCOMAddInModuleAddInInitialize(Sender: TObject);
begin
  if (GlobalData = -1) then
    GlobalData := GetTickCount();
end;

function TADX_UDFSample.MyTestFunction: OleVariant;
begin
  Result := GlobalData;
end;


Please note, global variables are initialized and accessible within the add-in scope, i.e. if you run 2 Excel processes you get 2 instances of your add-in, each with its own global variables.
Posted 10 May, 2011 07:42:08 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
Thank you! What is the name of the event handler for when the add-in is closed, so that I can free resources?
Posted 17 May, 2011 07:06:27 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Larry,

You can use the OnAddInBeginShutdown or OnAddInFinalize events for such purposes.
Posted 17 May, 2011 07:29:07 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
Thanks Dmitry! Where can I find a complete listing/description of events?
Posted 17 May, 2011 07:30:43 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Larry,

I am sorry, we don't have a complete Class Reference for Add-in Express for Office and VCL. Please tell me which events you are interested in and I will provide you with their descriptions.
Posted 17 May, 2011 09:25:17 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
"if you run 2 Excel processes you get 2 instances of your add-in, each with its own global variables"

If I open 2 excel apps, that's clearly 2 excel processes.

How about 2 excel worksheets within one excel app? is that still only one process?
Posted 17 May, 2011 15:32:46 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
Dmitry,

It appears that in my implementation of your code, the initialization event never gets called. Can you check what I have below and tell me what's wrong?

(I have other functions later on [not shown] that access the GlobalData variable and reveal that it's still equal to the initial -1, instead of 50).


type
TcoMyAutomationTest1 = class(TadxAddin, IcoMyAutomationTest1)
protected
function MyFunc(var Range: OleVariant): OleVariant; safecall;
end;

TAddInModule = class(TadxCOMAddInModule)
procedure adxCOMAddInModuleAddInInitialize(Sender: TObject);
procedure adxCOMAddInModuleAddInFinalize(Sender: Tobject);
private
protected
public
end;

var
GlobalDat a: integer = -1;

implementation

{$R *.dfm}

procedure TAddInModule.adxCOMAddInModuleAddInInitialize(Sender: TObject);
begin
GlobalData := 50;
end;

procedure TAddInModule.adxCOMAddInModuleAddInFinalize(Sender: Tobject);
begin

end;
Posted 17 May, 2011 16:09:14 Top
Dmitry Kostochko


Add-in Express team


Posts: 2875
 Joined: 2004-04-05
Hi Larry,

How about 2 excel worksheets within one excel app? is that still only one process?


Yes, it will be one process.

It appears that in my implementation of your code, the initialization event never gets called. Can you check what I have below and tell me what's wrong?


Could you please send me your project (or some demo project with the same behavior) for testing? You can find our support email address in the readme.txt file located in the Add-in Express installation folder.
Posted 18 May, 2011 01:52:51 Top
Larry X


Serious Developer


Posts: 34
 Joined: 2011-04-22
I sent you the project.
Posted 18 May, 2011 08:37:42 Top

Posts 1 - 10 of 13
First | Prev. | 1 2 | Next | Last