RangeクラスのSelectでエラー

VB2005でExcelを動かしていると、最後で落ちる。どうやらRangeクラスのSelectで落ちている。

最後に私はA1のセルを選んで終了させたいのよ…！と思ってウロウロしていたら、WorkSheetをSelectしてからRangeのSelectを行わないとダメのようです。Pasteとかと一緒ですね。

String型の連結-VBA編

今回は「VB6.0やVBAだとこういう書き方もアリなんだー」という、個人的には目から鱗ネタです。下記のソース、(1)と(2)、どちらが速いか？というわけですね。

(1)

For i = 0 To lngMax
    strText1 = strText1 & "あいうえおかきくけこさしすせそ"
Next

(2)

For i = 0 To lngMax
    strText2 = strText2 & "あいうえおかきくけこさしすせそ"
    If Len(strText2) > 3000 Then strText1 = strText1 & strText2: strText2 = vbNullString
Next
If Len(strText2) Then strText1 = strText1 & strText2

（1）の方が一般的な書き方だと思いますが、この場合、(2)の方が圧倒的に速いのです。

結果

(1):343 ミリ秒

(2):16 ミリ秒

※Acceess2003で実験

VB6.0のString型は値型なので文字列を追加する度に、データの文字列をコピーしているわけですね。コピーするものが、長ければ長いほど、コピーの作業にコストがかかる。

ひるがえって(2)ではstrText2にちまちま文字列を追加して、 If Len(strText2) > 3000 のタイミングでstrText1に突っ込んで、strText2をカラッポにしてまた文字列をためていく。その方が、長い文字列をコピーする回数が激減して、処理が速くなるというわけなんですね(ちなみに.Netだとstring型は一応参照型なので、文字列をセットする度にコンストラクタが発生している様子でVB6.0/VBAどころではない負荷を、もはや「体感」として感じます。なのでStringBuilderのインデクサで対応)。

サンプルソース

フォームにtxtDATAという名前のテキストボックスを作って、そこにタイムを表示する。clsTimerMMクラスは「時間経過計測クラス‐VBA編」参照。

Dim clsTimerMM As clsTimerMM
Dim strTextMsg As String
'************************
Dim strMsg As String
Dim strText1 As String
Dim strText2 As String
Dim lngMax As Long
Dim i As Long
'************************
Set clsTimerMM = New clsTimerMM
strMsg = "あいうえおかきくけこさしすせそ"

lngMax = 10000
'************************
clsTimerMM.SetStartDate
For i = 0 To lngMax
    strText1 = strText1 & strMsg
Next
strTextMsg = "(1):" & clsTimerMM.ElapsedTime & vbCrLf
Me.txtText1.Value = strText1 & vbCrLf
strText1 = vbNullString
'************************
clsTimerMM.SetStartDate
For i = 0 To lngMax
    strText2 = strText2 & strMsg
    If Len(strText2) > 3000 Then strText1 = strText1 & strText2: strText2 = vbNullString
Next
If Len(strText2) Then strText1 = strText1 & strText2
strTextMsg = strTextMsg & "(2):" & clsTimerMM.ElapsedTime & vbCrLf
Me.txtText2.Value = strText1 & vbCrLf

Me.txtDATA.Value = strTextMsg
Set clsTimerMM = Nothing

Function対決-VBA編

うーん、これも道理といば道理。さらに言えばC#を書きはじめるまで、ファンクションの返り値にこういう条件式をつっこむ、という書き方はしなかったんですけれど(なぜならVBで書くと、なんでも「=」で不気味だから)。

わざわざif文で評価してから返すよりも、ダイレクトに評価してしまった方が、当然処理は速いですものネ。…ま、Functionの中身はかなり短いロジックにならざるをえませんので需要があるかどうかはビミョーですが。とりあえず、速度対決。

(1)

Private Function IsTestTrue01(ByVal a As Long) As Boolean
If (a Mod 2 = 0) Then
    IsTestTrue01 = True
Else
    IsTestTrue01 = False
End If
End Function

(2)

Private Function IsTestTrue02(ByVal a As Long) As Boolean
IsTestTrue02 = (a Mod 2 = 0)
End Function

結果

(1):1756 ミリ秒

(2):1588 ミリ秒

※Access2003で実験

サンプルソース

フォームにtxtDATAという名前のテキストボックスを作って、そこにタイムを表示する。clsTimerMMクラスは「時間経過計測クラス‐VBA編」参照。

Dim clsTimerMM As clsTimerMM
Dim i As Long
Dim lngMax As Long
Dim strMsg As String

Set clsTimerMM = New clsTimerMM
lngMax = 10000000

'************************
clsTimerMM.SetStartDate
For i = 0 To lngMax
    Call IsTestTrue01(i)
Next
strMsg = "(1):" & clsTimerMM.ElapsedTime & vbCrLf
'************************
clsTimerMM.SetStartDate
For i = 0 To lngMax
    Call IsTestTrue02(i)
Next
strMsg = strMsg & "(2):" & clsTimerMM.ElapsedTime & vbCrLf

'************************
Me.txtDATA.Value = strMsg
Set clsTimerMM = Nothing

And と Ifのネスト対決-VBA編

これはまぁ意外性はないんですけど、とりあえず測定だけはしておこうかと。

.Netだったら。VBではAndAlso、C#では&&とかが使えるんですけど、VBAでは使えませんものね。

というわけで、またの名を、And　v.s AndAlso ってコトですかね？！これをVBAではAnd とIfのネスト構造で再現して対決します。

(1)If IsCheck(i) And IsCheck(i + 1) Then

(2)If IsCheck(i) Then if IsCheck(i + 1) Then

結果

(1):3436 ミリ秒
(2):2563 ミリ秒

※Access2003で実験

サンプルソース

フォームにtxtDATAという名前のテキストボックスを作って、そこにタイムを表示する。clsTimerMMクラスは「時間経過計測クラス‐VBA編」参照。

Dim ClsTimerMM As ClsTimerMM
Dim strTextMsg As String
'************************
Dim lngMax As Long
Dim i As Long
'************************
Set ClsTimerMM = New ClsTimerMM
lngMax = 10000000

'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    If IsCheck(i) And IsCheck(i + 1) Then
    End If
Next
strTextMsg = Left("If IsCheck(i) And IsCheck(i + 1) Then" & String(50, " "), 50) & ":" & ClsTimerMM.ElapsedTime & vbCrLf
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    If IsCheck(i) Then
        If IsCheck(i + 1) Then
        End If
    End If
Next
strTextMsg = strTextMsg & Left("If IsCheck(i) Then If IsCheck(i + 1) Then" & String(50, " "), 50) & ":" & ClsTimerMM.ElapsedTime & vbCrLf
'************************

Me.txtDATA.Value = strTextMsg
Set ClsTimerMM = Nothing

Private Function IsCheck(ByVal intChk As Long) As Boolean
 IsCheck = (intChk Mod 2 = 0)
End Function

空文字チェック対決-VBA編

VBAで空文字("")をチェックするときの速度１千万回対決。とりあえず、Nullは空文字ではないので除外。

結果

strData = "あいうえおかきくけこさしすせそ"
If strData = "" Then          :517 ミリ秒
If Len(strData) = 0 Then      :230 ミリ秒
If LenB(strData) = 0 Then     :234 ミリ秒

strData = "0123456789abcdefg"
If strData = "" Then          :516 ミリ秒
If Len(strData) = 0 Then      :270 ミリ秒
If LenB(strData) = 0 Then     :234 ミリ秒

※LenとLenBの速度差は、なんだか色々フクザツです。

strDataに「あいうえお」等が入っていると、Len/LenBの速度差の順位は、状況によってはひっくりかえったりしてマチマチ。「0123abcd」などの文字列のときは、何度やってもにLenBが早くなります。何故かはいまのところ不明。

サンプルソース

フォームにtxtDATAという名前のテキストボックスを作って、そこにタイムを表示する。clsTimerMMクラスは「時間経過計測クラス‐VBA編」参照。

Dim ClsTimerMM As ClsTimerMM
Dim strTextMsg As String
'************************
Dim strData As String
Dim strGet As String
Dim lngMax As Long
Dim i As Long
'************************
Set ClsTimerMM = New ClsTimerMM
strData = "あいうえおかきくけこさしすせそ"
'strData = "0123456789abcdefg"
strTextMsg = "strData:" & strData & vbCrLf
lngMax = 10000000
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    If strData = "" Then
    End If
Next
strTextMsg = Left("If strData = """" Then" & String(30, " "), 30) & ":" & ClsTimerMM.ElapsedTime & vbCrLf
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    If Len(strData) = 0 Then
    End If
Next
strTextMsg = strTextMsg & Left("If Len(strData) = 0 Then" & String(30, " "), 30) & ":" & ClsTimerMM.ElapsedTime & vbCrLf
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    If LenB(strData) = 0 Then
    End If
Next
strTextMsg = strTextMsg & Left("If LenB(strData) = 0 Then" & String(30, " "), 30) & ":" & ClsTimerMM.ElapsedTime & vbCrLf
'************************
Me.txtDATA.Value = strTextMsg
Set ClsTimerMM = Nothing

$マークつきの関数について-MidとMid$関数対決

今回、PDFCreatorのソースをみていて気になったのがやたらと出てくる$マーク。UCase$とかOct$とかChr$とかMid$とかがものすごーく気になっていたわけですよ。ググってもひっかかりにくいし、VB6.0のヘルプは手元にないし、でもどうやらフツーに$記号ナシのUcaseやらMidと同じ動作をする。

悶々と々としていたのですが、ちょっと別のコトを調べていたら、スゴい記述をみつけてしまいました。英文サイトのFaster Visual Basic Programs。

マジでー？！

$マークつけた方が、処理早いの？？？　Σ(ﾟДﾟ;≡;ﾟдﾟ)

Midの返り値はvariantなのに対して、Mid$の返り値はString型だから、という理由にはナットクしたものの、Mid$なんて隠し玉の存在を知りませんでした。$で文字列を返すというのは、Basicの名残っぽいですよね。

「….Netでも実験してみよー！」と一瞬、思ったものの、.Netに移行してから、Mid関数とかあんまり使わないかなぁ。(´・ω・`)

私の今の行動範囲で現実的にこの知識を生かせるのってむしろVBA？──というわけでAccess2003で実験してみました。

まずはMidとMid$対決の一千万回対決。

MID$の方がちょっと速いですね。

結果

MID :3277 ミリ秒

MID$:3011 ミリ秒

サンプルソース

フォームにtxtDATAという名前のテキストボックスを作って、そこにタイムを表示する。clsTimerMMクラスは「時間経過計測クラス‐VBA編」参照。

Dim ClsTimerMM As ClsTimerMM
Dim strTextMsg As String
'************************
Dim strData As String
Dim strGet As String
Dim lngMax As Long
Dim i As Long
'************************
Set ClsTimerMM = New ClsTimerMM
strData = "あいうえおかきくけこさしすせそ"
lngMax = 10000000
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    strGet = Mid(strData, "5", 1)
Next
strTextMsg = "MID :" & ClsTimerMM.ElapsedTime & vbCrLf
'************************
ClsTimerMM.SetStartDate
For i = 0 To lngMax
    strGet = Mid$(strData, "5", 1)
Next
strTextMsg = strTextMsg & "MID$:" & ClsTimerMM.ElapsedTime & vbCrLf
'************************

Me.txtDATA.Value = strTextMsg
Set ClsTimerMM = Nothing

参考

同類な関数

Chr ChrB CurDir Dir Format Hex Input InputB LCase Left LeftB Mid MidB Oct Right RightB RTrim Space Str String Trim UCase

時間経過計測クラス‐VBA編

いろいろAccessで測定してみたくなったので、まずは測定クラスの作成。

VBA単独はではミリ秒を測れないようなので、API関数を利用。

ClsTimerMM

Option Compare Database
Option Explicit

'============================
' ミリ秒　で経過時間を計測
'============================

Private Declare Function timeGetTime Lib "winmm.dll" () As Long
Public StartTime As Variant

Public Sub SetStartDate()
StartTime = timeGetTime()
End Sub
Public Function ElapsedTime() As String
ElapsedTime = (timeGetTime() - StartTime) & " ミリ秒"
End Function

そしてオマケで、hh時間nn分ss秒での計測バージョン。

ClsTimer

Option Compare Database
Option Explicit
'============================
' hh時間nn分ss秒　で経過時間を計測
'============================

Private StartDate As Variant
Public Sub SetStartDate()
StartDate = Now
End Sub
Public Function ElapsedTime() As String
ElapsedTime = Format(Now - StartDate, "hh時間nn分ss秒")
End Function

VBAのわかりにくさ

故あってAccessVBAの基礎編ネタを見直しいるのですが…。改めてAccess単体で見直すと、わっっっかりにくいですねぇ、やっぱり。

Access的にはユーザーに優しくするために色々なことを「隠蔽」しているのでしょうが、「隠蔽」しているが故に、「実はこれもこれも根っこは一緒なんだよねー」という原則ルールが見えづらい。

私の場合、クラスを理解するまで、いろいろな事が「謎」でした。VB.NET(VS2003）へ移行してから初めて、それまで「お作法」として捉えていたSetなんかの意味もようやくナットク、色々なことが一気にスッキリ、シンプルに見えてきたんですね。

そんなこんなでVBAユーザーからスタートしている自分を振り返ってみると、VBAでは「クラス」を隠蔽しているから、VBAからはじめるユーザーはいろいろな事がわかりづらいだろうな、と思うわけです。

たとえば他のクラスモジュール（Accessの場合、FormやReportとかしかありませんが）や標準モジュールから開いているフォームを操作したいときに、

Forms!フォーム名!コントロール名

とか

Form("フォーム名").Controls("コントロール名")

とかで突然表現しちゃうのってどーなのヨ？

Accessだって、Formクラスを継承して、新しいフォームクラスをユーザーが定義して、インスタンス生成をいるのに過ぎないだろうに、その辺のあれこれが、VBAの場合、すべて隠蔽されちゃっているわけです(これはVS2005にも当てはまりますね。私はVS2003をいじって、「Formって、Formクラスを継承したこーゆークラスを定義していたってコトだったのか」と、とても腑に落ちたのですが、2005からはVB6.0ライクになってこの辺が隠蔽されるようになりましたからねー…)。

Accessの場合、FormやReportオブジェクトについては、暗黙のインスタンスの生成をAccess側で勝手にやってしまっているのでしょうが、VBAがスタート地点で、コード先にありきだと、この辺が見えてこなくて、混乱の元になっていくんですよね。

自分を振り返ってみても、制御構造類などは見ればわかるものはいいですが、「こう書けばこう動くらしい」というみようみまねで書いていたオブジェクト変数の類は、長らく一種の不思議ボックス状態でした。あわせてSetステートメントもわかりづらい「呪文」でしたね。

DAOやADOなどでSetを使うこと──特にRecordsetなどの、自分でNewと書いているSetのイメージは、何度となく書いているうちに、自分なりにイメージは形成されてはいました。とはいえ、その「理解」も、今思えば、「New」の部分の「インスタンスが生成される」という感覚・イメージの部分だけで、Setの「メモリアドレスをオブジェクト変数に代入している」という役割の部分は、わかっていなかったわけですが…(この辺、我ながらホントに大混乱/未整理状態ですよねー。こんな状態でよく書いていたなー…)。

というわけで「お作法的」にしか飲み込めていなかった私にとって、かなりビミョーだったのが、NewをしないSetステートメント の存在です。Access単体で使うADOで云うならばCurrentProjectだとか。既に開いているFormやReportなどもそうですね。

Dim frmF_001 As Form
Set frmF_001 = Forms!F_001

frmF_001.txtData = "01"

Set frmF_001 = Nothing

というようなオブジェクト変数へ参照させる書き方は、当初、躓いていました。

F_001が開いたタイミングで「暗黙のうちにインスタンスが生成されている」とは明確に考えておらず、「Setステートメントなのに、なんでNewしないんだろ…？」と不思議だったわけです。いろいろな事が悲しくなるくらい、つながって見えていませんでした。

まーそれでも間違った書き方をしていればデバッグでエラーが出るので、デバッグ機能にダメ出しをくらい数をこなしているうちに、「ダメな理由をぼんやりイメージ/理屈を考える」積み重ね、Recordset等で書いているNewとの差を考えてみた結果、「mdbやフォームが既に開いているからには、CurrentProjectやFormなんかは、新しくNewしちゃいけないんだろうな」という感覚程度ならありましたけどね。

当然、継承関係も明確にはわかっていませんでしたので、

Dim frmF_001 As Form_F_001
Set frmF_001 = Forms!F_001

frmF_001.txtData = "01"

Set frmF_001 = Nothing

こうも書けるカラクリもイマヒトツわかっていませんでした(「Form_F_001」という型は、インテリセンス機能で出てくるので、そういう型が存在していること自体は、Accessをいじっているうちにわかるわけです）。断片的な情報から「Formという型がForm_F_001の親玉なんだろうな」というザックリとしたイメージはもってはいましたが、長らく「理論よりもコード先にありき」な状態だったので、それらはコードから自分なりに読み取ったイメージ/予測/解釈にすぎません。

VBAからはじめたユーザーにとっては、親切設計がすぎるがゆえに、いろいろな事が唐突な変形にしか見えないんですよね。フォームのことを「クラスモジュール」と呼ぶことは知っていても、自分がクラスの一部分を書いているという自覚も当時はありませんでしたし…。

これがclassの書き方を知ってからは、それまでのモヤモヤしたイメージ/理屈が、劇的に自分の中では一気に鮮明になったわけですね。

「クラス」と「インスタンス」の関係を知ったときの私の感想といえば、プラトンのイデア論に似ているなぁ、というものなのですが(洞窟の中で焚き火をして、その後ろで踊っているのがイデアで、洞窟の壁にうつる影法師は人間の住む世界だ、というあれですね。周囲からは同意を得ることはできませんでしたが、その関係性は似ていますよね？？？)。クラスが隠蔽されているVBAの世界は、私にとって影法師のみを見せられているようなものでしたね。

変数のスコープと有効期限

故あってAccessのVBAの講習会へ参加したことのある同僚から、講習会本を借りて、このテの教材はどんなものかなーとちょっと読んでいたわけですが、講習会って、スゴイんですね。「VBAだしなー」と甘く見ていたら、言語の基礎を思った以上にかなりがっつり網羅している。…今思うと、VBA/VB6.0をいじっていた当時、単に私がなにがなんだかわかっていなかっただけなのですが(理屈はわからず「お作法」的に覚えたクチ。VB6.0ユーザーには多いのではないでしょうか)、基本という意味では、VBAの基礎本といえどもそれなりに網羅しているんだなぁ、と妙に感心。普段通いなれた安全な道しか使わない方なので、「そーいえば…」というものも出てきて、勉強/復習になります。

そんなこんなでテキストをみながら、「あぁこのネタにはハマったことがあるなぁ…」と思い出したり発見することも多い今日この頃なのですが、そのひとつが正直に告白しますと、変数のスコープと有効期限です。…基本中の基本ですね！！

VBAにはブロック・レベルのスコープはありませんが、VB.NETにはブロック・レベルのスコープがあります。

「スコープ」についてはブロックの外でブロック内で宣言した変数を呼び出そうとしてもエラーになるので、「あぁこれは使えないんだな」ということがハッキリわかるわけですが、「有効期限」はちょっとわかりにくいですよね。…そうでもないですか？

無教養な私の場合、「スコープ＝有効期限」のように思いっきり錯覚していたわけですが、スコープと有効期限は別物なんですよね。VB.NETでのブロック・レベルのスコープでいうなら、変数の有効期間は、プロシージャ全体の有効期間です。

…で、その辺いい加減な理解をしていた私が何をやったかというと、お察しの通り、ブロック内で変数宣言した構造体を、自分で初期化せずに、使ってしまったわけです。当然、「宣言したところで初期化されている」という思い込み、プラス、有効期限もブロック内だという勘違いを前提を元にコーディング。その結果、恐ろしいデータができたので、すぐさまスコープと有効期限の違い、自分の勘違いにも気がつき、「とりあえず、なんでも自分の思うタイミングで初期化しておけば間違いない」というとても初歩的な教訓は自ら体験して得たわけなんですが。基礎本をちゃんと読めばそんなことは書いてあるのに、書いてあることがうまくつながらずに読んでいるとこうなるわけですね。

「スコープ/有効期限といえば、そんなこともやったっけなぁ」と我ながらイタイ思い出を振り返りながらテキストを読んでいると、Accessの場合、「標準モジュールに書いたPrivate変数の有効期限は、MDBを閉じるまで」と書いてあるではないですか。ひょええぇ…。これって、VB.NETでも同じことなんでは？

これまでVBA/VB6.0でいうところのクラスモジュール、およびインスタンス生成のクラスではPrivate変数宣言は使っていました。この場合「フォーム/レポートが開いている間は有効」、「インスタンス生成してそのクラスが生きている間は有効」ということで、迷うほどのこともなくわかりやすく特に問題はありませんでした。でも標準モジュールの類では、私は変数/プロシージャーを含めてPublicなものしか使ったことがなかったんでよね。故にあまり考えたことがなかったということもできますが、スコープ/有効期限は大事、と思いながら、標準モジュールの宣言セレクションで宣言したPrivate変数の有効期限をロクに認識していなかったとは、どーゆーことでしょう。

VB.NETでは標準モジュールにあたるModuleもありますし、それと同等の役割を果たすSharedを宣言しまくりのクラスの場合も、この辺の理屈は一緒の筈です。それなのにこれまでこの辺をロクに認識していなかったとは……。VBA/VB6.0時代同様、このテの場面ではPrivateで変数宣言をしようと思ったことがないので、幸いバグったことはありませんが。あああああ、足元で思いっきり躓いた感じでショックです。痛い、あまりにも痛い…。

モジュールみたいな場所でずっと値の生きているPrivate変数というのは、使いづらそうなので、今後も個人的にはあまり使う場面はイメージできないのですが（インスタンス生成のクラスでカプセル化した方が安心…）、言語の仕様はきっちり読んでいないと怖いですねぇ。自分の使ったことのない新ネタは使う前に仕様は確認はする方ですが、基礎がないと仕様を読む際のポイントもわからないですからねぇ。そしてワナにハマって「あの意味はこれだったのか！」と気がつく。…ときどきこうして基礎本を読んで振り返るのは、なんであれ、まだまだ勉強になります。