6
\$\begingroup\$

Work continues on the Rubberduck VBA Editor Add-in. I have a need to call some VBA code from the add-in with Application.Run. Unfortunately, the Application part of that refers to the particular Office Application that is hosting the VBA editor. So, if I'm working in Excel, Application refers to the instance of Excel (Word, Access, etc.). Unfortunately, these do not share a common interface, so I wrapped them up in one of my own so I could use them interchangeably.

[ComVisible(false)]
public interface IHostApplication
{
    void Run(string target);
    long TimedMethodCall(string projectName, string moduleName, string methodName);
}

Which is great, because now I can call VBA's Application.Run method without caring which Office Application is hosting the VBA Editor.

duration =_hostApp.TimedMethodCall( _projectName, _moduleName, _methodName);

The problem is my implementations of IHostApplication are all very similar. Like, copy/paste similar. I considered a base class, but don't see how that could work with all of the Application objects being a different type. Maybe some delegate "magic" would help me out here??

ExcelApp:

[ComVisible(false)]
public class ExcelApp : IHostApplication
{
    Excel.Application _application;
    public ExcelApp()
    {
        _application = (Excel.Application)Marshal.GetActiveObject("Excel.Application");
    }

    ~ExcelApp()
    {
        Marshal.ReleaseComObject(_application);
    }

    public void Run(string target)
    {
        _application.Run(target);
    }

    /// <summary>   Timed call to Application.Run </summary>
    ///
    /// <param name="projectName">  Name of the project containing the method to be run. </param>
    /// <param name="moduleName">   Name of the module containing the method to be run. </param>
    /// <param name="methodName">   Name of the method run. </param>
    ///
    /// <returns>   Number of milliseconds it took to run the VBA procedure. </returns>
    public long TimedMethodCall(string projectName, string moduleName, string methodName)
    {
        var stopwatch = Stopwatch.StartNew();
        //excel can have multiple projects, so it supports fully quantified names
        _application.Run(string.Concat(projectName, ".", moduleName, ".", methodName));

        stopwatch.Stop();

        return stopwatch.ElapsedMilliseconds;
    }
}

AccessApp

[ComVisible(false)]
public class AccessApp : IHostApplication
{
    Access.Application _application;
    public AccessApp()
    {
        _application = (Access.Application)Marshal.GetActiveObject("Access.Application");
    }

    ~AccessApp()
    {
        Marshal.ReleaseComObject(_application);
    }

    public void Run(string target)
    {
        _application.Run(target);
    }

    /// <summary>   Timed call to Application.Run </summary>
    ///
    /// <param name="projectName">  Name of the project containing the method to be run. </param>
    /// <param name="moduleName">   Name of the module containing the method to be run. </param>
    /// <param name="methodName">   Name of the method run. </param>
    ///
    /// <returns>   Number of milliseconds it took to run the VBA procedure. </returns>
    public long TimedMethodCall(string projectName, string moduleName, string methodName)
    {
        var stopwatch = Stopwatch.StartNew();
        //access supports only single project; I would think it could handle Module.Procedure syntax, but it blows up with a com exception if I try.
        _application.Run(methodName);

        stopwatch.Stop();

        return stopwatch.ElapsedMilliseconds;
    }
}

WordApp:

[ComVisible(false)]
public class WordApp : IHostApplication
{
    Word.Application _application;
    public WordApp()
    {
        _application = (Word.Application)Marshal.GetActiveObject("Word.Application");
    }

    ~WordApp()
    {
        Marshal.ReleaseComObject(_application);
    }

    public void Run(string target)
    {
        _application.Run(target);
    }

    /// <summary>   Timed call to Application.Run </summary>
    ///
    /// <param name="projectName">  Name of the project containing the method to be run. </param>
    /// <param name="moduleName">   Name of the module containing the method to be run. </param>
    /// <param name="methodName">   Name of the method run. </param>
    ///
    /// <returns>   Number of milliseconds it took to run the VBA procedure. </returns>
    public long TimedMethodCall(string projectName, string moduleName, string methodName)
    {
        var stopwatch = Stopwatch.StartNew();
        //Word supports single projects only
        _application.Run(string.Concat(moduleName, ".", methodName));

        stopwatch.Stop();

        return stopwatch.ElapsedMilliseconds;
    }
}
\$\endgroup\$
6
\$\begingroup\$

You could consider using an generic abstract base class.

[ComVisible(false)]
public abstract class HostApplicationBase<TApplication> : IHostApplication
{
    protected readonly TApplication _application;
    protected HostApplicationBase(string applicationName)
    {
        _application = (TApplication)Marshal.GetActiveObject(applicationName + ".Application");
    }

    ~HostApplicationBase()
    {
        Marshal.ReleaseComObject(_application);
    }

    public abstract void Run(string target);
    protected abstract string GenerateFullyQualifiedName(string projectName, string moduleName, string methodName);

    public long TimedMethodCall(string projectName, string moduleName, string methodName)
    {
        var stopwatch = Stopwatch.StartNew();

        Run(GenerateFullyQualifiedName(projectName, moduleName, methodName));

        stopwatch.Stop();
        return stopwatch.ElapsedMilliseconds;
    }
}

The Excel application would then be implemented as follows:

[ComVisible(false)]
public class ExcelApp : HostApplicationBase<Excel.Application>
{
    public ExcelApp() : base("Excel") { }

    public override void Run(string target)
    {
        base._application.Run(target);
    }

    protected override string GenerateFullyQualifiedName(string projectName, string moduleName, string methodName)
    {
        return string.Concat(projectName, ".", moduleName, ".", methodName);
    }
}

You could get rid of the abstract methods by using Actions, but there is really no great difference. Now only the Run-method will look the same for all applications. You could get rid of this using reflection.

(As a side-note: Scala would offer you structural typing in that case, which AFAIK will use reflection internally.)

\$\endgroup\$
  • \$\begingroup\$ This looks to be exactly what I'm looking for, but what does Scala have to do with it? We're developing this in C#... \$\endgroup\$ – RubberDuck Dec 11 '14 at 2:35
  • \$\begingroup\$ It was just a side-note. (-; \$\endgroup\$ – conrad Dec 11 '14 at 2:36
1
\$\begingroup\$

Every implementation of IHostApplication is expected to have a reference to a COM object as a member. Therefore, it should really extend IDisposable, because all of it's implementers should really be implementing the IDisposable interface. It's really not good to be letting the release of the COM object up to a finalizer that may never run at all.

\$\endgroup\$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.