追記: 更に改善版です！
フォーマットに指定した変数に対応する場所に書き込むことでセルの位置、つまり座標を意識しないでよくなるクラスを書きました。
抽象度が高まり、よいと存じます。
今回は、これを改善いたしましたので、それを記録いたします♪
ポイント
- 利用者が誤ってテンプレートの変数を削除した場合でもエラーとさせたくない。そのために、CellValues に設定していたキーに対応するテンプレートになくても無視するようにした。
- 書き込み対象ワークシートに書き込んだ後、書き込み先範囲を移動するメソッドを追加した。これにより、ループを使って同じフォーマットの複数の帳票を作成できる。
- WriteToVariableAndOffset → オフセット移動
- WriteToVariableAndDown → 下に移動
- プロパティにしなくとも問題ないものはプロパティから削除し、スリム化した。
- 省略しても可読性が維持できると思った部分は短く書き、行数を短くした。
次の点は、あえて対応いたしませんでした。
- フォーマットの変数に対応する値がコードに設定されていない場合は、テンプレートの変数がそのまま書き込み先シートに書き込まれてしまう。
- このとき、特にエラーは発生しない。
- コード側でのフォーマット変数定義漏れに気がつくチャンスが増えるメリットがある。
- また、コード側で値が取得できないことがあると予めわかっている場合は、空文字で初期化しておけば良い。
- よって、特に対処しない。
エクセル準備。Template シート
| **start | **name | |
| **today | ||
| **end |
VBA コード
Option Explicit
Private mstrClassName As String
Private mobjTargetRange As Range
' テンプレート Range.Value をコピーした 2 次元配列
Private mvntValues As Variant
Private mobjTplValuesPositions As Object
''' <summary>
''' ワークシートの変数 (Key) と、差し込む値 (Item)
''' </summary>
Public CellValues As Object
''' <summary>
''' コンストラクタ
''' </summary>
Private Sub Class_Initialize()
mstrClassName = TypeName(Me)
Debug.Print (mstrClassName & " : Constructor is called.")
Set CellValues = CreateObject("Scripting.Dictionary")
End Sub
''' <summary>
''' デストラクタ
''' </summary>
Private Sub Class_Terminate()
Debug.Print (mstrClassName & " : Destructor is called.")
End Sub
''' <summary>
''' 初期化処理を実行します。
''' </summary>
''' <param name="strTemplateWorksheet">テンプレートワークシート</param>
''' <param name="strTemplateRabge">テンプレート範囲</param>
''' <param name="strTargetWorksheet">書き込み先ワークシート</param>
''' <param name="strTargeteRabge">書き込み先範囲</param>
Public Sub Init( _
ByVal strTemplateWorksheet As String, _
ByVal strTemplateRabge As String, _
ByVal strTargetWorksheet As String, _
ByVal strTargeteRabge As String)
Debug.Print (mstrClassName & " : Init")
' 変数をセット
Set mobjTargetRange = Worksheets(strTargetWorksheet).Range(strTargetRange)
mvntValues = Worksheets(strTemplateWorksheet).Range(strTemplateRabge)
Set mobjTplValuesPositions = CreateValuesIndexesDictionary(mvntValues)
End Function
''' <summary>
''' Key が 2 次元配列の値、Item が 2 次元配列のインデックスのディクショナリを返却します。
''' Key が重複する場合は上書きします。
''' Item に格納する 2 次元配列のインデックスは Array(1, 1) 形式の配列です。
''' </summary>
''' <param name="vntTwoArray">2 次元配列</param>
Private Function CreateValuesIndexesDictionary( _
ByVal vntTwoArray As Variant) As Object
Dim objResults As Object
Set objResults = CreateObject("Scripting.Dictionary")
Dim i As Long
Dim j As Long
For i = LBound(vntTwoArray, 1) To UBound(vntTwoArray, 1)
For j = LBound(vntTwoArray, 2) To UBound(vntTwoArray,2)
objResults.(vntTwoArray(i, j)) = Array(i, j)
Next j
Next i
Set CreateValuesIndexesDictionary = objResults
End Function
''' <summary>
''' 書き込み対象ワークシートに書き込みます。
''' </summary>
Public Sub WriteToVariable()
Debug.Print (mstrClassName & " : WriteToVariable")
' Range.Value コピー配列の複製を用意し、元の配列はそのままの形で残す。
Dim vntCopied As Variant
vntCopied = mvntValues
Dim lngRow As Long
Dim lngCol As Long
Dim vntValue As Variant
Dim v As Variant
For Each v In CellValues
' 利用者が誤ってテンプレートの変数を削除した場合でもエラーとさせないために、
' CellValues に設定していた変数がない場合は無視
If mobjTplValuesPositions.Exsists(v) Then
' Range.Value コピー配列の複製への書き込み場所を取得
lngRow = mobjTplValuesPositions(v)(0)
lngCol = mobjTplValuesPositions(v)(1)
' Range.Value コピー配列の複製を CellValues に設定した値に更新
vntCopied(lngRow, lngCol) = CellValues(v)
End If
Next v
' 書き込み対象ワークシートに書き込み
mobjTargetRange = vntCopied
End Sub
''' <summary>
''' 書き込み対象ワークシートに書き込み、その後、書き込み先範囲を移動します。
''' </summary>
''' <param name="lngRowOffset">オフセットする範囲の行数</param>
''' <param name="lngColumnOffset">オフセットする範囲の列数</param>
Public Sub WriteToVariableAndOffset( _
ByVal lngRowOffset As Long, _
ByVal lngColumnOffset As Long)
Debug.Print (mstrClassName & " : WriteToVariableAndOffset")
Call WriteToVariable
Set mobjTargetRange = mobjTargetRange.Offset(lngRowOffset, lngColumnOffset)
End Sub
''' <summary>
''' 書き込み対象ワークシートに書き込み、その後、書き込み先範囲を下に移動します。
''' </summary>
''' <param name="lngRow">下に移動する行数</param>
Public Sub WriteToVariableAndDown(ByVal lngRow As Long)
Debug.Print (mstrClassName & " : WriteToVariableAndDown")
Call WriteToVariableAndOffset(lngRow, 0)
End Sub
Option Explicit
Public Sub TestVariableWriter()
' 初期化
Dim udtVw As VariableWriter
Set udtVw = New VariableWriter
Call udtVw.Init("Template", "A1:C4", "Target", "A1:C4")
' 書き込み先変数と、値を設定
udtVw.CellValues("**start") = "スタート！"
udtVw.CellValues("**name") = ""
' コードに書いた変数がフォーマットにない場合はエラーだが、対応する必要はあるか？
' → 利用者がフォーマットの変数を誤って消した場合、エラーで利用者へのフィードバックなしに止まってしまう。よって、対応した。
udtVw.CellValues("**option") = "オプション"
udtVw.CellValues("**end") = "エンド♪"
' 書き込み
udtVw.WriteToVariableAndDown(4)
' 2 回目
udtVw.CellValues("**start") = "スタート2！"
udtVw.CellValues("**name") = "名前入れる"
udtVw.CellValues("**today") = Format(Now, "yyyy/mm/dd")
udtVw.CellValues("**end") = "エンド2♪"
udtVw.WriteToVariableAndDown(4)
End Sub
おわりに
カスタマイズしているとだんだんとわかってきたことがございます。
- 書き込み先シートに予め固定文字列を入れていても、フォーマットのセルの値で上書きされてしまう。セルの値が空文字の場合は、空文字で上書きしてしまう。
- 書き込みができるのは、値のみ。文字の装飾は罫線などは、書き込み先シートに反映できない。
- 逆に、書き込み先シートに文字装飾や罫線を予め設定しておけば、そこに文字列だけ反映できる。
フォーマットシートの装飾を含めた書き込みは、難しいですの＞＜。なぜなら、Range に 2次元配列を代入して反映できるのは文字列だけで、装飾は別途方法を考えなければならないからですの。。。パフォーマンスも落ちてしまいそうです。。。
フォーマットは変数文字列だけで、書き込み先は変数以外の部分を予め用意しておく、というやり方が一番よいかしら。
もう一つ悩むのが、フォーマットは変数のみ反映するのか、フォーマットの Range 全体を反映するのか、ということですの。変数ではない固定文字列をそのまま書き込むのか、空文字もそのまま書き込むのか、決めの問題ですけれども悩んでしまいます＞＜。
どういった使い方を一番するのか、そこがポイントとなりそうですけれども、見極めきれておりません＞＜。
これからですわね♪
次のページが参考になりました♪ありがとう存じます！
以上です。