ワークシートから検索してきた値を定数名として扱いたいです。

Question

###前提・実現したいこと

Excelのワークシートに、こういうような、人名と守備位置が一覧になっている表を作っておいて

Excelシート名「選手一覧」

A     B
荒木  二塁
高橋  三塁
藤井  左翼
平田  右翼
森野  一塁
大島  中堅
福  投手
桂  捕手

定数として以下のように定義します。

const 投手 = "1"
const 捕手 = "2"
const 一塁 = "3"
const 二塁 = "4"
const 三塁 = "5"
const 遊撃 = "6"
const 右翼 = "7"
const 中堅 = "8" 
const 左翼 = "9"
const 不明 = "0"

これで、たとえば
「大島」
って入力したら
「8」
って返ってくるようにしたいのです。

「大島」って入れたら「中堅」って返ってくるのは、こんなのを書きました。
これで「findDP("大島")」ってやったら「"中堅"」って返ってきます。

Function findDP(men)

    Dim dbSh As Worksheet
    Set dbSh = ThisWorkbook.Worksheets("選手一覧")
    dbSh.Activate
    
    Dim tmpStr As String
    Dim dbEndRow As Long
    dbEndRow = dbSh.Cells(Rows.Count, 1).End(xlUp).Row
    
    Const dbFindCol As Integer = 2      ' 戻り値にする列
   
    'Match関数で検索する範囲とIndex関数で返答する範囲を指定する
    Dim dbRng, codeRng As Range
    Set codeRng = Range(dbSh.Cells(2, dbFindCol), dbSh.Cells(dbEndRow, dbFindCol))
    Set dbRng = Range(dbSh.Cells(2, 1), dbSh.Cells(dbEndRow, 1))

    'Match関数で、引数で指定した人名が入力されている行数を求めた後、Index関数で対象行に格納された守備位置を取り出す
    tmpStr = Application.WorksheetFunction.Index(codeRng, Application.WorksheetFunction.Match(men, dbRng, 0), 1)
    On Error Resume Next
    If Err.Number <> 0 Then            ' みつからなければ「不明」と返す
        tmpStr = "不明"
        Err.Clear
    End If

    findDP = tmpStr

End Function

###発生している問題

この返ってきた「中堅」を、定数として扱って「8」と読むためにはどうしたらいいか
というところがわかりません。

###補足情報(言語/FW/ツール等のバージョンなど)
Win10、Excel2016です。

Answer 1

質問のように文字列をキーにして、値を取得したい場合は、ディクショナリを使うのが良いです。

ディクショナリを用意するコードは、以下のような感じです。

  Dim baseballPositionDict as Object 'ディクショナリの変数を定義

  set baseballPositionDict = CreateObject("Scripting.Dictionary")
   'ディクショナリのオブジェクトを作って、変数に代入　「CreateObject("Scripting.Dictionary")」のところは、おまじないだと思って覚えてください。

   ` ディクショナリに、キー（検索に使う値）と、それに対して返される値の組を、加えていきます
  baseballPositionDict.Add "投手","1"  ' "投手"がキー、"1"がそれに対応する値です。
  baseballPositionDict.Add "捕手","2"
  baseballPositionDict.Add "一塁","3"
  baseballPositionDict.Add "二塁","4"
  baseballPositionDict.Add "三塁","5"
     ' 途中は省略
  baseballPositionDict.Add "中堅","8"
     ' 途中は省略
  baseballPositionDict.Add "不明","0"

このようにしてディクショナリに守備位置と背番号の組を登録した後は、Itemメソッドで値を取得することが出来ます。
例えば、baseballPositionDict.Item("中堅")は、"8"を返します。

このディクショナリを使って、「大島」って入力して「8」って返ってくるようにするには

      baseballPositionDictionary.Item(findDP("大島"))

Comments

[sumire_cl]

つまり、ディクショナリって連想配列ですね！
ってことはプロシジャの中でオブジェクトを作って変数に代入して…という作業が必要になり…。

守備番号の一覧表は標準モジュール内のいろんなプロシジャで使うのでPublicで定数にしたんですが
ここんとこがまずかったということですね…。
プロシジャの外でディクショナリに値を入れて、いろんなプロシジャで参照できる方法ってあるでしょうか？

Answer 2

選手一覧を作っているのなら、
守備位置一覧を作ればよいかと。

選手一覧のC列に、数式（Vlookup等）を追加し、
守備位置を検索して値を出しておけば、
わざわざプログラムで処理する必要が無いかと。

Answer 3

Const "1" = 投手　にすればよいのでは？
↑すいません。試してみたら無理でした。

Comments

[King_of_Flies]

findDP("大島"）で取得した中堅はConstで定義した中堅＝８になっているので、
Debug.Print(中堅)で8が帰ってきているか見てみてください。

[King_of_Flies]

イミディエイト　ウィンドウで出力が見れます。

[sumire_cl]

イミディエイトウィンドウで
?中堅
ってやると、8が返ってきます。

[sumire_cl]

わからないのは
findDP("大島")で返ってきた「中堅」は、定数で定義されている「中堅」と同じものですよ
とExcelにわかってもらうための方法です…。

[King_of_Flies]

tmpStrが大島になっているのを8にしたいってことでしょうか？

[King_of_Flies]

findDpの呼び出し元のコードを見せてください。

[sumire_cl]

したいことは
「大島」
って入力したら
「8」
って返ってくるようにしたいので
外のコードはどんなんでもかまいません。

findDPは
大島→中堅→8
っていう参照をしたいためにとりあえず書いてみたものなので
なんならfindDP自体使わなくてもいいと思っています。