vbaの.pasteが実行時エラー5097の問題が発生で止まった。どうすればいいのか？

vba paste 実行時エラー　5097でググってみると、クリップボードへのコピーが完了していないのに、pasteを試みたためらしい。
　application waitで３秒待つコードを追加してみた。これで、暫く、様子を見る。

これまで快調に動いていたexcel vbaのコードが突然、コケる（ABEND:ABnormalENDつまり、異常終了）ようになった。その原因を突き止めるまでのお話である。

　結論を先に言うと、原因は、ファイルの有無を調べるための１回目のdir(パス￥ファイル)がNullにもかかわらず、無謀に２回目のdir()を出していたため。
　だから、修正方法は、１回目のdirがNullの場合、２回目のdirは出さないようにしただけ。
　ワタシのvbaコードは、名前に日付があるファイル名の最大のものを探すと言う関数（FUNCTION）だった。1回目のdirがNullの場合、該当ファイルなしで復帰(exit)するべきところ、あるはずもない最大のもを求め、２回目のdirを出してしまった。この２回目のdirがコケていた訳だ。要するに、該当するファイルが一つもないケースを考慮していなかったのが敗因である。無謀な２回目のdirを出してしまっていた。
　EXCELが突然、コケたののに、このバグをいかに攻略したか？
　デバッグのコツは、自分のコードをじっくりと眺めることである。
ググっても自分のケースにフィットしたトラブル事例がない場合、もう自分のコードに向き合うしかない。
　そして、あるとき、フッと閃く。そう信じて、コードを眺めるしかない。

vbaで英字or空白を含む文字列かを判定する方法

vbaで英字か空白を含む文字列であるか否かを判定するコードを考えてみた。

Function IsAlphabet(str As String) As Boolean  
  Dim objReg As Object
  Set objReg = CreateObject("VBScript.REgExp")
  objReg.IgnoreCase = True '大文字と小文字を区別しない
  objReg.Pattern ="^[a-zA-Z a-zＡＺ　]+$
  IsAlphabet = objREg.test(str) '英字か空白ならばTrue そうでなければFalse
  Set objReg = Nothing
End Fuction

或いは、以下のコード。

Function IsAlpha(str as String) As boolean
  If str ="" then
    IsAlpha = False
    Exit Fuction
  End if
  IsAlpha = Not str Like"*[!a-zA-Z a-zＡＺ　]*"
End Function  

で、これを何に使うのかというと、例えば、名前に英字が使われていれば、外国人の名前か否かの判定に使える。
ま、パーフェクトではないが、大体それでＯＫといういう場合に使える。完璧を求めると、大変なので、これぐらいで手を打とうという時に使う。

vbaでInStrでは１回で市川市を抽出できないが、InStrRevならば抽出できる。が、しかし、InStrが正解だよ。

　vbaでInStr（住所を示す文字列,"市"）では、前から検索のため、市川市を抽出できない。ところが、InStrRevならば、後ろから検索なので、市川市を抽出できる。つまり、前から攻めて駄目(InStr)ならば、後ろから攻めろ(InStrRev)ってことだね。

Function AREA(address as String) as String
  If inStrRev(address,"市")>0 then
    AREA = Left(address,InstrRev(address,"市"))
  End If
End function

　最初は、以下のコードで市川市を特異点として特別に扱うこと（２段構造）で回避してたんだけど、InStrRevなら１回で済ませることができると勘違いしたんだ。

Function AREA(address as String) as String
  If inStr(address,"市川市")>0 then
    AREA = Left(address,Instr(address,"市")+2)
  ElseIf inStr(address,"市")>0 then
    AREA = Left(address,Instr(address,"市"))
  End If
End function

　ところが、InStrRevには思いもかけない副作用があった。それは○○市の後に市という文字が付く町、例えば、○○市市場町の場合、○○市市を抽出してしまうのだ。あーそれそれ。やっパリ、以下のコードが一番良い。市原市も市川市と同じ考慮が必要だよ。

Function AREA(address as String) as String
  If inStr(address,"市川市")>0 then
    AREA = Left(address,Instr(address,"市")+2)
  ElseIf inStr(address,"市原市")>0 then
    AREA = Left(address,Instr(address,"市")+2)  
  ElseIf inStr(address,"市")>0 then
    AREA = Left(address,Instr(address,"市"))
  End If
End function

Excel vbaで「1004 rangeクラスのautofilterメソッドが失敗しました」と怒られ、試行錯誤で辿り着いた原因。

Range("A1").Autofilter n,Array("○","×"),xlFilterValuesでフィルターをかけた。
　そしたら、「1004 rangeクラスのautofilterメソッドが失敗しました」というエラーが出た。
　色々と試したとこと、どうやら、A列からn番目の列の領域（current region）に切れ目（空白のセル、特にタイトル行の空白ありがよくあるパターン）があるとこのエラーが出るようだ。
　このエラーを回避するためには、ブランクのセルに何らかの値を入れるといいようだ。
　例えば、少なくとも１行目のタイトル行には何らかのタイトル（項目名）を入れておく。
　ここで、突然ですが、自分へのメモ。ステーキのたれのレシピです。醤油２に対して、日本酒１、みりん１。これに、ニンニクのローストが欠かせない。付け合わせは、ポテサラかな。そして、上質な赤ワイン。パンチがあるカバネー(Cabernet)がいい。

EXCEL vbaでwhereのnoをIN＋配列＋joinで複数指定するのではなく、noをひとつ指定したSELECT を必要な分だけ繰り返し発行する。

　EXCEL vbaでwhereのnoをIN＋配列＋joinで複数指定して、SQLを１回で済ませると手っ取り早いが、SQL文の最大文字数（バッチサイズ）65536バイト以内にする必要がある。
　そこで、noをひとつ指定したSELECT を必要な分だけ繰り返し発行する。
　ActiveWorkbook.Worksheets(1)のA列にNOが入っていると仮定する。これでどうじゃろかな？

Dim i As Long
Dim noStr as String
Dim strSQL as String
Dim maxRow as Long
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Const PROVIDER As String = "SQLOLEDB"
Private Const DATA_SOURCE As String	= "localhost"   'サーバ名
Private Const DATABASE As String = "dbname"         'データベース名
Private Const USER_ID As String = "UID=user"        'ユーザID
Private Const PASSWORD As String = "password"       'ユーザパスワード

'SQL Server認証で接続する場合
  cn.ConnectionString	= "Provider=" & PROVIDER _
                       & ";Data Source=" & DATA_SOURCE _
                       & ";Initial Catalog=" & DATABASE _
					   & ";UID=" & USER_ID _
                       & ";PWD=" & PASSWORD
  cn.Open

  maxRow = ActiveWorkbook.Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row

  For i = 2 to maxRow
    nostr = activeWorkbook.WorkSheet(1).Cells(i,1).Value 　
    strSQL = SELECT * from TBL_A A inner join TBL_B B on A.[NO] B.[NO] where A.[NO] IN (" & noStr & ") AND B.[TYPE] = 1;"
    rs.Open strSQL, cn
    rs.MoveFirst
    ActiveSheet.Range("A1").CopyFromRecordset rs
    If Not rs Is Nothing Then
     If rs.State = adStateOpen Then rs.Close
     Set rs = Nothing
    End If       
  Next
  
  If Not cn Is Nothing Then
   If cn.State = adStateOpen Then cn.Close
   Set cn = Nothing
  End If
   
End Sub

EXCEL vbaでSELECT文のwhere句をIN＋配列＋join関数で作る。

　SELECT文のwhere句のINを複数指定する場合、INの中身を配列とjoinで作る事になる。その例をぐぐっても出てこなかったので、やむを得ず作ってみた。
　ActiveWorkbook.Worksheets(1)のA列にNOが入っていると仮定する。これでどうじゃろかな？

Dim i As Long
Dim noStr as String
Dim strSQL as String
Dim noArr as variant
Dim maxRow as Long
maxRow = ActiveWorkbook.Worksheets(1).Cells(Rows.Count,1).End(xlUp).Row
reDim noArr(maxRow)
For i = 2 to maxRow
 noArr(i-2) = Cells(i,1) 　'1次元配列に入れる
Next
noStr = join(noArr,",")
strSQL = SELECT * from TBL_A A inner join TBL_B B on A.[NO] B.[NO] where A.[NO] IN (" & noStr & ") AND B.[TYPE] = 1;"