趣味のIT
自宅サーバーの構築
ApacheとTomcatの連携
Javaのインストール
JavaWebStartの実装
Webプログラミング
JavaScript
Java Applet
PHP
Java Web Start
エクセルプログラミング
VBAでお絵かき
値を貼り付け
セル参照←

エクセルプログラミング

セル参照

VBAでプログラミングしているときに一番困るのがセルの参照です。
例えば、
Cells(5, 2) = "A"
としていても、表の2行目に行を挿入した場合、
Cells(6, 2) = "A"
と修正しなければなりません。
これが2列目から10列目まであった場合、
Cells(6, 2) = "A"
Cells(6, 3) = "B"
Cells(6, 4) = "C"
Cells(6, 5) = "D"
Cells(6, 6) = "E"
Cells(6, 7) = "F"
Cells(6, 8) = "G"
Cells(6, 9) = "H"
Cells(6, 10) = "I"
            
といくつも修正する必要があります。

変数の利用

この煩わしい作業の解決には変数を使うことで解決できます。
r = 5
Cells(r, 2) = "A"
Cells(r, 3) = "B"
Cells(r, 4) = "C"
Cells(r, 5) = "D"
Cells(r, 6) = "E"
Cells(r, 7) = "F"
Cells(r, 8) = "G"
Cells(r, 9) = "H"
Cells(r, 10) = "I"
            
とすることで、変数 r を5から6に修正するだけで修正できます。
このように規則正しいデータならこの方法で何とかなりますが、 シート上に散在するセルを修正するにはやはり多くの手間がかかります。

関数の利用

そこで今回の解決方法です。
エクセルの関数にはセルの行番号と列番号を取得するものがあります。 これを利用してセルの参照を1箇所にまとめて行おうというものです。

  • Row関数:セルの行番号を取得
  • Column関数:セルの列番号を取得

例題

例題に沿って進めていきましょう。 まず、下のようなシートを用意します。

  • 数1、数2の二つの入力欄がある
  • 合計という欄に2つの数字の合計が表示される
  • 計算はボタンを押すことで実行される

次に、変数専用のシートを追加します。 そのシートには以下の関数を記述しましょう。

実際のワークシートには次のように表示されているはずです。

VBAの追加

デザインモードにした後、ボタンをダブルクリックしてコード入力画面にして、 以下のコードを入力します。
Option Explicit

Dim sNum1 As String
Dim sNum2 As String
Dim sAnswer As String
Dim rNum1 As Integer
Dim rNum2 As Integer
Dim rAnswer As Integer
Dim cNum1 As Integer
Dim cNum2 As Integer
Dim cAnswer As Integer

Private Sub CommandButton1_Click()
    SetConstants "Num1", sNum1, rNum1, cNum1
    SetConstants "Num2", sNum2, rNum2, cNum2
    SetConstants "Answer", sAnswer, rAnswer, cAnswer
    
    Worksheets(sAnswer).Cells(rAnswer, cAnswer) = _
                    Worksheets(sNum1).Cells(rNum1, cNum1) _
                    + Worksheets(sNum2).Cells(rNum2, cNum2)
    
End Sub

Private Sub SetConstants(n As String, s As String, r As Integer, c As Integer)
    Dim i As Integer
    i = 2
    With Worksheets("変数")
        Do Until .Cells(i, 1) = ""
            If .Cells(i, 2) = n Then
                s = .Cells(i, 3)
                r = .Cells(i, 4)
                c = .Cells(i, 5)
            End If
            i = i + 1
        Loop
    End With
End Sub
            

動作確認

デザインモードを解除して動作を確認してみましょう。
数字を2個入力して「計算」ボタンを押すと合計値が記入されます。

完成したら、行や列を挿入してセルの位置を変えて実行してみてください。
指定欄に合計値が記入されるはずです。
セルをドラッグして移動させても大丈夫と思います。
ただし、セルを削除したら関数がエラーとなりますのでマクロは正常に動作しません。

クラス

コードを見ると同じような引数を何度も受け渡ししています。 ここはクラスを使ってもう少しコードを簡便化しましょう。
Visual Basic Editor でクラスモジュールを追加して、ClassCell という名前にします。 このクラスに以下のコードを入力します。
Private cSheet As String
Private cRow As Integer
Private cCol As Integer

Public Function SetName(s As String)
    Dim i As Integer
    i = 2
    With Worksheets("変数")
        Do Until .Cells(i, 1) = ""
            If .Cells(i, 2) = s Then
                cSheet = .Cells(i, 3)
                cRow = .Cells(i, 4)
                cCol = .Cells(i, 5)
            End If
            i = i + 1
        Loop
    End With
End Function

Public Property Get Sheet() As String
    Sheet = cSheet
End Property

Public Property Get Row() As Integer
    Row = cRow
End Property

Public Property Get Col() As Integer
    Col = cCol
End Property
            

更に、前項で書いた計算シートのコードを以下のものに差し替えます。

Option Explicit

Dim Num1 As New ClassCell
Dim Num2 As New ClassCell
Dim Answer As New ClassCell

Private Sub CommandButton1_Click()
    Num1.SetName "Num1"
    Num2.SetName "Num2"
    Answer.SetName "Answer"
     
     Worksheets(Answer.Sheet).Cells(Answer.Row, Answer.Col) = _
                    Worksheets(Num1.Sheet).Cells(Num1.Row, Num1.Col) _
                    + Worksheets(Num2.Sheet).Cells(Num2.Row, Num2.Col)
    
End Sub
            
出来上がったら動作を確認してください。 コードはかなり簡潔になっていますが、問題なく合計が記入されたと思います。

おわりに

これで、セルの位置を変える度にコードを変更する手間は省くことができました。
変数を追加する時には、変数のシートに変数名と件数を下に付け足していきます。 VBAで変数を宣言して、プロシージャを呼び出せばOKです。
入力画面の設計にご使用ください。

ただ、コード的にはあまりスマートではありません。 引数から変数に変換できる方法はないのでしょうか?
と、言っている意味がわからないかと思いますが、もう少しきれいな方法があったら教えてください。

サンプルファイルのダウンロードはこちら→ Cells.xls
クラスを使ったサンプルファイルはこちら→ Cells2.xls

Copyright (C) 2006 T.Fujiwara , All rights reserved.