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を見直してみましょう。
頂いた回答
解説
今回は、参照する情報が多いので全てを引数に入れるのは面倒なので、そのあたりは工夫が必要です。
一通りの範囲は引数で指定して、範囲内の列位置等は関数ないで固定にしました。
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コードを掲載しています。
補足
シートでの数式入力方法
Currengregionプロパティ
Findメソッド
これらは正しく動作しません。
以下のツイートを参考にしてください。
https://twitter.com/kyrtnyy/status/1359665643100856320
サイト内関連ページ
同じテーマ「VBA100本ノック」の記事
82本目:ブックのドキュメントプロパティを取得
83本目:請求書を作成してPDF出力
84本目:ブックの自動バックアップ
85本目:請求日から入金予定日を算出
86本目:全シートの総当たり表を作成
87本目:数式のシート間の依存関係
88本目:クロスABC分析作成
89本目:2つのフォルダの統合
90本目:セルに重なっている画像の削除
91本目:時間計算(残業時間の月間合計)
92本目:セルの色を16進で返す関数
新着記事NEW ・・・新着記事一覧を見る
抜けている数値を探せ|エクセル雑感(2022-07-01)
.Net FrameworkのSystem.Collectionsを利用|VBA技術解説(2022-06-29)
迷路ネコが影分身の術を体得したら…|エクセル雑感(2022-06-27)
迷路にネコが挑戦したら、どうなるかな…|エクセル雑感(2022-06-26)
サロゲートペアに対応した自作関数(Len,Left,Mid,Right)|エクセル雑感(2022-06-24)
「マクロの登録」で登録できないプロシージャーは?|エクセル雑感(2022-06-23)
オブジェクトのByRef、ByVal、Variant|エクセル雑感(2022-06-22)
コメントから特定形式の年月を取り出す|エクセル雑感(2022-06-19)
4,9を使わない連番作成|エクセル雑感(2022-06-17)
連番を折り返して出力|エクセル雑感(2022-06-16)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Excelショートカットキー一覧|Excelリファレンス
7.マクロって何?VBAって何?|VBA入門
8.並べ替え(Sort)|VBA入門
9.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
10.エクセルVBAでのシート指定方法|VBA技術解説
- ホーム
- マクロVBA入門編
- VBA100本ノック
- 85本目:請求日から入金予定日を算出
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。