Case Select

[mykil]

Hi to all!!! is it possible to make Select Case Condition in VBA which have IIF statement or Condition rather?

 

[PNGBill]

You can Nest both ways.

Select Case can have If then within the case options and If Then can have Select case in there options.

I assume you have to complete the If Then set before you go on to the next Case and similar for case within If Then.

 

[mykil]

Huh!!Can you gave some example PNGBill?if that is okay

 

[PNGBill]

This code covers a few instances of select case and If then and Nested Select Case.

Select Case EmailCat
        Case "CompletedLoan"                    'loan completed
            strLoanPositionWeight = "Your loan appears to have been completed." & Chr(13) & Chr(10) & _
                "Please contact a Club Group Team Member, details below, for confirmation of your loan balance and " & Chr(13) & Chr(10) & _
                "discuss the use of your Club Points and or obtain a cessation Letter if this is required."
            strEmailOpen = "Dear "
            strEmailClose = "Kind regards,"
            MailImportance = olImportanceLow
        Case "CurrentLoan"                      'Repayments are up to date
            If SumLoanLateFee < 2 Then          'loan has no late fees so far
                strLoanPositionWeight = "Your loan repayments appear to be up to date. This is very good." & Chr(13) & Chr(10) & Chr(10)
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceLow
            Else                                'loan may have incurred late fees but it looks like they have been paid
                strLoanPositionWeight = "Your loan repayments appear to be up to date. This is very good." & Chr(13) & Chr(10) & Chr(10) & _
                    "In the event there is a delay in a repayment, please remember you agreed to pay a Late Fee of " & Format(LateFee, "Currency") & " " & Chr(13) & Chr(10) & _
                    "for each Fortnight a repayment is missed and for each Fortnight " & Chr(13) & Chr(10) & _
                    "the loan remains not Completed after " & EndDate & "."
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceLow
            End If
        Case "LateFeeOnly"                      'original loan has been repaid but there are late fees left to repay
            strLoanPositionWeight = "You may need to Improve your Repayments if you are to complete your loan by the " & EndDate & " as you promised to do." & Chr(13) & Chr(10) & Chr(10) & _
                "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                "Otherwise, please communicate with a Club Group Team Member regarding this issue."
            strEmailOpen = "Dear "
            strEmailClose = "Kind regards,"
            MailImportance = olImportanceLow
        Case "OverDueLoan"                      'loan overdue, may include late fees
            Dim OverDuePCent As Integer
            OverDuePCent = ((OverDueFortnights / LoanRepayNum) * 100)
            Select Case OverDuePCent            'evaluate number of fortnights loan overdue by
                Case Is <= 10                   'Missed fortnights equal to or less then 10%
                    strLoanPositionWeight = "You may need to Improve your Repayments if you are to complete your loan by the " & EndDate & " as you promised to do." & Chr(13) & Chr(10) & Chr(10) & _
                        "Please remember you agreed to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight a repayment is missed and " & Chr(13) & Chr(10) & _
                        "for each Fortnight the loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                        "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                        "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                        "Otherwise, please communicate with a Club Group Team Member regarding this issue."
                    strEmailOpen = "Dear "
                    strEmailClose = "Kind regards,"
                    MailImportance = olImportanceHigh
                Case Is <= 20                   'Missed fortnights is over 10 % and equal to or less then 20%
                    strLoanPositionWeight = "You appear to have missed a few Repayments and you may need to improve on this " & Chr(13) & Chr(10) & _
                        "if you are to complete your loan by the " & EndDate & ", as you promised to do." & Chr(13) & Chr(10) & Chr(10) & _
                        "Please remember you agreed to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight a repayment is missed and " & Chr(13) & Chr(10) & _
                        "for each Fortnight the loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                        "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                        "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                        "Otherwise, please urgently communicate with a Club Group Team Member regarding this issue."
                    strEmailOpen = "Dear "
                    strEmailClose = "Kind regards,"
                    MailImportance = olImportanceHigh
                Case Is <= 30                   'Missed fortnights is over 20% and equal to or less then 30%
                    strLoanPositionWeight = "You have missed a few Repayments and you will need to urgently improve on this " & Chr(13) & Chr(10) & _
                        "if you are to complete your loan by the " & EndDate & ", as you promised to do." & Chr(13) & Chr(10) & Chr(10) & _
                        "Please remember you agreed to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight a repayment is missed and " & Chr(13) & Chr(10) & _
                        "for each Fortnight the loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                        "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                        "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                        "Otherwise, please urgently communicate with a Club Group Team Member regarding this issue."
                    strEmailOpen = "Dear "
                    strEmailClose = "Kind regards,"
                    MailImportance = olImportanceHigh
    
                Case Is <= 50                   'Missed fortnights is over 30 % and equal to or less then 50%
                    strLoanPositionWeight = "Our records show you have missed up to half of your Repayments." & Chr(13) & Chr(10) & _
                        "As this matter is quite serious and you must make every effort to improve your repayments." & Chr(13) & Chr(10) & Chr(10) & _
                        "You Promised to complete your loan by the " & EndDate & "." & Chr(13) & Chr(10) & _
                        "You are reminded of your commitment to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight a repayment is missed and " & Chr(13) & Chr(10) & _
                        "for each Fortnight the loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                        "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                        "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                        "Otherwise, you should urgently communicate with a Club Group Team Member regarding this issue."
                    strEmailOpen = "Dear "
                    strEmailClose = "Kind regards,"
                    MailImportance = olImportanceHigh
                Case Else                       'Missed fortnights exceed 50%
                    strLoanPositionWeight = "Our records show you may have missed over half of your Repayments." & Chr(13) & Chr(10) & _
                        "This matter is very serious and you must make every effort to improve your repayments." & Chr(13) & Chr(10) & Chr(10) & _
                        "You Promised to complete your loan by the " & EndDate & "." & Chr(13) & Chr(10) & _
                        "You are reminded of your commitment to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight a repayment is missed and " & Chr(13) & Chr(10) & _
                        "for each Fortnight the loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                        "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                        "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                        "Otherwise, you are required to urgently communicate with a Club Group Team Member regarding this issue."
                    strEmailOpen = "Dear "
                    strEmailClose = "Kind regards,"
                    MailImportance = olImportanceHigh
            End Select
        Case "LateFeeOnlyPastDue"               'loan past end date and late fees only to repay
            If fncEvaluateRepay(RepayID) >= 2 Then
                strLoanPositionWeight = "Your loan is Past Due and should have been completed on or before " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                    "While we appreciate the repayments made to date, every effort should be to clear this outstanding debt ASAP." & Chr(13) & Chr(10) & _
                    "Please communicate with a Club Group Team Member should you have any problems regarding this issue."
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceHigh
            Else
                strLoanPositionWeight = "You have not honoured your promise to complete this loan on or before " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                    "If your Repayment is in your Employer's Payroll System, please confirm with " & Chr(13) & Chr(10) & _
                    "your Payroll Dept that they will forward payment ASAP." & Chr(13) & Chr(10) & _
                    "Otherwise, you should urgently communicate with a Club Group Team Member regarding this issue."
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceHigh
            End If
        Case "OverDueLoanPastDue"               'loan past end date with original loan plus any possibly late fees to repay
            If fncEvaluateRepay(RepayID) >= 3 Then
                strLoanPositionWeight = "Your loan is Past Due and should have been completed on or before " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                    "While we appreciate the repayments made to date, every effort should be to clear this outstanding debt ASAP." & Chr(13) & Chr(10) & _
                    "Please communicate with a Club Group Team Member should you have any problems regarding this issue."
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceHigh
            Else
                strLoanPositionWeight = "You have not honoured your promise to complete this loan on or before " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                    "Remember you agreed to pay a Late Fee of " & Format(LateFee, "Currency") & " for each Fortnight the " & Chr(13) & Chr(10) & _
                    "loan remains Not Completed after " & EndDate & "." & Chr(13) & Chr(10) & Chr(10) & _
                    "You are required to Urgently contact a Club Group Team Member, details shown below, as this matter is now serious and should have your full attention."
                strEmailOpen = "Dear "
                strEmailClose = "Kind regards,"
                MailImportance = olImportanceHigh
            End If
        Case Else                               'This shouldn't be an option - lets hope
            strLoanPositionWeight = ""
    End Select