インスタンスの生成について
前回は外部ライブラリを参照設定する方法を説明しました。今回は外部ライブラリのオブジェクトを使ってコーディングする方法を書いてみたいと思います。
まず、外部ライブラリのオブジェクトは参照設定するだけでは使用できないので、NEWキーワードを使ってインスタンスを生成する必要があります。インスタンスの生成というと初心者の方には分かりにくいかもしれませんが、つまり外部ライブラリにある○○オブジェクトをVBAProjectのプロシージャ側の変数（オブジェクト型）に代入する行為を指します。
少し変な例えかもしれませんが･･････、あの世（外部ライブラリ）のものをこの世（VBA）で使うには、この世で姿かたちのある変数に入れてインスタンス化（実体化）する必要があるのです。
変数に入れてしまえば、あとは変数名.プロパティや変数名.メソッドのようにVBAの基本構文どおりに外部ライブラリのオブジェクトを使用することができます。
で、インスタンスを生成する方法ですが、実際には大きく分けて3通りほどあるようです。先述したNEWキーワードを使用する点に関しては共通ですが。
以下に、クリップボードのデータを変数Vに格納するまでの処理を3通りのインスタンス生成方法で紹介します。
一つ目は、一番スタンダードだと思われる方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成1()
Dim myLib As Object
Set myLib = New DataObject
myLib.GetFromClipboard
V = myLib.GetText
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Set myLib = New DataObjectの行で、myLibというオブジェクト型の変数にDataObjectを代入しています（インスタンスの生成）。通常の代入と違うのはNewがついているところです。
二つ目は、変数の宣言時にNewをつけてしまう方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成2()
Dim myLib As New DataObject
myLib.GetFromClipboard
V = myLib.GetText
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Set～の代入式がなくなる分、コードが短くて済みます。ただし、インスタンスが生成されるタイミングは変数宣言時ではなく、コードの中で変数が使用されるタイミング（上の例ではmyLib.GetFromClipboardの行）になります。余談ですが、インスタンスが生成された後でSet myLib = Nothingと一旦変数を空にしても、次に変数を使用するタイミングで再びインスタンスが自動生成されます。なんだか、不死身のゾンビみたいです。
三つ目は、WithステートメントにNewをつける方法です。
－－－－－－－－－－－－－－－－－－－－－－
Sub インスタンスの生成3()
With New DataObject
.GetFromClipboard
V = .GetText
End With
End Sub
－－－－－－－－－－－－－－－－－－－－－－
Withについて補足すると、With ○○はSet 変数 = ○○と読み替えることができます。後のピリオドの前には常に変数があると思っていいでしょう。○○の頭にNewがあるとインスタンスの生成になります。余談ですが、Withで作られた仮の変数は内部的なものなので、ローカルウィンドウやウォッチウィンドウで見ることができません。End Withで仮の変数は空になるので、インスタンスが使用できるのはWith～End Withの間だけです。
以上の3つは、実行速度で微小な違いはあると思いますが、インスタンスが生成されるタイミング、インスタンス開放後の違い（再度、自動生成されるか否か）、インスタンスを使用できる範囲などに注意すれば、あとはお好みでいいと個人的には思います。
外部ライブラリの参照設定について
当ブログでちょくちょく外部ライブラリ（Microsoft Forms 2.0 Object Libraryなど）を使用したコードを掲載していますが、今回は外部ライブラリの参照設定について説明したいと思います。
まず、外部ライブラリとは何ぞや？ということですが、ざっくり言うと「VBAには無い機能（オブジェクト）」を収めたファイル（拡張子が.dllや.old.やtlbのようなファイル）ということなります。
VBAがそのような外部ライブラリを参照しますよ。ということをProjectごとに設定しなければいけない訳です。
外部ライブラリにはどのようなものがあるか例を挙げると、以下のようなものがあります。※カッコ内は実際のファイル名。xはバージョンが入ります。
・ファイルやフォルダー、ドライブを操作するためのFSOオブジェクト
Microsoft Scripting Runtime(scrrun.dll)
・外部データベースを操作するためのADOオブジェクト
Microsoft ActiveX Data Objects x.x Library(msado xx.dll)
・正規表現を使用するためのRegExpオブジェクト
Microsoft VBScript Regular Expressions x.x(vbscript.dll)
・クリップボードとテキストデータのやり取りを行うDataObjectオブジェクト
Microsoft Forms 2.0 Object Library(FM20.DLL)
その他、たくさんのライブラリがあるようですが、私のような素人にはすべてを把握することはできません。
で、本題の参照設定の方法ですが･･･参照設定するProjectを選択した状態で「ツール」→「参照設定」を選択します。
参照設定したいライブラリ名にチェックを入れ、OKを押します。
参照設定したいライブラリ名が一覧にない場合は、「参照」をクリックし実際のファイル名を入力（ファイル名の一部を入力すると候補が出るのでそこから選択してもいい）し、「開く」を押します。
ネットに転がっている参照設定ありきのVBAコードをコピペして使用するぶんには、ここまでやってもらえば使用できるようになります。
次回は、自分でコードを書く場合の外部ライブラリの使用方法について記載したいと思います。
マクロを高速化する（画面表示の更新をオフにする）
今更、私が書く必要もないかもしれませんが、マクロを高速化するために最も効果があると謳われる、
Application.ScreenUpdating プロパティ
の設定について紹介しておく必要があろうかと思います。
プロパティの設定はTrueかFalseの2通りです。
Application.ScreenUpdating=False ･･････画面表示の更新をオフにする
Application.ScreenUpdating=True ･･････画面表示の更新をオンにする（デフォルト）
例えば以下のようなコードがあったとします。
For i = 1 To 1000000
Range("A1").Value = Range("A1").Value + 1
Next i
これはセルA1に「1」を加算する処理を、For～Nextで百万回繰り返しています。
1が加算されるごとにリアルタイムで画面表示が更新され、デジタル時計の少数以下を見るような速さで数値がどんどん増えていきます。ですが、これは「1を加算する」という処理と同時に「画面表示を更新する」という処理を同時に行っているため、処理に時間がかかってしまいます。
では、処理中の「画面表示の更新」をやめてしまったらどうでしょうか？画面表示の処理にかかる負荷が軽減され、処理時間が短縮できるのではないでしょうか。
ということで、上記の処理を「画面表示更新あり」と「画面表示更新なし」の2バージョンでテストして時間を計測してみましょう。時間計測は処理前のTime関数の値と、処理後のTime関数の値を引き算して表示させます。
では、「画面表示更新あり」からやってみましょう。デフォルトで画面表示の更新はオンなので、何も設定する必要はありません。
－－－－－－－－－－－－－－－－－－－－－－
Sub 処理時間計測その1()
Dim i As Long
StartTime = Time
For i = 1 To 1000000
Range("A1").Value = Range("A1").Value + 1
Next i
StopTime = Time
MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
& "分" & Second(StopTime - StartTime) & "秒"
End Sub
－－－－－－－－－－－－－－－－－－－－－－
「画面表示更新あり」では3分38秒かかりました。
次に、「画面表示更新なし」でやってみましょう。処理の前にApplication.ScreenUpdating=Falseを追記します。コードの中で再度Trueに設定するか、もしくはプロシージャを抜けるまで画面表示の更新は行われなくなります。
－－－－－－－－－－－－－－－－－－－－－－
Sub 処理時間計測その2()
Dim i As Long
StartTime = Time
Application.ScreenUpdating = False '画面表示の更新をオフにする
For i = 1 To 1000000
Range("A1").Value = Range("A1").Value + 1
Next i
StopTime = Time
MsgBox "処理にかかった時間は" & vbCrLf & Minute(StopTime - StartTime) _
& "分" & Second(StopTime - StartTime) & "秒"
End Sub
－－－－－－－－－－－－－－－－－－－－－－
「画面表示更新なし」では1分18秒でした。約3分の1程度、処理時間を短縮できていることが分かります。
上記2バージョンのテストを何回かやってみましたが、ほぼ同じ結果になりました。画面表示更新なしバンザイ！
おまけ
Application.ScreenUpdatingについて、疑問点が3つほどあったので自分なりに調べてみました。
1.Application.ScreenUpdatingをTrueに戻さないままプロシージャを終了した場合どうなるのか？
ウォッチ式を使ってApplication.ScreenUpdatingの値を見てみましょう。
ここでFalseになる。
再度、プロシージャを開始するとTrueから始まる。
プロシージャの終了後に画面表示の更新が復活することからも、Application.ScreenUpdating=Falseはプロシージャ実行中のみ適用されていることが分かります。（End SubでTrueに戻る）
2.プロシージャの中で別のプロシージャを呼び出した場合はどうなるのか？
呼び出し元の状態が適用される（Trueに戻るわけではない）
3.ステップイン実行中は画面表示はどうなる？
ステップイン実行中は、Application.ScreenUpdating=Falseであっても画面表示は更新されるようです。ステップインによる動作確認中に「あれ？」と思う人が多いのではないでしょうか。
ちなみにステップイン実行中、ウォッチ式の値ではFalseになっていますが、コード中のApplication.ScreenUpdatingにカーソルを合わせるとTrueになっています。
VBAは仕事の役にたっているか？（雑記-1）
VBAの勉強を始めてはや5ヶ月近く･･････。ちょっとだけ便利なマクロを細々と作成してきました。
実際のところ仕事に役立っているかどうか？
答えは半々といったところでしょうか。何故なら仕事はEXCELオンリーではないからです。体を動かす肉体作業もあれば、お堅い決裁書を作ることもある･･････。
PCに関しては社内独自システムを使ったり、市販のソフトを使ったり、いろいろなものを使います。EXCELはその中の一つで、市販ソフトの中ではかなり使用頻度は高いけれど、ちょっとした仕事なら標準機能で事足りることも･･････。
まだ「1日かかるハズの仕事が数秒で終わった！」というような、快感は味わっていません（￣ｑ￣;）
がしかし！VBAを使い始めたことで、こっちにはVBAがあるぜ～ルーチンワークでもハローワークでも何でも来い。既存のマクロで対処できなければ、その都度新しいマクロを作ってやる･･････という心の余裕が生まれました。ヽ(・∀・)ノ
そして私は悟りを開き、職場が新たなマクロを生み出すキッカケの宝庫に見えてきたのです･･････(‐人‐)ﾅﾑﾅﾑ。アイデアはきっと無限にある筈。
マクロにパスワードを設定するには
第三者にマクロの内容を覗かれたり、変更されたりしないようにパスワードを設定することができます。
例えば、マクロを改変されたら業務に支障をきたす、マクロをパクられたくない、マクロの内容を見られるのが恥ずかしい･･････等々、人によって理由は様々だと思います。
パスワードを設定する方法は簡単です。まず、VBE（Visual Basic Edtor）を起動し、パスワードを設定したいVBAProjectを選択します。
次に「ツール」から「VBAProjectのプロパティ」を選択します。
保護タブの中にある、「プロジェクトを表示用にロックする」にチェックを入れ、パスワードを入力しＯＫを押します。
これでBookを閉じ、次にBookを開いた時からパスワードが有効になります。
パスワードを設定したVBAProjectを開こうとすると、このようにパスワードの入力を求められます。
数字を計算可能な数値に変換する
ここに外部アプリケーションが出力したEXCELファイルがあります。
SUM関数で数値を合計をしようとしたのですが、画像のように計算結果が「0」になってしまいました･･････。
結論からいうとこれはセル内にカンマが存在し文字列扱いとなっているためです。書式設定で「数値」や「通貨」などにしても計算対象になってくれません。
数値が文字列となってしまう原因は他にもありえます。
・冒頭にシングルクオートが入っている
・なんらかの原因で改行コードが入ってしまっている 等々
数字が入った列を選択した状態で、区切り位置→区切り位置指定ウィザード1/3で完了することで数字を計算可能な数値にすることができます。
･･････が、区切り位置指定ウィザードは1列ずつしか処理することができないため対象の列が多いと手間と時間がかかります。
そこでマクロの登場です。文字列となってしまっている数字の入ったセルを複数選択し、まとめて数値に変換します。シングルクオート、カンマ、余白、改行を取り除き、最後にVal関数で数値変換しています（ダメ押し）。
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Sub 数字を数値に変換()
Dim myRange As Range
For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
If myRange.Value <> "" And TypeName(myRange.Value) <> "Date" Then
'セルの値が空白,日付の場合は処理をしない
'数字以外はVal関数が0を返すので選択しないで下さい
myRange.Value = Trim(myRange.Value)
myRange.Value = Replace(myRange.Value, vbLf, "")
myRange.Value = Replace(myRange.Value, vbCrLf, "")
myRange.Value = Replace(myRange.Value, "'", "")
myRange.Value = Replace(myRange.Value, ",", "")
myRange.Value = Val(myRange.Value)
End If
Next myRange
End Sub
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。
数式を相対参照にする
今回は絶対参照になっている数式を相対参照にします。
数式内のセル参照を絶対参照⇔相対参照に変更するには･･････数式内の変更したい部分にカーソルを合わせてF4キーを押せば、以下のように順番に切り替わっていきます。
行列とも絶対参照→行のみ絶対参照→列のみ絶対参照→行列とも相対参照（に戻る）
でも、変更したい数式が何十個、何百個とあったらこんなことを手動でいちいちやってられません。マクロで一括変更しちゃいましょう。
構文や、引数については過去記事を参照下さい。
以下はApplication.ConvertFormulaメソッドを使った簡単なプログラムです（選択範囲内の数式を行列とも相対参照にします）
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
Sub 数式を行列とも相対参照にする()
Dim myRange As Range
For Each myRange In Selection.SpecialCells(xlCellTypeVisible)
If myRange.Value <> "" Then 'セルの値が空白の場合は処理をしない
myRange.Formula = Application.ConvertFormula(Formula:=myRange.Formula, _
FromReferenceStyle:=xlA1, ToReferenceStyle:=xlA1, ToAbsolute:=xlRelative)
End If
Next myRange
End Sub
＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊＊
※SubからEnd Subまでをコピーし、標準モジュール等に貼り付けて使用して下さい。なお、マクロで実行した処理は「元に戻す」ことができません。実行前に一旦保存しやり直しのできる状態にしておいて下さい。標準モジュールにコードを貼り付けてマクロを使用する方法はこちら。
