[[20230916121643]]　『マクロで最終行をfunctionに定義した際の呼び出し』（ぽぽ）　ページの最後に飛ぶ

『マクロで最終行をfunctionに定義した際の呼び出し方』（ぽぽ）

表題の件、教えてください。
色々試しているのですが、うまくいかない所があります。

Function Get最終行(ws As Worksheet, C As Long) As Long

    Get最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp).Row
End Function

と設定しておいて、

Sub テスト()
Dim ws1 As Worksheet
Set ws1 = Worksheets("sheet2")

With ws1

    .Rows("1:" & Get最終行(ws1, 1)).Copy
    .Range("A1", .Range("E" & Get最終行(ws1, 5))).Borders.LineStyle = xlContinuous

　Dim i As Long

　For i = 1 To Get最終行(ws1, 3).Row

    .Cells(i, 6).Value = "あ"
  Next i
End With
End Sub

として、マクロを稼働すると「コンパイルエラー　修飾子が不正です」と出て先に進めません。
For i ～の所で Get最終行を使っているのがエラーの元のような気がするのですが（コピーしたり、罫線引いたり…の所は個別に試したらエラー出ずに使えたので）
なぜだか理由が分かりません。よろしくお願いいたします。

あと素朴な疑問ですが、今回Withでws1をくくっていますが、
例えば、下記のコードの場合のGet最終行の中のws1は省略できませんか？

.Rows("1:" & Get最終行(ws1, 1)).Copy

Get最終行(., 1)と書いたらエラーになり、ここはもう一度ws1と書くしかないのでしょうか。

＜使用 Excel：Microsoft365、使用 OS：Windows10 ＞

 1番目の修正案  
    '　Get最終行はLong型なので
    Function Get最終行(ws As Worksheet, C As Long) As Long
        Get最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp).Row
    End Function
    Sub テスト1()
        Dim ws1 As Worksheet
        Set ws1 = Worksheets("sheet2")
        With ws1
            .Rows("1:" & Get最終行(ws1, 1)).Copy
            .Range("A1", .Range("E" & Get最終行(ws1, 5))).Borders.LineStyle = xlContinuous
          Dim i As Long
          For i = 1 To Get最終行(ws1, 3)  ' Get最終行はLong型なので Rowプロパティはない
            .Cells(i, 6).Value = "あ"
          Next i
        End With
    End Sub

 2番目の修正案  
    '　Get最終行をRange型にすると
    Function Get最終行(ws As Worksheet, C As Long) As Range
        Set Get最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp)   'Range型なのでSetステートメント使う
    End Function
    Sub テスト2()
        Dim ws1 As Worksheet
        Set ws1 = Worksheets("sheet2")
        With ws1
            .Range("A1", Get最終行(ws1, 1)).EntireRow.Copy   '  Get最終行はRange型なので...
            .Range("A1", Get最終行(ws1, 5)).Borders.LineStyle = xlContinuous '  Get最終行はRange型なので...
          Dim i As Long
          For i = 1 To Get最終行(ws1, 3).Row   ' Get最終行はRange型なのでRowプロパティがある
            .Cells(i, 6).Value = "あ"
          Next i
        End With
    End Sub

 3番目の修正案  
    'Sheet2のシートモジュールにこうする
    Function Get最終行(C As Long) As Range
        Set Get最終行 = Me.Cells(Me.Rows.Count, C).End(xlUp)
    End Function

   '標準モジュール
    Sub テスト3()
        Dim ws1 As Sheet2                '　変数ws1の型をシートオブジェクトにする
        Set ws1 = Worksheets("sheet2")
        With ws1
            .Range("A1", .Get最終行(1)).EntireRow.Copy                     'Sheet2.Get最終行をCallする
            .Range("A1", .Get最終行(5)).Borders.LineStyle = xlContinuous
          Dim i As Long
          For i = 1 To .Get最終行(3).Row  'Sheet2.Get最終行はRange型
            .Cells(i, 6).Value = "あ"
          Next i
        End With
    End Sub
（´・ω・`） 2023/09/16(土) 13:42:38

（´・ω・`）様

大変ありがとうございます。理解しました。
今色々試していたら、また一つ疑問が生じてしまいました。
例えば、この事例の場合は、「シート名」をSetしていますが、実際は複数のブックの複数シートを扱う場合が多いので、ワークブックをSetしてシート名は直接書くようにしていたり、シートが一つしかない場合はシートをSetしていたりと混在します。
いずれの場合でもGet最終行を使用したい場合はどうしたらよいでしょうか。
下記のコードだと、オブジェクトはこのプロパティまたはメソッドをサポートしていません。とエラーになります。

Sub テスト()
Dim wb1 As Workbook
Set wb1 = Workbooks("Test.xlsm")

With wb1

    .Worksheet("Sheet2").Rows("1:" & Get最終行(.Worksheet("Sheet2"), 1)).Copy
以下、省略

Function Get最終行(ws As Worksheet, C As Long) As Long

    Get最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp).Row
End Function

（ぽぽ） 2023/09/16(土) 17:27:50

 ＞Worksheet("Sheet2")
   Worksheets("Sheet2")
 　 　　 　↑

（半平太） 2023/09/16(土) 19:12:08

半平太様
ありがとうございます。大変失礼しました。単純ミスでした。。
ですが、罫線引く所で、同じく　438のオブジェクトはこのプロパティまたはメソッドをサポートしていませんのエラーが出ます。
スペルミスはないように思うのですが、ご教授いただけますと幸いです。。

Sub テスト()
Dim wb1 As Workbook

Set wb1 = Workbooks("Test.xlsm")

With wb1

    .Worksheets("Sheet2").Rows("1:" & Get最終行(.Worksheets("Sheet2"), 1)).Copy　　'←こっちは動く
    .Worksheets("Sheet2").Range("A1", .Range("E" & Get最終行(.Worksheets("Sheet2"), 5))).Borders.LineStyle = xlContinuous　　　'←こっちがエラー

End With
End Sub

Function Get最終行(ws As Worksheet, C As Long) As Long

    Get最終行 = ws.Cells(ws.Rows.Count, C).End(xlUp).Row
End Function

ちなみに、Setをwsでセットして、下記でやると動くのですが。
With ws1

    .Range("A1", .Range("E" & Get最終行(ws1, 5))).Borders.LineStyle = xlContinuous
（ぽぽ） 2023/09/16(土) 21:36:37

 ＞ .Worksheets("Sheet2").Range("A1",　　　　　　　　　　 .Range("E" & Get最終行(.Worksheets("Sheet2"), 5))).Borders.LineStyle = xlContinuous      '←こっちがエラー
    .Worksheets("Sheet2").Range("A1",.Worksheets("Sheet2").Range("E" & Get最終行(.Worksheets("Sheet2"), 5))).Borders.LineStyle = xlContinuous  
                                      ~~~~~~~~~↑~~~~~~~~~

（半平太） 2023/09/16(土) 22:10:50

半平太様
ありがとうございます。完全に見落としでした。。
Get最終行を使ってコードを短くしようと考えていましたが、SheetまでSetしないとかえって複雑で分かりにくくなってしまいますね。
使いどころを今一度勉強してみます。

（ぽぽ） 2023/09/16(土) 22:46:40

コメント返信：

[ 一覧(最新更新順) ]

[[20230916121643]] 『マクロで最終行をfunctionに定義した際の呼び出し』（ぽぽ） ページの最後に飛ぶ

[[20230916121643]]　『マクロで最終行をfunctionに定義した際の呼び出し』（ぽぽ）　ページの最後に飛ぶ