时间戳

首页 > 解决方案 > 会员费计算器的 Excel VBA 帮助

excel - 会员费计算器的 Excel VBA 帮助

问题描述

我的课程项目需要准备以下程序。这是体育俱乐部的会员费计算程序。费用变化如下。这应该没有任何日期、年份或月份功能。

2014 - 01 到 2014 - 02 ---> 100 美元（2014 年是 01 年是月份）

2014 - 03 至 2018 - 02 --->$120

2018 - 03 至 2019--------> -$150

https://i.imgur.com/TTWLz9m.jpg?1

我可以为上述程序创建一个变量（x1）来计算任何年份范围内的月份。

X1 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1)

计算从 2014 年开始。如果从 2014-01 或 2014-02 开始，则应显示在 A1 中。如果结束年份大于 2014 年，A1 值应显示为 200，其余其他值应显示在 B1 中，如下图所示。（意思是，当年终值（C）> 2014 时，计算停止并在 A1 中显示该值，其他计算继续进行，其余应在 B1 中显示） https://i.imgur.com/Se0CFyI.jpg？ 1

问题：如果 C 值 > 2018 且 D 值 > 02，我无法停止 B1 值

问题：如何在 Excel VBA 中修改此程序直到 2018 年 2 月才正确，但在 2018 年 3 月和 2019 年之后导致错误答案。

Private Sub CommandButton1_Click()

A1.Value = 0
B1.Value = 0
D1.Value = 0

X1 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1)
X2 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (10 - Val(B.Value) + Val(D.Value) + 1)
X3 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (11 - Val(B.Value) + Val(D.Value))
X4 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1) - Val(D.Value) + 2
X11 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1) - Val(D.Value) + 1
X5 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + 12
X6 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + 11
X7 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value)) - Val(D.Value) + 1
X8 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value)) - Val(D.Value) + 2
X9 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1) - Val(D.Value) + 1
X10 = (((Val(C.Value) - 1) - Val(A.Value)) * 12) + (12 - Val(B.Value) + Val(D.Value) + 1) - Val(D.Value) + 2

Y1 = ((Val(A.Value)) * 0) + (Val(B.Value))

'2014 January and February ===========================================================

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) > 2014 And Val(D.Value) = 1 Then
    A1.Value = 2 * Y1 * 100
End If

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) = 2014 And Val(D.Value) = 1 Then
    A1.Value = Y1 * 100
End If

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) >= 2014 And Val(D.Value) >= 2 Then
    A1.Value = 2 * Y1 * 100
End If

If Val(A.Value) = 2014 And Val(B.Value) = 2 And Val(C.Value) >= 2014 And Val(D.Value) <= 12 Then
    A1.Value = Y1 / 2 * 100
End If

'===================================================================================================

'2014- March to 2018 - February

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) < 2018 And Val(D.Value) >= 1 Then
    B1.Value = (X2 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 2 And Val(C.Value) < 2018 And Val(D.Value) >= 1 Then
    B1.Value = (X3 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) >= 3 And Val(C.Value) < 2018 And Val(D.Value) >= 1 Then
    B1.Value = (X1 * 120)
End If

If Val(A.Value) > 2014 And Val(B.Value) >= 1 And Val(C.Value) < 2018 And Val(D.Value) >= 1 Then
    B1.Value = (X1 * 120)
End If

If Val(A.Value) > 2014 And Val(B.Value) >= 1 And Val(C.Value) >= 2018 And Val(D.Value) >= 2 Then
    B1.Value = (X4 * 120)
End If

If Val(A.Value) > 2014 And Val(B.Value) >= 1 And Val(C.Value) >= 2018 And Val(D.Value) = 1 Then
    B1.Value = (X11 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) = 2018 And Val(D.Value) = 1 Then
    B1.Value = (X2 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) = 2018 And Val(D.Value) = 2 Then
    B1.Value = (X3 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 2 And Val(C.Value) = 2018 And Val(D.Value) = 1 Then
    B1.Value = (X6 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 2 And Val(C.Value) = 2018 And Val(D.Value) = 2 Then
    B1.Value = (X5 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) >= 3 And Val(C.Value) = 2018 And Val(D.Value) = 1 Then
    B1.Value = (X9 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) >= 3 And Val(C.Value) = 2018 And Val(D.Value) >= 2 Then
    B1.Value = (X10 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 1 And Val(C.Value) = 2018 And Val(D.Value) > 2 Then
    B1.Value = (X7 * 120)
End If

If Val(A.Value) = 2014 And Val(B.Value) = 2 And Val(C.Value) = 2018 And Val(D.Value) >= 3 Then
    B1.Value = (X8 * 120)
End If

'===================================================================================================
Me.Answer = (Me.A1 + 0) + (Me.B1 + 0) + (Me.D1 + 0)

End Sub

标签： excelvba

解决方案

我不确定 CDate 和 IsDate 是否被禁止。我还假设任何日期 >="2018-03-01" 是 150 美元，尽管您可以将其更改为介于两者之间。如果这些是可以接受的假设。我已经连接了“-01”以与您的演示文稿保持一致，但也许您也可以直接附加而无需该步骤。

Option Explicit
Public Sub test()
    Dim x As String, var As String
    var = "2014-01"
    var = var & "-01"
    If Not IsDate(CDate(var)) Then
        Debug.Print "please ensure valid input"
        Exit Sub
    End If
    Select Case True
    Case var >= CDate("2018-03" & "-01")
        x = "$150"
    Case var >= CDate("2014-03" & "-01")
        x = "$120"
    Case var >= CDate("2014-01" & "-01")
        x = "$100"
    Case Else
        x = "undefined"
    End Select
    Debug.Print x
End Sub

推荐阅读