＜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 でＶＢＡから使用可能です
・応用すると、変更の少ないプログラム書けるよ
でした。
拾い読みして、
１つでも何かの参考となれば幸いです。
いろいろな環境に対応できない不器用な、三流プログラマーＫｅｎ３でした。