新人EXCELマクロプログラマに送る、これはこうしましょう的な独り言

EXCELは表計算ソフトですが、職場によってはEXCELで仕様書を書いたり、EXCELで進捗管理をしたり、EXCELで方眼紙を作ったり、EXCELで勤怠管理することがあると思います。

職場でEXCELを使う際、EXCELのマクロを覚えておくと作業効率が上がります。
そこでEXCELマクロの開発現場から、これからマクロを習得する際、ちょっとした事だけど気をつけると開発が楽になると思ったことを書いていこうと思います。

EXCELマクロの予備知識

  • EXCELマクロはVBAという枠組みの中で実装されています。
  • バージョンはいくつかありますが基本的にバージョン6.0か6.5か7.0です。
    7.0は64bit対応というだけで実質VBA6.5です。
  • 言語仕様はVisual Basic6.0です。
  • Visual Basic 6.0は1998年に発売された化石のような言語だけど、Excel2016に乗っているVBAもほぼ同じです。
  • EXCELマクロは、「マクロの記録」を行うと、EXCEL上で操作した事がそのままVBAのマクロとして出力されます。
  • EXCELマクロを手で1から作成することは少ないです。
    マクロの記録で記録したものを汎用的に使えるように修正することが多い。

マクロ記録で記録されたマクロを流用する際に気をつけること

ActiveWorkbook、ActiveSheet、Selectionに頼らないマクロを書こう

例えばこのようなコードが該当します。
いわゆるマクロ記録で出力されたマクロにありがち。

    Range("B6:F16").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

上のマクロは With キーワードを使って以下のようにしましょう。

	Application.ScreenUpdating=False
    With Range("B6:F16")
        .Borders(xlDiagonalDown).LineStyle = xlNone
        .Borders(xlDiagonalUp).LineStyle = xlNone
        With .Borders(xlEdgeLeft)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeTop)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeBottom)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlEdgeRight)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlInsideVertical)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
        With .Borders(xlInsideHorizontal)
            .LineStyle = xlContinuous
            .ColorIndex = 0
            .TintAndShade = 0
            .Weight = xlThin
        End With
    End With
	Application.ScreenUpdating=True

セルから値を取得、セットするとき

例えば定型フォーマットが用意されていて、そこに値を手入力してマクロ側でそれを取得したり、結果を出力するようなマクロがあったとする。
発注書マクロ等でありがちな例

	TaxRatio=10
	Cells(10,30)=TaxRatio					'消費税率を取得する
	OrderCount = Cells(5,2)					'発注数量の取得

これはシート上に名前を定義して以下のように書き換えよう。
セルをクリックした時に左上に表示される、A1とかD20とかE11とか表示されるエリアに任意の名前をつける。
そこでつけた名前がVBAから参照できる。

	TaxRatio=10
	Range("TaxRatio").Value = TaxRatio		'消費税率 10%をセットする
	OrderCount = Range("OrderCount")		'発注数量の取得

こうすることによって、TaxRatioの位置がずれたとしてもEXCELシート上でカット&ペーストすればマクロ側は修正しなくても良い。

ある基準点から必ず同じ順で値を取得するとき、セットするときよくありがちな例

For i = 1 To 5
	TaxRatio = Cells(10 + i, 30)
	OrderCount = Cells(10 + i, 31)
	ItemName = Cells(10 + i, 32)
	Total = Cells(10 + i, 33)
Next

この場合は先程の名前定義で、TaxRatio1、TaxRatio2、TaxRatio3、TaxRatio4 を定義して・・・なんてやらないでね。
明細の先頭行を名前定義しておいて

Set r = Range("MeisaiStart")
For i = 0 To 4
	TaxRatio = r.Offset( i, 0).Value
	OrderCount = r.Offset( i, 1).Value
	ItemName = r.Offset( i, 2).Value
	Total = r.Offset( i, 3).Value
Next

Set r = Range("MeisaiStart")
For each v in Meisai						'Meisaiは明細が入ったコレクション
	r.Offset( i, 0).Value = v.TaxRatio
	r.Offset( i, 1).Value = v.OrderCount
	r.Offset( i, 2).Value = v.ItemName
	r.Offset( i, 3).Value = v.Total
	Set r = r.Offset(1,0)
Next

改ページが入るなら、1ページ全体を名前定義しておいて、Range(“FirstPageArea”).Rows.Count で1ページ分の行数を足せば良い。

上記の例は、セットしたり取得する数が少ない場合。

数万行、数千列に値を一気にセットしたり取得するのは配列で操作する

このあたりは以下参照
http://officetanaka.net/excel/vba/speed/s11.htm

一部抜粋するとこんな感じ

Sub Sample()
    Dim C As Variant
    C = Range("A1:A10000")
    Range("B1:B10000") = C
End Sub

他にもあるけど、またそのうち書きます。

コメント

タイトルとURLをコピーしました