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

最終更新日：2021-02-22

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

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

頂いた回答

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

解説

ユーザー定義関数は、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

サイト内関連ページ

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

マクロVBAを作成して、ボタンで一括処理…それほどではないが、関数だけではちょっと大変、そんな時は、ユーザー定義関数を使ってみましょう。作り方は簡単です。簡単な例でユーザー定義関数を作ってみましょう上記は、引数を足し算して返すユーザー定義関数です。

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

VLOOKUP関数の機能を変更した独自のZLOOKUP関数を作成する問題です。ツイッター連動企画です。ツイートでの見やすさを考慮して、ブック・シート指定等を適宜省略しています。VBAテスト用のサンプルデータはご自身でご用意ください。

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

エクセルにスピルが登場して、VBAのユーザー定義関数の用途も広がってきていると思います。そこで、文字列を指定した記号・文字列で区切て、セルに出力するユーザー定義関数を作ってみましょう。VBAにはSplit関数がありますが、これをワークシート関数として使えるようにしてみましょうという事です。

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

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

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

1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.マクロはどこに書くの（VBEの起動）｜VBA入門

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

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

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

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