Excel セルに名前を付けてみる

メルマガ発行内容

＜Excel セルに名前を付けてみる＞
こんにちは、
昔のメルマガを参考に楽して発行中の三流プログラマーＫｅｎ３です。

チョット退屈してきた人も居ると思うので、
今回は、
修正に強いプログラム作りの参考になれば
と
思い、
Range(番地)の参照方法から、
Excelのセルに付けた名前を使用する処理を書きたいと思います。

いつものセリフで、そんなたいしたことじゃないので今回もご安心を。

/*
 * １. Excel セルに名前を付けて使用する
*/

みなさんは、セルに名前を付けて、使用したことありますか？
なに？それ？

えっと、下記のような表を作りました。
    A列      B列
1 単価	      10
2 在庫数	 200
3 在庫金額	2000

ここで、B3に入っている計算式は？どんな式でしょうか？
簡単に書くと、
=B1*B2 でしょ。



はい、正解です。
なめてんじゃねえょ。

まぁまぁ、そんなに興奮しないで、、
気を取り直して続けると、
在庫金額って、単価*在庫数ですよね、
なので、=B1*B2ですよね。

話を進めると、
Excelの機能でセルに名前を付ける機能があって、
メニューから挿入--名前--定義
を選択します。


名前に単価を入力、
参照範囲に$b$1を選択してＯＫを押します。


同様に
メニューから挿入--名前--定義
名前に在庫数、参照範囲に$b$2を入れ、ＯＫを押します。


これで、下記のような使用方法が可能です。
=単価*在庫数 をＢ３のセルに入力します。

おっ、なんとか出来たみたいですね。

だから？何？って感じだけど。

セルに名前を付けておくと便利でしょ？

どこがぁ？

/*
 * ２．ＶＢＡから値を参照、セットするには
*/

おっと、これは、ＶＢＡ系のメルマガだったっけ。

ＶＢＡから値を参照、セットするには、
Rangeオブジェクトを使用します。
簡単に書くと、
Sub Macro1()
    Range("単価") = 20
End Sub
みたいな使い方が可能となります。

Sub Macro1()
    Range("B1") = 20
End Sub
より、わかり易いでしょ。

まぁ、何とも言えないよね。

う〜ん、コイツを説得するのには時間がかかりそうだなぁ、、、
手間かけて、あと２押しぐらいするか。


/*
 * ３．Excelのフォーマットに名前付きのセルを使用すると
*/

チョット前、ＭＤＢと同フォルダのExcelファイルを開く
( /vba/backno/vba007.html 参照)
をやったよね。

たしか、
ＡｃｃｅｓｓからＥｘｃｅｌファイルを開いて、データをセット
セット位置は、
ID・・・・・・セルB4
Name・・・・・セルC4
Address ・・・セルB6
TEL ・・・・・セルD7
にセットする。
だったよね。

で、完成したAccessのサンプルのソースは、

Private Sub コマンド0_Click()
On Error GoTo Err_コマンド0_Click

    Dim strXLSFILE As String   'Excelのファイル名格納場所
    Dim oApp       As Object   'Excelを操作するオブジェクト変数
    Dim strMDBPATH As String
    Dim strWORK    As String
    Dim i          As Integer  'カウンター変数


    'Accessの起動位置を取得 CurrentDb.NameにD:\xxxx\yyyy\zzz.mdbが入っている
    strWORK = CurrentDb.Name
        '後ろから１文字単位で￥を探す
    For i = Len(strWORK) To 1 Step -1
        If Mid(strWORK, i, 1) = "\" Then Exit For  '￥だったら抜ける
    Next i
        'D:\xxxx\yyyy\zzz.mdb --> D:\xxxx\yyyy\ にする
    strMDBPATH = Mid(strWORK, 1, i)

    'Excelの元ファイルの名前を作成　D:\xxxx\yyyy\ ＋ TYPE.xls
    strXLSFILE = strMDBPATH & "TYPE.xls"
    
    'ファイルの存在をチェックする
    If Dir(strXLSFILE) = "" Then
        MsgBox strXLSFILE & " を 確認して下さい"
        Exit Sub  '途中で抜ける
    End If

    Set oApp = CreateObject("Excel.Application")
    oApp.Visible = True
    'Only XL 97 supports UserControl Property
    oApp.UserControl = True

    '*1↓頭にCreateObjectで作成した変数を追加しただけ
    oApp.Workbooks.Open FileName:=strXLSFILE
    '^^^^

    '*2 Range("A1").Value ="XXXX" でデータをセットする
    oApp.Range("B4").Value = Me![ID]
    oApp.Range("C4").Value = Me![Name]
    oApp.Range("B6").Value = Me![Address]
    oApp.Range("D7").Value = Me![TEL]
    
Exit_コマンド0_Click:
    Exit Sub

Err_コマンド0_Click:
    MsgBox Err.Description
    Resume Exit_コマンド0_Click
    
End Sub

と、なんかやってたよね、ぐちゃぐちゃと。

それで、今回のポイントなんだけど、
    '*2 Range("A1").Value ="XXXX" でデータをセットする
    oApp.Range("B4").Value = Me![ID]
    oApp.Range("C4").Value = Me![Name]
    oApp.Range("B6").Value = Me![Address]
    oApp.Range("D7").Value = Me![TEL]
とAccessのフォームから開いたＥｘｃｅｌのセルにデータをセットしてるんだけど、

TYPE.xlsのセルに名前を付けて使用すると、
    '*2 Range(セルに付けた名前).Value ="XXXX" でデータをセットする
    oApp.Range("ID").Value = Me![ID]
    oApp.Range("氏名").Value = Me![Name]
    oApp.Range("住所").Value = Me![Address]
    oApp.Range("電話番号").Value = Me![TEL]
Ｂ４などのセル番地から、付けた名前で参照できます。

それが何か？
別にＢ４とかＣ４でいいんじゃない？
仕様書にＢ４にIDをセット、Ｃ４にNameをセット
明確に書いてあるんだからさ。

そうなんだけど、う〜ん、、、手ごわいなぁ。
よし次は、得意の架空の作り話だ。

/*
 * ４．将来、フォーマットを修正した時に効果が出る
*/

一行目にタイトル行を追加したい
と
客先から依頼が来ました。

そしたら、アナタの修正手順は？どんな感じですか？

Excel TYPE.xlsを開いて、一行挿入して、タイトル入力後に上書き保存、
Ａｃｃｅｓｓ側のセット処理は、
    oApp.Range("B4").Value = Me![ID]
    oApp.Range("C4").Value = Me![Name]
    oApp.Range("B6").Value = Me![Address]
    oApp.Range("D7").Value = Me![TEL]
を
    oApp.Range("B5").Value = Me![ID]
    oApp.Range("C5").Value = Me![Name]
    oApp.Range("B7").Value = Me![Address]
    oApp.Range("D8").Value = Me![TEL]
と
セット位置を＋１すれば修正完了でしょ。
チョロイ修正でしたね。

簡単に修正完了かぁ〜じゃなくって、
Ｅｘｃｅｌでセルに名前を付けてると、
一行挿入しても、名前の位置は相対的に移動するので、
Access側の
    '*2 Range(セルに付けた名前).Value ="XXXX" でデータをセットする
    oApp.Range("ID").Value = Me![ID]
    oApp.Range("氏名").Value = Me![Name]
    oApp.Range("住所").Value = Me![Address]
    oApp.Range("電話番号").Value = Me![TEL]
は、変更無しでそのままでＯＫなんですよ。（これホント！）

実行できるサンプル
http://www.ken3.org/vba/lzh/vba013.lzh
を落して、
行の追加や列の追加、
フォーマットを変更して、
遊んでみてください。

/*
 * ５．終わりの挨拶
*/

今日書いたことは、
・Ｅｘｃｅｌのセルに名前を付ける方法
・Range("単価") = 20 でＶＢＡから使用可能です
・応用すると、変更の少ないプログラム書けるよ
でした。

拾い読みして、
１つでも何かの参考となれば幸いです。

いろいろな環境に対応できない不器用な、三流プログラマーＫｅｎ３でした。

フィードバック

評価・感想

ページフッター(リンクや広告など)

情報を探す

Web www.ken3.org www.microsoft.com support.microsoft.com あとは、項目別にMSでお勉強かな？ ◆ マイクロソフト サイトの歩き方 ◇ How-To インデックス ◇ FAQ インデックス ◇ スキルレベル別おすすめコンテンツ ◇ テクノロジ マップ ◇ テーマ別技術資料一覧

書籍の購入

コンピュータ書籍の発送がハヤイ専門店

コンピュータの本・専門店

※種類が豊富で探し易いです。※在庫ありが48時間以内発送が急ぎで資料や書籍がほしい時、とても助かります。 お奨め本の目次を見るだけでも勉強になったり

amazon.co.jpでキーワード別チェック