第24回.納品書を作成、商品情報を取得(2)
エクセルで顧客管理を作ります、
前回、、明細部の「商品番号」で「商品マスタ」より情報を取得しました、
このコードを使って、「顧客番号」で「顧客一覧」より情報取得する部分を変更します。
変更箇所は限定的です。
コードの使い回しの参考にして下さい。
また、このコードをコピペして、自身で少し改造して使ってみると、良く理解できると思います。
先に、エクセルのサンプル をアップします。サイズが大きくなってきたのでZIPにしました。
「納品書」のシートモジュールです。
以下の２つのSubモジュールを新規に作成しました。
Private Sub 顧客情報取得(ByVal Target As Range)
Dim lngRow As
Long, lngCol As Long '行列の計算用
Dim rngCode As Range '商品番号のセル
Dim
rngVlookup As Range 'Vlookupの範囲
Dim rngMatch As Range
'Matchの検索範囲
Dim tCell As Range
'変更されたセルの取り出し
Application.EnableEvents =
False
Application.Calculation = xlCalculationManual
For Each tCell In
Target
With シート取得("顧客一覧")
lngRow =
.Range("顧客番号").Rows(.Range("顧客番号").Rows.Count).Row
lngCol
= .Cells.SpecialCells(xlLastCell).Column
Set rngCode =
Range("納品書_顧客番号").Cells(1, 1)
Set rngVlookup =
.Range(.Range("顧客番号").Cells(1, 1),
_
.Cells(lngRow, lngCol))
Set rngMatch =
.Range(開始セル取得("顧客一覧"),
_
.Cells(.Range("顧客一覧開始").Cells(1, 1).Row,
lngCol))
End With
If Not Intersect(tCell,
Range("納品書_顧客番号")) Is Nothing
Then
Range("納品書_郵便番号").Cells(1, 1) =
""
Range("納品書_住所１").Cells(1, 1) =
""
Range("納品書_住所２").Cells(1, 1) =
""
Range("納品書_顧客名").Cells(1, 1) =
""
Range("納品書_担当者名").Cells(1, 1) = ""
Call
Get顧客情報(Range("納品書_郵便番号").Cells(1, 1),
_
"納品書_郵便番号", rngCode, rngVlookup,
rngMatch)
Call Get顧客情報(Range("納品書_住所１").Cells(1, 1),
_
"納品書_住所１", rngCode, rngVlookup,
rngMatch)
Call Get顧客情報(Range("納品書_住所２").Cells(1, 1),
_
"納品書_住所２", rngCode, rngVlookup,
rngMatch)
Call Get顧客情報(Range("納品書_顧客名").Cells(1, 1),
_
"納品書_顧客名", rngCode, rngVlookup,
rngMatch)
Call Get顧客情報(Range("納品書_担当者名").Cells(1, 1),
_
"納品書_担当者名", rngCode, rngVlookup,
rngMatch)
End If
Call Get顧客情報(tCell, "納品書_郵便番号",
rngCode, rngVlookup, rngMatch)
Call Get顧客情報(tCell,
"納品書_住所１", rngCode, rngVlookup, rngMatch)
Call
Get顧客情報(tCell, "納品書_住所２", rngCode, rngVlookup,
rngMatch)
Call Get顧客情報(tCell,
"納品書_顧客名", rngCode, rngVlookup, rngMatch)
Call
Get顧客情報(tCell, "納品書_担当者名", rngCode,
rngVlookup, rngMatch)
Next
Application.Calculation =
xlCalculationAutomatic
Application.EnableEvents = True
End
Sub
'tCell:対象セル
'strName:名前定義
'rngCode:検索値セル
'rngVlookup:Vlookup範囲
'rngMatch:Match検索範囲
Private
Sub Get顧客情報(ByVal tCell As Range, _
ByVal strName
As String, _
ByVal rngCode As Range, _
ByVal
rngVlookup As Range, _
ByVal rngMatch As Range)
Dim lngMatch
As Integer 'Matchで取得した列番号
If Intersect(tCell, Range(strName)) Is Nothing
Or _
Not IsEmpty(tCell) Then
Exit Sub
End If
On
Error Resume Next
lngMatch = Application.WorksheetFunction.Match(
_
Cells(Range(strName).Cells(1, 1).Row, 1),
_
rngMatch, _
0)
tCell.Value =
Application.WorksheetFunction.VLookup( _
rngCode,
_
rngVlookup, _
lngMatch,
_
False)
On Error GoTo 0
End
Sub
太字が、「商品情報取得」からの変更箇所です。
ほぼ、名前定義の名前の変更だけです。
唯一の違いは、
商品は複数行あるので、対象行の特定をしていましたが、それが不要になっていることと、
見出しが、A列固定にしている所です。
つまり、「番号」を入力し、VLOOKUPで取得する場合なら、
名前定義さえすれば、どちらかのパターンで、ほぼそのまま使えるはずです。
関数の３つの使い方
顧客情報の取得は、二転三転しました。
しかし、これは無駄ではありませんし、何より、その変遷が重要だと思います。
以下のように進みました。
手入力でVLOOKUPの関数を入力・・・MATCHの組み合わせ、それなりに難易度は高い。
↓
VBAで、関数を自動作成・・・このような手段が必要な場合もあります。
↓
VBAのVLookUpで、値を取得し設定
この３通りとも、必要な技術です。
どれが良いかは、ケースバイケースでしょう。
多分、VBAで関数を自動作成するのが、もっとも面倒だとは思います。
Subモジュールから、機能の一部を別のSubモジュールに
今回のコードでは、WorksheetFunctionを使う部分を、別モジュールにしています。
このように、Subモジュール内で、複数回同じ処理を繰り返す場合は、
その部分を別のモジュールにすることで、コードが短くすっきりします。
また、変更時も１箇所ですむようになり、「保守性」が高まります。
しかし、複数回同じ処理と言っても、全く同一ではありません。
（完全に同一処理では、繰り返す意味がありませんよね）
そこで、どこに違いがあるかを見極めます。
その違いが、全て変数に入れる事ができる違いならば、
それは、別モジュールにする事ができるということです。
そして、その変数が、引数となる訳です。
かといって、引数が何十個もあったり、
引数をIF文で複雑な判定をしなければならない場合も出てくるでしょう。
その場合は、さらに、その中の一部を別モジュールにしていきます。
最大公約数を段階的に求めていくような感じでしょうか。
例えば、
36、30、16、42、32
この最大公約数は・・・あれっ、何だ・・・6ではダメ、4もダメ・・・2ですかね。
この2をいきなり探して、Subモジュールにしたら、余計に面倒になってしまいます。
まず、36、30、42の最大公約数の6をSubモジュールにし、
次に、16、32の最大公約数の8をSubモジュールにします。
そして、さらに、6、8の最大公約数の2をSubモジュールにしていきます。
36、30、42は、それぞれ引数を、6、5、7にして、6のモジュールをCallします。
16、32は、それぞれ引数を、2、4にして、8のモジュールをCallします。
そして、6のモジュールからは引数3、8のモジュールからは引数4で、2のモジュールをCallします。
たまに見かけるのは、多くの引数を使い、さらに複雑な判定で処理を分けているものがあります。
それなら、コピーして、似たようなSubモジュールを複数作成し、
そこから、共通部分を見つけ出し、別のSubモジュールを作るべきです。
なんか、余計に分かりづらくなってしまいましたでしょうか。
言いたいのは、思考の過程です、いきなり複雑な事を考えるのではなく、
なるべく単純化して考えるということです。
私も、なかなか出来ないのですが、常に気を付けている事です。
WorksheetFunctionについて
これは、VBAにおいては有効に活用すべきです。
セルを上からぐるぐる回して探していたのでは、さすがに処理時間がかかってしまいます。
ワークシートで使う関数と、使い方（引数等）は同じです。
しかし、注意すべき点はあります。
これについては、そうですね、次々回あたりに詳しくやることにします。
それでは、また次回に。
同じテーマ「エクセル顧客管理」の記事
第21回.イベント処理について
第22回.コントールについて
第23回.納品書を作成、商品情報を取得(1)
第24回.納品書を作成、商品情報を取得(2)
第25回.納品書を作成、商品情報を取得(3)
第26回.WorksheetFunctionについて
第27回.RangeとCellsの深遠
第28回.納品書データをデータベース化(1)
第29回.納品書データをデータベース化(2)
第30回.配列の使い方について
第31回.売上一覧（伝票合計の一覧）を作成(1)
新着記事NEW ・・・新着記事一覧を見る
ブール型（Boolean）のis変数・フラグについて｜VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する｜VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次｜エクセルの神髄(2024-03-20)
VBA10大躓きポイント（初心者が躓きやすいポイント）｜VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成｜ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。｜エクセル雑感(2024-02-17)
初級脱出10問パック｜VBA練習問題(2024-01-24)
累計を求める数式あれこれ｜エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE｜エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。｜エクセル雑感(2023-12-28)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。