VBA100本ノック 85本目：請求日から入金予定日を算出

公開日：2021-02-10　最終更新日：2021-07-23

VBA100本ノック 85本目：請求日から入金予定日を算出

請求日から入金予定日を算出するユーザー定義関数を作成する問題です。

ツイッター連動企画です。
ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。

VBAテスト用のサンプルデータは、VBA100本ノックの目次ページ からもダウンロードできます。

マクロVBAを初心者向けの基本から上級者向けの高度な内容までサンプルコードを掲載し解説しています。エクセル関数・機能・基本操作の入門解説からマクロVBAまでエクセル全般を網羅しています。

出題

#VBA100本ノック 85本目
請求日から入金予定日を算出するユーザー定義関数を作成。
「取引先マスタ」「支払パターン」を基に決定します。
支払パターン
・締め日：5,10,15,20,25,末
・支払月：0~6月後 ※n=0は当月、n=1は翌月
・支払日：5,10,15,20,25,末
入金日が土日祝日の場合は前日にする。

サンプルファイルです。
https://excel-ubara.com/vba100sample/VBA100_85.xlsm
https://excel-ubara.com/vba100sample/VBA100_85.zip

想定している入金予定日は、以下になります。

VBA作成タイム

この下に頂いた回答へのリンクと解説を掲載しています。
途中まででも良いので、できるだけ自分でVBAを書いてみましょう。

他の人の回答および解説を見て、書いたVBAを見直してみましょう。

頂いた回答

出題ツイートの引用ツイートでの回答

解説

ユーザー定義関数は、62本目のZLOOKUP関数で取り上げました。
今回は、参照する情報が多いので全てを引数に入れるのは面倒なので、そのあたりは工夫が必要です。
一通りの範囲は引数で指定して、範囲内の列位置等は関数ないで固定にしました。


  
    
      Function VBA100_85_01(取引先CD, 請求日, 取引先マスタ, 支払パターン, 祝日)

      　　Const 取引先支払列 = 3

      　　Const 支払_締め日列 = 2, 支払_支払月列 = 3, 支払_支払日列 = 4

      　　

      　　On Error GoTo ErrExit

      　　

      　　Dim 取引先支払:　　 取引先支払 = WorksheetFunction.VLookup(取引先CD, 取引先マスタ, 取引先支払列, 0)

      　　Dim i As Long:　　　i = WorksheetFunction.Match(取引先支払, 支払パターン.Columns(1), 0)

      　　Dim 支払_締め日:　　支払_締め日 = 支払パターン.Cells(i, 支払_締め日列)

      　　Dim 支払_支払月:　　支払_支払月 = Val(支払パターン.Cells(i, 支払_支払月列))

      　　Dim 支払_支払日:　　支払_支払日 = 支払パターン.Cells(i, 支払_支払日列)

      　　

      　　Dim 締め日, 加算月

      　　If 支払_締め日 = "末" Then

      　　　　締め日 = DateSerial(Year(請求日), Month(請求日) + 1, 0)

      　　Else

      　　　　加算月 = IIf(CInt(支払_締め日) >= Day(請求日), 0, 1)

      　　　　締め日 = DateSerial(Year(請求日), Month(請求日) + 加算月, CInt(支払_締め日))

      　　End If

      　　

      　　Dim 支払日: 支払日 = DateAdd("m", 支払_支払月, 締め日)

      　　If 支払_支払日 = "末" Then

      　　　　支払日 = DateSerial(Year(支払日), Month(支払日) + 1, 0)

      　　Else

      　　　　支払日 = DateSerial(Year(支払日), Month(支払日), CInt(支払_支払日))

      　　End If

      　　

      　　Set 祝日 = Intersect(祝日, 祝日.Worksheet.UsedRange).Offset(1)

      　　vba100_85_01 = WorksheetFunction.WorkDay(支払日 + 1, -1, 祝日)

      　　Exit Function

      ErrExit:

      End Function

ワークシート関数に頼った作りにしました。
シートでも使いますが、土日祝日の前を求めるならWorkDay関数が一番簡単ではないかという事で使っています。
補足はありません。記事にはVBAコードを掲載しています。

後から補足追加しました。

補足

シートでの数式入力方法

=vba100_85_01(A2,C2,取引先マスタ!A:C,支払パターン!A:D,祝日マスタ!A:A)

ユーザ定義関数内でのセル範囲取得の注意点

ユーザー定義関数内では、Rangeの
Currengregionプロパティ
Findメソッド
これらは正しく動作しません。
以下のツイートを参考にしてください。
https://twitter.com/kyrtnyy/status/1359665643100856320

サイト内関連ページ

ユーザー定義関数の作り方

・簡単な例でユーザー定義関数を作ってみましょう・この関数の使い方・ユーザー定義関数の実践使用例

62本目：独自のZLOOKUP関数を作成

・出題・頂いた回答・解説・補足・サイト内関連ページ

スピルに対応したXSPLITユーザー定義関数（文字区切り）

・区切り位置ウィザード・ワークシートの関数で文字区切りする場合・ユーザー定義関数のVBAｺｰﾄﾞ・XSPLIT関数の使用例・ユーザー定義関数の実践使用例

同じテーマ「VBA100本ノック」の記事

82本目：ブックのドキュメントプロパティを取得
 83本目：請求書を作成してPDF出力
 84本目：ブックの自動バックアップ
85本目：請求日から入金予定日を算出
86本目：全シートの総当たり表を作成
 87本目：数式のシート間の依存関係
 88本目：クロスABC分析作成
 89本目：2つのフォルダの統合
 90本目：セルに重なっている画像の削除
 91本目：時間計算（残業時間の月間合計）
92本目：セルの色を16進で返す関数

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
マクロVBA入門編
VBA100本ノック
85本目：請求日から入金予定日を算出

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

VBA練習問題
VBA100本ノック 85本目：請求日から入金予定日を算出