VBA自定义函数判断日期

黑狼-诺

Public Function LL(dt As Range)   
If dt > #10/24/2015# Then LL = 4.75
Else: LL = 5.5
End Function
我想把dt作为单元格内的时间，然后判断dt在哪个时间范围内，就用那个时间段的贷款利率来计算贷款，但是我这个一直提示函数错误,怎么回事呢？求大神指教
能不能更高级一点，直接实现VBA函数能够计算，在打款日期和截止日期之间总共产生多少利息？

我在最新上传的附件里边用公式解决了问题，但是公式太复杂了，而且太繁复，将来想验证和检查都会困难。所以我觉得VB和数组函数应该能更简单的解决问题，而且方便检查。

VB的循环计算语句应该是最优的选择，因为本金是定值，时间段和不同的时间段产生的利率是变量，每次都是在循环和迭代计算，每次循环的时候能够带入计算不同的时间段和利率，这个循环会很复杂吗？
我不会这个，有大神能够用吗？谢谢

以下是我在表格里编的计算公式：
’=IF(D3>DATE(2015,10,24), DATEDIF(D3,E3,"Y")*C3*K3+DATEDIF(D3,E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,8,26),DATEDIF(D3,"2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,6,28),DATEDIF(D3,"2015/8/26","D")*(C3*M3/J3)+
DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,5,11),DATEDIF(D3,"2015/6/28","D")*(C3*N3/J3)+
DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2015,3,11),DATEDIF(D3,"2015/5/11","D")*(C3*O3/J3)+
DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2014,11,22),DATEDIF(D3,"2015/3/11","D")*(C3*P3/J3)+
DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2014,9,11),DATEDIF(D3,"2014/11/22","D")*(C3*Q3/J3)+
DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2012,7,6),DATEDIF(D3,"2014/9/11","YD")*C3*R3+DATEDIF(D3,"2014/9/11","YD")*(C3*R3/J3)+
DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2012,6,8),DATEDIF(D3,"2012/7/6","D")*(C3*S3/J3)+
DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,7,7),DATEDIF(D3,"2012/6/8","D")*(C3*T3/J3)+
DATEDIF("2012/6/8","2012/7/6","YD")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,4,6),DATEDIF(D3,"2011/7/7","D")*(C3*U3/J3)+
DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2011,2,9),DATEDIF(D3,"2011/4/6","D")*(C3*V3/J3)+
DATEDIF("2011/4/6","2011/7/7","D")*(C3*U3/J3)+DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),
IF(D3>DATE(2010,12,26),DATEDIF(D3,"2011/2/9","D")*(C3*W3/J3)+
DATEDIF("2011/2/9","2011/4/6","D")*(C3*V3/J3)+DATEDIF("2011/4/6","2011/7/7","D")*(C3*U3/J3)+DATEDIF("2011/7/7","2012/6/8","D")*(C3*T3/J3)+DATEDIF("2012/6/8","2012/7/6","D")*(C3*S3/J3)+DATEDIF("2012/7/6","2014/9/11","Y")*C3*R3+DATEDIF("2012/7/6","2014/9/11","YD")*(C3*R3/J3)+DATEDIF("2014/9/11","2015/11/22","D")*(C3*Q3/J3)+DATEDIF("2014/11/22","2015/3/11","D")*(C3*P3/J3)+DATEDIF("2015/3/11","2015/5/11","D")*(C3*O3/J3)+DATEDIF("2015/5/11","2015/6/28","D")*(C3*N3/J3)+DATEDIF("2015/6/28","2015/8/26","D")*(C3*M3/J3)+DATEDIF("2015/8/26","2015/10/24","D")*(C3*L3/J3)+DATEDIF("2015/10/24",E3,"Y")*C3*K3+DATEDIF("2015/10/24",E3,"YD")*(C3*K3/J3),0)))))))))))))

黑狼-诺

用数组函数是不是能很简单的实现？

YZC51

需要自己建一个贷款利率表！

黑狼-诺

YZC51 发表于 2020-1-15 19:56
需要自己建一个贷款利率表！

是把利率表做成数组吗？

黑狼-诺

求大神的帮助，编写的VB代码问题在哪？？

黑狼-诺

VB的循环计算语句应该是最优的选择。
因为本金是定值，时间段和不同的时间段产生的利率是变量。
公式里边每次都是在重复的计算不同时间段产生的利息，这是不是就是在循环和迭代计算？
如果用循环语句的话，每次循环的时候能够带入计算不同的时间段和利率，这个循环会很复杂吗？
我不会这个，有大神能够用吗？谢谢
这是我自己琢磨的思路，不知道正确不正确。希望大神指正

黑狼-诺

@opiona
@YZC51

黑狼-诺

YZC51 发表于 2020-1-15 19:56
需要自己建一个贷款利率表！

大神有时间能再看看吗？？我自己编了个公式计算，虽然也能实现想要的结果。但是不方便后期的检查和纠误

microyip

简直就是高手中的高手，佩服，密集恐惧症发了

microyip

~~~~发错~~~