excelvbaでCreateThreadの動作

質問

Excel2007で、VBAを利用した簡単なデータエントリ、管理ソフトを作成しています。
ACCESSが無いため、データベースもExcelファイルを使用しています。

　ADODBで、データベース用のExcelファイルを開くのですが、エントリ数が増えるに従い、openに時間がかかるようになってきました。そのため、プログレスバーで、VBAが動作していることをアピールすることとしました。

　まず、非同期接続を試したのですが、connectionを数回OpenとCloseを繰り返すと、coinitializeでエラーが出てしまい、Excelが落ちる状況となってしまうためあきらめました。
　次の手段として、CreateThreadでスレッドを作成して、connectionOpenのスレッドと、プログレスバーのコントロールを分離しようと作成してみましたが、CreateThreadで作成した方のプログラムがうまいこと動作してくれません。
　ConnectionOpenをメイン、プログレスバーを別スレッドにしたもの、プログレスバーをメイン、ConnectionOpenを別スレッドにしたものを両方作成してみましたが、どちらも別スレッドにした方がうまく動きません。
　debug.print　"test"を別スレッドの1行目に入れたところ、イミディエイトに表示されるので、処理が渡っていないわけではないようです。
　また、openをメインスレッドにした時にわかっているのは、メインスレッドのADOCon.Openの行が実行されたと同時に、別スレッドが止まってしまっているようです。

　もしかして、CreateThreadは割り込みがかけられないような状況では別のスレッドは動作しないのでしょうか？また、CreateThreadで作成されたスレッドは、重たい処理は無理なのでしょうか？

テスト用のデータです。
'Busyというユーザーフォームに、PBerというプログレスバーを配置
'C:\Users\xx\Desktop\に、DBファイルを配置　XXは、ユーザー名
'mihon.xlsxは、約5MB

'変数等は、両タイプとも共通
Public bRun As Boolean
Public adoCON As New ADODB.Connection
Public Declare Function CreateThread Lib "kernel32" (ByVal lpThreadAttributes As Long, _
ByVal dwStackSize As Long, ByVal lpStartAddress As Long, _
ByRef lpParameter As Long, ByVal dwCreationFlags As Long, _
ByRef lpThreadID As Long) As Long
Public Declare Function CloseHandle Lib "kernel32" (ByVal hObject As Long) As Long
Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)

'connectionOpenをメイン、プログレスバーを別スレッド
Sub AdoOpen()
Dim ThreadId As Long
Dim hThread As Long
With Busy
.BusyMes.Caption = "DB接続処理中"
.PBar.Visible = True
.PBar.Value = 0
.PBar.Min = 0
.PBar.Max = 10
.Show vbModeless
End With
DoEvents
bRun = False
hThread = CreateThread(0&, 0&, AddressOf Counter, 0&, 0&, ThreadId)
Application.Wait [NOW()+"0:00:00.5"]

With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Excel 12.0"
.Open "C:\Users\xx\Desktop\mihon.xlsx"
End With
bRun = True
If hThread Then
CloseHandle hThread
hThread = 0
End If
With Busy
.BusyMes.Caption = ""
.PBar.Value = 0
.PBar.Visible = False
.Hide
End With
DoEvents
End Sub

Function Counter() ' As Boolean
Dim bCountup As Boolean

Do Until bRun
Select Case Busy.PBar.Value
Case 0
bCountup = True
Case 10
bCountup = False
End Select
If bCountup Then
Busy.PBar.Value = Busy.PBar.Value + 1
Else
Busy.PBar.Value = Busy.PBar.Value - 1
End If
Sleep 500
Loop
End Function

'プログレスバーをメイン、connectionOpenを別スレッド
Sub CounterStart()
Dim bCountup As Boolean
Dim ThreadId As Long
Dim hThread As Long 'スレッドハンドル
With Busy
.BusyMes.Caption = "DB接続処理中"
.PBar.Visible = True
.PBar.Value = 0
.PBar.Min = 0
.PBar.Max = 10
.Show vbModeless
End With
DoEvents

bRun = False
hThread = CreateThread(0&, 0&, AddressOf Counter2, 0&, 0&, ThreadId)

Do Until bRun
Select Case Busy.PBar.Value
Case 0
bCountup = True
Case 10
bCountup = False
End Select
If bCountup Then
Busy.PBar.Value = Busy.PBar.Value + 1
Else
Busy.PBar.Value = Busy.PBar.Value - 1
End If
Application.Wait [NOW()+"0:00:01.5"]
Loop

If hThread Then
CloseHandle hThread
hThread = 0
End If
With Busy
.BusyMes.Caption = ""
.PBar.Value = 0
.PBar.Visible = False
.Hide
End With
DoEvents
End Sub

Function Counter2()
With adoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source=" & ObjDB.Value & "; Extended Properties=""Excel 12.0;"""
.Open "C:\Users\xx\Desktop\mihon.xlsx"
End With
bRun = True
End Function

回答No.2

yorozu_ya · ベストアンサー

> ACCESSが無いため、データベースもExcelファイルを使用しています。

ACCESSが無くてもMDBファイルをデータベースとして扱えますよ。
データ量が多いなら、MDBファイルを使うことを検討してはいかがでしょう。

お礼

返信が遅くなりました。
結局、Excelファイルをデータベースファイルとして利用することをあきらめました。
ExcelでMDBを作成し、今までのデータをインポートして利用することができました。
adoで作成していたため、変更箇所も少なく、スムーズに移行しました。
ありがとうございました。

回答No.1

nda23

先ずCreateThreadで指定するメソッドは
整数のパラメータを１個持ち、整数の値を
返すpascal型でなければなりません。
VBAの関数はpascal型なので無意識でも
よいのですが、掲題のCounter2はパラ
メータが無く、戻り値がVARIANT型です。
VARIANT型を返す関数は内部的には
戻り値ではなく、値をセットするポインタを
パラメータに持つので、形式はたまたま合い
ますが、「運よく動いている」だけであり、
好ましい状態ではありません。

VBAおよびオブジェクトの大半はスレッド
セーフではありません。よって、VBAを使う
マルチスレッドは実行不可能といってよいと
思います。

C言語でDLLを作り、その中でマルチスレッドに
することはできます。但し、オブジェクトを
スレッド間で使うのはできないと思った方が
よいでしょう。スレッド内でオブジェクトの
インスタンス化～解放までするなら使用でき
ます。しかし、C言語でCOMオブジェクトを扱う
のはとても面倒で、VBでやるようなものとは
ワケが違います。

MySQLやPostgreSQLなどを使ってDBを構築し、
マルチプロセスでデータをロードすることを
考えてみてはいかがでしょうか。

お礼

Excelファイルでデータベースはあきらめました。
他の方の意見を参考に、MDBファイルに移行することにどうにか成功しました。
ありがとうございました。

補足

nda23様
早速の回答ありがとうございます。

やはり、VBAでマルチスレッドは難しいのですね。

MySQLなどのフリーソフトや自作Cなどは、会社のセキュリティポリシーの関係で自由な取り扱いができないんです。
業務に必要だからと本社のシステムグループに使用許可をもらうにしても、他のインストール済みソフト（業務用の基幹システム等）への影響や、複数のソフトの組み合わせによる著作権等への影響がないか、あらゆるチェックをしなければ許可は出せないそうです。（実際には影響範囲がチェックしきれないため許可はでない）

そのため、VBAであれば前述の問題が発生しないため、VBAのみでどこまでできるかに執着していた次第です。

CounterStartから、Counter2を別スレッドで呼び出すのは、戻り値が整数値ではないためだめなのですね。まぁ、connectionを開くのだからそうですよね。
実際、プログレスバーが動いてもconnectionが開かないので、Counter2が止まっているのは間違いないと思います。

AdoOpenから、Counterを別スレッドで呼び出す方のは、戻り値がVARIANT型ではないですけど、やはり難しいのでしょうか。
ネットで調べた中に、VBですが、フォームに配置したラベルのバックカラーを別スレッドで変更するっていうのがあって、それはExcelVBAでも問題なく動きました。
しかし、質問に記載したもの（フォームに配置したプログレスバーを別スレッドで操作する）を実行すると、メインスレッドのADOCon.openまで来ると、open処理が終了するまで別スレッドに処理がいかないようなんですが。