1. Home » 
2. Excel講座の総目次 » 
3. Excel VBA入門：目次 » 
4. 席替えを自動で行う(SortedListクラスを使う)

席替えを自動で行う(SortedListクラスを使う):Excel VBA入門

はじめに　　　Topへ

• SortedListクラスを使うを利用することで、容易にコード化することができそうです。
  詳細は下記をご覧ください。
  • SortedList クラス
    http://msdn.microsoft.com/ja-jp/library/system.collections.sortedlist.aspx
• .NET FrameworkのSortedListクラスを利用しますので、.NET Frameworkがインストールされている必要があるようです。
  この辺のことは下記のサイトをご覧ください。
  • .NET Frameworkのバージョンを確認する方法
    http://www.atmarkit.co.jp/fwin2k/win2ktips/246checkvdnfw/checkvdnfw.html
• ここで使用しているのはWindows 7(64bit) Excel2010(32bit)です。

生徒のリストをSheet2へ作成する　　　Topへ

• Sheet2のB列に下図のように生徒名のリストを作成します。
  

座席(机)を描画する　　　Topへ

• Sheet1の列に下図のような座席(机)を描画します。
  この部分のコードは　席替えを自動で行う　と同じです。
  • 描画した机はセル範囲B1:G8の中であれば、移動してもOKです。
    描画した時につけた名前をキーにしていますので、位置は関係ありません。

  Sub seat01() 　　Dim c As Range, ob As Object 　　Dim myShape As Shape 　　Dim i As Long, n As Long 　　'　nで座席数(生徒数)を指定 　　　　n = Application.WorksheetFunction.CountA(Worksheets("Sheet2").Range("B:B")) - 1 　　　　With Worksheets("Sheet1") 　　　　　　' B1:G20の範囲のオブジェクトを削除 　　　　　　For Each ob In .DrawingObjects 　　　　　　　　If Not Intersect(ob.TopLeftCell, .Range("B1:G20")) Is Nothing Then 　　　　　　　　　　ob.Delete 　　　　　　　　End If 　　　　　　Next 　　　　　　'　行高さ、列幅を指定 　　　　　　.Rows("1:10").RowHeight = 35 　　　　　　.Columns("B:G").ColumnWidth = 15 　　　　　　'　座席を描画する、描画したオートシェイプの名前は「座席」+数字としています 　　　　　　For Each c In .Range("B2:G8") 　　　　　　　　i = i + 1 　　　　　　　　If n < i Then Exit For 　　　　　　　　Set myShape = .Shapes.AddShape( _ 　　　　　　　　　　　Type:=5, _ 　　　　　　　　　　　Left:=c.Left, _ 　　　　　　　　　　　Top:=c.Top, _ 　　　　　　　　　　　Width:=c.Width - 15, _ 　　　　　　　　　　　Height:=c.Height - 10) 　　　　　　　　myShape.Name = "座席" & i 　　　　　　　　myShape.TextFrame.Characters.Text = myShape.Name 　　　　　　Next c 　　　　　　'　教卓を描画しています 　　　　　　Set myShape = .Shapes.AddShape( _ 　　　　　　　　　Type:=5, _ 　　　　　　　　　Left:=.Range("D1").Left + 30, _ 　　　　　　　　　Top:=.Range("D1").Top, _ 　　　　　　　　　Width:=.Range("D1").Width, _ 　　　　　　　　　Height:=.Range("D1").Height - 10) 　　　　　　　　　myShape.Name = "教卓" 　　　　　　　　　myShape.TextFrame.Characters.Text = myShape.Name 　　　　　　.Range("A1").Select 　　　　End With End Sub

• 実行後の画面
  

SortedListクラスを利用したコード　　　Topへ

• SortedListクラスを利用すると、短いコードで作成できました。
  • Keyに乱数を入れ、値にはSheet2のB列に入力された名前を入れます。
    Key順にSortされるので、ランダムに並んだ名前は.GetByIndex(i)で取り出して、描画していたシェイプに書き込んでいます。
  • なお、このコードでは生徒数40の例で、名前のリスト範囲など定数で指定してあります。
    必要に応じて修正する必要があります。

  Sub seki_B1() 　　Dim c As Range, i As Long 　　Dim DataList As Object 　　　　Set DataList = CreateObject("System.Collections.SortedList") 　　　　Randomize 　　　　For Each c In Worksheets("Sheet2").Range("B2:B41")　　' 名前のリスト範囲 　　　　　　DataList.Item(Rnd()) = c.Value 　　　　Next 　　　　For i = 0 To DataList.Count - 1　'0から始まっていますので-1としてあります 　　　　　　　Worksheets("Sheet1").Shapes("座席" & i + 1).TextFrame.Characters.Text = DataList.GetByIndex(i) 　　　　Next i 　　　　Set DataList = Nothing End Sub

• 実行後の画面です。
  

スポンサードリンク

PageViewCounter
　
　Since2006/2/27