投稿者: 246riser

神奈川県在住、会社員、2児の父
技術ブログ（エクセルマクロの覚書き）、趣味ブログ（英語学習、バドミントン）、アラフォー男子の育児ブログなど、思いつきをただブログに羅列していきたいと思います。（ただの日記です）

カテゴリー
未分類

エクセルマクロでツールを作ってみた①

【目次】
　１．目的：シリアル通信の送受信を行う
　２．作業１：シートを作る
　３．作業２：コードを書く

１．目的：シリアル通信の送受信を行う

シリアル通信の超簡易的な送受信ツールをエクセルで作成します。

２．作業１：シートを作る

超簡易的なツールのため、必要最小限の機能をワークシート上に用意します。シートは以下の2つです。

①送受信シート
　送信データの設定、受信データの表示、送受信を行うボタンを
　配置したシート
　※ボタンの配置方法は、別のテーマで記載します（宿題）。

②通信設定シート
　ポート番号、ボーレート（通信速度）、データ長、パリティ
　ビットの種類、ストップビット（長）、各通信処理の待ち
　時間を設定するシート

３．コードを書く

今回、コードは「標準モジュール」の「Module1」に書きます。いろんなやり方があると思いますが、とにかくシンプルにします。
尚、私もいろいろなサイトを調べながら作ったもので、全てのコードを完璧に理解しているわけではありませんので、あくまで参考に留めて頂きたく、予めご了承下さい。また、参考ついでに作成、及び、動作確認した環境は以下の通りです。
　OS：Windows10 Home Edition（64ビット）
　アプリ：Microsoft Office Home and Business 2019

‘シリアルポートのオープン関数の定義
Declare Function CreateFile Lib “kernel32” Alias “CreateFileA” _
        (ByVal lpFileName As String, ByVal dwDesiredAccess As Long, _
         ByVal dwShareMode As Long, ByVal lpSecurityAttributes As Long, _
         ByVal dwCreationDisposition As Long, ByVal dwFlagsAndAttributes As Long, _
         ByVal hTemplateFile As Long) As Long

‘シリアルポートのクローズ関数の定義
Declare Function CloseHandle Lib “kernel32” _
        (ByVal hObject As Long) As Long

‘シリアルポートの設定関数の定義
Declare Function SetCommState Lib “kernel32” _
        (ByVal hCommDev As Long, lpDCB As DCB) As Long

‘タイムアウトの設定関数の定義
Declare Function SetCommTimeouts Lib “kernel32” _
    (ByVal hFile As Long, lpCommTimeouts As COMMTIMEOUTS) As Long

‘データの送信関数の定義
Declare Function ReadFile Lib “kernel32” _
        (ByVal hFile As Long, lpBuffer As Any, ByVal nNumberOfBytesToRead As Long, _
         lpNumberOfBytesRead As Long, lpOverlapped As Long) As Long

‘データの受信関数の定義
Declare Function WriteFile Lib “kernel32” _
        (ByVal hFile As Long, lpBuffer As Any, ByVal nNumberOfBytesToWrite As Long, _
         lpNumberOfBytesWritten As Long, lpOverlapped As Long) As Long

‘ DCB構造体の定義
Type DCB
    DCBlength As Long ‘構造体のサイズ
    baudrate As Long ‘ボーレイト（bps）の設定
    fBitFields As Long ‘ビット単位のフィールド定義
    wReserved As Integer ‘予約（0をセットする）
    XonLim As Integer ‘受信バッファ中のデータが何バイトになったらXon文字を送るかを指定
    XoffLim As Integer ‘受信バッファの空きが何バイトになったらXoff文字を送るかを指定
    ByteSize As Byte ‘1データのビット数を指定
    Parity As Byte ‘パリティの方式を指定
    StopBits As Byte ‘ストップビット数を指定
    XonChar As Byte ‘Xon文字を指定
    XoffChar As Byte ‘Xoff文字を指定
    ErrorChar As Byte ‘パリティエラーの場合に使う文字を指定
    EofChar As Byte ‘非バイナリモードの場合のデータ終了文字の指定
    EvtChar As Byte ‘イベントを生成する文字を指定
End Type

‘ COMMTIMEOUTS構造体の定義
Type COMMTIMEOUTS
    ReadIntervalTimeout As Long ‘文字の読み込みの待ち時間
    ReadTotalTimeoutMultiplier As Long ‘読み込みの１文字あたりの時間
    ReadTotalTimeoutConstant As Long ‘読み込みの定数時間
    WriteTotalTimeoutMultiplier As Long ‘書き込みの１文字あたりの時間
    WriteTotalTimeoutConstant As Long ‘書き込みの定数時間
End Type

Private hComm As Long ‘ポートのハンドル
Private stDCB As DCB ‘DCB構造体を変数に割り付ける
Private timeOut As COMMTIMEOUTS ‘COMMTIMEOUTS構造体を変数に割り付ける

‘ 送受信ボタン押下時の処理
Sub comm_start()

    ‘=== シリアルポートをオープンする ===
    Const GENERIC_READ = &H80000000 ‘読み取り用定数
    Const GENERIC_WRITE = &H40000000 ‘書き込み用定数
    Const OPEN_EXISTING = 3 ‘既存のファイルの処理
    comname = Sheets(“通信設定シート”).Cells(2, 2).Value ‘使用するポートの選択

    ‘ポートのオープン
    hComm = CreateFile(comname, GENERIC_READ Or GENERIC_WRITE, 0, 0, OPEN_EXISTING, 0, 0)
    CommInfo = hComm

    ‘ポートのオープン失敗時
    If hComm = -1 Then
        ‘警告メッセージ アイコンを表示
        MsgBox comname & “が使えません”, vbCritical
        End ‘強制終了
    End If

    ‘=== シリアル通信の設定 ===
    stDCB.baudrate = Sheets(“通信設定シート”).Cells(3, 2).Value ‘転送速度の指定
    stDCB.ByteSize = Sheets(“通信設定シート”).Cells(4, 2).Value ‘ビット長の指定
    stDCB.fBitFields = &H3001 ‘バイナリモードのフラグを有効にし、
                                        ‘RTSの制御を設定する（それ以外はFalseにする）
    stDCB.Parity = Sheets(“通信設定シート”).Cells(5, 2).Value ‘パリティの設定
    stDCB.StopBits = Sheets(“通信設定シート”).Cells(6, 2).Value ‘ストップビットを指定
    dummy = SetCommState(hComm, stDCB) ‘必要な部分だけ書き換える

    ‘=== タイムアウトの設定 ===
    timeOut.ReadIntervalTimeout = Sheets(“通信設定シート”).Cells(7, 2).Value ‘文字の読み込み待ち時間
    timeOut.ReadTotalTimeoutMultiplier = Sheets(“通信設定シート”).Cells(8, 2).Value ‘読み込みの１文字あたりの時間
    timeOut.ReadTotalTimeoutConstant = Sheets(“通信設定シート”).Cells(9, 2).Value ‘読み込みの定数時間
    timeOut.WriteTotalTimeoutMultiplier = Sheets(“通信設定シート”).Cells(10, 2).Value ‘書き込みの１文字あたりの時間
    timeOut.WriteTotalTimeoutConstant = Sheets(“通信設定シート”).Cells(11, 2).Value ‘書き込みの定数時間
    dummy = SetCommTimeouts(hComm, timeOut) ‘タイムアウトの設定

    ‘=== データの送信 ===
    Dim wData As String ‘送信データの変数宣言（文字変数として定義）
    Dim wLen As Long ‘送信されたデータ長の変数宣言
    Dim dLen As Long ‘送信するデータ長の変数宣言
    Dim snd_data As String ‘送信するデータ

    snd_data = Sheets(“送受信シート”).Cells(2, 3).Text ‘送信データの設定
    wData = snd_data & Chr(13) ‘入力データ＋リターン符号の付加
    dLen = LenB(StrConv(wData, vbFromUnicode)) ‘ANSI+DBCS文字でのバイト数に換算
    dummy = WriteFile(hComm, ByVal wData, dLen, wLen, 0) ‘データの送信

    ‘=== データの受信 ===
    Dim rData As String ‘受信データの変数宣言（文字変数として定義）
    Dim rLen As Long ‘受信されたデータ長の変数宣言

    rData = space(100) ‘100文字分の領域確保
    dummy = ReadFile(hComm, ByVal rData, 100, rLen, 0) ‘データの受信
    Sheets(“送受信シート”).Cells(3, 3).Value = RTrim(rData) ‘後ろの空白を削除する

    ‘=== シルアルポートを閉じる ===
    dummy = CloseHandle(hComm)

End Sub

参考になれば幸いです。

カテゴリー
未分類

エクセルマクロで使える関数を作ってみた③

【目次】
　１．「Range」で指定する行の範囲（文字列）を作る関数
　２．「Range」で指定する列の範囲（文字列）を作る関数

１．「Range」で指定する行の範囲（文字
　　列）を作る関数

通常、「Range」で行を指定する場合、”1:9″ のように指定行を数字とコロン（:）を組み合わせた文字列で与える必要があります。

（例．1～9行目に空行を挿入する）
        Sheets(“Sheet1”).Range(“1:9”).Insert

しかし、マクロで行番号を使ってセル操作をする場合、このセルの指定方法は不便なので、行番号から “1:9” のような文字列を取得する関数を作ってみました。

'===========================
' 関数名：MakeRowRange
' 引数　：
' ①row1: 行番号1 (Long型)
' ②row2：行番号2（Long型）
' 戻り値：正常時：開始行と終了行の文字列（例."2:5"）（String型）
'    ：異常時："ERR"
'===========================
Function MakeRowRange(row1 As Long, row2 As Long) As String
    'ローカル変数の宣言
    Dim ret As String       '戻り値

    'ローカル変数の初期化
    ret = "ERR"

    '入力値の範囲判定
    If row1 >= 1 And row1 <= 1048576 And row2 >= 1 And row2 <= 1048576 Then
        '行情報から文字列を作成
        ret = CStr(row1) & ":" & CStr(row2)
    Else
        MsgBox "1～1048576の範囲で入力して下さい" & vbCrLf & "入力値：開始行：" & row1 & " , 終了行：" & row2
    End If

    MakeRowRange = ret
End Function

２．「Range」で指定する列の範囲（文字
　　列）を作る関数

通常、「Range」で列を指定する場合、”A:Z” のように指定列をアルファベットとコロン（:）を組み合わせた文字列で与える必要があります。

（例．1～9行目に空行を挿入する）
        Sheets(“Sheet1”).Range(“1:9”).Insert

しかし、マクロで列番号（数字）を使ってセル操作をする場合、このセルの指定方法は不便なので、列番号（数字）から “A:Z” のような文字列を取得する関数を作ってみました。
（要領は「エクセルマクロで～②」と同じです）

'===========================
' 関数名：MakeColumnRange
' 引数　：
' ①col1: 列番号1 (Long型)
' ②col2：列番号2（Long型）
' 戻り値：正常時：開始列と終了列の文字列（例."C:F"）（String型）
'    ：異常時："ERR"
'===========================
Function MakeColumnRange(col1 As Long, col2 As Long) As String
    'ローカル変数の宣言
    Dim col_array(2) As Long        '列情報
    Dim col_str_array(2) As String  '列情報
    Dim tmp_num1 As Long            '途中計算の一時変数１
    Dim tmp_num2 As Long            '途中計算の一時変数２
    Dim tmp_num3 As Long            '途中計算の一時変数３
    Dim err_array(2) As Boolean     '入力値判定結果
    Dim loop_cnt As Long            'ループカウンタ
    Dim ret As String               '戻り値

    'ローカル変数の初期化
    col_array(1) = col1
    col_array(2) = col2
    col_str_array(1) = ""
    col_str_array(2) = ""
    tmp_num1 = -1
    tmp_num2 = -1
    tmp_num3 = -1
    err_array(1) = True
    err_array(2) = True
    ret = "ERR"

    '列情報を文字列に変換
    For loop_cnt = 1 To 2
        If col_array(loop_cnt) >= 1 And col_array(loop_cnt) <= 16384 Then           'A～XFD列'
            err_array(loop_cnt) = False
            If col_array(loop_cnt) >= 1 And col_array(loop_cnt) <= 26 Then          'A～Z列'
                col_str_array(loop_cnt) = Chr(Asc("A") + col_array(loop_cnt) - 1)
            ElseIf col_array(loop_cnt) >= 27 And col_array(loop_cnt) <= 702 Then    'AA～ZZ列'
                tmp_num1 = Int((col_array(loop_cnt) - 26 - 1) / 26)
                tmp_num2 = (col_array(loop_cnt) - 1) Mod 26
            col_str_array(loop_cnt) = Chr(Asc("A") + tmp_num1) & Chr(Asc("A") + tmp_num2)
            Else                                    'AAA～XFD列'
                tmp_num1 = Int((col_array(loop_cnt) - 702 - 1) / (26 ^ 2))
                tmp_num2 = Int((col_array(loop_cnt) - 702 - 1 - tmp_num1 * (26 ^ 2)) / 26)
                tmp_num3 = (col_array(loop_cnt) - 1) Mod 26
            col_str_array(loop_cnt) = Chr(Asc("A") + tmp_num1) & Chr(Asc("A") + tmp_num2) & Chr(Asc("A") + tmp_num3)
            End If
        End If
    Next

    '入力値が正常範囲内の場合、正常な戻り値を設定する
    If err_array(1) = False And err_array(2) = False Then
        ret = col_str_array(1) & ":" & col_str_array(2)
    Else    '入力値が範囲外の場合、エラーとする
        MsgBox "1～16384の範囲で入力して下さい" & vbCrLf & "入力値：開始列：" & col1 & " , 終了列：" & col2
    End If

    MakeColumnRange = ret
End Function

参考になれば幸いです。

カテゴリー
エクセルマクロ

エクセルマクロで使える関数を作ってみた②

【目次】
　 １．「Range」で指定するセルの内容を、行情報と列情報
　　　から作成する関数

１．「Range」で指定するセルの内容を、行情報と列情報から作成する関数

通常、「Range」を使う場合、”A1″ のようにアルファベットと数字を組み合わせたセル指定をする必要があります。

（例．指定セル「A1」の文字色を青色に設定する）
        Sheets(“Sheet1”).Range(“A1”).Font.Color = RGB(0, 0, 255)

しかし、マクロで行番号と列番号を使ってセル操作をする場合、このセルの指定方法は不便なので、行番号と列番号から “A1” のようなセル位置を取得する関数を作ってみました。

‘===========================
‘ 関数名：MakeCellID
‘ 引数　：
‘ ①row: 行番号 (Long型)
‘ ②col：列番号（Long型）
‘ 戻り値：正常時：セルID（例.”A1″）（String型）
‘ ：異常時：”ERR”
‘===========================
Function MakeCellID(row As Long, col As Long) As String

    ‘ローカル変数の宣言
    Dim row_id As String ‘行情報
    Dim col_id As String ‘列情報
    Dim tmp_num1 As Long ‘途中計算の一時変数１
    Dim tmp_num2 As Long ‘途中計算の一時変数２
    Dim tmp_num3 As Long ‘途中計算の一時変数３
    Dim err As Boolean ‘入力値判定結果
    Dim ret As String ‘戻り値

    ‘ローカル変数の初期化
    row_id = “”
    col_id = “”
    tmp_num1 = -1
    tmp_num2 = -1
    tmp_num3 = -1
    err = True
    ret = “ERR”

    ‘行情報を文字列に変換
    row_id = CStr(row)

    ‘列情報を文字列に変換
    If col >= 1 And col <= 16384 Then ‘A～XFD列
        err = False
        If col >= 1 And col <= 26 Then ‘A～Z列
            col_id = Chr(Asc(“A”) + col – 1)
        ElseIf col >= 27 And col <= 702 Then ‘AA～ZZ列
            tmp_num1 = Int((col – 26 – 1) / 26)
            tmp_num2 = (col – 1) Mod 26
            col_id = Chr(Asc(“A”) + tmp_num1) & Chr(Asc(“A”) + tmp_num2)
        Else ‘AAA～XFD列
            tmp_num1 = Int((col – 702 – 1) / (26 ^ 2))
            tmp_num2 = Int((col – 702 – 1 – tmp_num1 * (26 ^ 2)) / 26)
            tmp_num3 = (col – 1) Mod 26
            col_id = Chr(Asc(“A”) + tmp_num1) & Chr(Asc(“A”) + tmp_num2) & Chr(Asc(“A”) + tmp_num3)
        End If
    Else ‘入力値が範囲外の場合、エラーとする
        MsgBox “1～16384の範囲で入力して下さい” & vbCrLf & “入力値：行：” & row & ” , 列：” & col
    End If

    ‘入力値が正常範囲内の場合、正常な戻り値を設定する
    If err = False Then
        ret = col_id & row_id
    End If

    MakeCellID = ret
End Function

■■追記■■
よく調べてみると、上記のような変換をしなくても、RangeにCells、Rows、Columnsを組み合わせることで行情報と列情報からセルを指定することができることが分かりました。何か活用方法を思いついたら、また書きたいと思います。

参考になれば幸いです。

カテゴリー
エクセルマクロ

エクセルマクロで使える関数を作ってみた①

【目次】
　 １．セルの内容を取得する関数
　 ２．セルに値や文字を設定する関数

１．セルの内容を取得する関数

通常、セルの内容を取得する場合、以下のような記述となります。

    val1 = Sheets(“Sheet1”).Cells(1,1).Value ‘ セル内容をval1に取得

しかし、この記述の構成が分からないといけないし、沢山あるとコードが冗長になるので、分かりやすい関数名と引数の構成で関数を作ってみました。

‘===========================
‘ 関数名：GetCellValue
‘ 引数　：
‘ ①sht：シート名（String型）
‘ ②row：行番号（Long型）
‘ ③col：列番号（Long型）
‘ 戻り値：セルの内容（数値・文字）（String型）
‘===========================
Function GetCellValue( sht As String, row As Long, col As Long ) As String
    GetCellValue = Sheets( sht ).Cells( row, col ).Value
End Function

２．セルに値や文字を設定する関数

通常、セルに値や文字を設定する場合、以下のような記述となります。

　    Sheets(“Sheet1”).Cells(1,1).Value = val1 ‘ セル”A1″にval1を設定

セルに値や文字を設定する関数も、取得する関数と同様に作ってみました。

‘===========================
‘ 関数名：SetCellValue
‘ 引数　：
‘ ①sht：シート名（String型）
‘ ②row：行番号（Long型）
‘ ③col：列番号（Long型）
‘ 戻り値：なし
‘===========================
Function SetCellValue( sht As String, row As Long, col As Long, val As String )
    Sheets( sht ).Cells( row, col ).Value = val
End Function

参考になれば幸いです。