[ネタ] アプリ版ExcelでもTypeScript使いたいメモ

概要

いよいよOffice ScriptsとしてOfficeでTypeScript(Javascript)が使えるようになったのですが、残念ながらWeb版限定でした…。

Web版ExcelでTypeScriptベースの「Office Scripts」提供開始。ユーザー操作を自動で記録可能

というわけで、アプリ版ExcelでもTypeScriptが使えるように試行錯誤してみました。

単純にTypeScript(Javascript)側からExcelを動かすのはActiveXさえ扱えれば簡単で、ActiveXからのExcelの操作はググれば使えそうなサンプルがゴロゴロ出てきてそれ参考にすれば十分な状態なので、

逆に最小限の労力でExcel(VBA)側からTypeScriptのコードを動かせるようにもしてみたい。

新しい仕様のJSを使えるようにする

今回の構想においてJavascript実行環境はActiveXが簡単に使えてユーザー側で追加のインストールが不要なWindowsScriptHost(WSH)を使うのですが、

何もしなければ相当に古いJavascript仕様相当であるJScript環境で動いてしまうのでさすがに現実的ではない。

ただ、WSHはオプションとしてスクリプトエンジンをProgIDやCLSIDで指定することができるようになっており、

Windows10ならEdge(Chromiumじゃないほう)のES6(2015)相当のJavascriptも使えるようになっています。

WSH JScript Chakra を使用した ES2015(ES6) 対応 （ スクリプトエンジン まとめ ）

いろいろ試してみましたが、今のところsubプロシージャや関数が呼ばれた都度wscript.exeを実行してJavascriptコードを動かすのが確実そうです。

他にVBAからJavascriptを使う方法としてScriptControlを使う方法やHTMLDocumentを使う方法なんかがありますが、いずれもEdge相当のJavascriptは使えないようです。

WSH側からExcel側(起動済み)のオブジェクトを取得

WSH(wscript.exe)を実行する方式にする場合、そのままではExcel関係のActiveXオブジェクトをWSH側に渡す方法が無いのでどうにかする必要があるのですが、

GetObject(,"Excel.Application") では既に別のExcelが動いていたとしても新しいExcelウインドウが開いてしまってうまくいきません。

しかし、GetObjectにはActiveXのクラス名の他にファイルのフルパスも指定できるのですが、既に開いているエクセルファイルの場合は新しく開かずに既に開いているファイルのWorkbookオブジェクトが取得できる仕組みになっているようです。

参考: GetObject(, "Excel~")だけで無く、GetObject(ファイル名)も活用しよう

つまり、呼び出すExcel側のExcelファイルのフルパスさえわかればGetObjectで新しく(二重に)Excelを起動することなくExcel(VBA)側のWorkbookオブジェクトが取れるということです。

VBAから自身のExcelファイルのフルパスは ThisWorkbook.FullName で簡単に取得できるので、

これをコマンドラインオプションとして渡せば実質Excel(VBA)側のWorkbookオブジェクトをWSH側に渡すことができるようになります。

ちなみに (Workbookオブジェクト).Application で親となるApplicationオブジェクトが取得できます。

対象のExcelファイルが開いていない場合は新しく開いたうえでそのWorkbookオブジェクトを取得できるので、工夫すれば同じコードでExcelを開いていない状態からWSH側からExcelを開いてスクリプトを動かすということも可能になります。

データ保存領域としてカスタムドキュメントプロパティを使う

WSHを都度起動する方式にした以上は終了するとWSH側の変数はすべて消えてしまうので、プロシージャや関数をまたがってデータをやり取りしたい場合はどこかにデータを保存しておく必要があります。

こういう時は専用のシートでも作ってセルに書き込んでおくのが王道だとは思いますが、もう一つの選択肢として、カスタムドキュメントプロパティも使えるようです。

以下のようにVBA側でヘルパー関数を定義しておけばWSH側から (Applicationオブジェクト).Run(Excelファイル名 + "!関数名", 引数...) で呼び出すことができて便利です。

Sub SetCDP(Pname As String, PValue As String)
    If Not IsCDPExist(Pname) Then
        ThisWorkbook.CustomDocumentProperties.Add Pname, False, msoPropertyTypeString, PValue
    Else
        ThisWorkbook.CustomDocumentProperties(Pname).Value = PValue
    End If
End Sub

Function GetCDP(Pname As String) As String
    GetCDP = ""
    If IsCDPExist(Pname) Then
        GetCDP = ThisWorkbook.CustomDocumentProperties(Pname).Value
    End If
End Function

Sub DelCDP(Pname As String)
    If IsCDPExist(Pname) Then
        ThisWorkbook.CustomDocumentProperties(Pname).Delete
    End If
End Sub

Function IsCDPExist(Name As String) As Boolean
    Dim prop As Object
    IsCDPExist = False
    For Each prop In ThisWorkbook.CustomDocumentProperties
        If prop.Name = Name Then
            IsCDPExist = True
            Exit For
        End If
    Next prop
End Function

WSHからExcelのユーザーフォームを開く

WSHというかExcel関係のActiveXオブジェクトからユーザーフォームを開くのは無理なようなので、VBA側でユーザーフォーム名からフォームオブジェクトを取得するヘルパー関数を作ってみました。WSH側から (Applicationオブジェクト).Run(Excelファイル名 + "!GetFormObject", "フォーム名") でフォームオブジェクトを取得したら (フォームオブジェクト).Show(1) で開いたりできます。存在しないフォーム名を指定するとVBA側ではNothingを返していますが、WSH(JavaScript)側ではnullとなるようです。
また、このままではWSH側からUnload出来ない(hideで隠すことはできる)ので、同様にヘルパー関数が必要のようです。

参考: Show Any Form

Function GetFormObject(FormName As String) As Object
    Dim Obj As Object

    For Each Obj In VBA.UserForms
        If Obj.Name = FormName Then
            Set GetFormObject = Obj
            Exit Function
        End If
    Next Obj

    On Error Resume Next
    Err.Clear
    Set Obj = VBA.UserForms.Add(FormName)
    If Err.Number <> 0 Then
        Set GetFormObject = Nothing
        Exit Function
    End If
    Set GetFormObject = Obj
End Function

Sub UnloadForm(formname As String)
    Dim obj As Object
    Set obj = GetFormObject(formname)
    If Not obj Is Nothing Then
        Unload obj
    End If
End Sub

VBAからWSH(wscript.exe)を起動

VBA内蔵のRunは実行したら即制御が戻る非同期実行となり非常に都合が悪いのでWScript.ShellのRunで同期実行するようにしています。

Function CallJScript(funcname As String, Optional formname As String = "", Optional script As String = "", Optional book As String = "") As Boolean

    Static fso As Object
    Static wsh As Object

    If fso Is Nothing Then Set fso = CreateObject("Scripting.FileSystemObject")
    If wsh Is Nothing Then Set wsh = CreateObject("WScript.Shell")

    If book = "" Then
        'bookパラメータ(対象のExcelファイル)を指定しなかった場合は自身のExcelファイルを設定
        book = ThisWorkbook.FullName
    ElseIf Left(book, 1) = "." Then
        '1文字目が.なら相対パスと判断して絶対パスに変換
        book = fso.GetAbsolutePathName(fso.BuildPath(ThisWorkbook.Path, book))
    End If

    If script = "" Then
        'scriptパラメータを指定しなかった場合はbookパラメータと同じフォルダにある同じファイル名のjsファイルを設定
        script = fso.GetParentFolderName(book) & "\" & fso.GetBaseName(book) & ".js"
    ElseIf Left(script, 1) = "." Then
        script = fso.GetAbsolutePathName(fso.BuildPath(ThisWorkbook.Path, script))
    End If

    If fso.FileExists(book) = False Then
        Err.Raise Number:=53, Description:="ファイル" & book & "が見つかりません"
        Exit Function
    End If

    If fso.FileExists(script) = False Then
        Err.Raise Number:=53, Description:="ファイル" & script & "が見つかりません"
        Exit Function
    End If

    'edgeのJScriptエンジン(Chakra)を指定してスクリプトを実行
    wsh.Run "wscript //E:{1B7CD997-E5FF-4932-A7A6-2A9E636DA385} " & script & " " & book & " " & funcname & " " & formname, 0, True


    'イベントキャンセル用のカスタムプロパティを取得して戻り値に設定しカスタムプロパティを削除
    CallJScript = False

    If LCase(GetCDP("_JScriptIsCancel")) = "true" Then
        CallJScript = True
    End If

    If IsCDPExist("_JScriptIsCancel") Then
         DelCDP "_JScriptIsCancel"
    End If
End Function

WSHでスクリプトを書く

今のところこんな感じ。

var sh = WScript.CreateObject("WScript.Shell")
var xlBook = WScript.GetObject(WScript.Arguments(0))
var xlApp = xlBook.Application

sh.popup(xlBook.Sheets("Sheet1").Range("A3").Value)
xlBook.Sheets("Sheet1").Range("A1").Value = "test"
var formtest = xlApp.Run(xlBook.Name + "!GetFormObject", "UserForm1")

if(formtest == null){
    sh.popup("Nothing!")
}else{
    formtest.Show(1)
}

xlApp.Run(xlBook.Name + "!SetCDP", "test", "Test Prop2")
sh.popup(xlApp.Run(xlBook.Name + "!IsCDPExist","test"))
sh.popup(xlApp.Run(xlBook.Name + "!GetCDP", "test"))

都度wscript.exeを起動しなくてもいいのでは…

参考：JScript と Chakra を共存させる

をみてよくよく考えたら都度都度wscript.exeを起動しなくてもお互いにカスタムドキュメントプロパティの内容を監視して協調動作すればいいのではと考え中…(リンクのほうはInternetExplorerオブジェクトでやっている模様)