エクセルで検索したセルの隣のセルを調べるユーザー関数

Question

初めて質問致します。お手柔らかにどうぞよろしくお願いいたします。

vba初心者で、上記タイトルの関数を作ろうとしています。
まず"ID"で、データベースの1列目を検索して範囲を1行にを絞り、
次に"キーワード"で行内を検索して、
そのキーワードの2つ隣の値を調べる関数です。

以下のコードで、どこに間違いがあるかのか分かりません。
分かる方がいらっしゃったら、どうかご指摘ください。

Function SSEARCH(word As Variant, ID As Variant) As Variant
    Dim row As Range
    Dim cell As Range
    For Each cell In ThisWorkbook.Worksheets("データベース").Columns(1)
        If cell = ID Then
            Exit For
        End If
    Next cell
    row = cell.row
    For Each cell In row
        If cell = word Then
            Exit For
        End If
    Next cell
    SSEARCH = cell.Offset(0, 2)
End Function

WindFaller · Accepted Answer

こんばんは。

本来は、もう少し、文法的な部分を学んだほうがよいと思います。どこかに書いてあったものと、自分で合わせたものとを組み合わせたようにお見受けできます。しかし、今、自己流になってしまうと、なかなか癖が抜けなくなります。それは、結果的に、上達を遅らせ、ミス(バグ）が多くなります。

row = cell.row
特に、この部分は、いずれ、VBAがなくなってしまって、VB.Net にでもなった時には、有効にはなりますが、その意味を十分に理解したほうがよいです。

まず、変数の付け方のルールがあります。

VBAなどのプログラミングでは、5文字ぐらいになると大文字・小文字を組み合わせて使います。決まったものがないようでいながら、ある程度、使いこなすようになると決まったスタイルがあるものなのです。

それから、ユーザー定義関数で、VBAを学ぶなら、ぜひ、ご自身で、SUM関数を観察しながら作ってみてください。ほぼ同じ働きに近づけます。初級から、中級と幅広いテクニックを学べます。VBAの一般のテキストには書かれていないことも含まれています。生半可な知識ではできません。SUM関数の原型や他の約20個ぐらいの基礎の関数を、アメリカの大学生が思いついて作ったそうです。

さて、問題点を挙げさせていただきます。
・今回のようなスタイルは、一般的には「ユーザー定義関数」とは言いにくいものがあり、そうでないものを、「Function プロシージャ」と呼んでもよいと思います。「ユーザー定義関数」は、汎用性がありますが、今回の内容は、ブックやシートを決めてしまっているからです。Excel VBAでは、あまりこういうスタイルのFunctionは作りません。

・Row はプロパティ名や関数名を、変数に使ってもエラーは、ほとんど出ませんが、プロパティの大文字・小文字の組み合わせが狂って、コードが読みにくくなってしまいます。Row →rw , Cell→c (Cellはプロパティやオブジェクト名ではありませんが、間際らしいです）, word 本来は好ましくありませんが、以下では残しました。

余談ですが、ベテランの人でも間違う、変数名に「str」 というものがあります。古くからVB6を使っいた方は、お気づきになると思いますが、Str関数というものがあります。だから、VBAでも、それを使うのは好ましくありません。

・ユーザー定義関数では、先に書きましたが、本来、ThisWorkbook.Worksheets("データベース")のような、オブジェクトを特化するコードは入れません。入れたい場合、引数側から入れます。Rangeオブジェクトを引数にしたほうがよいです。

・.Columns(1)は、全体がひとつですから、セルが必要なら、.Columns(1).Cells とします。

・セルのオブジェクトには、.Valueなどのプロパティを入れたほうが見やすいです。RangeやCellsは、「暗黙的(Implicit)」に、Value値になりますが、できるだけ、「明示的(Explicit)」に使用したほうがよいです。(この片方の英単語は、ご存知ですよね。）

・コードだけでは気づきませんが、"If c.Value = ID Then"の「Exit For」 で抜け出す項目を一つ増やしたほうがよいかもしれません。例えば、"If c.Value ="" Then などです。

ループをして、仮に、もし検索語が見当たらずに、その行・列の全てのセルを調べることになったら、現在ではハング状態になります。なるべく全てのセルを調べるのは避けたいです。

本来、Rangeオブジェクトを引数にして、範囲を限定する必要があります。(今回のコードのままでは、あまり原型を崩してしまうのでやめました。）

'//今回のコードを修正した内容です。これがベストではありません。
'//(なるべく、元の部分を残しています。）

Function sSearch(word As Variant, ID As Variant) As Variant
　Dim rw As Range
　Dim c As Range
　With ThisWorkbook.Worksheets("データベース")
　　For Each c In .Columns(1).Cells
　　　If c.Value = ID Then
　　　　Exit For
　　　End If
　　Next c
　　Set rw = .Rows(c.Row)
　　For Each c In rw.Cells
　　　If c.Value = word Then
　　　　Exit For
　　　End If
　　Next c
　　sSearch = c.Offset(0, 2).Value
　End With
End Function

tom04 · Answer

こんばんは！

通常のワークシート関数で対応できそうですが、質問がユーザー定義関数ですので・・・

No.1さんも仰っているように、Sheetを決め打ちしてしまうと汎用性がなくなってしまいますね。
ユーザー定義関数も他のワークシート関数のように汎用性を持たせた方が良いと思いますので
一例です。

Function sSearch(myRng As Range, ID As Variant, myWd As Variant)
    Dim c As Range, r As Range
        Set c = myRng.Find(what:=ID, LookIn:=xlValues, lookat:=xlWhole)
        Set r = c.EntireRow.Find(what:=myWd, LookIn:=xlValues, lookat:=xlWhole)
        sSearch = r.Offset(, 2).Value
End Function

※　myRng　の欄で「ID」の範囲を選択してみてください。m(_ _)m

エクセルで検索したセルの隣のセルを調べるユーザー関数

こんばんは。

こんばんは！

関連するカテゴリからQ&Aを探す

デイリーランキングこのカテゴリの人気デイリーQ&Aランキング

マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング