LotusScriptのメソッドが実行できない……

LotusNotes連携

実行時エラー438に悩まされる

オブジェクトは、このプロパティまたはメソッドをサポートしていません

80箇所ぐらいの宛先に、それぞれ添付ファイルの異なるメールを送信する、というしちめんどくさい仕事をすることになった。まあ、Excelで表さえ作ったらメールを送ること自体は楽勝（参考）なんですが、前任の方が非常に丁寧なお仕事をなさる方で、何と、

全てのメールについてメール文書をプリントアウトしてファイル

してあったのです。

マジか……。

で、調べてみた

LotusScript使ったら自動印刷ぐらいできるんじゃね？

と思ったときにはココですよ。

まずは、コチラ。

Call notesUIDocument.Print( [ numCopies% [, fromPage% [, toPage% [, draft [, printerName]]]]] )

んで、

パラメータ
numCopies%
Integer 型。省略可能。印刷する部数です。このパラメータを省略すると、[印刷] ダイアログボックスが表示されます。0 を指定すると 1 部印刷されます。

だなんて書いてあるもんだから、当然

notesUIDocument.Print(0)

と書けば印刷してくれるはず……。

あ、ちなみに変数notesUIDocumentというのは、

Dim notesSession As Object         'NotesSession'
Dim notesDatabase As Object        'NotesDatabase'
Dim notesDocument As Object        'NotesDocument'
Dim notesUIWorkSpace As Object     'NotesUIWorkspace'
Dim notesUIDocument As Object      'NotesUIDocument'
Set notesSession = CreateObject("Notes.NotesSession")
Set notesUIWorkSpace = CreateObject("Notes.NotesUIWorkspace")
Set notesDatabase = notesSession.GetDatabase("", "")
notesDatabase.OpenMail
Set notesDocument = notesDatabase.CreateDocument()
notesDocument.Save False, False
Set notesUIDocument = notesUIWorkSpace.EditDocument(True, notesDocument, False)

こういう順序で中身をセットしています（本文や添付ファイルなんかをセットした部分は省略）。

ところが、実際には

notesUIDocument.Print (0)

のところで実行時エラー438が出る。

お願い

たぶん、LotusNotesという外部アプリのメソッド名がVBAのメソッド名とかぶっているのがイカンのだと思うのだけれど、打開策をご存じの方がいらっしゃったら教えろてください

あ、仕事そのものは、結局手作業でやりました。

@akashi_keirin on Twitter

配列関係の覚書(2)

VBA覚書

いただいたコメント

前回の記事に達人の皆様からコメントをいただいていたにもかかわらず、返事もできないまま１週間近くたってしまっていた。

私にとっては結構重要なご指摘だったので、コメント欄にではなく、本編にてお返事させていただきます。

id:imihito さんからのコメント

Split関数のように配列が返される関数は、
同じ型の動的配列の変数へ直接代入できます（上書き）。

なんと！　そうだったのか！

んで、やってみた。

リスト１
Public Sub hageHoge()
  Dim hageArray() As String
  hageArray() = Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")
  Dim i As Integer
  For i = LBound(hageArray) To UBound(hageArray)
    Debug.Print hageArray(i)
  Next
  hageArray() = Split("アホ,ボケ,クズ", ",")
  For i = LBound(hageArray) To UBound(hageArray)
    Debug.Print hageArray(i)
  Next
End Sub
実行結果

f:id:akashi_keirin:20170429063906j:plain

おおっ！　ホントだ！

たしかに上書きだ。

id:imihito さん、ありがとうございました！

thom (id:t-hom)さんからのコメント

動的型付け言語をやってみると、あんまりVariant気にならなくなりますよ。RubyとかPHPとかJavaScriptとか。

まさにそのJavaScriptで遊んでいたときに、

何だよ！　何でもかんでも「var」で済ませるのかよ！
雑やのー！

と思ったことがありますｗ

でも、それに馴染んだらそんなに気にならなくなるかもしれませんね。

静的型付けって所詮は安全機構なので、コードが捻じ曲げてまでそれにこだわる必要はない

これはホントにおっしゃるとおり。プログラミングする側がちゃんと押さえるべきところを押さえていたら何ら問題ないわけで、コード捻じ曲げてまでこだわるというのは、それこそ本末転倒ですね。

今回のまとめ

  • Split関数の戻り値はいきなり配列変数にぶち込んでおK
  • 言語仕様の便利なところは理解した上でこだわりなく使う

達人の皆様のアドヴァイス、ただの素人のワタクシには本当にありがたいものです。

御礼が遅れてすみませんでした。

@akashi_keirin on Twitter

配列関係の覚書

VBA覚書

Split関数（VBA）など配列回りの覚書

Split関数

Split関数は、


Split(文字列, デリミタ)

とすれば、第１引数の文字列を、第２引数のデリミタで区切った文字列を配列にして返してくれるとっても便利な関数。

んで、めちゃくちゃ基本的なことなんだが、

配列であるからには添え字は0スタート

なんである。

ところが、素人の悲しさ。こういう当たり前のことがしばしばあやふやになるｗ

というわけで、覚書として残しておこう。

Split関数の使用例

リスト１
Public Sub testHage1()
  Dim hageArray As Variant    '……(1)'
  hageArray = Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")    '……(2)'
  Debug.Print LBound(hageArray)    '……(3)'
  Debug.Print UBound(hageArray)    '……(4)'
  Dim i As Integer
  For i = LBound(hageArray) To UBound(hageArray)    '……(5)'
    Debug.Print hageArray(i)
  Next
End Sub

(1)の

Dim hageArray As Variant

は、Split関数の返り値（配列）を受け取るための変数。配列の要素数が分からないのでVariant型にしている。

(2)の

hageArray = Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")

で、Splitを実行。返り値を変数hageArray（なんちゅう名前や……）にセットしている。

(3)、(4)の

Debug.Print LBound(hageArray)
Debug.Print UBound(hageArray)

で、LBound及びUBound関数を使って最小の添え字と最大の添え字をイミディエイトに表示するようにしている。

(5)の

For i = LBound(hageArray) To UBound(hageArray)
  Debug.Print hageArray(i)
Next

では、Forループを使って配列hageArrayの全ての要素をイミディエイトに表示するようにしている。

実行結果

f:id:akashi_keirin:20170423110156j:plain

この通り、配列の添え字は「0」～「3」ですね。当たり前だけど。

Variant型の使用を避ける

Variant型が嫌いです。なんか、すっげえ雑な対応のような気がするのです。

そこで、考えた。

Split関数の返り値をUBoundに突っ込んでその数でReDimすりゃいいんじゃね？

と。

で、やってみた。

リスト２
Public Sub testHage2()
  Dim hageArray() As String    '……(1)'
  ReDim hageArray(UBound(Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")))    '……(2)'
  hageArray() = Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")    '……(3)'
  Debug.Print LBound(hageArray)
  Debug.Print UBound(hageArray)
  Dim i As Integer
  For i = LBound(hageArray) To UBound(hageArray)
    Debug.Print hageArray(i)
  Next
End Sub

(1)では、リスト１と異なり、

Dim hageArray() As String

String型の配列として正々堂々と（？）変数を宣言。

(2)では、

ReDim hageArray(UBound(Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")))

Split関数の返り値をUBound関数の引数にぶち込んで配列変数hageArray()をReDim。

んで、(3)の

hageArray() = Split("ち～んｗ,ウホッｗ,ウマーｗ,アヒャｗ", ",")

で配列hageArrayに要素をぶち込んでいる。

実行結果

f:id:akashi_keirin:20170423110156j:plain

ほれ、同じ結果になった。

・・・・・・

でもねえ・・・・・・。

なんて無駄なコードなんだ!!!!!!!!

まとめと感想

まとめ
  • Split関数でできた配列の添え字は、「0」～「要素数-1」です
  • LBound関数の返り値は、配列の添え字の最小値です
  • UBound関数の返り値は、配列の添え字の最大値です
  • 配列用の変数をReDimするときは、添え字の最大値で行いましょう
感想

まだまだ分かっていないことが多いなあ。

Thunderbirdメール自動作成マクロで複数アドレス指定に対応するのは簡単だった

VBA覚書 Thunderbird連携

Thunderbirdで複数の宛先を指定するのは簡単だった

Shell関数でThunderbirdのメールを作成する

このときにも紹介したが、VBAでThunderbirdのメールを作成するには、次のようなコードを書けば良い。

リスト１
Shell "Thunderbird実行ファイルのフルパス  -compose _
to=送信先メールアドレス, _
cc=CCアドレス, _
subject=メール件名, _
body=本文文字列, _
attachment='添付ファイルフルパス'"

普通、複数の宛先を設定するときには「,」（半角カンマ）でアドレスを区切ったら良さそうなものなんだが（実際、LotusNotesの場合はそれでうまく行く）、Thunderbirdだとうまく行かない。

実験

リスト２
Public Sub testHoge()
  Dim tbPath As String
  tbPath = "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird.exe"
  tbPath = """" & tbPath & """ -compose """
  Dim mailTo As String
  mailTo = "hoge@foo.bar.jp,fuga@foo.bar.jp"    '……(1)'
  Dim mailSubj As String
  mailSubj = "ち～んｗ"
  Dim mailBody As String
  mailBody = "ち～んｗ"
  Shell tbPath & _
    "to=" & mailTo & "," & _
    "subject=""" & mailSubj & """," & _
    "body=""" & mailBody & """"
End Sub

たとえば、(1)のように送信先アドレスに半角カンマ区切りで複数のアドレスを与えたつもりでも、これを実行すると、

f:id:akashi_keirin:20170423074123j:plain

こうなる。一つ目のアドレスにしか反応しとらん。

対応

あれこれとggっていて、このページにたどりついた。

カンマではなく、セミコロン　";" で連結するのでは？

ということだったので、そうしてみた。

リスト３
Public Sub testHoge()
  Dim tbPath As String
  tbPath = "C:\Program Files (x86)\Mozilla Thunderbird\thunderbird.exe"
  tbPath = """" & tbPath & """ -compose """
  Dim mailTo As String
  mailTo = "hoge@foo.bar.jp;fuga@foo.bar.jp"    '……(1)'
  Dim mailSubj As String
  mailSubj = "ち～んｗ"
  Dim mailBody As String
  mailBody = "ち～んｗ"
  Shell tbPath & _
    "to=" & mailTo & "," & _
    "subject=""" & mailSubj & """," & _
    "body=""" & mailBody & """"
End Sub

変えたのは(1)のところのみ。「,」（半角カンマ）を「;」（半角セミコロン）に変えただけ。

実行結果

コードを実行してみると、

f:id:akashi_keirin:20170423074127j:plain

おおっ！　ちゃんと２箇所あてのメールになっとる！

感想

めちゃくちゃ簡単でした。

幸せというものは、案外足下に転がっているものなんだなあ。。。（『青い鳥』風）

これで、このときのThunderbirdメール作成メソッドで、メールアドレスを渡す部分

Shell thunderbirdPath & _
    "to=" & mailTo_ & "," & _
    "cc=" & CC_ & "," & _
    "bcc=" & BCC_ & "," & _
    "subject=""" & mailSubject_ & """," & _
    "body=""" & strBody & """," & _
    "attachment=""" & strAttFile & """"
End Sub

を、

Shell thunderbirdPath & _
    "to=" & Replace(mailTo_, ",", ";") & "," & _
    "cc=" & Replace(CC_, ",", ";") & "," & _
    "bcc=" & Replace(BCC_, ",", ";") & "," & _
    "subject=""" & mailSubject_ & """," & _
    "body=""" & strBody & """," & _
    "attachment=""" & strAttFile & """"
End Sub

に変えるだけで複数アドレス指定に対応できるなあ。

VBAで名前の定義をするといろいろ楽

VBA一般 VBA覚書

セル範囲の名前の定義をVBAでやったら便利

転記するごとにセル範囲を定義し直す

このときみたいなデータ転記系の処理をした場合、ワークシート関数のCOUNTIFなんかを使って種別ごとの数を勘定したい、ということがよくある。

しかしながら、転記件数が変化する場合、COUNTIFの第１引数（範囲）が変化することになる。

だからといって、毎回転記処理が終わってから手動でCOUNTIFをセットするというのもマヌケな話。

そこで、

抽出するごとにCOUNTIFの第１引数になるセル範囲に名前を定義すりゃいいじゃん！

と考えた。

方針

手順は次の通り。

  1. まず、対象となるセル範囲に「StyleRangeForCount」と名前を付けておく
  2. VBAで、一旦「StyleRangeForCount」と名付けられたセル範囲のNameプロパティをDeleteする
  3. 新たにCOUNTIFの第１引数にしたいセル範囲を取得する
  4. 3.で取得したセル範囲のNameプロパティに「StyleRangeForCount」という名前をセットする

このようにしておいて、COUNTIFの第１引数を「StyleRangeForCount」にしておけば、転記結果が変化してもCOUNTIFの第１引数が変化に追随してくれるはずだ。

リスト１
Public Sub renameRange()
  Set styleSh = ThisWorkbook.Worksheets("戦法別")
  Dim objRange As Range
  Dim lastRow As Integer
  Range("StyleRangeForCount").name.Delete    '……(1)'
  With styleSh
    lastRow = .Cells(Rows.Count, 1).End(xlUp).Row
    Set objRange = Range(.Range("D3"), _
                         .Range("D" & lastRow))    '……(2)'
  End With
  objRange.name = "StyleRangeForCount"    '……(3)'
End Sub

実際には、もう少し複雑なコードを書いたんだが、説明のために単純化したコードを載っけている。

まず、(1)の

Range("StyleRangeForCount").Name.Delete

で、もともとの「StyleRangeForCount」という名前をDelete。

次に、(2)の

Set objRange = Range(.Range("D3"), .Range("D" & lastRow))

で、新たにできたCOUNTIFの第１引数にすべき範囲を変数objRangeにセット。

最後に、(3)の

objRange.name = "StyleRangeForCount"

で新たにできたCOUNTIFの第１引数にすべき範囲に「StyleRangeForCount」と名前を付けている。

実行

f:id:akashi_keirin:20170422215310j:plain

ちょっと分かりにくいかも知れないが、最初は、Ｄ３～Ｄ37のセル範囲に「StyleRangeForCount」という名前が定義されている。

f:id:akashi_keirin:20170422215316j:plain

転記元データをちょっといじくって、失格選手（ｗ）を増やし、再度転記処理を行ってみる。

f:id:akashi_keirin:20170422215327j:plain

当然、転記される件数が減るので、こんな状態になる。依然、「StyleRangeForCount」と名付けられたセル範囲はＤ３～Ｄ37のまま。

ここで、リスト１を実行。

f:id:akashi_keirin:20170422215344j:plain

セル範囲Ｄ３～Ｄ37から名前がぬぐい去られていることがお分かりだろうか。

んで、セルの選択範囲をＤ３～Ｄ27に変えると、

f:id:akashi_keirin:20170422215406j:plain

ほれ、「StyleRangeForCount」という名前が定義されている。

f:id:akashi_keirin:20170422215417j:plain

こんなふうに、COUNTIFの第１引数を「StyleRangeForCount」にしておくと、

失格者を増やす前

f:id:akashi_keirin:20170422215424j:plain

失格者を増やした後

f:id:akashi_keirin:20170422215433j:plain

と、COUNTIFの結果が転記結果に追随していることが分かる。

感想

手作業で名前の定義や編集・削除を行うのはかなりメンドクサイんだけど、VBAでやると簡単・便利だと思いました。

ActiveWindowプロパティでちょっとハマる……

VBA一般

ActiveWindowプロパティの怪

Excel2010でのエラー

職場のPCはOffice2010なんだが、妙なエラーが出た。

ThisWorkbookモジュールに仕込んだWorkbook_Openイベントマクロでの話。

データを集約するマクロを作っていて、データ集約が終わったら、マクロを仕込んだブックから、不要なシートを削除して新しいブックとして保存するようにしていると思ってください。

データ集約用本体のブックと、新たにできあがった集約データ入りのブックは、シートの数が異なることになるので、ブックオープン時に処理を切り替えるようにした。

リスト１
Private Sub Workbook_Open()
  If ThisWorkbook.Worksheets.Count = 2 Then
    ActiveWindow.DisplayHeadings = True    '……(1)'
    Exit Sub
  End If
  '通常の処理'
       ・
       ・
       ・
End Sub

たとえばこんな感じ。

オープン時にシートの数が「2」だということは、データ集約済みのブックだということになるので、(1)の処理をしてプロシージャを抜けるようにしたわけです。

で、これを職場のPCで、

他のブックが開いている状態で

実行すると、

オブジェクト変数または With ブロック変数が設定されていません。

というエラーが出た。

単独で開くときにはエラーにならないのに。

対応

「デバッグ」をクリックしてVBE上で確認すると、リスト１の(1)のところ、

ActiveWindow.DisplayHeadings = True

がハイライトされている。

んで、「ActiveWindow」が「Nothing」になっているらしい。

わけわからん。

で、リスト１の(1)を

Application.Windows(1).DisplayHeadings = True

にしたら直った。

分からないこと

  • なぜ「ActiveWindow」が「Nothing」になってしまうのか。「Workbook_Open」が実行されているということは、そのブックが「ActiveWindow」になっているはずなのに。
  • しかも、この現象は2013では再現できなかった。

納得いかないぜーーー！

データ抽出用クラスを作る

小さなクラス ちょい書きマクロ VBAクラス・モジュール VBA自作クラス

データ抽出用のクラス

AdvancedFilterメソッドを気軽に使う

あんまり役に立たないと思うけど、ちょっと作ってみた。

準備として、

f:id:akashi_keirin:20170416102826j:plain

データ抽出元のシートを用意。

f:id:akashi_keirin:20170416102837j:plain

こんなふうに抽出条件設定用の表を作り、

f:id:akashi_keirin:20170416102833j:plain

セル範囲に名前を付けておく。

ちなみに、抽出条件は、ヨコの並びがAND、タテの並びがOR条件。

この画像だと、「戦法が先捲か捲先で、80期未満の選手」を抽出することになる。

競輪を例にしているだけに、タテだのヨコだの言ったらややこしいな。

f:id:akashi_keirin:20170416102843j:plain

抽出先のデータラベルもこのように準備。同じく、名前を付けておく。

クラスモジュールのコード

クラスモジュールを挿入して、オブジェクト名は「DataExtractor」にした。

リスト1-1　フィールド・アクセサ部分
Option Explicit
'Fields;Module Level Variables'
Private dataSource_ As Range    '……(1)'
Private rangeOfCriteria_ As Range
Private copyTo_ As Range
'Accessor;Properties'
Public Property Get extractedRange() As Range    '……(2)'
  Set extractedRange = copyTo_.CurrentRegion
End Property

Public Property Get dataCount() As Long
  dataCount = extractedRange.Rows.Count - 1
End Property

珍しく、仮変数とPropertyプロシージャの名前（？）が一致していない。

(1)からの３行、

Private dataSource_ As Range
Private rangeOfCriteria_ As Range
Private copyTo_ As Range

は、AdvancedFilterメソッドの実行に必要なオブジェクトや引数。

従って、メソッド実行時に引数として渡せば良いし、後で取得することもないだろうから、変数のみにした。

逆に、(2)からの６行、

Public Property Get extractedRange() As Range
  Set extractedRange = copyTo_.CurrentRegion    '……(a)'
End Property
Public Property Get dataCount() As Long
  dataCount = extractedRange.Rows.Count - 1    '……(b)'
End Property

は、抽出実行後に自ずと決まるものなので、仮変数は必要ないと思った。

(a)は、抽出先のセルのCurrentRegionプロパティを取得することで、抽出されたデータ範囲をセットしている。

(b)は、(a)で決まった抽出データの範囲の行数を取得し、１を引くことで、抽出されたデータの件数をセットしている。

リスト1-2　メソッド部分
Public Sub extractData(ByVal dataSource As Range, _
                       ByVal rangeOfCriteria As Range, _
                       ByVal copyTo As Range)    '……(1)'
  Set dataSource_ = dataSource    '……(2)'
  Set rangeOfCriteria_ = rangeOfCriteria
  Set copyTo_ = copyTo
  copyTo_.CurrentRegion.Offset(1, 0).Clear    '……(3)'
  dataSource_.AdvancedFilter _
                Action:=xlFilterCopy, _
                criteriaRange:=rangeOfCriteria_, _
                CopytoRange:=copyTo_    '……(4)'
  extractedRange _
    .Offset(1, 0) _
    .Borders.LineStyle = xlNone    '……(5)'
End Sub

メソッドはとりあえず一つだけ。

(1)の

Public Sub extractData(ByVal dataSource As Range, _
                       ByVal rangeOfCriteria As Range, _
                       ByVal copyTo As Range)

でお分かりのように、３つの引数を受け取って実行する。

  • 第１引数は抽出元のデータ範囲
  • 第２引数は抽出条件のデータ範囲
  • 第３引数は抽出先のデータラベルの範囲

それぞれの引数の役割は以上の通り。

(2)からの３行、

Set dataSource_ = dataSource
Set rangeOfCriteria_ = rangeOfCriteria
Set copyTo_ = copyTo

は、引数をクラス内の仮変数に代入している。

(3)の

copyTo_.CurrentRegion.Offset(1, 0).Clear

によって、一旦抽出先の表をクリア。データラベルを消さないようにOffsetしている。

(4)の

dataSource_.AdvancedFilter _
              Action:=xlFilterCopy, _
              CriteriaRange:=rangeOfCriteria_, _
              CopytoRange:=copyTo_

これがAdvancedFilterメソッドの本体。これで抽出が行われる。

あと、(5)の

extractedRange _
    .Offset(1, 0) _
    .Borders.LineStyle = xlNone

は、抽出されたデータ範囲の罫線消去。

別になくても困らないとは思うけど、罫線が残ったままだとブサイクなのでこうした。

DataExtractorクラスを使う

宣言セクションにEnumを追加する。

リスト2-1　標準モジュールの宣言セクション
Public Enum extractCol
  rcName = 1
  rcPhonetic
  belongsTo
  graduateTerm
  rcGrade
  rcClass
  rcStyle
  isEliminated
End Enum
リスト2-2　実行用コード
Public Sub test03()
  With ThisWorkbook
    Set orgSh = ThisWorkbook.Worksheets("選手データ")
    Set extractSh = ThisWorkbook.Worksheets("抽出")
  End With
  Dim dtExtractor As DataExtractor    '……(1)'
  Set dtExtractor = New DataExtractor
  With dtExtractor    '……(2)'
    .extractData orgSh.Range("A1").CurrentRegion, _
                 Range("RangeOfCriteria"), _
                 Range("CopyToRange")    '……(3)'
    MsgBox "全 " & .dataCount & " 名、抽出完了。", vbInformation
    Dim str As String
    str = "抽出したのは、" & vbCrLf & vbCrLf
    If .dataCount = 0 Then
      MsgBox str & "……て、誰もおらんやないかーーーーい！", vbCritical
      Exit Sub
    End If
    Dim i As Integer
    Dim flg As Boolean
    For i = 1 To .dataCount
      If i > 5 Then
        flg = True
        Exit For
      End If
      str = str & extractSh.Cells(i + 1, extractCol.rcName).Value _
            & "選手、" & vbCrLf
    Next
  End With
  str = Left(str, Len(str) - 1)
  If flg = True Then
    str = str & vbCrLf & "……て、人数多すぎるんじゃぼけーーー！" & _
            vbCrLf & "やってられっか！"
    MsgBox str
    Exit Sub
  End If
  MsgBox str & "です。"
End Sub

(1)からの２行、

Dim dtExtractor As DataExtractor
Set dtExtractor = New DataExtractor

は、インスタンス用の変数の宣言とNewによるインスタンス化。

(2)の

With dtExtractor

は、おなじみの記述。

(3)で抽出実行。セル範囲に名前を付けているので、簡単に指定できる。

以下のコードは単なるギミック。よって説明は省略。

実行結果

このコードを実行すると、

f:id:akashi_keirin:20170416102848j:plain

f:id:akashi_keirin:20170416102853j:plain

無事抽出処理ができた。

このままだと、まあ直接AdvancedFilterメソッド使った方が楽なので、改良が必要かな。