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

VBAを100本の練習問題で鍛えます
公開日: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,末
入金日が土日祝日の場合は前日にする。

マクロ VBA 100本ノック

マクロ VBA 100本ノック

マクロ VBA 100本ノック

マクロ VBA 100本ノック


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


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

マクロ VBA 100本ノック


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進で返す関数
・出題 ・頂いた回答 ・解説 ・補足 ・サイト内関連ページ


新着記事NEW ・・・新着記事一覧を見る

TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.繰り返し処理(For Next)|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.変数宣言のDimとデータ型|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.メッセージボックス(MsgBox関数)|VBA入門
9.条件分岐(Select Case)|VBA入門
10.ブック・シートの選択(Select,Activate)|VBA入門




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


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


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