Where can I find Excel Specific Documentation

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

Where can I find Excel Specific Documentation
Where can I find Excel Specific Documentation 
Glen Lewis




Posts: 29
Joined: 2021-03-12
I have been programing for years, but this site is very hard to navigate. I am looking for the documentation on XLL and COM for Excel.

Looking for:
1) How to get current cell(s) selected when button is clicked.
2) Where to capture the Events within a sheet or document
a) Value changed
b) Document Saved
c) Document Opened
d) a general list of events I can capture would be fine and where in the C# XLL Module or AddinModule code I would apply those events.
3) Get current User and Device name (VBA as environment variables) I assume this does too?
4) Does this have the ability to cache data to use that data when function is called? Or is each function a new thread?
5) How to get the Active sheet and iterate the rows and columns.
6) How to create a Table and link to DB source through the Add-in

A list of the methods and functions available through the Add-In express would be great!

Like I said, the documentation is for this is very disjointed. If the owner of the site could have the documentation where you can find the information it would be good.


You have great documentation on how to say hello world, but detail API is needed.
Posted 12 Mar, 2021 16:23:48 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Glen,

The Excel Object Model's starting point is at https://docs.microsoft.com/en-us/office/vba/api/overview/excel. Note that sometimes they give a bit different description for the same class/member at https://docs.microsoft.com/en-us/dotnet/api/microsoft.office.interop.excel?view=excel-pia. Since the descriptions aren't wordy that difference may give you a bit of useful info.

As to the XLL, Microsoft doesn't provide VB.NET/C#-oriented documentation so your best starting point would be section Excel UDF tips; see the PDF file in the folder {Add-in Express}\Docs on your development PC. Section "Your first Microsoft Office COM add-in" and the example it describes should answer your question #1 and #2.

#3 - What is User? What is Device? How VBA is involved in getting them?

#4 - This misses me completely. Could you give a bit more details?

#5 - This is in the Excel object model. We don't do this in the sample project mentioned above.

#6 - you can record a VBA macro while performing this in the UI and then use the code of the macro as an example.

I would also like to remind you that an XLL allows creating custom functions: a function *returns* a value; it doesn't change other cells. Also, we strongly suggest that you do not use the Excel object model in an XLL. This is because the object model isn't prepared to incoming calls at this moment.


Andrei Smolin
Add-in Express Team Leader
Posted 15 Mar, 2021 02:58:44 Top
Glen Lewis




Posts: 29
Joined: 2021-03-12
#3) From VBA you can get the User Id from the environment variables.
#4) I am grabbing data from an API call to return back to excel, the round trip for the data is a couple seconds, but caching the dataset I can just use the data to retrieve the values as needed.
#5) Figured this out..
#6) I create Lsit Objects already in VBA would like to set them up with the Addin Module to build a table when a cell is selected and the button in the ribbon bar is clicked.

New Question, if a cell is selected and contains a formula, is there an easy way to get the parameters of that function to be used in a button?

Example:
A1 contains: =ACTMTD({Company},{year},{Month},{Account}) This is a function to retrieve the Balance of an account.

If I select that cell, I want to grab the values of the parameters to use in a new sheet for a table to generate the details of the balance. I have the UDF working just fine, but I have no idea how to get the parameters values. The formula could be =ACTMTD($A$1,$A$2,$B$2,"123456"). How to do I evaluate that string formula to the values they are mapped too.

Short of taking the string and parsing it out.
Posted 10 Apr, 2021 17:00:50 Top
Andrei Smolin


Add-in Express team


Posts: 18793
Joined: 2006-05-11
Hello Glen,

Glen Lewis writes:
=ACTMTD($A$1,$A$2,$B$2,"123456").


If that function is yours, you could store that info somewhere to avoid parsing the formula string and getting the values. That is, when this function is called, it can store the parameters in a static structure right in the XLL or, in the COM add-in.

Glen Lewis writes:
3) Get current User and Device name (VBA as environment variables) I assume this does too?


This is .NET; see https://docs.microsoft.com/en-us/dotnet/api/system.environment.getenvironmentvariables?view=netframework-4.8.

Glen Lewis writes:
4) Does this have the ability to cache data to use that data when function is called? Or is each function a new thread?


A new function call is a new function call made by Excel. When your function is called you can cache data or reuse cached data and provide the function result. All calls are performed on the main thread.

Glen Lewis writes:
#6) I create Lsit Objects already in VBA would like to set them up with the Addin Module to build a table when a cell is selected and the button in the ribbon bar is clicked.


If you have a VBA macro performing these steps, you can translate it to the programming language that you use and invoke that code in the Click event of the Ribbon button component.


Andrei Smolin
Add-in Express Team Leader
Posted 12 Apr, 2021 03:20:29 Top