【Excel】QRコードを一括作成する

「Google Charts API」を利用して、URL等を一括でQRコードにするプログラムを作成します。
商品パッケージやパンフレットなどへの添付にご活用ください。

このエクセルファイルの作り方

①マクロを使えるようにする
マクロを使えるようにする」を参照

②URL、QRコードの表を作成

③ボタン配置時の以下の画面で「マクロ名」を「CreateQrcode」として「新規作成」を押す

④Microsoft Visual Basic for Applications」にて以下のコードを記載

Sub CreateQrcode()
    '開始行
    Dim firstRow As Long: firstRow = 3
    '最終行の取得
    Dim lastRow As Long: lastRow = Cells(Rows.Count, 1).End(xlUp).Row

    'firstRow~lastRowまで繰り返し(途中エラーが出た場合も止めずに最後まで処理)
    On Error Resume Next
        For i = firstRow To lastRow

            'QRコード表示セルの高さなどを調整
            With Cells(i, "B")
                .RowHeight = 65
                .VerticalAlignment = xlTop '上詰め
            End With

            'GoogleAPIでQRコードを作成
            Set qr = ActiveSheet.Pictures _
                    .Insert("http://chart.apis.google.com/chart?cht=qr&chs=80x80&chl=" _
                            + Cells(i, "A").Value)

            'QRコードの表示位置を指定
            With qr
                .Top = Cells(i, "B").Top + 2
                .Left = Cells(i, "B").Left + 2
            End With
        Next i
    On Error GoTo 0
End Sub

【Excel】背の順や成績順で座席表を自動作成

背の小さい順や成績の悪い順で、座席を割り振りたいケースがあると思います。
今回は生徒の名簿一覧から背の順で座席を割り振る方法を紹介させて頂きます。

このエクセルファイルの作り方

①マクロを使えるようにする
マクロを使えるようにする」を参照

②生徒情報の表を作成

③座席表を作成

④席決め配置ボタンを配置する
 ※ ボタンの配置方法は「表を複数の条件で絞り込む②」を参照

⑤ボタン配置時の以下の画面で「マクロ名」を「 StartSeatSelection 」として「新規作成」を押す

⑥「Microsoft Visual Basic for Applications」にて以下のコードを記載

Sub StartSeatSelection()

Dim noCol As Integer: noCol = 1 '★
Dim nameCol As Integer: nameCol = 2 '★
Dim heightCol As Integer: heightCol = 3 '★

Dim startRow As Integer: startRow = 2 '★
Dim lastRow As Integer: lastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1

Dim studentList As Dictionary
Set studentList = New Dictionary

Dim tempStudentList As Dictionary
Set tempStudentList = New Dictionary

'「生徒情報」シートの内容を1行ずつ読み込み、studentListに身長順で格納します。
For i = startRow To lastRow Step 1

    Set tempStudentList = studentList
    Set studentList = New Dictionary

    Dim no As String
    no = Cells(i, noCol).Value
    Dim name As String
    name = Cells(i, nameCol).Value
    Dim height As String
     height = Cells(i, heightCol).Value
    
    If IsEmpty(no) Then
        MsgBox "生徒番号が空欄です。"
    End If
    If Not IsNumeric(no) Then
        MsgBox no & ":生徒番号には数値を指定してください。"
    End If
        
    If IsEmpty(name) Then
        MsgBox "氏名が空欄です。"
    End If
        
     If IsEmpty(height) Then
        MsgBox "身長が空欄です。"
    End If
    If Not IsNumeric(height) Then
        MsgBox height & ":身長には数値を指定してください。"
    End If
    
    If tempStudentList.Count > 0 Then
        For Each tempStudent In tempStudentList.Items
            If (tempStudent(2) > height Or (tempStudent(2) = height And tempStudent(0) > no)) And Not studentList.Exists(no) Then
                studentList.Add no, Array(no, name, height)
            End If
            
            studentList.Add tempStudent(0), Array(tempStudent(0), tempStudent(1), tempStudent(2))
         Next tempStudent
    End If
    

    If Not studentList.Exists(no) Then
        studentList.Add no, Array(no, name, height)
    End If

Next

'studentListを基に、座席に名前をあてはめます。

Dim startSheetRow As Integer: startSheetRow = 4 '★
Dim lastSheetRow As Integer: lastSheetRow = 14 '★
Dim startSheetCol As Integer: startSheetCol = 2 '★
Dim lastSheetCol As Integer: lastSheetCol = 12 '★

Dim targetRow As Integer
Dim targetCol As Integer

Dim studentListCnt As Integer: studentListCnt = 0 '★

For targetRow = startSheetRow To lastSheetRow Step 2
    For targetCol = startSheetCol To lastSheetCol Step 2
    
    Worksheets("座席表").Cells(targetRow, targetCol).Value = studentList.Items(studentListCnt)(1)
    studentListCnt = studentListCnt + 1
    
    Next targetCol
    
Next targetRow

End Sub

【Excel】CSVファイルをリアルタイム読み取り

VBAを利用して、随時更新されるcsvファイルをリアルタイムで読み取るプログラムを作成しました。
随時更新される売上データを題材に作成しています。

このエクセルファイルの作り方

①マクロを使えるようにする

マクロを使えるようにする」を参照

②元データとするCSVファイルを用意

③読み取ったCSVを書き込むテーブルを作成

④テーブルの値を集計する任意のピボットグラフを作成

⑤集計開始をするボタンを用意

※ボタンの追加方法は「以前の記事」を参考

⑥マクロを作成する

ボタン配置時の以下の画面で「マクロ名」を「Addup_Click」として「新規作成」を押す

⑤「Microsoft Visual Basic for Applications」にて以下のコードを記載

必要に応じて「★」のついた箇所を変更

Sub Addup_Click()
    Call ReadCSV
End Sub

Function ReadCSV()
    '★読み込み対象ファイルの絶対パスを指定
    Dim filePass  As String: filePass = "C:\SalesData.csv"
    '★書き込みを開始する行・列を指定
    Dim startRow As Integer: startRow = 2
    Dim startCol As Integer: startCol = 1
    '★定期実行する間隔を指定
    Dim waitSec As Single: waitSec = 5
    
    Dim targetRow As Integer: targetRow = startRow
    Dim targetCol As Integer: targetCol = startCol
    Dim rowBuf  As String
    Dim cellBufList As Variant
    
    Open filePass For Input As #1
    
    '全行読み込み終わるまで繰り返し
    Do Until EOF(1)
        '1行読み込み
        Line Input #1, rowBuf
        '読みこんだ1行をカンマで区切り配列化
        cellBufList = Split(rowBuf, ",")
    
        '配列分繰り返し
        For Each cellBuf In cellBufList
            Cells(targetRow, targetCol).Value = cellBuf
            '次の列へ
            targetCol = targetCol + 1
        Next cellBuf
        
        '次の行へ
        targetCol = startCol
        targetRow = targetRow + 1
    Loop
    Close #1
    
    'ピボットテーブルの更新
    Dim pvt As PivotTable

    For Each pvt In ActiveSheet.PivotTables
        pvt.PivotCache.Refresh
    Next
    
    '待機処理
    Dim Tm As Single
    Tm = Timer
    
    Do
        DoEvents
    Loop Until Timer > Tm + waitSec
    
    '再帰
    Call ReadCSV
    
End Function

【Excel】ビンゴゲームを作る

 

このエクセルファイルの作り方

①ビンゴゲームのレイアウトを作成する

・A1:L16 ⇒ ビンゴの数字表示欄
・M2:Q16 ⇒ ビンゴで出た数字の背景色を緑色で表示
・「BINGO」ボタン ⇒ ルーレットを開始して「A1:L16」に数字を表示
・「Initialize」ボタン ⇒ 初期化
※各ボタン配置時に設定するマクロ名はそれぞれ「Bingo」、「Initialize」とする

②Microsoft Visual Basic for Applications」にて以下のコードを記載

Sub Bingo()
'結果
Dim result As Integer
'(これまでに出た)結果リスト
Dim resultList As Variant: resultList = Range("A21:A95")

'1~75まで全て出ている場合はなにもしない
If (IsEmpty(Range("A95").Value) = False) Then
GoTo PROCESSINGEXIT
End If

'ボタンを配置したセルを選択する
Cells(1, 13).Select

'フォントサイズを設定
Worksheets("BINGO").Range("A2").Font.Size = 350

'ルーレット表示
For i = 1 To 100
Application.Wait [Now()] + 5 / 86400000
result = Int(75 * Rnd + 1)
Worksheets("BINGO").Range("A2").Value = result
Next

'重複制御
Do While (1)
result = Int(75 * Rnd + 1)
For i = 1 To UBound(resultList)
If (resultList(i, 1) = result) Then
GoTo BREAK
End If
Next
Exit Do
BREAK:
Loop
Worksheets("BINGO").Range("A2").Value = result

'結果書き込み
For i = 1 To UBound(resultList)
If (IsEmpty(Cells(i + 20, 1).Value) = True) Then
Cells(i + 20, 1).Value = result
Exit For
Else
GoTo CONTINUE
End If
CONTINUE:
Next

PROCESSINGEXIT:

End Sub
Sub Initialize()
Range("A21:A95").ClearContents<img src="https://exceldemon.net/wp-content/uploads/2018/11/条件付き書式.png" alt="" width="379" height="396" class="alignnone size-full wp-image-432" />
Worksheets("BINGO").Range("A2").Font.Size = 15
Worksheets("BINGO").Range("A2").Value = "Please press ""BINGO"" button"
End Sub

③条件付き書式を設定

※すでに出た数値の背景色を緑色とする

プログラムの解説

'ルーレット表示
For i = 1 To 100
Application.Wait [Now()] + 5 / 86400000
result = Int(75 * Rnd + 1)
Worksheets("BINGO").Range("A2").Value = result
Next

100回ループさせ、「Application.Wait [Now()] + 5 / 86400000」の記載で5ミリ秒ごと1~75のランダムな数値を表示

'重複制御
Do While (1)
result = Int(75 * Rnd + 1)
For i = 1 To UBound(resultList)
If (resultList(i, 1) = result) Then
GoTo BREAK
End If
Next
Exit Do
BREAK:
Loop
Worksheets("BINGO").Range("A2").Value = result

「Do While (1)」で無限ループを発生。
「result = Int(75 * Rnd + 1)」でresultに1~75のランダムな数値を格納する。

「For i = 1 To UBound(resultList)~Next」でresultList分ループし、
resultListにresultの数値がすでに含まれている場合はBREAKして再度1~75のランダムな数値を格納し直す。
含まれていない数値だった場合は「Exit Do」でループを抜けて、「A2」にresultの数値をセット。

'結果書き込み
For i = 1 To UBound(resultList)
If (IsEmpty(Cells(i + 20, 1).Value) = True) Then
Cells(i + 20, 1).Value = result
Exit For
Else
GoTo CONTINUE
End If
CONTINUE:
Next

「For i = 1 To UBound(resultList)~Next」でresultList分ループし、
値が入っていないCellまで来たら今回Resultに格納している数値を書き込む

Sub Initialize()
Range("A21:A95").ClearContents
Worksheets("BINGO").Range("A2").Font.Size = 15
Worksheets("BINGO").Range("A2").Value = "Please press ""BINGO"" button"
End Sub

「Range(“A21:A95”).ClearContents」で背景色が緑色になったところを元に戻す。
初期のフォントサイズと初期値を設定。

【Excel】指定した数だけデータをコピーする

①コピー元シートを用意
※ここでは「表を複数の条件で絞り込む②」で作成したシートを使用(数量列だけ追加)

②ペーストするシートを用意

③コピーを実行するボタンを配置

④ボタン配置時の以下の画面で「マクロ名」を「CreateLabelData」として「新規作成」を押す

⑤「Microsoft Visual Basic for Applications」にて以下のコードを記載
※表の形式に応じて「★」のついた箇所の数値を変更

Sub CreateLabelData()
'コピー開始行
Dim copyStartRow As Integer: copyStartRow = 5 '★
'コピー最終行
Dim copyLastRow As Integer: copyLastRow = ActiveSheet.UsedRange.Row + ActiveSheet.UsedRange.Rows.Count - 1
'コピー開始列
Dim copyStartCol As Integer: copyStartCol = 2 '★
'コピー終了列
Dim copyLastCol As Integer: copyLastCol = 4 '★
'数量列
Dim quantityCol As Integer: quantityCol = 5 '★

'ペーストするシート名
Dim pasteSheetName As String: pasteSheetName = "商品ラベルデータ" '★
'ペースト行
Dim pasteRow As Integer: pasteRow = 2 '★
'ペースト列
Dim pasteCol As Integer: pasteCol = 1 '★
'ペーストするシートの最終行
pasteLastRow = Worksheets(pasteSheetName).UsedRange.Row + Worksheets(pasteSheetName).UsedRange.Rows.Count - 1

'ペーストするシートの初期化
For i = pasteRow To pasteLastRow Step 1
Worksheets(pasteSheetName).Rows(pasteRow).Delete
Next

'コピー開始行から終了行までループ
For i = copyStartRow To copyLastRow Step 1
'数量の指定が不正な場合は次の行へ
If Cells(i, quantityCol).Formula = "" Or IsNumeric(Cells(i, quantityCol).Value) = False Or Cells(i, quantityCol).Value < 1 Then
GoTo Continue
Else
End If

'対象行の数量を取得
Dim quantity As Integer: quantity = Cells(i, quantityCol).Value

'数量分コピー&ペーストを行う
For j = pasteRow To pasteRow + quantity - 1 Step 1
Range(Cells(i, copyStartCol), Cells(i, copyLastCol)).Copy Destination:=Worksheets(pasteSheetName).Cells(j, pasteCol)
Next

'次回ペースト行の更新
pasteRow = pasteRow + quantity

'数量の値を初期化
Cells(i, quantityCol).Value = ""
Continue:
Next

'ペーストしたシートを選択
Worksheets(pasteSheetName).Select
Worksheets(pasteSheetName).Cells(1, 1).Select
End Sub