講師の教務可能科目の検索システムを運用開始前にテストをしつつマニュアルを作成しているところです。
テストをしていたら不具合と修正したほうが良い箇所を見つけたのでそのメモです。
目次
1. リストボックスのスクロールバーが動かない
リストボックスのスクロールバーはマウスホイールで動かないことは仕様です。しかし、今回の不具合はそもそもスクロールバー自体に触れられませんでした。
解決方法は、該当のリストボックスのプロパティの一つであるEnableをTrue、LockedをFalseにすることです。どちらも既定値であるので、変に弄ってなければそのままだと思います。
2. ListBosの表示を昇順にしたい
ListBoxは二次元配列を表示していますが、二次元配列をソートするのは面倒くさい。しかし、テーブルの上から順に配列に格納していく実装をしているので、テーブルをソートしてしまえばListBoxの表示もソートされた状態にできます。
現在テーブルのコピー、結合は次のような実装をしています。
| Public passwordResult As Boolean | |
| 'メイン部分' | |
| Sub getDataMain() | |
| Call password | |
| 'Call queriesReflesh' | |
| Call margeData | |
| End Sub | |
| 'パスワード認証をする' | |
| Private Sub password() | |
| passwordResult = False | |
| UserForm2.Show | |
| If passwordResult = False Then | |
| End | |
| End If | |
| End Sub | |
| 'テーブル存在チェック' | |
| Private Sub queriesReflesh() | |
| 'dataシートにテーブルが存在するか確認' | |
| With Worksheets("data") | |
| If .ListObjects.Count = 0 Then | |
| 'テーブルがない場合はエラーメッセージを出す' | |
| MsgBox "データが存在しません。マニュアルに沿って再接続してください。" | |
| End | |
| End If | |
| End With | |
| Call reflesh | |
| End Sub | |
| 'クエリ更新はWithブロック内に(うまく動かない)' | |
| Private Sub reflesh() | |
| Sheets("data").Select | |
| Selection.ListObject.QueryTable.Refresh BackgroundQuery:=False | |
| End Sub | |
| '名簿とデータを結合させる' | |
| Private Sub margeData() | |
| Dim supreadsheetData 'スプレッドシートから得たデータとsarchableシートのデータ' | |
| Dim meiboData '名簿のデータ' | |
| Dim lastRow, lastColumn '最終行、最終列' | |
| Dim i As Long 'For文のindex用' | |
| Dim resultRg As Range '検索結果のRangeオブジェクト用' | |
| Dim myTable As ListObject 'sarchableのlistobject' | |
| 'スプレッドシートから得たデータをsarchableにコピー' | |
| Set supreadsheetData = Worksheets("data").UsedRange | |
| 'sarchableシート全体のセルをクリア' | |
| Worksheets("sarchable").UsedRange.ClearContents | |
| 'データをコピー' | |
| supreadsheetData.Copy Destination:=Worksheets("sarchable").Range("A1") | |
| With Worksheets("sarchable") | |
| 'B,C列を挿入する(講師番号と電話番号が入る)' | |
| .Columns("B:C").Insert | |
| 'B1とC1に列の名前を入れる' | |
| .Range("B1").Value = "名前" | |
| .Range("C1").Value = "電話番号" | |
| 'テーブルに名前をつける' | |
| Set myTable = .ListObjects.Item(1) | |
| myTable.Name = "mergedTable" | |
| End With | |
| '名簿のデータをRangeオブジェクトとして取得' | |
| With Worksheets("meibo").UsedRange | |
| lastRow = .Cells(Rows.Count, 1).End(xlUp).Row '最終行の取得' | |
| lastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column '最終列の取得' | |
| meiboData = .Range(.Cells(2, 1), .Cells(lastRow, lastColumn)).Value '最終行までデータを取得する' | |
| End With | |
| 'sarchableシートの講師番号を講師名簿から検索し、名前と電話番号を入れる' | |
| With Worksheets("sarchable") | |
| '電話番号の列の形式を文字列にする' | |
| .UsedRange.Columns("B:C").NumberFormatLocal = "@" | |
| '講師番号を講師名簿から検索する' | |
| For i = LBound(meiboData) To UBound(meiboData) | |
| Set resultRg = .UsedRange.Columns(1).Find(meiboData(i, 1), LookIn:=xlValues) | |
| '見つかればsarchableデータのB列とC列にデータを書き込む' | |
| If Not resultRg Is Nothing Then | |
| '名前を書き込む' | |
| .Cells(resultRg.Row, 2).Value = meiboData(i, 2) | |
| '電話番号を文字列として書き込む' | |
| .Cells(resultRg.Row, 3).Value = meiboData(i, 3) | |
| End If | |
| Next i | |
| '書き込んだあとテーブルを講師番号をkeyにして昇順にソートする' | |
| .Range("mergedTable").Sort Key1:=.Range("mergedTable[講師番号]"), Order1:=xlAscending, Header:=xlYes | |
| End With | |
| End Sub |
by sarchableというシート内のセルを全てクリアし、別シートからテーブルをコピーするというのが大雑把な流れです。
2-1. テーブルを昇順にソートする
With Worksheets("sarchable")
.Range("mergedTable").Sort Key1:=.Range("mergedTable[講師番号]"), Order1:=xlAscending, Header:=xlYes
End With
テーブル名がmergedTableであり、その講師番号の列の値をkeyとして昇順にソートしています。テーブルのソートはkeyを参照して行の順番を入れ替えることができます。
Range.Sortメソッドの構文は次のとおりです。
式.Sort (Key1, Order1, Key2, Type, Order2, Key3, Order3, Header, OrderCustom, MatchCase , Orientation , SortMethod, DataOption1, DataOption2 , DataOption3)
Orderは昇順、降順があり、それぞれ次のように指定します。
- 昇順: xlAscending
- 降順: xlDescending
(XlSortOder列挙より)
2-2. テーブルに名前をつける
先程はテーブル名を記述することでテーブルを指定していました。しかし、もともとの実装が一度シート内のセルを全てクリアしてからコピーするというものなので、コピーする度にテーブルの名前が変わります。
テーブルに名前をつけない場合はExcelが自動的に名前をつけてくれます。末尾に数字をつけるようですが、いちいち計算してその名前を当てるのも面倒くさいので、テーブルをコピーしたらテーブルに名前をつけることにしました。
Dim myTable As ListObject
With Worksheets("sarchable")
Set myTable = .ListObjects.Item(1)
myTable.Name = "mergedTable"
End With
ListObject.NameプロパティはListObjectの名前を表す文字列型の値を取得または設定します。リファレンスの例では取得のやり方だけ書かれていますが、上のようにすると名前を設定できます。
3. おわりに
今の所Googleスプレッドシート上のスクリプトもちゃんと動作しています。
問題はVBAでクエリの更新ができないことですが、どうしたらいいんだろう。マクロの記録を使って作ったマクロすら動かないってどういうこと？