Page 4 of 4 FirstFirst ... 234
Results 31 to 38 of 38

Thread: VBA: Popup message based on scenarios
Thanks Thanks: 0 Likes Likes: 0

  1. #31
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    All good

  2. #32
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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 by Yongle; Sep 10th, 2019 at 12:43 AM.

  3. #33
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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

  4. #34
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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
    
    '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
    
    'use STRING type for ALL message string values
        Dim strTotalAbs As String, strInterestAbs As String, strPrincipalAbs As String
    
    'get values (variable type = CURRENCY)
        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
    
    'get absolutes (variable type = CURRENCY)
        TotalAbs = Abs(Total)
        InterestAbs = Abs(Interest)
        PrincipalAbs = Abs(Principal)
    
    '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")
        
       '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

  5. #35
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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 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

  6. #36
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    That is because one or more of the variables used in the code has not been declared.

  7. #37
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    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.

  8. #38
    New Member
    Join Date
    Feb 2019
    Posts
    33
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA: Popup message based on scenarios

    Hello Yongle - we forgot to declare " Dim msg As String". All fixed up now and working well.

    Thank you so much for your help!!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •