Some Excel-specific UX development questions

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

Some Excel-specific UX development questions
A few things I need to be able to do to port my .NET app functionality to Excel 
bobcalco




Posts: 64
Joined: 2019-03-20
Is it possible to:

1. Add a button to the formula bar. I'm talking about the button where Fx button is located, to the left of the text edit for a formula. I am able to do this in my spreadsheet app, but there doesn't seem to be a hook in the COM API to do that.

2. Create a pop-up form that implements custom editing from the Fx dialog (the one where you add parameters to a function call) ... IOW, is it possible to add a button for specialized editing of the function name? Or alternatively invokes the Fx dialog when the user closes the custom editing form.

3. Create a custom drop-down editor in a cell. Moreover, I'd like to be able to call the Fx dialog when the user finishes editing in this drop down.

4. Capture when the value of the ActiveCell changes, and optionally invoke a pop-up form or task pane based on its text value. (I'm guess this in an easy one, but an example of doing it would be great.)

5. Use custom .NET components on popup and task pane forms. I have specialized editing needs and have components for these I can and would prefer to use from my application.
Posted 28 Nov, 2021 11:06:16 Top
Andrei Smolin


Add-in Express team


Posts: 18506
Joined: 2006-05-11
Hello Bob,

1. No.

2. I do not understand "from the Fx dialog". You can't extend that dialog. You can add a custom function to the list of functions; actually, the Excel add-in registers your functions for you and this adds the functions to the list. If "specialized editing of the function name" means showing a custom form for editing the parameters of the function, then this is impossible: you can't customize the Insert function dialog. Consider showing the corresponding controls on a custom pane.

3. You can add a custom Ribbon control to the context menu that that cell shows. I don't understand "a custom drop-down editor". You edit a cell in the Formula Bar or use the in-place editor (press F2 to start editing). You can let the user edit the cell on a pane. Add-in Express provides two events: before and after the user edits a cell. Maybe, you talk about showing a form and use an analogue of the RefEdit Control (available in VBA) to select a cell? If so, you can try to google for such a control. Anyway, a non-modal form usually interferes with the windowing of the host application; affected areas are focus, keyboard shortcuts, tooltips, etc. I suppose these issues can be solved using Windows API.

4. You intercept the SheetChange event (see https://docs.microsoft.com/en-us/office/vba/api/excel.application.sheetchange); in Add-in Express this event is mapped to the SheetChange event of the ADXExcelAppEvents component. Yes, this is an easy one.

5. You can use .NET controls on forms and task panes.

Does this help?

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 29 Nov, 2021 05:59:51 Top
bobcalco




Posts: 64
Joined: 2019-03-20
Hi Andrei,

1. Didn't think so. :)

2. Understand.

3. I mean, a custom in-place editor, that can drop down in a kind of overlay window beneath the cell being edited. Kind of like Smart Tags, I guess, but more useful.

4. Got it.

5. Can I create pop up .NET form via the Ribbon or a custom task pane that AREN'T modal, like the way the VBA IDE is popped up? (I get that modal forms can mess with main UI thread of Excel.)

Yes, quite helpful, thanks for your responsiveness.

BTW I have my functions with ParamArray parameters working great via ExcelDNA, even got to write them in F# (my favorite .NET language these days); but am still resolved to use AddIn Express for more intensive UX aspects of my solution.
Posted 30 Nov, 2021 23:21:10 Top
Andrei Smolin


Add-in Express team


Posts: 18506
Joined: 2006-05-11
Hello Bob,

3. No, Add-in Express doesn't provide such a way.

5. These are non-modals who interfere with the windowing of the host application, not modals; see my previous post. You can create a non-modal form but you will need to solve all issues yourself.

Regards from Poland (CEST),

Andrei Smolin
Add-in Express Team Leader
Posted 01 Dec, 2021 02:59:50 Top