Calling a COM DLL Excel Addin function from VBA

Question

I have an Excel add-in that is not an .XLA or .XLAM file. It is a .DLL file. If I inspect the add-in with the Insert Function dialog, I can see the ProgID ofThomsonONEaAddinShim.SpreadsheetFormulas in the list of categories and I can see there are four available functions in the .DLL addin: TF, TFGRID, TFRT, and TFTABLE. If I enter the following in a worksheet cell, it works fine:

=TF("BAC", "TF.PR.PriceClose(sdate=20101018)")

I want to call this from a VBA macro instead of from the worksheet.  Not sure if there is a way to do this. I tried adding the .DLL to the References in VBA, but the compiler does not find the "TF" function (error: Sub or Function not defined).

Is there a way to call these non XLA functions from within a VBA macro in Excel? I tried a few variations of Application.Run, but they did not seem to work.

BTW, if I iterate through the AddIns collection, this DLL shows up as an installed addin:

AddIns(i).Name = ThomsonReutersAddinShim.dll

AddIns(i).FullName = C:\Program Files\Thomson Reuters\Thomson Reuters Spreadsheet Link\ThomsonReutersAddinShim.dll

AddIns(i).Title = Thomson Reuters Excel Formulas (Shim)

AddIns(i).Installed = TRUE

Answer 1

In my original post, I stated that I attempted to register the DLL with no luck. Here is some more detail, perhaps I am doing it incorrectly:

1. I select I select my VBA Project then select Tools-->References-->Browse..
2. I then select the ThomsonReutersAddinShim.DLL and press Open
3. This shows "AddinShim 1.0 Type Library" as checked in the References list.
4. I select OK.

Is it possible the DLL does not export the functions for use in VBA (only for worksheet use)?

What would be the "library name?" Should I be using ThomsonReutersAddinShim.TF to call the function (i.e., the library name is the file name of the DLL file? The ProgID is different in the Excel Insert Function  dialog ( ThomsonONEaAddinShim.SpreadsheetFormulas). Neither of these seem to work, however.

Answer 2

You need to set a reference from your VBA project to the DLL. In the VBA editor, select your workbook project, go to the Tools menu, choose References, and find your DLL name in the list. Put a check next to that entry and click OK. Once the reference is established, you call any function in the DLL from the VBA in that workbook as if it were a native VBA function. For clarity, I recommend that you prefix the call with the library name.

---

Cordially, Chip Pearson Microsoft MVP, Excel Pearson Software Consulting, LLC www.cpearson.com