Re: Custom dialog boxes for user input

[Govert van Drimmelen]

Hi Farhan,

There are a few ways you can approach this.

* I'd probably suggest you do this as a Ribbon tool, with some
indication of the login state, and some textboxes and a button on the
ribbon. I'm not much of a ribbon expert but there's a fair amount of
help. I'm starting to gather some references here:
http://exceldna.codeplex.com/wikipage?title=Ribbon%20Customization

* Add a button or something that inserts the function and pops up the
function wizard. See https://groups.google.com/group/exceldna/browse_frm/thread/fec7b714281e95ca
and https://groups.google.com/group/exceldna/browse_frm/thread/c13abbd8859342b9.

* Create a dialog using the C API. This is quite tricky (you might
need to get the macro help file first), but gives a fast a lightweight
UI, including easy use of the Excel range selection dialog control.
This might be a start: https://groups.google.com/group/exceldna/browse_frm/thread/53a8253269fdf0a5.

* Create a Windows Forms or WPF dialog and display from a ribbon, menu
or command bar button.

If you need some more information about any of these approaches,
please write again.

Regards,
Govert


On Jun 13, 11:14 pm, Farhan Ahmed <Far...@ycharts.com> wrote:
> Hey folks -
>
> This might not be an Excel-DNA question per-se but since I've been using
> this excellent framework to develop my add-in, thought I'd ask here.
>
> I have a UDF that takes a username and password (string parameters) and
> authenticates against a network service. I want to allow this functionality
> to be invoked through a button on the ribbon/command bar and display a
> dialog box where the user can enter his credentials (rather than supplying
> it as parameters to the UDF).
>
> How do I go about doing this?
>
> Of course, the simplest case would be to somehow invoke the formula builder
> for the UDF I already have when the user clicks on a button since the
> formula builder already has a decent UI to enter the information. Would
> love some pointers on how to achieve this first step as well.
>
> Thanks,
> Farhan

[Govert van Drimmelen]

Hi Liang,

If your Ribbon is defined in the Excel-DNA add-in, then they will be
loaded into the same AppDomain. This means a static variable would be
'shared' by the ribbon and the UDF functions.

-Govert

On Jun 24, 7:50 am, LY <liangyi2...@gmail.com> wrote:
> Hi Govert,
>
> Basically, user needs to login from ribbon tool to change the login state,
> as UDF is going to check the login state every time before it starts doing
> its functionality.
>
> My question is how the ribbon is going to share the same login state with
> the UDF? How can the ribbon tool refer to the same object that holds the
> UDF, so that Ribbon and UDF can share the same state?
>
> Thanks,
> Liang

>
>
>
>
>
>
>
> On Saturday, 16 June 2012 06:52:44 UTC+8, Govert van Drimmelen wrote:
>
> > Hi Farhan,
>
> > There are a few ways you can approach this.
>
> > * I'd probably suggest you do this as a Ribbon tool, with some
> > indication of the login state, and some textboxes and a button on the
> > ribbon. I'm not much of a ribbon expert but there's a fair amount of
> > help. I'm starting to gather some references here:
> >http://exceldna.codeplex.com/wikipage?title=Ribbon%20Customization
>
> > * Add a button or something that inserts the function and pops up the
> > function wizard. See

> >https://groups.google.com/group/exceldna/browse_frm/thread/fec7b71428...
> > and
> >https://groups.google.com/group/exceldna/browse_frm/thread/c13abbd885....

>
> > * Create a dialog using the C API. This is quite tricky (you might
> > need to get the macro help file first), but gives a fast a lightweight
> > UI, including easy use of the Excel range selection dialog control.
> > This might be a start:

> >https://groups.google.com/group/exceldna/browse_frm/thread/53a8253269....

[Govert van Drimmelen]

Hi Liang,

* The static variables could hold anything, including references to
whatever objects you want, so you can share any amount and kind of
state information you want.

public static class LoginState
{
public static bool IsLoggedIn;
public static string UserName;
public LoginToken Token;
}

// From the Ribbon handler
LoginState.IsLoggedIn = true;
LoginState.UserName = theUserName;

// From the UDF
if (!LoginState.IsLoggedIn) ....


* How to deal with the not-logged-in state from the UDF?

In my own closest case the UDFs call a database, so if there is a
problem with the database connection they show #VALUE, and I have a
different function that is just a diagnostic, and returns either "OK"
or the full exception message when the test connection fails, from
where I can normally debug.

My first suggestion would be:
1. Clear indication on the ribbon of what the login status is.
2. Return ExcelError.ExcelErrorValue, or throw an Exception, which
also returns #VALUE, if user is not logged in.
3. Have a diagnostic functions that just displays login status.

If you need a more obvious indication if why the function failed -
maybe have a pop-up window but check that it is only displayed once
(so a form that is not modal). Or display a custom task pane with
diagnostic info.

There are some workarounds for your Excel-DNA function to write to the
status bar or other parts of the sheet, if you're willing to try the
latest unstable development version. Latest check-ins of Excel-DNA
have some helpers to allow you to do something like this from a
function:

AsyncUtil.QueueAsMacro(() => {XlCall.Excel(XlCall.xlcMessage, true,
"You're not logged in");});

But that should probably not be the first thing to try.
What if different add-ins were to be writing all kinds of things to
the status bar?

Regards,
Govert

On Jun 24, 6:31 pm, LY <liangyi2...@gmail.com> wrote:
> Hi Govert,
>

> Thank you for your prompt reply.
>
> Shared through a static variable, do you mean that ribbons and UDF
> functions created their own copy of objects? If so, only static variable
> can be shared, but not object state. Is it correct to say so?
>
> A practical question relating to login is, assuming UDF will only do work
> until the user login successfully, how to inform user to login before UDF
> will do work. A pop up message box might not be the best idea, is if
> there're many UDF calls from different cells, the same message "Please
> login" box would pop up many times and become undesirable. I tried to use
> excel status bar, it seems not responding. And as I recall, Automation
> Addin can not alter the contents of other cells. Can ExcelDNA help to
> convey this message?
>
> Best Regards,
> Liang

[Govert van Drimmelen]

Hi Liang,

Ah - I see where some of the confusion is coming from.

1. Excel-DNA integrates with Excel via the C API, and not as an
Automation Add-in. This has a few advantages - no registration is
required, so no admin permission on the client are needed to use the
add-in. The UDF performance is generally better. There is more
flexibility in support for how functions are displayed in the function
wizard. Multi-threaded UDFs can be created. And more...
The main difference in your code between an Automation Add-In and
Excel-DNA functions is that the Excel-DNA functions have to be
declared as 'public static' functions, and that you'd deal with
'Range' parameters in a different way.

2. Although the Excel-DNA ribbon support uses COM integration behind
the scenes, your add-in will not be created using either the COM add-
in templates or the VSTO libraries in Visual Studio. So you would
never use the 'Create an Excel add-in' options in Visual Studio with
Excel-DNA. You will never use anything like 'ThisAddIn' whcih is
generated for you.

3. Excel-DNA does not use the ribbon wrapper classes that are part of
VSTO (the references that start with Microsoft.Office.Tools.*), so you
will have to manage the ribbon xml yourself, and perhaps make some
helpers classes to keep it the ribbon updated (you can't just set
'properties' on the ribbon - everything is calllback-based).

To get started using Excel-DNA with Visual Studio and C#, you can
follow this step-by-step guide to make your first C# add-in:
http://www.codeplex.com/Download?ProjectName=exceldna&DownloadId=372242.

Then for the ribbon you can look at the examples in Distribution
\Samples\Ribbon to get you started - the easiest route is to put the
ribbon .xml in your .dna file, and then define a handler class in your
library that is marked [ComVisible(true)] and derives from
ExcelDna.Integration.CustomUI.ExcelRibbon.


I don't yet have a nice write-up that positions Excel-DNA vs. the
other ways of integrating with Excel. So you have more specific
questions, I'd be happy to answer if you post back.


I think once this confusion is sorted out, the rest of this discussion
will make more sense.

Regards,
Govert

On Jun 25, 1:28 pm, LY <liangyi2...@gmail.com> wrote:
> Hi Govert,
>

> I tried the share state idea. But couldn't get it to work. I think I miss
> something, therefore the login state wouldn't share. I didn't exactly
> follow your suggestion of using the same assembly, but rather added the
> reference, and I didn't use ExcelDNA, as I just try to figure the general
> idea first.
>
> What I did was:
>
> 1. Created a class library (MyFunctions class) for Automation addin for UDF.
>
> 2. Created a Excel add in (2010) and added a Ribbon (actually, how the
> Ribbon loads itself without being in the Addin reference and constructor I
> also couldn't understand, please forgive my understanding).
>
>         private void ThisAddIn_Startup(object sender, System.EventArgs e)
>         {
>             MessageBox.Show("Add in loaded");
>         }
>
> 3. In the Ribbon class, I adds the an object reference for the automation
> addin (Myfunctions), and changed its login state in the initialization.
>     public partial class Ribbon1
>     {
>         public MyFunctions autoAddin = null;
>
>         private void Ribbon1_Load(object sender, RibbonUIEventArgs e)
>         {
>         }
>
>         private void btnLogin_Click(object sender, RibbonControlEventArgs e)
>         {
>             autoAddin = new MyFunctions();
>             autoAddin.IsAuthorizaed = true;
>             MessageBox.Show("The auto addin is logged in in the Ribbon");
>         }
>     }
>
> Build the Excel Addin project, and open an excel application.
>
> The result is:
> 1. Excel add in loaded immediately after excel started.
> 2. After excel started, I tried to execute UDF, the login state is false.
> 3. I clicked the login button in the ribbon, message shows an automation
> addin has been created (MessageBox.Show("The auto addin is logged in in the
> Ribbon") in the ribbon class). But this did not change the existing UDF
> login state.
>
> The problem is the Automation Addin in the UDF is loaded by excel. And
> there is no reference to it that can be used to manipulate its state. Even
> though create another automation addin in ribbon would not work.
>
> So i guess my question is how ribbon can get the reference of the existing
> instance of the automation addin, therefore manipulate its state? Is this
> possible without using ExcelDNA? If not, how does ExcelDNA tackle this
> issue?
>
> Best Regards,
> Liang

[Govert van Drimmelen]

Hi Liang,

OK - it look like your ribbon is still not integrated with the Excel-
DNA add-in. The examples in the Excel-DNA distribution, under
Distribution\Samples\Ribbon\ can help you get starting making ribbons
with Excel-DNA. There are also lots of message on this Google group if
you search for 'ribbon'.

First step is to put the ribbon xml into your add-in's .dna file, in a
CustomUI tag, so you'll have:

<DnaLibrary ...>
<ExternalLibrary ... />
<CustomUI>
<customUI xmlns="http...">
<ribbon ...>
...
<button ... onAction="CallbackOnAction" ... />
</ribbon>
<customUI>
</CustomUI>
</DnaLibrary>

Notice the nested CustomUI and customUI - the uppercase / lowercase
matters here.

Second step is to define your ribbon handler class. In your add-in
library add code like this:

using ExcelDna.Integration.CustomUI;

[ComVisible(true)]
public class MyRibbonHandler : ExcelRibbon
{
public void CallbackOnAction(IRibbonControl control)
{
MessageBox.Show("Hello from control " + control.Id);
}
}

The ExcelRibbon class that is defined in ExcelDna.Integration
imiplements IRibbonExtensibility, but also has some more functionality
to integrate with the rest of Excel-DNA. So you should use this,
instead of implementing IRibbonExtensibility yourself.

I hope this helps.
If anything if still doesn't work and anything is unclear, please
write back.

Regards,
Govert

On Jun 26, 6:58 pm, LY <liangyi2...@gmail.com> wrote:
> Hi Govert,
>

> Thanks again for clearing my understanding.
>
> I try to combine the ribbon and UDF the following way, the UDF works
> perfectly following the step-by-step guide (http://www.codeplex.com/Download?ProjectName=exceldna&DownloadId=372242), but
> I couldn't get the callback for ribbon to work. It always says
> "Cannot run the macro 'Callback', The macro may not be available in this
> workbook or or all macros may be disabled."
> The excel setting is enable all macros to run and indeed there're no macros
> called "Callback" in the workbook, as using macro/VBA isn't my purpose, I
> wanted to use C# code to perform actions.
>
> 1. The ribbon is created through XML as below using CustomUI editor:
> <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
> <ribbon startFromScratch="false">
> <tabs>
> <tab id="customTab" label="Custom Tab">
> <group id="customGroup" label="Custom Group">
> <button id="customButton" label="Custom Button" imageMso="HappyFace"
> size="large" onAction="*Callback*" />
> </group>
> </tab>
> </tabs>
> </ribbon>
> </customUI>
> This works correctly when opening the excel file, the custom ribbon appears.
>
> 2. Created the FirstAddin.xll using ExcelDNA, added public static UDF
> function (working correctly):
>         [ExcelFunction(Description = "My first Excel-DNA function")]
>         public static string MyFirstFunction(string name)
>         {
>             return "Hello " + name;
>         }
>
> 3. Assigning Callback function is the problem, Ribbon cannot find the
> callback function as follows.
>         [ExcelFunction(Description = "Callback Excel-DNA function")]  //
> the callbackcannot work, w/wo the tag.
>         public void CallbackOnAction(Microsoft.Office.Core.IRibbonControl
> control)
>         {
>             if (control.Id == "customButton")
>             {
>                 System.Windows.Forms.MessageBox.Show("Button clicked!");
>             }
>         }
>
> Further information is I combined the ribbon class with UDF. It
> implemented Office.IRibbonExtensibility, and made COMVisible.
> [ComVisible(true)]
>     public class FirstRibbon : Office.IRibbonExtensibility
>
> I expect the ribbon button could trigger the callback function, but instead
> it cannot find the function and gave the cannot find macro error as above
> shows.
>
> In conclusion, the UDF worked great. My question now is how to create the
> call back function for the ribbon button. I tried to find online, but seems
> not able to find an example. Looking forward to your response!
>
> Best regards,

> ...
>
> read more »