見出し画像

Excel for MacのVBA基礎

Eugenes Works

前置き

この記事ではthe-forme.netのブログでExcel for MacのVBA関連記事を一つにまとめ、読みやすくしたものです。主にExcel2019を対象にしていますが内部バージョンの変わらないExcel2021などでも使用できる汎用的なものになっていると考えています。

まず、Excelなどの表計算ソフトの歴史からExcel for MacのVBAの特徴と対応方法を説明し、実践編で具体的なコーディングの流れを説明します。


表計算の始祖

今日では事務処理やデータ解析に欠かせない表計算ソフト。その始まりは初期の一般向けパソコンとして発売されたApple][用に開発したVisiCalcと言われています。

このVisiCalcはアメリカ人のダニエル・ブリックリン氏が経営学の授業で何度も手計算をさせられた経験を元に自動で再計算をしてくれるプログラムの開発を元にスタートし、1979年の第3回WCCFに出展後、同年10月に発売されました。

当時は事務処理や各計算には手計算かメインフレームにアクセスしたターミナルから操作するのが当たり前だった時代にメインフレームの性能には劣るが比較的手の届く価格であるパソコンにこのVisiCalcとワードプロセッサをインストールすることで手計算より早く正確に計算処理ができる点が大衆に受け、大ヒットを飛ばしました。

当時のアメリカを視察した日本のビシネスマンは一般人がパソコンを使って事務処理を行っていることに大変驚いたそうです。勿論、新しくできた市場に他の者が指を咥えてみているわけがありません。

その後、Lotus1-2-3やMultiplanなど多くの表計算ソフトが発売・使用されてきました。そんな中、表計算ソフトのデファクトスタンダードがついに登場します。

Excelの誕生

1985年にMicrosoftはGUIを導入したMacintosh向けにExcel1.0を発売します。元々移植性の高いMultiplanを開発販売していましたが、移植性の高さとのトレードオフに処理能力がライバル製品に劣ることから劣勢が続いていました。その状況をひっくり返すために誕生したのがExcelでした。

Excel1.0には印刷プレビューが実装され、ライバル製品との差別化を図っていました。また、処理能力の高さやGUIによる直感的な操作性により、ライバル製品に追いつき、差を広げていきました。

2年後にWindows版のExcel2.0が発売され、今日まで続く表計算ソフトの王道を歩むことになります。

Excel for Macの簡易年表(買い切りモデル)

  • 1985：Excel1.0が発売(Excelの誕生。Macintosh専売ソフト)

  • 1989：Excel2.2が発売
    省略

  • 2004：Excel2004が発売(VBAを実装。VBA自体はWindows用Excel5.0から実装されていた。)

  • 2008：Excel2008が発売(VBAを廃止してAppleScriptを推奨)

  • 2010：Excel2011が発売(VBAを再実装し、インラインのAppleScript実行関数MacScriptを実装)

  • 2015：Excel2016が発売(MacScript関数が廃止、AppleScriptTask関数に変更。ユーザーフォーム機能の廃止)

  • 2021：Excel2021が発売

  • 2024：Excel2024発売予定

Excel for Mac(VBA)の変遷

今ではios、Androidなど様々なOSに対応しているExcelですが、どちらかといえばExcel=Windowsのイメージが強いと思います。しかし、Microsoftは古くからMacintosh向けにもExcelを提供し続けています。

表計算機能ではほぼ同じものを提供できていますが購入理由にも挙げられるであろうVisualBasic for Application(以降VBAと記入)VBA機能では紆余曲折が見られます。元々OS依存の機能が多いので仕方有りませんが、古参のMacユーザーでは泣かされた人や技術習得に脱落した人も多いでしょう。

Excel for MacにVBAが搭載されたのは2004年のExcel2004からでした。Windows版であるExcel5.0にVisualBasicVer3.0をベースにしたVBAが初搭載されたのが1994年であることを考えると開発陣の苦労が伺いしれます。しかしExcel2008でVBAも廃止されAppleScriptの強化で自動化の環境をカバーしようとします。

大きな変化点になったのがExcel2011です。VBAが復活しただけではなくインライン型のAppleScript文を実行できるMacintosh専用関数MacScriptが実装されます。このたった一つの関数でExcel for Macの自動化環境は大きく進みました。OSの垣根を超えたVBA開発が可能になった瞬間でもあります。ただしインラインでAppleScript文を書くのは意外と苦行でしたし、動作速度も褒められたものではありませんでした。それでもDir関数やフォルダピッカーなどの機能が実装されていないExcel for Macでは非常に役に立ちました。

次の大きな変化点がExcel2016と2019です。Excel2011で実装されたインライン型のAppleScript実行関数MacScriptが廃止され、新しくAppleScriptTask関数が実装されました。

このAppleScriptTask関数は所定のディレクトリにAppleScriptファイルを保存した状態でそのファイルを呼び出す形式を取っています。AppleScriptを別ファイルにすることでコーディングのし易さとセキュリティの向上に寄与しています。セキュリティ向上の意思はVBAで別ファイルにアクセスする際の挙動にも見られます。

Excel2011までは別ファイルにアクセスするのは簡単にできましたが、Excel2016以降からは初めてアクセスするファイルの場合、ファイルアクセス権限譲渡ダイアログが表示され、明示的に権限を与える必要があります。AppleScriptを使うことで回避が可能ですが、初めてAppleScriptを使用する際にもAppleScriptにExcel操作を許可する権限の譲渡が求められます。

ファイルアクセス権限譲渡ダイアログ

他にもフォーム機能の廃止(コードの編集は可能)、VBAの挙動の変更など多くの小さな変化点も見られました。Excel2016の時点では使用頻度の多いDir関数はまだ使用できないままであったり、VisualBasic for Application Editor(以降ではVBE)は日本語入力を受け付けない。(貼り付けは可能)実行速度が致命的に遅いなど改悪と取られかねない変化点が多くありました。当時のMicrosoftのWEB相談窓口は珍しく荒れている事が多かったように覚えています。

各言う筆者も書き溜めたマクロ類をExcel2016用に書き直した後は燃え尽きてしまい、トラウマとなったExcel2016をアンインストールしてWindows版のExcel2013や他の表計算ソフトであるLibreOfficeのCalcやGoogleSpreadSheetに時間を割くようになっていました。

それから4年後に発売されたExcel2019ではネックになっていたVBEの日本語入力問題や実行速度の高速化、Dir関数の実装など実用に耐えうるものになっていきました。フォーム機能は復活しませんでしたがそれに目を瞑っても良いほどの進化を遂げていました。(実を言うと内部コードは”16”とExcel2016と変わらないのでアップデートをしたExcel2016でも同じ進化があったかもしれません。)

Windows版Excel VBAとの違い

Windows版と比べてMac版のVBAの違いは大きく以下の4つになります。

  1. ユーザーフォーム、入力フォームの使用不可。フォームコントロールはActiveXは使用不可。

  2. Windowsオブジェクトは使用不可。各オブジェクトもプロパティやメソッドがない場合がある。

  3. ファイルアクセス権譲渡ダイアログの有無。

  4. VBEの2バイト文字入力時のキャレットずれ発生。

１のユーザーフォームは新規作成はできませんがプロパティを通した編集は可能です。(Excel2016から)但し、２に通じますがプロパティやメソッドはWindows版と比べて少なくなっています。VBAで動的に変更しようとした場合、プロパティに表示されていないものはクラッシュします。画像表示用のイメージパーツは３のダイアログを使ってアクセス権を与えないと表示されません。Mac版ではPNGファイルの指定ができます。

入力フォームは無いのでマクロで呼び出すとクラッシュします。ActiveX系のフォームパーツは表示されません。

２のオブジェクトの存在確認はイミデイトウィンドウでは表示されるものの、実際は無い場合が多いのでまずは入力後に当該のオブジェクトやプロパティ・メソッドにマウスのカーソルを合わせるとツールチップが表示されます。その内容がNothingとなっていたら存在しません。但し、これも実行してみないとわからない場合もあるので基本はデバックで洗い出しをすることが肝要になります。


オブジェクトの存在確認

３のファイルアクセス権譲渡はExcel for Macではファイルが始めて別のファイルやディレクトリにVBAでアクセスする場合はアクセス権譲渡ダイアログで許可を与える必要があります。アクセス権を与えないとエラーが発生します。直接ファイルの中身を見るだけでなくサイズを調べたりするだけでも発生するので、もしあるディレクトリ内のファイルを列挙するなどの処理をする場合はダイアログ表示を回避できるAppleScriptで実装する必要が出てきます。権限を与えるコードは以下になります。

Sub requestFilesPermission(ByRef files() As String)
    Dim fileAccessGranted As Boolean
    Dim filePermissionCandidates

    filePermissionCandidates = files()
    fileAccessGranted = GrantAccessToMultipleFiles(filePermissionCandidates)
End Sub

因みにアクセス権を与えても一切アクセスができないディレクトリも存在します。

４のVBEはExcel2011からある不具合です。日本語などの２バイト文字を使用する際はVBEの設定でフォントをMSゴシックなどの等幅フォントに変更することで解消することができます。Excel2016では２バイト文字を直接入力することができないのでコピー＆ペーストで対応するしかありません。(あくまで個人談。アップデート後は良くなっている可能性もあります。)Excel2019ではフォント変更で確実にキャレットズレは直ります。

AppleScriptを適用するケース

Excelの自動化を行う上で重要なVBAとAppleScript。可能であればVBAで完結できればいいのですが、そうも行かない場合もあります。Excel for Macでは主には2つのケースがあります。

  • APIが用意されていない。

  • ファイルアクセス権譲渡ダイアログを表示させたくない。

1つ目のAPIが用意されていないは同一の機能がない場合AppleScriptで実装するのは仕方ないと言えますが、盲点なのが2つ目のファイルアクセス権譲渡ダイアログを表示させたくないです。

前にも説明しましたがExcel2016 for MacからはExcelファイルが初めてファイル・フォルダにアクセスするする場合、ファイルアクセス権譲渡ダイアログが表示され、マクロが一時的に止まり、アクセス権を与えない場合は意図しない挙動をする場合があります。この挙動は別のファイル・フォルダにアクセスする場合でも同じ様に表示されます。

ここで言うファイルアクセスとは内容読み込みだけでなく、書き込み・存在確認・ファイル情報・子ファイル情報・ファイル操作なども含まれます。

バックグラウンドで動くマクロだけでなく対話式のマクロであっても、いちいちファイルアクセス権譲渡ダイアログが出ては自動化の意味がありません。その為、Excel for Macではファイルアクセス権譲渡ダイアログが表示されないAppleScriptを使ったファイル操作が基本となります。

AppleScriptとJXA

Macには自動化機能の一つとして長年AppleScriptが実装されています。このスクリプトは英語に近い記述方法でコーディングできる点と強力なOSや各アプリの操作ができることが評価されています。ただし、文字列操作や区切り文字の指定方法が独特であったり、他の言語との共通性のなさから学習負荷が若干高い点が見受けられていました。そんな中、JavascriptをベースにしたJXAが登場しました。

汎用性の高いJavascript言語をベースにすることで学習負荷を減らすことができ、AppleScriptが不得意な文字列操作や区切り文字操作関連が簡単にできる事と特定の宣言をすることでAppleScriptの得意とするOSやアプリの操作もできるJXAは今後メインになるだろうと個人的に予想していました。

でも実際はAppleScriptを主役の座から奪うようなことは起こりませんでした。Macで自動化を行うユーザーは多くは簡易自動化アプリであるオートマタで済みますし、古くからのMacユーザーであれば確実に動いているAppleScriptをいちいちJXAに変換するのは手間です。

更に個人的に拍車を掛けたと思っているのがVBAのAppleScriptTaskではJXAはうまく機能しないことが多々あるという点です。特にOSやアプリの操作では動作しない事が多く見られました。この一点だけでも大きな問題です。ただし、文字列操作や区切り文字操作は動作するため、必要に応じてAppleScriptとJXAを使い分けることが効率の良い開発に繋がるようです。

実践編

ここからは実際にコーディングをしながら、AppleScriptを併用したVBAの実装方法を説明していきます。今回はフォルダピッカーを表示してフォルダを選択後、指定フォルダの子ファイルを列挙するマクロを作成していきます。

このマクロをExcel for Macで動かす場合に問題となるのが以下の項目です。

  1. フォルダ選択ダイアログが利用できない。

  2. 指定したフォルダアクセス時にファイルアクセス権譲渡ダイアログが表示される。

  3. 子ファイルアクセス毎にファイルアクセス権譲渡ダイアログが表示される。

この問題を迂回するためにAppleScriptを併用します。奇しくもAppleScriptを利用する場合の条件である。APIが存在しないとファイルアクセス権譲渡ダイアログを回避するを満たすことになりました。尚、これから提示するコードはExcel内部バージョン16以降を対象にしています。(Excel2016以降)

今回使用するスクリプトはデフォルトで設定されているAppleScriptです。JXAを利用したい方はスクリプト部分を読み替えてください。因みに何故JXAバージョンも説明しないのかについて簡単に説明するとOS標準機能を使用する場合、JXAは意図しない挙動をする場合が多いので今回は外しました。

作業の流れとしては以下の様になります。

ここから先は

10,215字 / 5画像

¥ 300

この記事が気に入ったらサポートをしてみませんか？