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

1. Re: VBA: Popup message based on scenarios

All good   Reply With Quote

2. 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/  Reply With Quote

3. 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  Reply With Quote

4. 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  Reply With Quote

5. 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  Reply With Quote

6. Re: VBA: Popup message based on scenarios

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

7. 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.  Reply With Quote

8. 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!!  Reply With Quote

User Tag List

Tags for this Thread

c29, c31, cell, message, values  Posting Permissions

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