エクセルでフォルダ内の画像を自動で取得して表示をさせたい

Question

VBAについて質問をさせてください。
ほぼ触ったことのない素人です。

エクセルに記載している品番から、特定のフォルダ内にある同じ品番の
jpg（品番がaaaであれば、aaa.jpg）を隣のセルに表示させたいのですが、
実行時にエラーが出てしまいました。

該当のソースコード

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim p As String
Dim h As Range

'写真の保存場所
p = "C:\Users\●●（※ユーザー名が入っています）\Desktop\banner\"

'現在表示されている写真は一度削除する
ActiveSheet.Pictures.Delete

'商品名が入力されている行まで繰り返す
For Each h In Sheet5.Range("E3:E" & Sheet5.Range("E1048576").End(xlUp).Row)

'写真ファイルが保存されている時
If Dir(p & h) <> "" Then
With ActiveSheet.Pictures.Insert(p & h)

'写真ファイル名が入力されているセルから２つ左のセルに挿入
.Top = h.Offset(0, 1).Top
.Left = h.Offset(0, 1).Left
'写真サイズの設定
.Width = h.Offset(0, 1).Width
.Height = h.Offset(0, 1).Height
End With
End If
Next

End Sub

・エクセルの保存場所：Desktop
・画像の保存場所：Desktop\banner
・画像のサイズはバラバラ

・品番のある列はE列（数字8桁、-が間に入る時もあります）、画像を表示させたい列はF列
・Shhet5の「コードを表示」で出てきた個所に入れています
（WorksheetとSelectionChangeとの表示があります）

・参考：https://oshiete.goo.ne.jp/qa/8357181.html
記事のものをコピペし、保存場所やシート名、セルの場所などの手を加えています。

発生している問題・エラーメッセージ

For Each h In Sheet5.Range("E3:E" & Sheet5.Range("E1048576").End(xlUp).Row)
の部分が黄色くなり、
['Range'メソッドは失敗しました:'_worksheet'オブジェクト]
と表示されます。

該当のエラーで調べてみてもSheetがアクティブになっていないのでは？
といった事例が多く出てきたので、Rangeの前にシート名を
入れてみたりしたのですが解決にはなりませんでした。

これから勉強するつもりではいるのですが、正直初心者にはハードルが高すぎたなと感じております。
どうかご教示いただけないでしょうか。

Answer 1

動作の重さなどを気にしないのであれば、
そのコード自体には問題はなく、
当方の環境で新しく作ったブックのSheet5のE3からファイル名を入れ、
画像ファイルを用意し、そのコードを入れてやれば、
エラーが再現することなく動いております。

シートがアクティブかどうかは関係ないかと思います、試してみたところ、
シートがアクティブでなくとも実際に画像は貼り付けられませんがエラーも発生しませんでした。

その為、そのコードに問題があるのではなく、
現在テストに使用しているブック・シートに問題があるのではないかと予測されます。
新規ブックでの動作確認（問題の切り分け）は行いましたか？
Sheet5.Range("E1048576").End(xlUp).Row の値はどうなっていますか？
（イミディエイトウィンドウで " ? Sheet5.Range("E1048576").End(xlUp).Row " と入力）

実使用するブックでなにか問題があって最終行の検出がうまくいかない場合で、
どうしてもそのブックにこの機能を適用したい場合は
適当なセルに " =COUNTA(E:E) " を入れその値を使用する、
さらにその値を使用した上で Range の指定を Cells で行う、
（ Rangeの指定をCellsで行う例 " Range(Cells(1,1),Cells(i,j)) " ）
などにより強引に回避できる場合もあります。

まずは問題の切り分けをお試しください。

Comments

[sor]

shinobu_osakaさんありがとうございます！
数点ご指摘をいただきまずは問題の切り分けをするという点、
運用ファイルでどうにかしないとと思っていたため目から鱗でした。。

イミディエイトウィンドウでコードを入力してみたところ、
実行時エラー'1004';　アプリケーション定義またはオブジェクト定義のエラーです。
と表示されてしまいました。

ブックやシートに問題があるのではというご指摘からハッと確認してみたところ、
エクセル自体が他の方が作成した古いもので97-2003のxls形式になっていました。
ただ、xlsm形式で保存し直しただけでは解決にいたりませんでした。

そして新しくシートを作り直し、同じコードで試したところ
無事求めていた挙動の通り実行がされました！
古いエクセルをどうしても使わないといけないわけでは無いので、
新しく構成しなおしてみようと思います。
ご回答いただき、ありがとうございました！

[shinobu_osaka]

問題が解決いたしましたようで何よりです。

状況から鑑みますに、
「バージョンによりExcelの最大行数が異なる」ことによるエラーのようですね、
新しく構成し直すのならそちらが理想ですが、
どうしても旧バージョンのまま動作する必要があれば、
E65536 とすることによりエラーを回避し動作できるかもしれません。
また、「新しい形式で保存して開き直すことで使用できる行数が増える」
と、どこかで読んだので、エクセルを閉じて開き直せば動くかもしれません。

それでは頑張ってください。

[ExcelVBAer]

根本的な原因は、概念的な値を使うべき所で「固定値」を使っている事です。
つまり、概念的にはシートの「最終行」から上へ移動、という事を「1048576」行目から上へ移動、としているからです。

[Sheet].Rows.Count で、該当シートの行数が分かるので、
そちらを使う方が適当でしょう。

Answer 2

Sheet5はグローバル変数でしょうか。
用意されていないのであれば作る必要があります。

Dim Sheet5 As Worksheet
Set Sheet5 = Worksheets("Sheet5")

もしくは直接参照するか。

For Each h In Worksheets("Sheet5").Range("E3:E" & Sheet5.Range("E1048576").End(xlUp).Row)

Comments

[ExcelVBAer]

グローバル変数ではないと思います。

最初から存在する自ブックのシートについては、
オブジェクト名（コードネーム）で直接アクセス可能です。
※自分はこの手法をよく使います（インテリセンスが使えるし、変数宣言しなくていいし、楽です）

[ttyp03]

そういやそうですね。
なんで今まで自分使わなかったんだろう…。
とすると的外れな回答でしたね。
ご指摘ありがとうございます。

[sor]

ttyp03さん、ExcelVBAerさんご回答ありがとうございます！
いただいたコードを試してみていたのですが、
同じエラーになってしまい解決にはいたりませんでした...

ワークシート自体を変数に入れることができたり、
オブジェクト名で直接アクセス可能なことを知らなかったため
いただいた意見もとても勉強になりました。
ありがとうございました！

[ttyp03]

私の回答は見当違いだったのでスルーしてくださいまし。申し訳ない。