レガシーは消えず

リタイアしたプログラマのコーヒータイム

エクセルVBAワークショップ(2)メニュー作成ツールAutoMenuを作る

前回アドインにはメニューが不可欠だというお話をしました。アドイン内のプロシージャを呼ぶためのトリガーとして、メニューがなければならないという話でした。

エクセルのメニューについてはExcel2007からリボンUIが導入されたので、それまで使われていたメニューバーはすっかり影をひそめてしまいました。メニューバーが使えなくなったのかというとそういうことはなく、現在のExcel2019でもまったく支障なく使うことができます。リボンUIのようなデザイン性はありませんが、ツリー表示ができるのでプログラム内の構造を直観的に表現することに優れています。

f:id:mz-80k:20210220211251j:plain:w300:left

メニューバーを追加すると[アドイン]という名前のリボンができます。このリボンの中に追加したメニューツリーが表示されます。


ワークショップの内容

メニューバーの基本については他の多くのサイトにサンプルが載っています。一般的にメニューはロジックを持ちません。どのサイトでも「メニューツリーの作り方」「クリック時に呼び出すプロシージャの登録方法」が説明の中心です。簡単なメニューであれば十数行で実現することができるでしょう。しかし複雑なメニューではどうでしょうか?プログラム行数はメニュー数に比例して増えてきます。工夫してもロジックがないので行数はそれほど少なくはなりません。しかも単純作業です。メニュー数が増えてもメニュー作りが苦にならないような方法はないものでしょうか?

ということで簡単にメニューを作成するツール(AutoMenu)を作ってみました。VBEでAutoMenuを参照登録すればコードを書かなくてもメニューを作ることができます。作りたいメニューの指図書をワークシートに作成しAutoMenuに渡すだけです。今回のワークショップではAutoMenuを実際に作ってみましょう。

1.AutoMenuの概要

AutoMenuを実行するとワークシートのメニュー指図書に従ってメニューが作成されます(AutoMenuはAuto_Openプロシージャで実行します)。作成されたメニューをクリックすると関連付けられたプロシージャが呼ばれます。

f:id:mz-80k:20210222114211j:plain:w400

2.メニュー指図書

メニュー指図書は作成するアドインのワークシートに作成します。メニューのツリー構造、クリック時に実行するプロシージャ、クリックのショートカットキーを登録します。

f:id:mz-80k:20210222094349j:plain:w400

  • A列(NodeMenu)親メニュー
  • B列(LeafMenu)子メニュー
  • C列(onAction)メニュークリックで呼び出すプロシージャ
  • D列(ShortCutKey)メニュークリックのショートカットキー

A列とB列でメニューの親子関係を表現します。子メニューがない場合は親メニューのみで子メニューは空にします。C列はメニューをクリックしたときのプロシージャです。イベントは子メニューに(子メニューがない場合は親メニューに)設定されます。D列はショートカットキーです(記法は"^"と"{}"を知っておけば十分でしょう。"^q"はCtrl+q、"{F10}"はF10の意味です)。またセルの上下罫線でメニューの区切り線を判断します。図の子メニューの上下罫線を削除すればメニューの区切りがなくなります。

3.AutoMenuを実行するまで

ここからは完成したAutoMenuがアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)に保存されているものとして話を進めます。AutoMenuの作成についてはそのあとで説明します。
テスト用に新しいプロジェクトを作成してください。プロジェクト名は説明の都合上"VBAワークショップ"とします。

(1)ワークシートにメニュー指図書をつくる

f:id:mz-80k:20210224111740j:plain:w200:left

アドインも通常のブックですからワークシートを持っているのですが、通常は非表示になっていて操作することはできません。ワークシートを表示させるにはVBEでThisWorkBookのisAddinプロパティをFalseにします。するとエクセルのウィンドウにワークシートが表示され、通常の操作が可能となります。

このワークシートに前項(2)のメニュー指図書を作っていきます。

NodeMenu LeafMenu onAction ShortCutKey
親メニュー 子メニュー1 Module1.Sub1 ^q
子メニュー2 Module1.Sub2 {F10}
子メニュー3 Module1.Sub3

メニュー指図書が完成したら、isAddinプロパティをTrueに戻しておきます。

(2)AutoMenuを参照設定する

VBAで他のアドインのプロシージャや関数を利用するには参照設定が必要になります。VBEの[ツール]→[参照設定]で表示されるダイアログで[参照]をクリックします。

f:id:mz-80k:20210223113649j:plain:w250
f:id:mz-80k:20210215113518p:plain:w250
f:id:mz-80k:20210225195304j:plain:w250

ファイルの場所にアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)、ファイルの種類に「Microsoft Excel Files (*.xlsm,・・・)」を指定すると、ペインにAutoMenuが表示されるので選択して[開く]をクリックします。戻ったダイアログのリストにAutoMenuが表示されるのでチェックを確認して[OK]をクリックしてください。

(3)Auto_OpenプロシージャにAutoMenuを記述する

プロジェクトにモジュールを追加します。モジュール名は"Module1"のままでかまいません。"Module1"に以下のコードを記述します。

Const ProjectName = "VBAワークショップ.xlam!VBAワークショップ"
Const RootMenuCaption = "ルートメニュー"
Public myMenu As Collection

Sub Auto_Open()
   Set myMenu = AutoMenu.AutoMenu(RootMenuCaption, ThisWorkbook.Sheets(1), ProjectName)
End Sub
  • ProjectName:"VBAワークショップ.xlam!VBAワークショップ"はプロジェクト名の正式な書式です。プロジェクトを一意にするために、ブック名.拡張子+"!"+プロジェクト名と書きます。前回も触れましたがブック名とプロジェクト名は同じ方が便利です。上の例ではブック名もプロジェクト名も同じ"VBAワークショップ"です。

  • RootMenuCaption:作成するルートメニューのボタンキャプションなので文字列は自由です。

  • myMenu:AutoMenuの戻値を保存するコレクションです。AutoMenuが作成したメニューオブジェクトが作成した順に保存されます(1オリジン)。このコレクションを使ってメニューのプロパティが操作できます。例えばEnabledプロパティをFalseにすればクリック不可にできます。

  • AutoMenu.AutoMenu:AutoMenuプロジェクト内のAutoMenuプロシージャ

  • ThisWorkbook.Sheet(1):メニュー指図書のワークシート

(4)アドインを実行する

VBEの[実行]→[マクロの実行]は開発途中のデバッグ機能です。アドインを正式に実行するためには保存する必要があります。VBEの[ファイル]→[〇〇の上書き保存]を選択して表示されるダイアログで、ファイルの種類を"xlam"にすると保存場所がアドインフォルダ(C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns)に変更されるので、ファイル名をプロジェクト名と同じにして保存、一旦エクセルを終了します。
次にエクセルを起動して[ファイル]→[オプション]で表示されるダイアログの左ペインで[アドイン]、右ペインの下の管理リストが「Excelアドイン」であることを確認して[設定]をクリックします。表示されるダイアログに先ほど保存したファイルがあるのでチェックして[OK]をクリックします(このとき"AutoMenu"も一緒に表示されますが、前述した参照登録用なのでチェックは不要です)。
以上でエクセルの起動と同時にアドインのAuto_Openが実行されます。

f:id:mz-80k:20210223172135j:plain:w250

(5)メニューの動作を確認する

メニュー指図書では「子メニュー1」をクリックすると「Module1.Sub1」を実行するようになっています。VBEでModule1 に Sub1 を作って動作を確かめてみましょう。

Sub Sub1()
   MsgBox "Hello World !!", , ThisWorkbook.Name
End Sub

変更をコンパイル・保存して一度エクセルを終了します。その後エクセルを再起動して「子メニュー1」をクリックすると"Hello World !!"が表示されるはずです。

次はAutoMenuから返されたコレクション(myMenu)を使って、メニューボタンのプロパティを変更してみましょう。

Sub Sub2()
   With myMenu.Item(3)
      If .State = msoButtonUp Then
         .State = msoButtonDown
      Else
         .State = msoButtonUp
      End If
   End With
End Sub
  • myMenu.Item(3):myMenuにはAutoMenuで作られたメニューオブジェクトが作られた順に保存されます。「親メニュー」「子メニュー1」「子メニュー2」「子メニュー3」の順です。OptionBaseを0に指定しても、なぜか1オリジンとなるのでmyMenu(3)は「子メニュー2」です。

  • State:ボタンの状態(msoButtonUpとmsoButtonDown)を設定(取得)できます。リボンUIの導入前は3D表現だったのですが、現在はチェックマークの有無に変更されています。

  • Enabled:ここでは取り上げませんでしたがボタン押下の可否も設定(取得)できます。

4.AutoMenuの作成

最後にAutoMenuの作成です。本来は最初にAutoMenuを作成しないと他の作業ができないのですが、説明の都合で順番が最後になってしまいました。

新規にVBAプロジェクトを作成して、プロジェクト名を"AutoMenu"としてください。つぎにモジュールを追加します。名前は"Module1"のままでかまいません。


まずメインのAutoMenu関数です。他から呼ぶことができるようにpublicで宣言します。

Option Explicit
Option Base 0

Public Function AutoMenu(RootCaption As String, Sheet As Worksheet, ProjectName As String) As Collection
   '見出し辞書を作成
   Dim Header As Object
   Set Header = CreateObject("Scripting.Dictionary")
   If HeaderInitialize(Header, Sheet) = False Then
      Exit Function
   End If
   'ワークシートメニューバーを取得
   Dim WorksheetMenuBar As Object
   Set WorksheetMenuBar = Application.CommandBars("Worksheet Menu Bar")
   'ルートメニューがなければ作成
   Dim Root As Object
   Set Root = addRoot(WorksheetMenuBar, RootCaption)
   'スクリプトを読んでルートメニューにツリーを作成
   Set AutoMenu = addTree(Header, Root, Sheet, ProjectName)
End Function
  • RootCaption:エクセルのリボンに表示されるルートメニューの文字列

  • Sheet:メニュー指図書のあるワークシート

  • ProjectName:呼び出し元のプロジェクト名(ブック名+"."+拡張子+"!"+プロジェクト名)


次にAutoMenu関数から呼ばれる関数です。

  • HeaderInitialize関数:見出し辞書をHeaderに作成。戻り値は見出しのチェック結果(OK=True,NG=False)
Private Function HeaderInitialize(Header As Object, Sheet As Worksheet) As Boolean
   Dim Col As Integer
   Dim Caption As String
   For Col = 1 To 4
      Select Case Col
         Case 1: Caption = "NodeMenu"
         Case 2: Caption = "LeafMenu"
         Case 3: Caption = "onAction"
         Case 4: Caption = "ShortCutKey"
      End Select
      If Sheet.Cells(1, Col).Text <> Caption Then
         MsgBox "メニュー指図書 見出しエラー! " & "(行,列)=(1," & Col & ")" & _
                vbCrLf & vbCrLf & Caption, , Sheet.Name
         HeaderInitialize = False
         Exit Function
      End If
      Header.Add Caption, Col
   Next
   HeaderInitialize = True
End Function
  • addRoot関数:ルートメニューを検索し同じものがなければ追加します
Private Function addRoot(WorksheetMenuBar As Object, RootCaption As String) As Object
   Dim Root As Object
   For Each Root In WorksheetMenuBar.Controls
      If Root.Caption = RootCaption Then
         Set addRoot = Root
         Exit Function
      End If
   Next
   Set addRoot = WorksheetMenuBar.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   addRoot.Caption = RootCaption
End Function
  • addTree関数:メニュー指図書を読みながらメニューツリーを新規作成します
Private Function addTree(Header As Object, Root As Object, Sheet As Worksheet, ProjectName As String) As Collection
   Set addTree = New Collection
   Dim myNode As Object   '親メニュー用ワーク
   Dim myLeaf As Object   '子メニュー用ワーク
   Dim Row As Integer
   Row = 2
   Do While True
      Dim NodeCaption As String   '親メニューのキャプション
      Dim LeafCaption As String   '子メニューのキャプション
      Dim onAction As String      'クリック時の処理
      Dim ShortCutKey As String   'ショートカットキー
      '指図書1行分を取得
      With Sheet
         NodeCaption = .Cells(Row, Header.Item("NodeMenu"))
         LeafCaption = .Cells(Row, Header.Item("LeafMenu"))
         onAction = .Cells(Row, Header.Item("onAction"))
         If onAction <> "" Then
            onAction = ProjectName & "." & onAction
         End If
         ShortCutKey = .Cells(Row, Header.Item("ShortCutKey"))
      End With
      'メニュー作成
      Select Case True
         '親メニューのみ
         Case NodeCaption <> "" And LeafCaption = ""
            Set myNode = addButton(Root, NodeCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 1))
            addTree.Add myNode
         '親子両方あるとき
         Case NodeCaption <> "" And LeafCaption <> ""
            Set myNode = addPopup(Root, NodeCaption, isBeginGroup(Sheet, Row, 1))
            addTree.Add myNode
            Set myLeaf = addButton(myNode, LeafCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 2))
            addTree.Add myLeaf
         '子メニューのみ
         Case NodeCaption = "" And LeafCaption <> ""
            Set myLeaf = addButton(myNode, LeafCaption, onAction, ShortCutKey, isBeginGroup(Sheet, Row, 2))
            addTree.Add myLeaf
         '親子両方ないときは終了
         Case Else
            Exit Do
      End Select
      Row = Row + 1
   Loop
End Function


最後にaddTree関数からコールする関数です。

  • isBeginGroup関数:区切り線の判断(上罫線があればTrue)
Private Function isBeginGroup(Sheet As Worksheet, Row As Integer, Col As Integer) As Boolean
   With Sheet
      .Activate
      .Cells(Row, Col).Select
      isBeginGroup = (Selection.Borders(xlEdgeTop).LineStyle <> xlLineStyleNone)
   End With
End Function
  • addPopup関数:ポップアップメニューを追加する
Private Function addPopup(Parent As Object, Caption As String, BeginGroup As Boolean) As Object
   Set addPopup = Parent.Controls.Add(Type:=msoControlPopup, Temporary:=True)
   With addPopup
      .Caption = Caption
      .BeginGroup = BeginGroup
   End With
End Function
  • addButton関数:ボタンメニューを追加する
Private Function addButton(Parent As Object, Caption As String, onAction As String, ShortCutKey As String, BeginGroup As Boolean) As Object
   Set addButton = Parent.Controls.Add(Type:=msoControlButton, Temporary:=True)
   With addButton
      .Caption = Caption
      .onAction = onAction
      If ShortCutKey <> "" Then
         Application.OnKey ShortCutKey, onAction
      End If
      .BeginGroup = BeginGroup
   End With
End Function


コードが完成したらアドインとして保存しておきます。それからこの記事の最初「1.AutoMenuの概要」に戻って読んでいただくと分かりやすいと思います。

5.最後に

こうしたツールのようなプログラムは単体では動作できないので開発時のデバッグ環境に苦労します。なので最初はプロジェクト内でツール本体とそれを使うデバッグ用プログラムを一緒に開発し、あとでバラして仕上げたりします。今回は(バラしたあとの)完成したものを順序良く説明しようとして肝心の本体の説明が一番最後になってしまいました。

とても読みにくい状態でリリースしてしまいましたが、最後までお読みいただきありがとうございました。

NEC LZ550 のバッテリーパックを交換する

DIYと言えるのかどうか分かりませんが、NECノートPC LZ550のバッテリーがダメになったので交換しました。本体は2013年に購入、バッテリー交換は2回目です。前回の交換は2017年頃で業者さんにお願いしました。今回は自分でやってみようと思いGoogleでバッテリーパックを検索してみるとあるものですねぇ、しかも安い。

f:id:mz-80k:20210217152749j:plain:w300

私のパソコン(LZ550/N)用の型番は

  • PC-VP-BP94
  • PC-VP-BP95

の2種類ありました。たぶんBP94の後継がBP95なのでしょう(違っていたらごめん)。分からないのでパソコンの裏蓋を開けてみました。3の写真のようにバッテリーパックがうつ伏せ状態で取り付けてあるため型番が見えません。どうしたものかと思っていたらQRコードが見えたので読み込んだらピンポン!型番が表示されました。私のPCはBP94でした。
今回は冒険ですが非純正の最安値を注文しました。注文して4日で入荷、さっそく交換作業に取り掛かりました。

1.LZ550/N 外観です
f:id:mz-80k:20210217155233j:plain:w300

2.裏返します(ビス12か所、目隠しシールを剥がした後)
f:id:mz-80k:20210217154302j:plain:w300

3.裏蓋を外すとこのような状態
f:id:mz-80k:20210217162628j:plain

・白点線:交換するバッテリーの外周
・黄丸:本体に固定するビス(4か所)
・赤丸:QRコード(型番が文字列で記録されている)
・赤四角:本体へのコネクタ(横をドライバでこねると取り外せる)
・赤線/白線:本体から左右のスピーカーへの配線(バッテリーの外周に配線してあるので注意)
・青矢印:ゴム(日の字形、裏は糊付けなので再利用できる)
・黄緑矢印:固定用の部材(これも糊付けで再利用する)

交換にかかる時間は全部で1時間弱でした。ドライバーの選択さえ間違えなければ誰でもできる作業です。交換後は裏蓋をビスでとめて目隠しのシールを貼れば終了です。

f:id:mz-80k:20210217165355j:plain:w300

交換した古いバッテリーの裏側の写真です。
新しいバッテリーの寿命がどのくらいかは分かりませんが、前回の半額以下で交換できたので大満足です。
皆さんもぜひ挑戦してみてください。

エクセルVBAワークショップ(1)アドイン事始め

みなさんはアドインという言葉を聞いたことがありますか?

エクセル関係の書籍で「マクロ」は多く見かけますが「アドイン」はあまり見かけません。両方とも「VBA」を使うのですが違いがはっきりしません。というわけで少し考えてみました。

VBAとはMicrosoftOfficeの各アプリケーションに付属しているプログラミング言語のことです。Visual Basic for Applicationの略でエクセルVBA、アクセスVBA、ワードVBA、というようにそれぞれのオフィスアプリケーションに対応したものが用意されています。その中で一番使われているのはエクセルVBAです。私も2000年頃から汎用ツールを中心にかなりの開発を経験しました。このブログでも特に断らない限り"VBA"は"エクセルVBA"のこととします。

エクセルの初期、"VBA"が導入される前に"マクロ"と呼ばれるものがありました。マクロは操作手順を記録して同じ操作を反復させることが主な目的です。今のRPAのようなものです。使われた言語もVBAではなく独自の簡易言語でした。"マクロ"とは本来は"ミクロ"に対して"巨大な"という意味ですが、現在のように超巨大化したコンピュータ環境に対しては比較的小さい処理のかたまりを指す言葉になってしまいました。「マクロの記録」も「マクロブック」も小さい処理が対象でした。

その後、Excel97になってマクロ用の簡易言語は実質的に廃止されてVBAに替わりました。「マクロの記録」もVBAで記述されることになりました。より複雑なロジックも表現できるようになったわけです。しかし"マクロ"という言葉は残ってしまいました。今でも"マクロの記録" "マクロブック"が使い続けられています。

VBAが導入されると同時に提供されたものに"アドイン"があります。アドインもVBAで記述されるものですがエクセルの追加機能のようにふるまうところがマクロブックと違います。


・マクロブック

f:id:mz-80k:20210215112117j:plain:w300

マクロブックにはワークシートとマクロが同居しています。そのブックに特化した処理をしたいときに使います。ブックを複製するとマクロも一緒に複製されます。マクロブックをコピーして複数のユーザーに配布したあとでマクロがうまく動かなかったときは、配布したすべてのマクロブック内のマクロ部分を修正しなければなりません。普通は修正版のマクロブックを再配布することになります。大変です。


・アドイン

f:id:mz-80k:20210215112243j:plain:w300

アドインはマクロブックと違いマクロ部分のみのブックです。エクセルをより便利にするための機能をVBAで作成して常駐させて使います。汎用的にどんなブックでも使えるのが特徴です。アドインがうまく動かない場合はアドインの修正版を配布するのでブックには影響は与えずに済みます。


ワークショップの内容

このワークショップでは「アドイン事始め」として、アドインの作り方を学習します。MsgBoxがひとつだけのアドインです。マクロブックと違って登録までの手続きはそれなりに必要ですが、仕組みが分かればそれほど難しくはありません。

準備

まず、エクセルでVBAが使えるよう[開発]メニューを表示させる必要があります。意外に深いところに設定があります。

f:id:mz-80k:20210215112452j:plain:w250:left

エクセルの[ファイル]→[オプション]→[リボンのユーザー設定]で表示されるダイアログの[開発]にチェックして[OK]をクリックしてください。

シート表示に戻るとメニューに[開発]が追加されています。VBAの開発はこの[開発]メニューから行うことになります。

f:id:mz-80k:20210215113221j:plain:w300

やっと準備が整いました。 それでは実際の作業に入ることにしましょう。

1.VBE(Visual Basic Editor)を表示させる

エクセルで「空白のブック」を開いてください。[開発]→[VisualBasic]をクリック(またはAlt+F11)するとVBE(Visual Basic Editor)が表示されます。

f:id:mz-80k:20210215113420j:plain:w300
f:id:mz-80k:20210215113518p:plain:w300
f:id:mz-80k:20210215113620j:plain:w500

VBAのすべての作業はこのVBEで行います。エクセルのウィンドウはVBEの裏側に隠れて残っています。VBEとエクセルはそれぞれ独立したウィンドウですが、エクセルを終了させるとVBEも終了します。VBEだけを残すことはできないようです。 VBEの左上ペインをみると、VBProject(Book1)というプロジェクトがすでに作成されていることがわかります。

2.プロジェクトに名前をつける

[ツール]→[VBProjectのプロパティ]で表示されるダイアログの「全般]タブでプロジェクト名が変更できます。[保護]タブではプロジェクトにパスワードを設定できます。

f:id:mz-80k:20210215113910j:plain:w200:left f:id:mz-80k:20210215113953j:plain:w200

プロジェクト名はデフォルトでVBAProjectとなっています。必ず変更するようにしましょう。複数のプロジェクトが同一名だと同時に開いているときに名前が特定できずに不都合が起こることがあります。 変更したプロジェクト名はVBEの左上ペインに表示されます。

f:id:mz-80k:20210215114223j:plain:w250

3.標準モジュールに"Hello World"を記述する

定番の"Hello World"を記述してみましょう。まず[挿入]→[標準モジュール]をクリックします。

f:id:mz-80k:20210215114321j:plain:w250

追加された標準モジュールに次のコードを記述しましょう。

Sub Auto_Open()
   Greeting
End Sub

Sub Greeting()
   MsgBox "Hello World !!", , ThisWorkbook.Name
End Sub

Auto_Openはプロジェクトがロードされたとき最初に実行されるプロシージャです。このプロシージャがないとプロジェクトは実行することができません。上の例ではAuto_Openに"Greeting"と記述しているので、起動時に"Greeting"が実行されます。
コードを走らせてみましょう。ツールバーの緑の右向き三角アイコン(または[実行」→[Sub/ユーザーフォームの実行])をクリックします。

f:id:mz-80k:20210215114454j:plain:w300

"Hello World !!"というメッセージボックスが表示されましたか?もしメッセージボックスではなくマクロを選択するダイアログが表示されたら、"Auto_Open"または"Greeting"を選択して[実行]をクリックしてください。

f:id:mz-80k:20210215114647j:plain:w200

4.アドインを保存する

[ファイル]→[上書き保存](またはツールバーのフロッピーアイコン)をクリックして保存ダイアログを表示します。

f:id:mz-80k:20210215114741j:plain:w300

ファイルの種類は「Excelアドイン」を指定します。指定すると保存場所の表示が自動的に C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns となります。このフォルダは完成したアドインを常駐させるための特別なフォルダ(隠しフォルダ)です。デバッグ中は「ドキュメント」などの別フォルダに変更しましょう。 ファイル名にはプロジェクト名と同じものを入力します。別の名前で保存することもできますが混乱を避けるため同一名にします。拡張子はxlamです。

入力を確認して[保存]をクリックします。これでアドインが保存されます。VBEを終了したあとエクセルを終了させるときブックの保存ダイアログが表示されますが、アドインは保存されているので、ここは無視して終了します。

5.アドインの変更・デバッグ

保存したアドインは完成まで何度も開いて変更・デバッグを繰り返します。エクスプローラからアドインを開くと、マクロの警告が表示されるので[マクロを有効にする]をクリックしてください。開いたエクセルウィンドウで[開発]→[VisualBasic]をクリック(またはAlt+F11)するとVBEが前回保存の状態で表示されるので、変更・デバッグを繰り返します。

f:id:mz-80k:20210215115116j:plain:w200
f:id:mz-80k:20210215113518p:plain:w200
f:id:mz-80k:20210215113420j:plain:w300
f:id:mz-80k:20210215113518p:plain:w200
f:id:mz-80k:20210215153050j:plain:w250

6.完成したアドインを常駐させる

完成したアドインをエクセルの起動とともに常駐させるには次のようにします。

(1)アドイン用フォルダに保存する

アドイン用フォルダは「4.ブックを保存する」にあるように C:\Users\<ユーザー>\AppData\Roaming\Microsoft\AddIns です。エクスプローラを使ってこのフォルダに完成したアドインを保存してください。(このフォルダは隠しフォルダです。表示するにはエクスプローラの「表示リボン」の「隠しファイル」をチェックする必要があります。)

(2)エクセルで常駐指定をする

エクセルを起動して[ファイル]→[オプション]で表示されるダイアログの左ペインの[アドイン]をクリックします。 表示された右ペインの下に[管理 Excelアドイン]と表示されていることを確認して[設定]をクリックします。

f:id:mz-80k:20210215153207j:plain:w250
f:id:mz-80k:20210215113518p:plain:w200
f:id:mz-80k:20210215153305j:plain:w150

アドインを選択するダイアログで常駐させたいアドインを選択して[OK]をクリックするとエクセルに読み込まれます。次回からもエクセルの起動時に自動的に読み込まれます。(常駐を解除するときはこのダイアログでチェックをはずします)

7.アドインにメニューが必要なわけ

今回はAuto_OpenにGreetingというプロシージャを1つ記述しました。起動時にAuto_Openは1回だけ自動実行されるので、Greetingは起動時に1回だけ実行されます。何度でもGreetingを実行させるにはどうしたらいいでしょうか?

フォームを作りますか?フォームにボタンを配置してボタンをクリックするたびにGreetingを実行すれば確かに何度でもGreetingが実行できます。しかしフォームを閉じてしまうと再表示する方法がありません。

結局、常駐するプログラムには何らかのトリガーが必要となります。エクセルに備わっているトリガーはメニュー(リボン)です。必要に応じてメニュー(リボン)から処理を呼び出すことがエクセルの基本動作になります。

VBAでもメニューを作ることができます。Auto_Openでメニューを作っておけばメニューから何度でもプロシージャを呼び出したり、フォームを表示したりすることができることになります。

次回はメニューの作り方を取り上げたいと思います。

ダイソーのプチブロックでストップモーションを撮影してみた

ストップモーションとはコマ撮りのことです。対象を少しずつ動かして撮った写真をつなぎ合わせて動画にしたものです。粘土細工が生き物のように動く動画がそれです。今回カメラを新調したので以前からやってみたかったストップモーションに初挑戦することになりました。

素材は粘土ではなくダイソーのプチブロックです。ダイソーのプチブロックは種類も豊富ですべて100円ですが、本当に百均かと疑いたくなる精度で気持ちよく組み立てられます。

今回はオリジナルの作品(SHARP MZ-80K)を作ってみました。

 

・カメラ ソニーα6600 + SEL1855

・撮影属性 シャッター速度 1/8、絞り5.6

・撮影枚数 約300枚

 


プチブロックでMZ-80Kを作ってみた

 

300枚の写真で作品の位置を動かないようにするため正方形(10X10)の基盤ブロックをテーブルに両面テープで固定し、その基盤の周りに各ブロックを組み立てるようにしました。ブロックを1個追加するごとに1枚写真を撮るので撮影には丸3日かかりました。太陽光が部屋に入らないように黒いゴミ袋で窓を覆っての作業はなかなか骨の折れるものでした。

  

 

 

今さらの CInt, Int, Fix について

CIntとIntは式を評価してInt型へキャスト、Intは数値の整数部分を返す関数です。問題は名前がよく似ているため間違ってもなかなか気が付かないことです。整数部分を取り出す関数Fixもついでに調べてみましょう。

'例1:正数のとき
Dim Num = 10.6
Console.WriteLine("CInt={0}, Int={1}, Fix={2}", CInt(Num), Int(Num), Fix(Num))
’結果は CInt=11, Int=10, Fix=10

'例2:負数のとき
Dim Num = -10.6
Console.WriteLine("CInt={0}, Int={1}, Fix={2}", CInt(Num), Int(Num), Fix(Num))
’結果は CInt=-11, Int=-11, Fix=-10

CIntは結果を丸めますが、IntとFixは丸めません。Intは「指定した数を越えない最大の整数」を、Fixは「指定した数の整数部分」を返します。従ってIntとFixは引数の正負で結果が違います。
またCIntの戻り値はIntegerですがIntとFixの戻り値はDecimalです。なので上の例を正確に表現すると

'例1:正数のとき
Dim Num As Double = 10.6#
Console.WriteLine("CInt={0}, Int={1}, Fix={2}", CStr(CInt(Num)), CStr(CInt(Int(Num))), CStr(CInt(Fix(Num))))
'結果は CInt=11, Int=10, Fix=10

'例2:負数のとき
Dim Num As Double = -10.6#
Console.WriteLine("CInt={0}, Int={1}, Fix={2}", CStr(CInt(Num)), CStr(CInt(Int(Num))), CStr(CInt(Fix(Num))))
'結果は CInt=-11, Int=-11, Fix=-10

私は Int関数 は使いません。私の関わってきた業務用アプリでは「整数に丸める」ことがほぼなかったためです。ならば最初から紛らわしい関数は使わないのが一番です。整数部分を求めるのは文字通り Fix のみで事足ります(CIntは文字通りキャストで使いますが)。

四捨五入クラスを作ろう

四捨五入の計算にはwikiで調べるといろいろな方法があるのが分かります。業務システムでは0.4以下のときは切り捨て、0.5以上のときは切り上げるという一般的なものを多く使用します。小学校で習ったものと同じです。通常は
  124.4 ---> 124
  124.5 ---> 125
となります。ところが
  124.4 ---> 124
  124.5 ---> 124 ※
となってしまう四捨五入もあったりします。※印の部分が通常の四捨五入と違いますが決して間違っているわけではありません。VBでは四捨五入用の関数(Math.Round)の引数(mode)にAwayFromZeroとToEvenがあり、中間点(0.5)の下に何もないときAwayFromZeroはゼロから遠い方へToEvenは近い偶数に丸められます。

Dim dec1 As Decimal = 124.5@
Dim dec2 As Decimal = Math.Round(Dec1, 0, MidpointRounding.AwayFromZero)
Dim dec3 As Decimal = Math.Round(Dec1, 0, MidpointRounding.ToEven)
Console.WriteLine("{0}, {1}, {2}", dec1, dec2, dec3)   '-->124.5, 125, 124 

このことを知っていないと消費税の計算でバグが出たりします。ちょっとしたことですが仕様の確認を怠るとえらい目にあいます。こういうものはシステムで使用する四捨五入関数をクラスで定義しておくと間違うことが無くなります。

Public Function Round(Dec As Decimal) As Decimal
   Return Math.Round(Dec, 0, mode:=MidpointRounding.AwayFromZero)
End Function

こうした関数を集めた汎用クラス(dll)の使用を強制することで事故が防げます。

年月計算用クラスを作ろう

業務システムでは不可欠の年月計算用のクラスを作りませんか。もちろんDate型の変数でもDateDiffやDateAddで年月計算をすることができますが、このクラスは数式として使用することを前提にしてオペレーション(+,-などの演算子)を定義しています。月初や月末の計算もつけています。
大まかな構成はこのようになります。

Public Class YearMonth
   (1) 変数定義
   (2) コンストラクタ
   (3) プロパティ
   (4) メソッド
End Class

(1)変数定義の部分は次のようにYear,Monthを定義します

   Public Property Year As Integer
   Public Property Month As Integer

   Sub Initialize(Year As Integer, Month As Integer)
      Me.Year = Year
      Me.Month = Month
   End Sub

(2)コンストラクタはいくつか用意すると便利です

   '日付を直接指定(大かっこをつけるとDateのような予約語でも使えます)
   Sub New([Date] As Date)
      Initialize([Date].Year, [Date].Month)
   End Sub

   'yyyy/MM形式の文字列を指定(形式は正規表現で厳しくチェックすべきなのでしょうが省略)
   Sub New(YMString As String)
      Dim Spl = Split(YMString, "/")
      Initialize(CInt(Spl(0)), CInt(Spl(1)))
   End Sub

   '他のインスタンスをクローン(月前進/月後退オプションあり)
   Sub New(Clone As YearMonth, Optional Forward As Integer = 0, Optional Backward As Integer = 0)
      CopyFrom(Clone)
      Me.Forward(Forward - Backward)
   End Sub

(3)プロパティもいくつか用意します

   '西暦下2桁
   Public ReadOnly Property Year2 As Integer
      Get
         Return Year Mod 100
      End Get
   End Property

   'yyyy/MM形式の文字列(Shadowsをつけるとカスタムクラスにも標準文字列(toString)を定義できる
   Public Shadows ReadOnly Property toString As String
      Get
         Return Format(Year, "0000") & "/" & Format(Month, "00")
      End Get
   End Property

   '月初
   Public ReadOnly Property FirstDate As Date
      Get
         Return DateValue(CStr(Year) & "/" & CStr(Month) & "/01")
      End Get
   End Property

   '月末
   Public ReadOnly Property LastDate As Date
      Get
         Return DateAdd(DateInterval.Day, -1, DateAdd(DateInterval.Month, 1, FirstDate))
      End Get
   End Property

(4)メソッドは月前進/月後退と演算子の定義です

   '月前進
   Public Sub Forward(Months As Integer)
      Dim Years As Integer = (Months ¥ 12)
      Months = Months Mod 12
      With Me
         .Year += Years
         .Month += Months
         Select Case .Month
            Case Is < 1
               .Year -= 1
               .Month += 12
            Case Is > 12
               .Year += 1
               .Month -= 12
         End Select
      End With
   End Sub

   '月後退
   Public Sub Backward(Months As Integer)
      Forward(0 - Months)
   End Sub

   '演算子(=)
   Public Shared Operator =(c1 As YearMonth, c2 As YearMonth) As Boolean
      If c1.Year = c2.Year AndAlso c1.Month = c2.Month Then
         Return True
      Else
         Return False
      End If
   End Operator

   '演算子(<>)
   Public Shared Operator <>(c1 As YearMonth, c2 As YearMonth) As Boolean
      Return Not (c1 = c2)
   End Operator

   '演算子(-)
   Public Shared Operator -(c1 As YearMonth, c2 As YearMonth) As Integer
      Return (c1.Year - c2.Year) * 12 + (c1.Month - c2.Month)
   End Operator

   '演算子(<)
   Public Shared Operator <(c1 As YearMonth, c2 As YearMonth) As Boolean
      Return (c1 - c2) < 0
   End Operator

   '演算子(<=)
   Public Shared Operator <=(c1 As YearMonth, c2 As YearMonth) As Boolean
      Return (c1 - c2) <= 0
   End Operator

   '演算子(>)
   Public Shared Operator >(c1 As YearMonth, c2 As YearMonth) As Boolean
      Return (c1 - c2) > 0
   End Operator

   '演算子(>=)
   Public Shared Operator >=(c1 As YearMonth, c2 As YearMonth) As Boolean
      Return (c1 - c2) >= 0
   End Operator

以上でクラスができました。使い方の例として今日から10か月後の月末を表示してみましょう。

   Dim YM = new YearMonth(Today)
   YM.Forward(10)
   Dim Target As Date = YM.LastDate()
   Msgbox(Format(Target, "yyyy/MM/dd")

2つの日付の月差を計算するときはこのようになります。

   Dim YM1 = new YearMonth(Date1)
   Dim YM2 = new YearMonth(Date2)
   Msgbox("月差=" & YM1-YM2)

というように便利に使用できます。実際の業務システムで使用するクラスは西暦と会計期を連動させるようなちょっと複雑なものになります。