読者です 読者をやめる 読者になる 読者になる

自作クラスのプロパティに配列をセットする

VBA覚書 VBAクラス・モジュール

f:id:akashi_keirin:20170226073430j:plain

f:id:akashi_keirin:20170226073437j:plain

f:id:akashi_keirin:20170226073445j:plain

VBAを使って、ExcelからLotusNotesのメールを送るマクロ。ずいぶん前に作った素人丸出しのマクロだったから、いっそクラス・モジュールの練習も兼ねて作り直してみようと思い立った。

基本は、上の画像のようなワークシートに必要な値を入れ、B列の番号のところを選択した状態で実行するものとする。

送り手、つまりユーザの情報は、「ユーザ情報」というシートを別に作って、

f:id:akashi_keirin:20170226073813j:plain

こんな感じで管理しているものとする。

列数が多いので、まずは、標準モジュールの宣言セクションで、

Public Enum colNum
  isSent = 1
  numOf
  sendTo
  mailTo
  CC
  BCC
  mailSubj
  belongsTo
  jobTitle
  personName
  p01
  p02
  p03
  p04
  p05
  p06
  p07
  p08
  p09
  p10
  att01
  att02
  att03
  att04
  att05
  att06
  att07
  att08
  att09
  att10
  returnReceipt
End Enum

列挙体で列名を定義しておく。これで、Cellsプロパティでセルを指定するのがかなり楽になる。異様に縦が長くなるのはまあ仕方がないと割り切ろう。

で、次にクラス・モジュールで、メールそのものを表すクラスを作りたい。送信先アドレスとか、メールの件名なんかは単なる文字列だから、コンストラクタで単純に代入するだけで済むが、メール本文とか、添付ファイルのフルパスなんかは、LotusNotesメール作成時の扱い(1要素づつappendTextとかaddNewLineで書き込んでいく)からして配列として持たせておきたい。また、そうしておくことで他のメーラーThunderbirdとか)への拡張も可能だから。

今回は、配列として持たせたい3つのプロパティについて、自身の覚書も兼ねて残しておく。

ちなみに、コチラを参考にさせていただきました。ありがとうございました。

まず、クラス・モジュールに、フィールド部分を並べる。

'クラス名は"CreatedMail"としています。
Private baseCell_ As Range
Private mailTo_ As String
Private CC_ As String
Private BCC_ As String
Private mailSubject_ As String
Private belongsTo_ As String
Private jobTitle_ As String
Private personName_ As String
Private mailBody_() As String         '……(1)
Private numOfBody_ As Integer
Private attFiles_() As String         '……(2)
Private numOfAttFiles_ As Integer
Private returnReceipt_ As String
Private senderData_(1 To 9) As String '……(3)

メールが持つ属性を列挙している。1個目の「baseCell」は、マクロ実行時にユーザが選んでいるセルを格納する。B列の番号のところを選んでマクロを実行するようにし、その列のデータに基づいてメールを作成することにする。

(1)~(3)が配列にするプロパティ。(1)と(2)は、その時々で要素数が変わるので、カッコ内は空白。(3)は要素数が決まっているのでカッコ内に「1 to 9」と記述している。

  • (1)は、メール本文を格納する配列。10段落まで設定可能。
  • (2)は、添付ファイルのフルパスを格納する配列。10個まで設定可能。
  • (3)は、送信者、すなわちユーザのデータを格納する配列。これは、上の4つめの画像のとおり、項目が9つあるので、要素数を9に固定している。添え字部分を「(1 to 9)」と書く、というのが特徴的ですな。

重要なのはここから。

まずは、上記の3つのプロパティのみ、アクセサ部分のコードを挙げる。

'mailBodyプロパティ
Public Property Get mailBody(ByVal i As Integer) As String
  mailBody = mailBody_(i)
End Property
'attFilesプロパティ
Public Property Get attFiles(ByVal i As Integer) As String
  attFiles = attFiles_(i)
End Property
'senderDataプロパティ
Public Property Get senderData(ByVal i As Integer) As String
  senderData = senderData_(i)
End Property

どうやら、プロパティを配列にした場合、値を取得するためのProperty Getプロシージャに配列の添え字を渡して、その添え字に対応する要素がプロパティの値としてセットされる、という処理の流れになっているらしい。右辺にのみ添え字があるのも、そういうカラクリなんだろうな。プロパティの値が参照されたときだけ値をセットすりゃいいんだから、プロパティそのものが複数の値を配列として保持しておく必要はない、ということなんだろう。

今回は値の取得のみが可能なプロパティにしているので、Letの場合の書き方は割愛する。それはまた機会があれば……。

基本的に、この点にさえ気をつけていれば、クラスのプロパティを配列として扱うことはできそう。割と簡単なんだなー。

後は、クラス・モジュールのコードを全部載っけとこう。

Option Explicit
'クラスフィールド
Private baseCell_ As Range
Private mailTo_ As String
Private CC_ As String
Private BCC_ As String
Private mailSubject_ As String
Private belongsTo_ As String
Private jobTitle_ As String
Private personName_ As String
Private mailBody_() As String
Private numOfBody_ As Integer
Private attFiles_() As String
Private numOfAttFiles_ As Integer
Private returnReceipt_ As String
Private senderData_(1 To 9) As String

'アクセサ
Public Property Get baseCell() As Range
  Set baseCell = baseCell_
End Property
Public Property Get mailTo() As String
  mailTo = mailTo_
End Property
Public Property Get CC() As String
  CC = CC_
End Property
Public Property Get BCC() As String
  BCC = BCC_
End Property
Public Property Get mailSubj() As String
  mailSubj = mailSubj_
End Property
Public Property Get belongsTo() As String
  belongsTo = belongsTo_
End Property
Public Property Get jobTitle() As String
  jobTitle = jobTitle_
End Property
Public Property Get mailSubject() As String
  mailSubject = mailSubject_
End Property
Public Property Get personName() As String
  personName = personName_
End Property
Public Property Get mailBody(ByVal i As Integer) As String
  mailBody = mailBody_(i)
End Property
Public Property Get numOfBody() As Integer
  numOfBody = numOfBody_
End Property
Public Property Get attFiles(ByVal i As Integer) As String
  attFiles = attFiles_(i)
End Property
Public Property Get numOfAttFiles() As Integer
  numOfAttFiles = numOfAttFiles_
End Property
Public Property Get returnReceipt() As String             '……(※)
  returnReceipt = returnReceipt_
End Property
Public Property Get senderData(ByVal i As Integer) As String
  senderData = senderData_(i)
End Property

'コンストラクタ
Private Sub Class_Initialize()
  Set baseCell_ = ActiveCell                              '……(1)
  Dim n As Integer  'カウント用変数
  Dim baseRow As Long
  baseRow = baseCell_.Row
  With baseCell_.Parent
    '送信相手の基本情報を各プロパティにセット             '……(2)
    mailTo_ = .Cells(baseRow, colNum.mailTo).Value
    CC_ = .Cells(baseRow, colNum.CC).Value
    BCC_ = .Cells(baseRow, colNum.BCC).Value
    mailSubject_ = .Cells(baseRow, colNum.mailSubj).Value
    belongsTo_ = .Cells(baseRow, colNum.belongsTo).Value
    jobTitle_ = .Cells(baseRow, colNum.jobTitle).Value
    personName_ = .Cells(baseRow, colNum.personName).Value
    returnReceipt_ = .Cells(baseRow, colNum.returnReceipt).Value
    Dim i As Integer
    n = 0
    '文字列の入っている段落を数えてnumOfBodyプロパティにセット  '……(3)
    For i = colNum.p01 To colNum.p10
      If .Cells(baseRow, i).Value = "" Then                     '……(4)
        Exit For                                                '……(5)
      Else
        n = n + 1                                               '……(6)
      End If
    Next
    numOfBody_ = n                                              '……(7)
    'mailBodyプロパティに本文をセット
    ReDim mailBody_(numOfBody_)                                 '……(8)
    For i = 1 To numOfBody_                                     '……(9)
      mailBody_(i) = .Cells(baseRow, colNum.p01 + i - 1).Value
    Next
    n = 0
    '添付ファイル名の入っているセルを数えてnumOfAttFilesプロパティにセット  '……(10)
    For i = colNum.att01 To colNum.att10
      If .Cells(baseRow, i).Value = "" Then
        Exit For
      Else
        n = n + 1
      End If
    Next
    numOfAttFiles_ = n
    ReDim attFiles_(numOfAttFiles_)
    For i = 1 To numOfAttFiles_
      attFiles_(i) = .Cells(baseRow, colNum.att01 + i - 1).Value
    Next
  End With
  'ユーザ情報をsenderDataプロパティにセット                                 '……(11)
  For i = 1 To 9
    senderData_(i) = ThisWorkbook.Worksheets("ユーザ情報").Cells(i, 2).Value
  Next
End Sub

一応、コードの解説。

  • (1)で、基準となるセルをプロパティにセット。以後、「インスタンス.baseCell」で取得できる。
  • (2)は、単純に代入するだけのプロパティ群。これは説明不要だと思う。
    ※returnReceiptプロパティがString型なのに注意。後で説明する。
  • (3)では、「本文(1行目)」~「本文(10行目)」のセル(K~T列)のうちいくつのセルに文字列が入っているのかをカウントしている。
  • Forループで本文の入っているセルを左から調べていく。(4)の条件は、「セルが空白ならば」。
  • (4)の条件を満たしていれば、すなわち、空白セルに当たったら、(5)でループを抜ける。
  • (4)の条件を満たしていなければ、変数nをインクリメントしてループ。
  • (7)まで来たら、変数nには文字列の入ったセルの数が格納されているはずなので、numOfBodyプロパティに値をセットする。
  • これでmailBodyプロパティの要素数が確定しているので、(8)でReDimする。
  • (9)で、Forループを用いて配列に要素を格納していく。
  • (10)では、mailBodyプロパティと同様にattFilesプロパティをセットしていく。
  • (11)で、同じようにsenderDataプロパティもセットする。

とりあえずこれで、コンストラクタまではできあがったことになる。

ひとまず挙動を確かめるために次のコードを標準モジュールに書く。

Public cm As CreatedMail
Sub test()
  Set cm = New CreatedMail        '……(1)
  Dim i As Integer
  For i = 1 To cm.numOfBody
    Debug.Print cm.mailBody(i)    '……(2)
  Next
End Sub

コードの説明。

  • (1)でCreateMailクラスのインスタンスを生成。
  • (2)では、Debug.Printを使って、Forループで

実行結果は、

f:id:akashi_keirin:20170226222310j:plain

ほれ、この通り。mailBodyプロパティに格納した各要素が全部順番にイミディエイト・ウィンドウに表示されている。

とりあえず、これでメールを作るための材料はひととおりクラスに持たせることができた。

後は、LotusNotesなり、Thunderbirdなり、メーラーに合わせてメールを作成するメソッドやクラスを書いていったらいいと思う。

あ、そうそう。リスト中の(※)のところ、returnReceiptプロパティをString型にしているのにはわけがあるのです。

コチラに「受信者が文書を開いたときに開封確認を送る場合は 1 を使用します。」だなんて書いてあるもんだから、てっきり

wkNDoc.ReturnReceipt = 1

と書いたら「受信確認あり」になると思うじゃないですか!

ところが、このように書くと、「送信オプション」の「受信確認」欄に不自然に「1」が埋まっているだけで、「受信確認あり」になってくれなかったんです。あまりにも謎現象だったので、しばらく放置していたのですが、

まさか、「1」とか「0」とかって、文字列じゃないよね?

と実験してみたらアンタ、

アッサリできちまった

じゃねーの!!!!!!!!

もし、同じ悩みを抱えている人がいたら、参考にしてください。

@akashi_keirin on Twitter