はじめに
ここではExcelブックの外部のJScriptによるスクリプトがExcelブックを操作・設定する方法として「データの入力規則」を紹介します
Excelの「データの入力規則」で「選択リスト」を設定するのは通常、Excelブック内部のVBAで実装するようです。しかし、ここでは、あえてWSH(Windows Scripting Host)上で動作するJScript(Microsoft JScript)で外部実装する方法を紹介します
入力規則によるプルダウンメニュー
ここで紹介するJScriptで実装したスクリプトを実行すると、該当するセルに以下のような入力メッセージとプルダウン型の選択リストが表示されます
入力メッセージ
選択リスト
なぜJScriptなのか
JScriptを使う理由は軽量なスクリプトを起動して、対象となるExcelブックに設定できる点にあります。このようにするとExcelブック内部にVBAによるロジックを持たなくてよくなります。その結果、拡張子がマクロ同梱の「xlsm」ではなく、通常のExcelブックである「xlsx」とすることができます
Excelブック外部のJScriptを使用する方法は、不用意にVBAを実行してもらいたくない他部署配布や公開用のExcelブックの設定用途に適していると思います
- VBScriptはVBAライクでVBAの知識のある人は比較的容易に実装できます。しかし、VBScriptはMicrosoft社がWebブラウザEdgeでのデフォルトの利用を中止したように、幸先があまりよくありません。そこで、私はWebブラウザの標準スクリプト言語である「JavaScript」互換の「JScript」を選択しました
Excel定数の定義
JScriptでは「xl」などで始まるExcelシステム定数を定義する必要があります。ここでは、Excelシステム定数をJScriptのオブジェクトとして定義しています
var Button = {
OK : 1 //[OK] ボタン
,
CANCEL : 2 //[キャンセル] ボタン
};
var Icon = {
STOP : 16 //[Stop] アイコン
,
QUESTION : 32 //[?] アイコン
,
EXCLAMATION : 48 //[!] アイコン
,
INFORMATION : 64 //[i] アイコン
};
var XlTypes = {
XlDVType : { //入力規則の種類
xlValidateList : 3 //入力規則をリストで指定
}
,
XlDVAlertStyle : { //入力規則でのエラーのスタイル
xlValidAlertStop : 1 //中止アイコン
}
,
XlFormatConditionOperator : { //データ入力規則の演算子
xlEqual : 3 //イコール(=)演算子
}
,
XlIMEMode : { //IMEの入力モード
xlIMEModeNoControl : 0 //IME無効
}
};
メイン処理
メイン処理ではWSHとExcelのオブジェクトを生成しておきます。さらにユーザー定義クラスのオブジェクトを生成しておきます。そうして、オブジェクトを介して必要なメソッドを呼び出します
var wsh = WScript.CreateObject("WScript.Shell");
var excelApp = WScript.CreateObject("Excel.Application"); excelApp.Visible = true;
var utility = new UtilityClass();
var process = new ProcessClass();
process.set部署名ListTo社員名簿();
process.cleanup();
処理を行う「ProcessClass」の定義
社員名簿の部署名セルに入力規則でプルダウンメニュー形式で選択リストを表示します。そのためのリスト型の入力規則を部署名セル領域の「Validation」オブジェクトに対して設定します。まず「Validation」オブジェクトに対して「Delete()」メソッドを実行して既存の設定値をクリアします
そうして、「Validation」オブジェクトの「Add()」メソッドで新規に入力規則として部署名リストを設定します。部署名リスト「List部署名」は「ProcessClass」のプロパティとして配列定義をしておきます。この「List部署名」を「Add()」メソッドの4つ目の引数「Formula1」として設定します
また「Validation」オブジェクトの「InputTitle」、「InputMessage」の両プロパティを設定すると、セルが選択されると吹き出し形式のメッセージが表示されるようになります
function ProcessClass(){
this.List部署名 = [
"アプリケーション開発部"
, "Webサービス開発部"
, "クラウドサービス部"
, "事業戦略部"
, "経営企画部"
];
this.set部署名ListTo社員名簿 = function(){
var 社員名簿 = new 社員名簿Class();
with( 社員名簿 ){
with( getRange部署名() ){
with( Validation ){
Delete();
Add(
XlTypes.XlDVType.xlValidateList //Type ：リストで指定
, XlTypes.XlDVAlertStyle.xlValidAlertStop //AlertStyle：中止アイコンを表示
, XlTypes.XlFormatConditionOperator.xlEqual //Operator ：イコール(=)演算子を使用
, this.List部署名 //Formula1 ：リスト配列を指定
, null //Formula2 ：無指定
);
IgnoreBlank = true; //空白入力を無視する
InCellDropdown = true; //セルのドロップダウンリストを有効にする
InputTitle = "部署名入力";
ErrorTitle = "部署名入力エラー";
InputMessage = "部署名を入力してください";
ErrorMessage = "決められたリスト以外の部署名は入力出来ません";
IMEMode = XlTypes.XlIMEMode.xlIMEModeNoControl; //IMEを無効にする
ShowInput = true;
ShowError = true;
}
}
utility.showPopUpMessage(
"「" + Book.Sheet社員名簿.Cell.Title.ColumnName.部署名
+ "」の入力規則に以下の部署名リストを設定しました\n\n"
+ this.List部署名
, 5, "入力規則にリスト設定" );
with( getWorkbook() ){ Save(); Close(); }
}
};
this.cleanup = function(){
if( excelApp != null ) { excelApp.Visible = false; excelApp = null; }
if( utility != null ) utility = null;
if( process != null ) process = null;
if( wsh != null ) wsh = null;
};
}
JScriptの引数の与え方の注意事項
ここで、JScriptでは、引数をVBAのように名前付き引数として指定するとエラーになりますので、注意が必要です。VBAでは名前付き引数を指定すると、設定不要な引数「Formula2」は省略することができます。しかし、JScriptの場合には全ての引数の設定が必要となります。省略したい引数には明示的に「null」を与えないといけません
さらに、引数「Formula1」は、VBAでは名前付き引数「Formula1:=」に続いて直接リストのメンバーを設定することができます。しかし、JScriptでは、直接リストのメンバーを設定するエラーとなりますので、注意が必要です
Add( _
Type:=xlValidateList _
, AlertStyle:=xlValidAlertStop _
, Operator:=xlEqual _
, Formula1:= _
"アプリケーション開発部" _
,"Webサービス開発部" _
,"クラウドサービス部" _
,"事業戦略部" _
,"経営企画部" _
)
this.List部署名 = [
"アプリケーション開発部"
, "Webサービス開発部"
, "クラウドサービス部"
, "事業戦略部"
, "経営企画部"
];
Add(
XlTypes.XlDVType.xlValidateList //Type
, XlTypes.XlDVAlertStyle.xlValidAlertStop //AlertStyle
, XlTypes.XlFormatConditionOperator.xlEqual //Operator
, this.List部署名 //Formula1
, null //Formula2 ：無指定
);
「社員名簿Class」の定義
社員名簿Classに社員名簿ブックのパスやセル情報などをクラス内オブジェクトとしてクラスの先頭で定義します
「社員名簿Class」内オブジェクト定義
this.Book = {
BasePath : "C:\\Dev\\JScript\\Validation\\",
Name : "社員名簿.xlsx",
Sheet社員名簿: {
Name : "社員名簿"
,
Cell : {
Title : {
RowNo : 1
,
ColumnName : { 部署名 : "部署名", 社員番号 : "社員番号", 氏名 : "氏名", 役職 : "役職" }
}
,
Data : {
RowNo : { Head : 2, Tail : 40 }
,
ColumnNo : { 部署名 : 2, 社員番号 : 3, 氏名 : 4, 役職 : 5 }
}
}
}
};
「社員名簿Class」のプロパティとメソッドの定義
社員名簿Classのプロパティとメソッドを定義します。メソッドは主にプロパティへのアクセッサメソッドになります。プロパティが初期化されているかどうかを調べて、初期化されていなければ、必要な処理を行って初期化します
function 社員名簿Class(){
this.FullPath = this.Book.BasePath + this.Book.Name;
this.WorkBook = null;
this.Sheet社員名簿 = null;
this.Range部署名 = null;
this.open = function(){
utility.showPopUpMessage( "以下の社員名簿を開きます\n\n" + this.FullPath
, 5, "社員名簿オープン" );
try{
this.WorkBook = excelApp.Workbooks.Open( this.FullPath );
} catch ( err ){
utility.showPopUpMessage( "Error(" + (err.number & 0xFFFF) + "):\n" + err.message );
process.cleanup();
}
};
this.getWorkbook = function(){
if( this.WorkBook == null ){
this.open();
}
return( this.WorkBook );
};
this.getSheet社員名簿 = function(){
with( this.getWorkbook() ){
if( this.Sheet社員名簿 == null ){
this.Sheet社員名簿 = WorkSheets( this.Book.Sheet社員名簿.Name );
}
}
return( this.Sheet社員名簿 );
};
this.getRange部署名 = function(){
with( this.getSheet社員名簿() ){
if( this.Range部署名 == null ){
this.Range部署名 = Range( CellS( this.Book.Sheet社員名簿.Cell.Data.RowNo.Head
, this.Book.Sheet社員名簿.Cell.Data.ColumnNo.部署名 )
,
CellS( this.Book.Sheet社員名簿.Cell.Data.RowNo.Tail
, this.Book.Sheet社員名簿.Cell.Data.ColumnNo.部署名 )
);
}
}
return( this.Range部署名 );
};
}
ユーティリティクラス「UtilityClass」の定義
ユーティリティクラス内にポップアップダイアログを表示する「showPopUpMessage」メソッドを定義します。このメソッドは引数「waitSeconds」で指定する秒数ダイアログを表示した後、自動的に閉じます
function UtilityClass(){
this.showPopUpMessage = function( message, waitSeconds, title ){
if( waitSeconds <= 0 ) waitSeconds = 0;
title += "※自動的に閉じます";
var inputButton = wsh.Popup(
message, waitSeconds, title, Icon.INFORMATION
);
if( inputButton == Button.CANCEL ) WScript.Quit();
};
}