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マクロを手で１から作成することは少ないです。
マクロの記録で記録したものを汎用的に使えるように修正することが多い。
マクロ記録で記録されたマクロを流用する際に気をつけること
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
改ページが入るなら、１ページ全体を名前定義しておいて、Range(“FirstPageArea”).Rows.Count で１ページ分の行数を足せば良い。
上記の例は、セットしたり取得する数が少ない場合。
数万行、数千列に値を一気にセットしたり取得するのは配列で操作する
このあたりは以下参照
http://officetanaka.net/excel/vba/speed/s11.htm
一部抜粋するとこんな感じ
Sub Sample()
Dim C As Variant
C = Range("A1:A10000")
Range("B1:B10000") = C
End Sub
他にもあるけど、またそのうち書きます。
コメント