Creating PowerShell Automatic Variables from C#

Question

I trying to make automatic variables available to Excel VBA (like ActiveSheet or ActiveCell) also available to PowerShell as 'automatic variables'. PowerShell engine is hosted in an Excel VSTO add-in and Excel.Application is available to it as Globals.ThisAddin.Application. I found this thread here on StackOverflow and started created PSVariable derived classes like:

public class ActiveCell : PSVariable
{
    public ActiveCell(string name) : base(name) { }

    public override object Value
    {
        get 
        { 
            return Globals.ThisAddIn.Application.ActiveCell; 
        }
    }
}

public class ActiveSheet : PSVariable
{
    public ActiveSheet(string name) : base(name) { }

    public override object Value
    {
        get 
        { 
            return Globals.ThisAddIn.Application.ActiveSheet; 
        }
    }
}

and adding their instances to the current POwerShell session:

runspace.SessionStateProxy.PSVariable.Set(new ActiveCell("ActiveCell"));
runspace.SessionStateProxy.PSVariable.Set(new ActiveSheet("ActiveSheet"));

This works and I am able to use those variables from PowerShell as $ActiveCell and $ActiveSheet (their value change as Excel active sheet or cell change). Then I read PSVariable documentation here and saw this:

"There is no established scenario for deriving from this class. To programmatically create a shell variable, create an instance of this class and set it by using the PSVariableIntrinsics class."

As I was deriving from PSVariable, I tried to use what was suggested:

PSVariable activeCell = new PSVariable("ActiveCell");
activeCell.Value = Globals.ThisAddIn.Application.ActiveCell;
runspace.SessionStateProxy.PSVariable.Set(activeCell);

Using this, $ActiveCell appears in my PowerShell session, but its value doesn't change as I change the active cell in Excel.

Is the above comment from PSVariable documentation something I should worry about, or I can continue creating PSVariable derived classes? Is there another way of making Excel globals available to PowerShell?

score 6 · Accepted Answer · edited Sep 08 '15 at 14:38

6

Our documentation is wrong - it is a supported scenario.

Here's a bit more about the technique:

Lee Holmes [MSFT] Windows PowerShell Development

edited Sep 08 '15 at 14:38

Scoregraphic

7,110
4
42
64

answered Jan 02 '11 at 22:49

LeeHolmes

2,177
16
5

score 0 · Answer 2 · answered Jan 02 '11 at 22:09

Obviously in your second example, where you are not deriving from PSVariable, you couldn't expect the $ActiveCell variable to change with the value of the ActiveCell property since you're capturing its value just once.

I don't believe deriving from PSVariable is a supported scenario, but it does work and I've done it to add variables such as $Now and $Today.

It might be a better idea to just expose an $Application variable to PowerShell script instead of the various properties of the Application object. The upside to this is that you wouldn't need to create a bunch of automatic variables and PowerShell scripts could access anything the Application object has to offer by using $Application.ActiveCell. The other benefit is that it doesn't need to be an automatic variable at all because the Application object reference will never change.

Having said all that, I've included a subclass of PSVariable that I use from time to time which takes a ScriptBlock for the getter and setter. This lets me define automatic variables from PowerShell without needing a separate derived class for each one.

using System;
using System.Management.Automation;

namespace Einstein.PowerShell
{

    public sealed class DynamicVariable : PSVariable
    {

        #region Constructors

        /// <summary>
        /// </summary>
        public DynamicVariable(string name, ScriptBlock onGet)
            : this(name, onGet, null)
        {
        }

        /// <summary>
        /// </summary>
        public DynamicVariable(string name, ScriptBlock onGet, ScriptBlock onSet)
            : base(name, null, ScopedItemOptions.AllScope)
        {
            OnGet = onGet;
            OnSet = onSet;
        }

        #endregion

        #region Properties

        /// <summary>
        /// The ScriptBlock that runs to get the value of the variable.
        /// </summary>
        private ScriptBlock OnGet
        {
            get;
            set;
        }

        /// <summary>
        /// The ScriptBlock that runs to get the value of the variable.
        /// </summary>
        private ScriptBlock OnSet
        {
            get;
            set;
        }

        /// <summary>
        /// Gets or sets the underlying value of the variable.
        /// </summary>
        public override object Value
        {
            get
            {
                if (OnGet == null) {
                    return null;
                }
                return OnGet.Invoke();
            }
            set
            {
                if (OnSet == null) {
                    throw new InvalidOperationException("The variable is read-only.");
                }
                OnSet.Invoke(value);
            }
        }

        #endregion

    }

}

I already expose Excel.Application as $Application, I use Runspace.SessionStateProxy.SetVariable("Application", app) and it works fine (app is an Microsoft.Office.Interop.Exce.Application instance). I know I ca use Application.ActiveCell or Application.ActiveSheet, but I was trying to create an environment similar to Excel VBA where ActiveCell and ActiveSheet are available as globals. Thank you. — , Jan 03 '11 at 07:58

Creating PowerShell Automatic Variables from C#

2 Answers2