指定した文字列のある行番号が知りたい

Question

お世話になっております。

VBA - 文字列をVBAで見つける方法を知りたい。｜teratail
にて数式は"()"で括らないと計算してくれないと理解しました。

今現在セルの値が”37期”との場合のセルの行数が知りたいと思い
以下のように実装しました。

Dim year As Integer, gKi As String

Private Function getGyou() As Integer()
    
    Dim tmp As Integer
    tmp = year - 1984'yearはこの関数呼ぶ前に値代入している
    gKi = Str(tmp) & "期"
    gKi = Replace(gKi, " ", "")'セルの中に半角スペースがあったら削除
    '配列を宣言
    Dim kiGyouArray(), i As Integer
    
    Set searchRng = Range("B:B")
    ' 最初はFindで検索
    Set rng = searchRng.Find(gKi)
    ' 検索結果を一時保存
    Set tempRng = rng
    i = 1
    ' 条件にあったセルが見つからなければNothingが返るので何もしません
    Do While Not rng Is Nothing
        Debug.Print rng.Row
        kiGyouArray(i) = rng.Row
        i = i + 1
        ' FindNextで検索を継続
        Set rng = searchRng.FindNext(rng)
        ' すべて検索し終えると最初に戻るので、Addressで確認！
        ' これを忘れると無限ループになるので注意！
        If rng.Address = tempRng.Address Then
            Exit Do
        End If
    Loop
    getGyou = kiGyouArray()
End Function

ですが呼ぶ↓内で

Sub 利益の色更新_Click()
    Dim wb As Workbook, fname, zantei As String
    Dim month, tatejiku, tatejikuji As Integer
    fname = Range("B1").Value
    year = Range("B8").Value
    month = Range("B9").Value
    month = Format(month, "##")
    tatejiku = 10 + month
    tatejikuji = month - 2
    zantei = Range("B10").Value
    Dim tounendoGyuu() As Integer
    
    Set wb = Workbooks.Open(fname)
    With Worksheets("利益")
        tounendoGyuu = getGyou
：

の tounendoGyuu = getGyou　で

コンパイルエラー：
配列には割り当てられません。

取得した行番号の該当月に値を入れる予定です。

何故かとググった
【VBA配列定義】Array：配列には割り当てられません。というエラーはなぜ出るか｜あんもちブログ
には
Dim kiGyouArray As Variant
とすれば良いと書いてあったのでそうするとコンパイルは通るのですが
kiGyouArray(i) = rng.Row　にて

実行時エラー：13
型が一致しません

となってお手上げ状態なのです……

大変申し訳ないのですが
何か情報をお持ちな方はコメント頂けますようお願い申し上げます。

Answer 1

取り敢えずDim kiGyouArray()だけの状態ですと要素数が無の配列となってしまい、何も入れることができません。
要素数がわからないために動的配列にしているのだと思いますが、この形の動的配列は以下のようにRedim Preserve kiGyouArray(i)を使ってその都度要素数を定義しなおす必要があります。

ただ、これで型の不一致エラーが出るのも変なので、まだ別の問題が潜んでいる気はします。

Do While Not rng Is Nothing

        Redim Preserve kiGyouArray(i)
        Debug.Print rng.Row
        kiGyouArray(i) = rng.Row
        i = i + 1
（以下略

Comments

[ma2hiro]

ありがとうございます。
[VBAのReDim Preserveとは:エクセルマクロ・Excel VBAの使い方/配列] (https://www.relief.jp/docs/excel-vba-what-is-redim-preserve.html)
で要素を増やせる事が分かりました。

```
▼Preserveとは
配列に格納されている値を残したまま、配列の最後の次元の要素数を変更するために、ReDim文の中で使用するキーワード
```

本当にありがとうございました。

Answer 2

回答の前に、下記の点は修正しておきましょう。

• 宣言せずに使用している変数がある（searchRng, rng, tempRngなど）

Dim searchRng As Range, rng As Range, tempRng As Rangeと宣言しておく。
バグの原因になりますので、モジュール先頭でOption Explicitを宣言して変数宣言を矯正するようしておくといいでしょう。あと、オプションで自動でOption Explicitを装入してくれるように設定しておくいいでしょう。
下記参照。
Office TANAKA - 今さら聞けないVBA[Option Explicitって何？]

• 型を指定せずに宣言している変数がある

    Dim wb As Workbook, fname, zantei As String
    Dim month, tatejiku, tatejikuji As Integer

上記の fname, month, tatejiku は型指定していないのでVariant型になります。ちゃんと一つずつ型指定しましょう。

    Dim wb As Workbook, fname As String, zantei As String
    Dim month As Integer, tatejiku As Integer, tatejikuji As Integer

• 数値をInteger型で宣言している

Integerだと最大32,767なので、シートの行数以下なのでオバーフローエラーになる可能性があります。Long型で宣言しておいたほうがいいでしょう。

---

で、本題の質問のエラーの原因ですが、

Private Function getGyou() As Integer()
で返り値を整数型の配列で宣言していますが、
Dim kiGyouArray(), i As Integer
ここでは、型指定していないのでVariant型の配列になっています。
getGyou = kiGyouArray
ここで、整数型の配列にVariant型の配列を代入しているので、提示のエラーになります。

---

上記の点をもろもろ考慮して、下記のコードで動作確認できました。

Option Explicit
Dim year As Integer, gKi As String

Private Function getGyou() As Long()
    year = 2021
    Dim tmp As Integer
    tmp = year - 1984 'yearはこの関数呼ぶ前に値代入している
    gKi = tmp & "期"
'    gKi = Replace(gKi, " ", "") これは不要、Str(tmp) も不要、Str(tmp) とすると余計な空白が付く
    '配列を宣言
    Dim kiGyouArray() As Long, i As Integer
    Dim searchRng As Range, rng As Range, tempRng As Range '追加
    Set searchRng = Range("B:B")
    ' 最初はFindで検索
    Set rng = searchRng.Find(gKi)
    ' 検索結果を一時保存
    Set tempRng = rng
    i = 1
    ' 条件にあったセルが見つからなければNothingが返るので何もしません
    Do While Not rng Is Nothing
        Debug.Print rng.Row
        ReDim Preserve kiGyouArray(i)
        kiGyouArray(i) = rng.Row
        i = i + 1
        ' FindNextで検索を継続
        Set rng = searchRng.FindNext(rng)
        ' すべて検索し終えると最初に戻るので、Addressで確認！
        ' これを忘れると無限ループになるので注意！
        If rng.Address = tempRng.Address Then
            Exit Do
        End If
    Loop
    getGyou = kiGyouArray
End Function

関数呼び出し

    Dim tounendoGyuu() As Long
    tounendoGyuu = getGyou

追記

• 動的配列でサイズを指定せずに使用している

これは書き忘れましたが、他の方が回答されているので参照してください。

Comments

[ma2hiro]

本当に度々ありがとうございます。
Option Explicit　でバグがいっぱい出てきたので今後は付けないと駄目ですね……
お教え頂いたURLには
```
BEの[ツール]-[オプション]で開く[オプション]ダイアログボックスの[編集]タブに[変数の宣言を強制する]というチェックボックスがあります。これをオンにしておくと、それ以降に挿入されたモジュールでは、先頭にOption Explicitが自動入力されます。
```
とありましたがので今後はそのチェックを付けるようにします。

それで　配列AsInteger　と配列AsLongが混ざっていて上手く出来なかった模様でした。
ご助力により解決出来ました。

本当にありがとうございました。感謝いたします。

[ma2hiro]

と思いましたが
デバッガで追っている時は良いのですが（そのシートがアクティブになっているので）
```
Set wb = Workbooks.Open(fname)
With Worksheets("利益")
Worksheets("利益").Select ' 「利益」のシートを選択
```
とSelectしないと上手く動作しないことを気をつけるため追記失礼いたします。