Question 281502 in Visual Studio Code Question posted in
View the official documentation.

Visual Studio Code – How do I pass a reference to the Excel StatusBar to another class in C#?

I have a simple XLL (Excel add-in) that uses C# to:

  1. Gather some simple input from a template workbook (XLSX).
  2. Decide (based on that input) which piece of 3rd-party software the user is trying to extract data from; and then call the appropriate DLL to connect to the relevant software package.
  3. Receive back a standardised data structure (collection of custom objects I have defined in an Interfaces.dll) and write these to the Excel sheet.

Structurally, I have a *.csproj for the top-level entry point (the XLL) and another for the Interfaces.dll (so that all other projects can see the definition of the IO class I have defined for passing data around). I then have a set of sibling projects for each piece of 3rd-party software to do whatever COM interactions are required. This means my top-level XLL project is the only one that ‘knows about’ Excel, and gets a reference to the Excel application object (via ExcelDNA and Microsoft.Office.Interop.Excel) – but my lower-level projects only need to ‘see’ their respective COM objects for the 3rd-party software.

The issue is that some of the data collection takes a while, and users want to see the Excel status bar update for the different categories of data harvesting that is going on – I do not need a progress bar, just some high-level notes like "Extracting stream data", "Extracting pump data" etc.
Is there an elegant way of passing a reference from my XLL (that has the Excel application object reference) down to the lower-level objects? Specifically, is it possible to pass a reference to the Excel Application.StatusBar text field only?

My code looks something like this:

xl.Application app = (xl.Application)ExcelDnaUtil.Application;  // This gets the root Excel Application object from the Excel-DNA environment
app.StatusBar = "Starting export"

HysysExport.HysysExporter exporter = new HysysExport.HysysExporter(sim_version, CompMap, HexMap);
results = exporter.GetData();

Where HysysExport is one of my lower-level DLLs, with a class constructor that passes in some mapping dictionaries and a software version number.

I want to know if it is possible to pass in a reference/pointer to the app.StatusBar, something like:

HysysExport.HysysExporter exporter = new HysysExport.HysysExporter(sim_version, CompMap, HexMap, ref app.StatusBar);
results = exporter.GetData();

And where the class constructor for HysysExport.HysysExporter looks like:

public HysysExporter(string simVersion, Dictionary<string, string> CompMap, Dictionary<string, string[]> HexMap, ref string xlStatus)
{
    this.simVersion = simVersion;
    this.CompMap = CompMap;
    this.HexMap = HexMap;
    this.xlStatus = xlStatus;
}

Would anything similar to that work? What form would I need to define the class field to store a reference to a string, rather than a copy? And is this even possible with the Excel app.StatusBar field, as Visual Studio gives me an error when I try to pass it by ref.

I am using .Net 4.8 – so some options I can see that are available in later versions may be out of my reach for now.

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Heavily inspired by @rotabor's answer, here's what I ended up doing: Moved the xlApp object to be a static member of the existing 'interfaces' DLL (which I use to define the interface for all other projects i.e. they already have the relevant scope visibility of that class) and then I can just access that static object from anywhere - this works here because the Excel application object is static, by definition, for an XLL - that fact was the key, so thanks to @rotabor for prompting me in that direction!

    So my interfaces project has:

    public class SimIO
{
    public static xl.Application xlApp = (xl.Application)ExcelDnaUtil.Application;
    [... other code defining the interface class]
}

    Then everywhere else I can just use, for example:

    SimIO.xlApp.StatusBar = "Writing data to Excel";

  2. 0

    Since the StatusBar text will be updated once in the constructor, you need do it externally like in the first code fragment. It will be better approach from OOP point of view.

    Generally, it can be resolved through callback methods or such interfaces like NotifyPropertyChanged, but the class with which you want to interact should support any of this.

    it’s not possible to pass ‘ref app.StatusBar’ because StatusBar is a property not field. In fact, a property is the set of two methods get and set. You can pass the app variable only then access its members including StatusBar.

    In your particular case since your app is Excel Add-in, it has sense to make the app variable global (static) to be accessible from anywhere and do not pass it as a parameter.

    static class HysysExport {
  static xl.Application app;
  class HysysExporter {
    public HysysExporter(string simVersion, Dictionary<string, string> CompMap, Dictionary<string, string[]> HexMap, string xlStatus) {
      simVersion = simVersion;
      CompMap = CompMap;
      HexMap = HexMap;
      app.StatusBar = xlStatus;
    }
  }
  static void YourInitMethod() {
    app = (xl.Application)ExcelDnaUtil.Application;
    app.StatusBar = "Starting export";
  }
}

    But this approach doesn’t look good – you need to don’t forget to include status bar update in every constructor.

    To further develop approach, the Factory method pattern can help you to simplify the code and make it more reliable.

    In the Factory method you will update StatusBar following creation every new object.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search