【VBA】最終行取得時に400エラー

Question

Excel上F列の最終行数によって、印刷するページを変更して自動印刷するVBAを作成しようと思っております。

実行をしてみた所、以下最終行を取得する所で400エラー（※）となります。
考えられる原因教えて頂けますでしょうか。

ポップアップウィンドウで400のみ表示されました。
調べてみた所、「VBA エラー 400 アプリケーション定義または
オブジェクト定義のエラーです。」となっておりました。

MaxRow = wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp).Row

wb.Worksheets(i)をActivesheetに変更しても同様のエラーが発生しました。

Sub insatu_xls()
Dim fol As String
Dim f As String
Dim wb As Workbook
Dim wscnt As Long
Dim i As Long


fol = "C:\temp"
f = Dir(fol & "*.xls")
Do While f <> ""
    Set wb = Workbooks.Open(fol & "\" & f)
    wscnt = wb.Worksheets.Count

For i = 1 To wscnt

    MaxRow = wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp).Row

    MsgBox MaxRow

    With wb.Worksheets(i).PageSetup
        .Orientation = xlPortrait
        .Zoom = False
        If MaxRow > 20 Then
            .FitToPagesTall = 1
        Else
            .FitToPagesTall = 2
        End If
    
        .FitToPagesWide = 1
　　　End With

　　　wb.Worksheets(i).PrintOut
　　　Next i
　　　wb.Close
　　　f = Dir()
Loop
Set wb = Nothing
End Sub

Comments

[n_takapyon]

エラーメッセージは省略せずに貼り付けるようにしてください。

[n_takapyon]

あと、インデントも見やすいように調整してください。

Answer 1

xlsを読み込んでいるようですね。

xlsは65535行ですが、マクロが記載されているのがxlsx/xlsmなら1048576行なので「Rows.Count」が後者を指定しているのが原因かもしれません。

というのは「Rows.Count」は「ActiveWorkbook.ActiveSheet.Rows.Count」の省略形だからです。Open直後なので滅多にこの問題は起こりませんが、省略すると今後エラーの原因となりうるので止めたほうが良いです。

しかし上記マクロをワークシートモジュールに記述している場合は、ThisWorkbook.ActiveSheet.Rows.Countの省略形になりますので必ずエラーの原因となります。

このような場合「wb.Worksheets(i).Rows.Count」と書いたほうが良いでしょう。
しかし私の環境ではエラー1004になったので、関係無かったら申し訳ございません。

Comments

[Tiroze]

wb.Worksheets(i).Rows.Countと指定しましたが、400エラーとなりました。
～必ずエラーの原因となる理由が理解できなかったのですが、省略形になるとどうしてエラーの原因となるのでしょうか。

[退会済みユーザー]

停電で回答が遅れました。

マクロの記述してあるファイル形式と、記述してあるモジュールがどこに書いてあるか、エクセルのバージョンが明記されていないので、あくまでxlsmのワークシートモジュールに書いてあると仮定した場合ですが

マクロの記述されたファイルがxlsmの場合、ThisWorkbook.ActiveSheet.Rows.Countは1048576を返します。つまり次のようになるわけです。
wb.Worksheets(i).Cells(1048576, 6).End(xlUp).Row
しかしxls形式の場合は行数は65535までしか指定できませんからエラーが発生します。

もしお時間有りましたら、それぞれの値を該当箇所で確認すればご納得いただけると思います。
Debug.Print Rows.Count
Debug.Print ThisWorkbook.ActiveSheet.Rows.Count
Debug.Print wb.Worksheets(i).Rows.Count
またエラーの確認だけなら定数で確認してみても良いでしょう。
Debug.Print wb.Worksheets(i).Cells(65535,1).row　'←65535が出力されるはず
Debug.Print wb.Worksheets(i).Cells(65536,1).row　'←エラーになるはず
Debug.Print wb.Worksheets(i).Cells(1048576,1).row　'←エラーになるはず

しかし「wb.Worksheets(i).」を付けても400エラーが出たとのことなので、原因は別の所にありそうですね。

[Tiroze]

ご回答ありがとうございます。内容理解できました。
debugの方法試してみます。

Answer 2

エラーコードを元に検索すると「アプリケーション定義またはオブジェクト定義のエラーです。」と出てきたのでそのようにメッセージが表示されたとして対処を考えます。

このエラーメッセージから宣言されていない、未定義のものを呼び出しているという事が伺えます。
このことから、宣言漏れが無いか、呼び出してエラーになるオブジェクトが無いかを確認します。

問題箇所は
MaxRow = wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp).Row
ですので、ここでの登場人物を洗い出します。
下記登場人物はウォッチ式で確認してみてエラーが起きないでしょうか。

• MaxRow
• wb.Worksheets(i)
• Rows.Count
• wb.Worksheets(i).Cells(Rows.Count, 6)
• wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp)
• wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp).Row

提示されているコードにMaxRowの宣言が無いのでDim MaxRowを追記して試してみてください。

Comments

[Tiroze]

試してみたところ、以下の結果でした。
MaxRow⇒空文字
wb.Worksheets(i)⇒（※）の項目でアプリケーション定義またはオブジェクト定義のエラーです。
※OnCulculate,OnDate,OnDoubleClick,OnEntry,OnSheetActivate,OnSheetDeactivate
Rows.Count⇒1048576
wb.Worksheets(i).Cells(Rows.Count, 6)⇒アプリケーション定義またはオブジェクト定義のエラー
wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp)⇒アプリケーション定義またはオブジェクト定義のエラー
wb.Worksheets(i).Cells(Rows.Count, 6).End(xlUp).Row⇒アプリケーション定義またはオブジェクト定義のエラー

なお、エラー箇所を一旦削除し実行するとWith wb.Worksheets(i).PageSetup部分はうまくいったので、同じwb.Worksheets(i)でもうまくいく場合とうまくいかない場合があるみたいです。

[n_takapyon]

wb.Worksheets(i)の取得に成功はしているのですね。

wb.Worksheets(i).Cells(Rows.Count, 6)がエラーに成っている事から
cellsの範囲指定に問題が生じていると判斷できそうです。

例えばエクセルで指定できる範囲を超えている等です。

下記コードではどうでしょうか。
wb.Worksheets(i).Cells((Rows.Count - 1), 6)

ちなみに、私は最終セルを取得したい場合はxlLatCellを使用します。
（わざわざ最終行まで居りてからxlUpするのが無駄に感じる為）

wb.Worksheets(i)Cells.SpecialCells(xlLastCell)

[Tiroze]

回答頂きありがとうございます。
wb.Worksheets(i)部分もエラーになっています。
wb.Worksheets(i).Cells((Rows.Count - 1), 6)に変更してみたのですが、wb.Worksheets(i).Cells((Rows.Count - 1), 6)は、同様に「アプリケーション定義またはオブジェクト定義のエラー」となります。
また、wb.Worksheets(i).Cells.SpecialCells(xlLastCell)に変更してみますと、MaxRowとして数値取得できますがwb.Worksheets(i)部分は同様のエラーです。また、数値が想定している値より多くなっています。

[n_takapyon]

wb.Worksheets(i)　がエラーになる場合の iの値はどうなってますか？その値が範囲外の可能性も考えられます。
また、数値が想定より多いのは何かしらの変更（スペースが入ってるとか、書式が設定されているとかです）が加えられたセルがその位置にある事が考えられます。

[Tiroze]

iは1になっています。今までのようにポップアップされてエラーは出なくなったのですが、ウォッチ式の一部項目（OnCulculate,OnDate,OnDoubleClick,OnEntry,OnSheetActivate,OnSheetDeactivate）でエラーになっています。
数値が想定より多い件ですが、セル幅を調整しているのが原因になる事はありますか？
あくまで文字が入力されている最終行を取得する方法はありますでしょうか？

[n_takapyon]

（OnCulculate,OnDate,OnDoubleClick,OnEntry,OnSheetActivate,OnSheetDeactivate）はイベントのように思います、同エクセルが手元にないので答えかねます。

幅の調整で影響することは考えにくいです。

xlLastCellで値が無ければその行からxlUpするなり、ForEachで探すなりの処理を考えれば良いかと思います。

[Tiroze]

ご回答ありがとうございます。
エラー部分は解決できなかったので、別の対応にしました。
必ずExcelファイル内の最終行に記載される文言があるので、その文言をMATCH関数で検索する処理にしました。