ユーザー定義関数について

Sub 範囲()

Dim ws As Worksheets


With ws
.Range(.Cells(1, 1), Cells(maxR, 1)).Copy
End With

End Sub

Function maxR(target As Worksheets) As Long
With target
maxR = .Cells(Rows.Count, "A").End(xlUp).Row
End With
End Function


ユーザー定義関数について勉強しているのですが、
上記のコードを実行すると、
コンパイルエラー、引数は省略できませんとなります。

間違っているところをご指摘いただけないでしょうか？

お手数かと思いますが、ご教示をお願いします。

No.1 ベストアンサー 回答者： Qchan1962 回答日時： 2021/04/14 19:52

こんにちは、

コンパイルエラーも実行時エラーもありますね。
なさりたい事がイマイチ分かりませんが、
想定、一例として考えていきますので違っている場合は、読み飛ばしてください。

Sub 範囲()プロシージャの方から
Dim ws As Worksheets
これは　 As Worksheets もありますが（単一シートオブジェクトを指定する場合） Dim ws As Worksheet　ではないかと
また、wsはセットされていません
Set ws = Worksheets("Sheet1")　や　Set ws = ActiveSheet　など

ご質問部分

Function maxRには引数でシートオブジェクトが設定されている為
Cells(maxR, 1)　これは　Cells(maxR(ws), 1)　などになります

Function maxR(target As Worksheets) As Longも
Worksheets　でなくWorksheet　で

また、問題にならない場合はあるものの
.Range(.Cells(1, 1), Cells(maxR, 1))は、2つ目のcellsにも
.が必要と思います。

.Range(.Cells(1, 1), .Cells(maxR(ws), 1))

以上を鑑みて書き直すと
（.Copyになっていますが　とりあえず.Selectで）

Sub 範囲()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
.Range(.Cells(1, 1), .Cells(maxR(ws), 1)).Select
End With
End Sub

Function maxR(target As Worksheet) As Long
With target
maxR = .Cells(Rows.Count, "A").End(xlUp).Row
End With
End Function

As Worksheetsが正しい場合は、対象のWorksheetオブジェクトを特定（取得）するコードやインデックスなどが必要になります

No.4 回答者： Qchan1962 回答日時： 2021/04/14 21:27

#1です

すみません。間違った表現を訂正します。
＃１の
As Worksheetsが正しい場合は、対象のWorksheetオブジェクトを特定（取得）するコードやインデックスなどが必要になります
を、訂正します。

As Worksheetsは正しくないです。
Worksheetsオブジェクト型の変数に、Worksheetsプロパティをセットすることは出来ませんので、Worksheetコレクション（Worksheets）を、
Worksheetオブジェクトで取得します。

従って、
＃１の
Dim ws As Worksheets
これは　 As Worksheets もありますが（単一シートオブジェクトを指定する場合） Dim ws As Worksheet　ではないかと
は、
Dim ws As Worksheet　で変数宣言を行い
特定シートオブジェクトをSetします。

＞ユーザー定義関数について勉強しているのですが、
ついでに蛇足です。

Functionは、VBAからやシート関数としても使えます。
最終行の行№取得を目的にするのは少々もったいないと言うか
汎用性に欠けると思います。
シートを引き数にしないでカラム番号や記号を引数にしてセル自体を取得す方が良いように思います。

セルを取得するサンプルです。添付図はシート関数で使用した例

Sub 範囲1()
Dim ws As Worksheet
Set ws = ActiveSheet
With ws
.Range(.Cells(1, 1), LASTCELL("A")).Select
End With
’参考イミディエイトウィンドウへ出力
Debug.Print LASTCELL("A").Row; " : "; LASTCELL("A").Value; " : "; LASTCELL("A").Address

End Sub

Function LASTCELL(TargetCLM As String) As Range
Set LASTCELL = ActiveSheet.Cells(Rows.Count, TargetCLM).End(xlUp).Cells
End Function

No.3 回答者： fujillin 回答日時： 2021/04/14 20:11

こんばんは

ご提示の内容で全文でしょうか？
だとすると、おかしなことがいろいろ。

まず、ご提示のものは、いわゆる「ユーザ定義関数」とは関係のないコードです。

「Sub範囲」で変数wsを宣言していますが、実際の値の代入のないままWithで使われています。
また、そのあとの文脈からすれば、wsはWorksheetオブジェクトのはずなので、型宣言と矛盾しています。

maxRは引数を必要としているのに、呼び出しの際に何も与えていません。
この部分が、ご質問のエラーメッセ－ジの直接の原因です。（メッセージ通りの意味）
エラー発生時にエディタを見れば、対象部分が選択状態になっていると思いますので、まずはそこを確認すればよいはずです。

更に、関数maxR側の引数の型宣言がWorksheetsになっていますが、内容を見ると、これまたWorksheetオブジェクトと推察されますので、引数を渡しても「型が合わない」というエラーになると思われます。

想像するところ、質問者様はWorksheetsとWorkshhtを区別できていないのではないでしょうか。

この回答へのお礼

回答ありがとうございました。

お礼日時：2021/04/14 21:27

No.2 回答者： めぐみん_ 回答日時： 2021/04/14 19:54

Function maxR(target As Worksheets)

ですので maxR(ワークシートオブジェクト) と引数を与えなければなりませんが、多分 As Worksheet でしょうね。

Sub 範囲()

Dim ws As Worksheet

Set ws = Worksheets("実際のシート名")

With ws
.Range(.Cells(1, 1), .Cells(maxR(ws), 1)).Copy
End With

End Sub

Function maxR(target As Worksheet) As Long
With target
maxR = .Cells(Rows.Count, "A").End(xlUp).Row
End With
End Function

かな？
どのシートの最終行を取得したいかで maxR(~) の~に与えるシートが変わると思いますけど、何をされたいのか不明なので取り敢えず。

この回答へのお礼

回答ありがとうございました。

お礼日時：2021/04/14 21:27