VBA: Popup message based on scenarios

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,251
C29: -2645.61379593897
C30: 8426.61542448785
C31: -11072.2292204268

1. the values of your variables based on those values

Tx -2645.61379593897
Ix 8426.61542448785
Pr-11072.2292204268
Tabs 2,645.61
IAbs 8,426.62
PAbs 11,072.23

2. what is the problem ?

Tx
is NEGATIVE but NONE of your cases allow for a negative value for Tx

Case Tx > 0 And Ix > 0 And Pr > 0
Case Tx > 0 And Ix > 0 And Pr < 0 And CDbl(IAbs) > CDbl(PAbs)
Case Tx > 0 And Ix < 0 And Pr > 0 And CDbl(IAbs) < CDbl(PAbs)

3. solution
Case Tx < 0 And Ix > 0 And Pr > 0
etc
the number of cases dependes on how many different messages you require

and what about Tx = 0 ?


4. Perhaps you need to use Case Else as final case for anything that you have not included

Select Case explained with examples: https://trumpexcel.com/vba-select-case/
 
Last edited:

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Thanks again Yongle

I have amended my code.

Keeping input the values the same:
C29= -2645
C30= 8426
C31= -11072

I get the "something is odd here" message.
I would expect to get message "10" instead (Total < 0 And Interest > 0 And Principal < 0 And InterestAbs < PrincipalAbs)

Code:
Option ExplicitSub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
Dim msg As String, Total As Currency, Interest As Currency, Principal As Currency, Symbol As String
  Symbol = Sheets("User interface").Range("D6").Text
  Total = Sheets("User interface").Range("C29").Value
  Interest = Sheets("User interface").Range("C30").Value
  Principal = Sheets("User interface").Range("C31").Value
   
Dim TotalAbs As String, InterestAbs As String, PrincipalAbs As String
  TotalAbs = Format(Abs(Total), "#,##0.00")
  InterestAbs = Format(Abs(Interest), "#,##0.00")
  PrincipalAbs = Format(Abs(Principal), "#,##0.00")
   
  Dim IAbs As Currency, PAbs As Currency, IPsum As Currency, IPAbs As String
   IAbs = Round(Abs(Interest), 2)
   PAbs = Round(Abs(Principal), 2)
   IPsum = IAbs + PAbs
   IPAbs = Format((IPsum), "##,##0.00")
                   
   'Case scenarios======
Select Case True
    'Scenario 1 and 2
    Case Total > 0 And Interest > 0 And Principal > 0
    msg = "1 and 2"
 
    'Scenario 3
    Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
    msg = "3"


    'Scenario 6
    Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
        msg = "6"
    
    'Scenario 10
   Case Total < 0 And Interest > 0 And Principal < 0 And InterestAbs < PrincipalAbs
        msg = "10"


   'Scenario 11
   Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
   msg = "11 "
   
   'Scenario 13 and 14
   Case Interest = 0
   msg = "13 and 14 "
   
   'Scenario 15
   Case Interest < 0 And Principal = 0
   msg = "15"
   
   'Scenario 16
   Case Interest > 0 And Principal = 0
   msg = "16"
             
    Case Else
    msg = "Something is odd here"


End Select


MsgBox msg
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,251
It would be cleaner to use 2 types of variables with these rules
- type CURRENCY to hold all values and to use in all calculations and numeric tests
- type STRING to display correctly in the message

Amend all calculations and comparisons to only use type CURRENCY
Use STRING variables in message strings

Code:
Sub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
    Dim msg As String, Symbol As String

[I][COLOR=#ff0000]'use CURRENCY type for ALL calculations and numeric tests[/COLOR][/I]
    Dim Total As Currency, Interest As Currency, Principal As Currency
    Dim TotalAbs As Currency, InterestAbs As Currency, PrincipalAbs As Currency

[I][COLOR=#006400]'use STRING type for ALL message string values[/COLOR][/I]
    Dim strTotalAbs As String, strInterestAbs As String, strPrincipalAbs As String

[I]'get values (variable type = CURRENCY)[/I]
    Symbol = Sheets("User interface").Range("D6").Text
    Total = Sheets("User interface").Range("C29").Value
    Interest = Sheets("User interface").Range("C30").Value
    Principal = Sheets("User interface").Range("C31").Value

[I]'get absolutes (variable type = CURRENCY)[/I]
    TotalAbs = Abs(Total)
    InterestAbs = Abs(Interest)
    PrincipalAbs = Abs(Principal)

[I]'formatting the value to output in messages (variable type = STRING)[/I]
    strTotalAbs = Format(TotalAbs, "#,##0.00")
    strInterestAbs = Format(InterestAbs, "#,##0.00")
    strPrincipalAbs = Format(PrincipalAbs, "#,##0.00")
    
   'Case scenarios======
    Select Case True
    'Scenario 1 and 2
        Case Total > 0 And Interest > 0 And Principal > 0
            msg = "1 and 2"
 
    'Scenario 3
        Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
            msg = "3"

    'Scenario 6
        Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
            msg = "6"
    
    'Scenario 10
        Case [COLOR=#ff0000]Total[/COLOR] < 0 And [COLOR=#ff0000]Interest[/COLOR] > 0 And [COLOR=#ff0000]Principal[/COLOR] < 0 And [COLOR=#ff0000]InterestAbs[/COLOR] <[COLOR=#ff0000] PrincipalAbs[/COLOR]
            msg = "10"


   'Scenario 11
        Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
            msg = "11 "
   
   'Scenario 13 and 14
        Case Interest = 0
            msg = "13 and 14 "
   
   'Scenario 15
        Case Interest < 0 And Principal = 0
            msg = "15"
   
   'Scenario 16
        Case Interest > 0 And Principal = 0
            msg = "16"
             
        Case Else
            msg = "Something is odd here"

End Select


MsgBox msg
End Sub
 

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Changed the code accordingly.

Now I get a compile error "variable not defined" when I try to run it, highlighting the line with the message in Scenario 1 and 2.

Code:
Option ExplicitSub Messagetest()
   Worksheets("User interface").Activate
 
'Popup messages---------------------------------------


'Variable definitions======
'use CURRENCY type for ALL calculations and numeric tests
    Dim Total As Currency, Interest As Currency, Principal As Currency
    Dim TotalAbs As Currency, InterestAbs As Currency, PrincipalAbs As Currency, IPsum As Currency


'use STRING type for ALL message string values
    Dim strTotalAbs As String, strInterestAbs As String, strPrincipalAbs As String, symbol As String, strIPAbs As String


'get values (variable type = CURRENCY)
    Total = Sheets("User interface").Range("C29").Value
    Interest = Sheets("User interface").Range("C30").Value
    Principal = Sheets("User interface").Range("C31").Value
    
 'get values (variable type = STRING)
     symbol = Sheets("User interface").Range("D6").Text


'get absolutes (variable type = CURRENCY)
    TotalAbs = Abs(Total)
    InterestAbs = Abs(Interest)
    PrincipalAbs = Abs(Principal)
    IPsum = InterestAbs + PrincipalAbs


'formatting the value to output in messages (variable type = STRING)
    strTotalAbs = Format(TotalAbs, "#,##0.00")
    strInterestAbs = Format(InterestAbs, "#,##0.00")
    strPrincipalAbs = Format(PrincipalAbs, "#,##0.00")
    strIPAbs = Format((IPsum), "##,##0.00")

'Case scenarios======
    Select Case True
    'Scenario 1 and 2
        Case Total > 0 And Interest > 0 And Principal > 0
            msg = "1 and 2"
 
    'Scenario 3
        Case Total > 0 And Interest > 0 And Principal < 0 And InterestAbs > PrincipalAbs
            msg = "3"

    'Scenario 6
        Case Total > 0 And Interest < 0 And Principal > 0 And InterestAbs < PrincipalAbs
            msg = "6"
    
    'Scenario 10
        Case [COLOR=#ff0000]Total[/COLOR] < 0 And [COLOR=#ff0000]Interest[/COLOR] > 0 And [COLOR=#ff0000]Principal[/COLOR] < 0 And [COLOR=#ff0000]InterestAbs[/COLOR] <[COLOR=#ff0000] PrincipalAbs[/COLOR]
            msg = "10"


   'Scenario 11
        Case Total < 0 And Interest < 0 And Principal > 0 And InterestAbs > PrincipalAbs
            msg = "11 "
   
   'Scenario 13 and 14
        Case Interest = 0
            msg = "13 and 14 "
   
   'Scenario 15
        Case Interest < 0 And Principal = 0
            msg = "15"
   
   'Scenario 16
        Case Interest > 0 And Principal = 0
            msg = "16"
             
        Case Else
            msg = "Something is odd here"

End Select


MsgBox msg
End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,251
That is because one or more of the variables used in the code has not been declared.
 

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Thank you Yongle - much appreciated

I am travelling at the moment and will have a closer look at the variable definition when I get back.
 

Whiox

New Member
Joined
Feb 19, 2019
Messages
33
Hello Yongle - we forgot to declare " Dim msg As String". All fixed up now and working well.

Thank you so much for your help!!
 

Forum statistics

Threads
1,077,918
Messages
5,337,180
Members
399,131
Latest member
Vinnyjuice

Some videos you may like

This Week's Hot Topics

Top