求助将不同场景的应收租金的计算公式使用VBA代替 - 编程论坛

#2

阳光上的桥2023-05-31 09:04

就是遇到下面这个编译错误吗，GetMonth是获取单元格日期类型变量的月份值吗，直接使用Month即可

只有本站会员才能查看附件，请登录

#3

小白痴一个2023-05-31 10:24

是整个自定义函数运行之后在目标单元格中的计算的值差异过大

#4

阳光上的桥2023-05-31 11:43

需求就是把下图这个EXCEL公式翻译为VBA自定义函数吗？可以直接依样画葫芦的弄些IIF吗

只有本站会员才能查看附件，请登录

#5

小白痴一个2023-05-31 12:40

是这样的需求，以前的EXCEL公式过于繁杂，太不方便了，还有一点就是这个EXCEL公式没有考虑不满一个月时的应收租金的计算

#6

阳光上的桥2023-05-31 14:20

我写了月结的代码，测试了两行数据，第一行是整月，第二行开始和结束都有半月，应该是需要的意思吧

只有本站会员才能查看附件，请登录

说明，我调整了参数顺序，增加了G1作为参数，函数参数为=CalculateRent(起租日期,止租日期,结算周期,月租金,计算年,计算月)，这样就不需要循环处理

程序代码：

Option Explicit

Function CalculateRent(startDate As Date, endDate As Date, period As String, rentAmt As Double, curYear As Integer, curMonth As Integer) As Double
    Dim preDate1 As Date, preDate2 As Date, curDate1 As Date, curDate2 As Date, rent1#, rent2#
    preDate1 = DateSerial(curYear, curMonth - 1, 1)     '上月初
    preDate2 = DateSerial(curYear, curMonth, 1) - 1     '上月末
    curDate1 = DateSerial(curYear, curMonth, 1)         '本月初
    curDate2 = DateSerial(curYear, curMonth + 1, 1) - 1 '本月末
    If startDate < curDate1 And endDate >= curDate1 Then
        If period = "月结" Then
            If startDate >= preDate1 Then rent1 = rentAmt * (preDate2 - startDate) / (preDate2 - preDate1) Else rent1 = 0 '上月租金
            If endDate > curDate2 Then rent2 = rentAmt Else rent2 = rentAmt * (endDate - curDate1) / (curDate2 - curDate1) '本月租金
        ElseIf period = "季结" Then
        ElseIf period = "半年结" Then
        ElseIf period = "年结" Then
        End If
    End If
    CalculateRent = rent1 + rent2
End Function

#7

小白痴一个2023-05-31 14:54

好像首月的应收租金是计算了两个月的金额，正确的理解应该是先用后付的概念，也就是说比如1月开始起租，1月的租金是2月支付，2月的租金是3月支付，以次类推至合同结束后的下一个月收取最后一个月的租金，季结的也是这样的理解，合同开始后的前三个月不收取租金，第四个月开始收取前三个月的租金，第七个月收取前三个月的租金，半年结就是前6个月不收租金，第七个月收取前6个月的租金，第13个月再收前6个月的租金

#8

阳光上的桥2023-05-31 15:40

那就简单了

程序代码：

Option Explicit

Function CalculateRent(startDate As Date, endDate As Date, period As String, rentAmt As Double, curYear As Integer, curMonth As Integer) As Double
    Dim preDate1 As Date, preDate2 As Date, renDate1 As Date, renDate2 As Date, perMons&, curPeriodMon
    Select Case Left(Trim(period), 1)
        Case "月":            perMons = 1 '单个周期包含的月份数量
        Case "季":            perMons = 3
        Case "半":            perMons = 6
        Case "年":            perMons = 12
    End Select
    curPeriodMon = ((curMonth - 1) \ perMons) * perMons + 1     '本周期(月/季/半年/年)开始的月份
    preDate1 = DateSerial(curYear, curPeriodMon - perMons, 1)   '上周期(月/季/半年/年)初日期
    preDate2 = DateSerial(curYear, curPeriodMon, 1) - 1         '上周期(月/季/半年/年)末日期
    renDate1 = IIf(startDate < preDate1, preDate1, startDate)   '上周期(月/季/半年/年)租赁开始日期
    renDate2 = IIf(endDate < preDate2, endDate, preDate2)       '上周期(月/季/半年/年)租赁结束日期
    If curMonth = curPeriodMon Then '只在周期开始月份收租
        If startDate < preDate2 And endDate > preDate1 Then '上周期内发生出租
            CalculateRent = rentAmt * perMons * (renDate2 - renDate1) / (preDate2 - preDate1)  '上月/季/半/年租金
        End If
    End If
End Function

#9

小白痴一个2023-05-31 15:55

季结、半年结、年结的是根据合同起始的月份往后推3个月，6个月，12个月，比如合同开始于23年1月1日，那第一季度的应收租金应该在4月份体现，合同开始于23年2月1日的，那第一个季度的应收租金应该体现在5月份，半年结与年结的也是这样理解

[此贴子已经被作者于2023-5-31 15:57编辑过]

#10

阳光上的桥2023-06-01 09:02

感觉这是一道数学题哈

curPeriodMon = ((curMonth - 1) \ perMons) * perMons + 1 '本周期(月/季/半年/年)开始的月份
修改为：
curPeriodMon = (((curMonth - 1) \ perMons) * perMons + Month(startDate) - 1) Mod 12 + 1 '本周期(月/季/半年/年)开始的月份

#11

小白痴一个2023-06-01 15:46

谢谢大神的解惑

#12

小白痴一个2023-06-01 17:14

回复 10楼阳光上的桥

感觉月结的重新计算后除了1月份起租的可以正常计算外，其他月份起租的计算结果全为0了

#13

阳光上的桥2023-06-02 10:34

好像没问题吧

只有本站会员才能查看附件，请登录

#14

小白痴一个2023-06-02 10:58

只有本站会员才能查看附件，请登录

当合同起租期改为23年2月或其他月份时，就会出现图上显示的计算结果为0的情况

#15

阳光上的桥2023-06-02 12:51

curPeriodMon = IIf(perMons = 1, curMonth, (((curMonth - 1) \ perMons) * perMons + Month(startDate) - 1) Mod 12 + 1) '本周期(月/季/半年/年)开始的月份

只有本站会员才能查看附件，请登录

#16

小白痴一个2023-06-02 14:13

回复 15楼阳光上的桥

问题已完美解决，

#17

小白痴一个2023-06-09 11:01

回复 15楼阳光上的桥

大神你好，上次我求助将不同场景的应收租金的计算公式使用VBA代替时，你帮我解决了问题，今天我在实际使用中遇到了一个特殊的问题，当结算方式为季结时，合同开始的月份为4及以上时，计算出来的值为0，能否帮我一下，解决这个小问题