A1形式で位置の加算と減算ができるVBAを作成する:Excel

セル位置を設定するのにOFFSET関数がありますが、直観的に使いにくいので、単純に加算と減算でセル位置を取得できるVBAを作成します。



Homeに戻る > Excel セルのTipsへ

Excelでセルを指定するには、CellsやOffsetなどを使いますが一番分かり易いのは「A5」等のA1形式です。

そこで、A1形式でセル位置が加減算できるようにしました。

セル位置に行の移動数と列の移動数を指定すると、移動先のセル位置がA1形式で求められます。

列の場合はASC関数で数字に変換し算出し、その後、CHR関数でアルファベットに戻しています。

関連するTipsの「ADDRESS セルのアドレスを取得するExcel関数」も参照してください。


呼び出し側のExcelコード

セルAA5から 5行 -1列 のセルをA1形式で得るには

s = A1Add("AA5", 5, -1) のように呼び出します。

sは[ Z10 ]になります。




セル位置を加算・減算する Excelモジュールコード

プログラムの流れ

  1. A1形式のローマ字をチェックし、ローマ字部分(列)と数字部分(行)に分けます。
  2. 列のローマ字をASC関数で数字に変換し、加算を行います。
  3. 列を数字からCHR関数でローマ字に戻します。
  4. 行番号の加算を行います。
  5. 列と行を連結し返します。

Public Function A1Add(sa As String, nr As Long, nc As Long) As String
    Dim i As Long
    Dim j As Long
    Dim srow As String
    Dim scol As String
    Dim lrow As Long
    Dim lcol As Long
    
    srow = ""
    scol = ""
    sa = UCase(sa)
    '行列を分ける
    For i = 1 To Len(sa)
        If Mid(sa, i, 1) >= "A" And Mid(sa, i, 1) <= "Z" Then
            srow = srow & Mid(sa, i, 1)
        ElseIf Mid(sa, i, 1) <> "$" Then
            scol = scol & Mid(sa, i, 1)
        End If
    Next
    
    'A1形式の列を列番号にする
    If Len(srow) = 1 Then
        lrow = Asc(srow) - 64
    Else
        lrow = (Asc(Left(srow, 1)) - 64) * 26
        lrow = lrow + (Asc(Right(srow, 1)) - 64)
    End If
    
    '加算
    If nc > 0 Then
        If lrow + nc <= 65536 Then
            lrow = lrow + nc
        End If
    ElseIf nc < 0 Then
        If lrow + nc >= 1 Then
            lrow = lrow + nc
        End If
    End If
    
    '列番号をA1形式に戻す
    If lrow <= 26 Then
        srow = Chr(64 + lrow)
    Else
        i = Int(lrow / 26)
        j = lrow Mod 26
        If j = 0 Then
            srow = Chr(64 + i - 1) & "Z"
        Else
            srow = Chr(64 + i) & Chr(64 + j)
        End If
    End If
    
    '行番号の加算
    lcol = scol
    If nr > 0 Then
        If lcol + nr <= 65536 Then
            lcol = lcol + nr
        End If
    ElseIf nr < 0 Then
        If lcol + nr >= 1 Then
            lcol = lcol + nr
        End If
    End If
    scol = lcol
    
    A1Add = srow & scol
End Function



Homeに戻る > Excel セルのTipsへ

■■■
このサイトの内容を利用して発生した、いかなる問題にも一切責任は負いませんのでご了承下さい
■■■
当ホームページに掲載されているあらゆる内容の無許可転載・転用を禁止します


Copyright (c) Excel-Excel ! All rights reserved