VBAはOfficeの二次開発に使用できることが誰も知っています。VBAを活用できれば、仕事の効率は大幅に向上させることができます。次には読者の皆さんと共有したいのは、VBA自体ではなく、VBAコードで常用な最適化方法です。VBAの初心者や常用者もご覧下さい。少しのインスピレーションやリファレンスができれば、嬉しいです。
目次
組み込み機能をできるだけ呼び出します。つまり、システムによって提供されるプロパティ、メソッド、および関数を使用します
特定の機能を使用する必要がある場合、excelに慣れていないため、より複雑な操作を行いがちですが、Excelはすでにこのような機能を提供しているかもしれません。時々1つの関数で解決できるのに、その関数が知らなくて、他の方法で時間がかかって問題を解決しましたが、組み込み機能より面倒で、効果もよくありません。
考えてみればわかります。あなたがMicrosoftソフトウェアを使用して、プロの開発者より物凄い機能を開発できますか（天才は論外です）なので、組み込み機能があれば、できるだけ使用してください。
例外の場合もあるかもしれません。例えば、
sub 配列の最大値を取得する() 'VBAメソッド
dim arr(),temp as byte,i,j,tim as long
for j=1 to 100000 'より良い比較を行うために、100,000回にループする
arr=array(1,7,8,6,9,3,5,7,6,8,9,4,1,2,4) '配列の値
temp=arra(1) '配列にある最初の値を変数tempに割り当てる 'temp=worksheetfunction.max(arr) max関数を呼び出すが、効率が低い
for i=1 to UBound(arr) '配列内のすべての要素をループする
if arra(i)>temp then temp=arr(i) '配列内の要素が変数より大きい場合、値は変数に割り当てられる
temp
next i
next j
end sub以前、生徒や友人がVBAの問題を解決することに手伝っていた時、パソコンの先生が、生徒さんにシート関数に基づいて、コードで編集させるようなお題に遇ったことがあります。これは組み込み関数を使用せずに自分で編集する典型的な例です。 もちろん、先生の出発点は、学生が論理的に思考とアルゴリズムの理解能力を訓練することかもしれません。
オブジェクト参照の使用を最大限に抑えます
ループ内のオブジェクトへの参照を最大限に抑え、できればwith… end withステートメントでより多くを使用し、点が少なければ時間を節約できます。
変数はメモリに格納されているため、setステートメントを使用して、繰り返し参照されるオブジェクトをオブジェクト変数として設定します。
セルコメントの判断など、セルループの代わりにオブジェクトループを使用すると、commentsオブジェクトをループでコメントでき、各コメントを直接検索してから、parentでコメントされた親オブジェクトのセルを取得できます。
subコメントをループする()
dim com as comment,address asstring
for each com in activeSheet.comments
address=address&com.parent.address(0,0)&chr(10)
next
msgbox address
endsub
sub セルをループする()
dim rng as range,address as string,bl as boolean
on error resume next
for each rng in activesheet.usedrange
bl=rng.comment.visible
if err=0 then address=address&rng.address(0,0)&chr(10)
err.clear:
next rng
msgbox address
end subオブジェクトのアクティブ化と使用を減らします
selectステートメントとactivateステートメントの使用を減らす
これは通常、マクロコードを自分で記録する場合、必要ではない多数のクリックとアクティブ化アクションを記録されます。ほとんどを省略できます。(アクティブ化または使用する必要がある一部の操作を除く)
画面の更新をオフにします
プログラムの実行中には、画面が明滅しています。これは、コンピューターのパフォーマンスに影響し、目がくらむことも可能です。これより、基本的にプログラマーはデフォルトでこれを重視します。(特に実行時間が長い場合です。状況に応じて実行時間が短い場合は画面の更新をオフしません。)
Application.ScreenUpdating=False ‘通常はループステートメントの前に配置されます… Application.ScreenUpdating=True
変数を使用します
- 必須の変数宣言（Option Explicit）：設定でオンにします。時間を節約でき、手動で入力する代わりに自動的に引き出します。
- 変数をできるだけ明示的に宣言します。事前接続-early binding。object型を定義することは事後接続(late binding)に属します。通常、事前に定義することで時間を節約できますが、互換性の問題が頻繁に発生し、事後に定義するのは適応性が向上します。
- 適切な変数の型を選択します。Varian型はデフォルトですが、メモリ消費量が増えるため、明確に指定する方がいいです。
- 変数を有効に活用します。値または文字列が反復に出る場合、値を置き換えて、定数を宣言して直接呼び出すようにします。変更する時も、複数の場所を変更せずに実行できるように変更します。
ここまで強調したいことがあります。効率を上げるために、コードを書くときに省略形や暗黙の宣言を使用することを好む人がいます。これより、入力が簡単になりますが、コードに問題が発生すると、デバッグやエラーの発見は面倒になります。全体的から言うと、効率的ではありません。そのため、よいプログラミングの習慣を身に付けることをお勧めします。変数を明確に記述し、頻繁にコメントすれば、コードは読みやすくなり、訂正も容易になります。
$付きの文字列処理関数をうまく利用します。
VBAには、$がある場合とない場合の2つの文字列処理関数があります。
$のない関数が文字列を処理する場合、VBAは文字列をvariantデータとして計算し、$のある関数が使用される場合、それはstringとして処理されます。 前者はより多くのメモリを必要とします。
ループの増分値をうまく利用して、ループの増分値を減らします
奇(偶)数を判定する場合、増分値が指定すれば、判定の構文を省略して速度を上げることができます。
ループと言えば、類似する例があります。
つまり、特定の範囲を検索する場合、ターゲット範囲(Target)と使用範囲(UsedRange)の交差範囲(Intersect)を使用してループできれば、ループ範囲が減少し、一部の空白のセルの比較が回避されます。
セルオブジェクトの代わりに配列を使用します
中間プロセスを配列に格納し、メモリから直接呼び出して、最後に読み取ります。
これが最も使用されて、効果がはっきり見え、セルの相互作用がないため、便利です。
もちろん、辞書は特別な配列の形式として、この操作を行います。
例：何千人の中、不合格の成績を[不合格]にマークします
sub 60点未満の成績にコメントを付ける()
Dim i as integer,tim as long,arr1(),arr2()'成績に配列変数を割り当てるarr1=range([b2],cells(rows.count,2).end(xlup))
'2番目の配列変数の大きさをリセットします
ReDim arr2(1 to UBound(arr1),1 to 1)
'配列をループするfor i=1 to UBound(arr1)
if arr1(i,1)<60 then arr2(i,1)="不合格"
next i
'2番目の配列の値をセルに割り当てます
range([c2],cells(rows.count,2).end(xlup).offset(0,1))=arr2
end subUDFを繰り返し呼び出す場合のみ使用します
UDFはuser-defined-functionの略称で、ユーザーが定義する関数のことです。
プログラムを効率的に実行するために、通常、長いコードをいくつかのサブコードまたはカスタム関数に分割して、相互に呼び出します。 便利にデバッグと相互参照できる目的を達成するためです。 UDFは複数の呼び出しに適する場合に使用します。それ以外の場合、呼び出し速度は実行速度よりも遅いです。
比較的に短く、1回しか使用されないfunctionの場合、UDFを呼び出すのではなく、サブコードに直接組み込むことをお勧めします。
値または属性を変更しないステートメントをループステートメントの外に置きます
這一點往往會被人忽略，可能循環的重要語句本身就一兩句，其他無關語句很多的話，在循環的時候會反覆この点はよく見落とされます。ループ自体に重要なステートメントが1つか2つがあり、他に無関係なステートメントがたくさんある場合は、ループ中にcheckが繰り返されます。プロセスには時間がかかってしまいます。
長さを計算して、セルが空白であるかどうかを判断します
初めてこれの使用を見るときは、意味が分かりませんでした。フォーラムでたくさんの人に聞いてみましたが、知りませんでした。その後、実際の操作によって意味をわかりました。これがより効率的です。(非常に理解しにくいですが、これを見つけて、すごいです。)多くの場合、lenメソッドは他のより難しい問題を解決できます。 空白のセルでない、または特定のオブジェクトを判断するなどの問題により、返す値の属性がわからない場合があります。
xx=””
xx is empty
xx is nothing
len（xx）=0のほうが簡単です。range(“a1″)=”” ‘より効率的なlenメソッドをお勧めします。len(range(“a1”))=0’.valueは省略され、rangeのデフォルト属性です。
たくさんの内容を説明しました。それでは自分の最適化結果をテストしてみましょう！テストコードは次となります。
プログラム実行時間のテストコード：
sub aaa()
dim tim as long
tim=timer '現在の時刻を取得する
for …
next
msgbox format(timer-tim,"0.00")&"秒" '報告時間を実行する
end sub通常、timer関数とtime関数には2つの方法があります。インターネット上の多くの人が違いを尋ねますが、ここで簡単に説明します。(間違った場合は、専門家からアドバイスをいただければ幸いです。)
時間が短い場合は、timer機能を使用して、秒を返します。つまり当日の深夜0時からの経過秒数を返すことと相当します。(時間が長すぎと感じ、統計が精確ではなく、プログラミングの過程から察します。)
時間が比較的長い場合は、timeを使用できます。timeは22:58:30のように記録され、つまり操作の実行時間です。
コメントを残す