Troubleshooting VBA Min/Max Code

Posted on 2007-08-05
Last Modified: 2013-11-27
I have the following code that shows the values in a MsgBox. It is a small part of the programme and I am testing it. The MsgBox  is not being shown....Can you help please!!

Private Sub Command78_Click()   'This is the VBA for Main Plans and Not for Corporate or Family Plans
On Error GoTo Err_Command78_Click

Dim iContractTermServed As Double
Dim iContractTermRemaining As Double
Dim iContractTermRemaininginYears As Double
Dim iContractValueRemaining As Double
Dim iContractvalueRemainingMax As Double
Dim iContractTermServedinYears As Double

iContractTermServed = Me.Termination_Date.Value - Me.Main_Pt_Contract_Start_Date
iContractTermServedinYears = iContractTermServed / 365
iContractTermRemaining = Me.Contract_End_date.Value - Me.Termination_Date.Value
iContractTermRemaininginYears = iContractTermRemaining / 365
iContractValueRemaining = iContractTermRemaininginYears * Me.Plan_Premium * 12
iContractvalueRemainingMax = 1.5 * Me.Plan_Premium * 12  

If iContractTermServedinYears <= 0.25 Then  
    masgBox = IIf(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9)

End If

    Exit Sub

    MsgBox Err.Description
    Resume Exit_Command78_Click
End Sub
Question by:robbhat
    LVL 46

    Expert Comment

    by:Wayne Taylor (webtubbs)
    Hi robbhat,

    The first thing I've noticed is the spelling error. 'masgBox' should be MsgBox.


    LVL 84
    webtubbs is correct, and the formatting of your msgbox is incorrect ... generally it'd be this:

    MsgBox "Message  " & IIf(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9), vbOkOnly, "Information"

    Are you sure this is true:

    iContractTermServedinYears <= 0.25

    If not, then the msgbox won't be shown. Set a breakpoint (place your cursor at the beginning of the code and press F9), then execute the code. You can then step through the code with the F8 button. Note you can also "check" your statements during break mode by running them in the Immediate window (View - Immediate will show you this). For example, if you want to verify the above line returns true, type this in the Immediate window:

    ?iContractTermServedinYears <= 0.25

    and press the Enter key ... this will return True or False, depending on the value

    Author Comment

    I have rectified the MsgBox spelling and the problem is slightly worse. The Debug appears, highlights MsgBox and states "Function call on left-hand side of assignment must return Variant or Object"

    Hi LSMConsulting, the value is true. The reason I know this is that on my code under each line I have a MsgBox displayed to show the answer (I took these MsgBoxes off to make it easier to trouble shoot for the Experts). But, in anycase, because of what I wrote above, the syntax must be faulty. Can you please help me?

    LVL 3

    Expert Comment

    try assigning the value of your IF statement to a declared variable, then use:

    Dim myValue as <long, integer, whatever>

    myValue = <your IF calculation>
    Msgbox ("The value is " & myValue)
    LVL 84

    Accepted Solution

    You must format your MsgBox call correctly ... it should look something like what I posted earlier (there is no = in your MsgBox call, unless you are comparing the RESULT of your MsgBox to a value, which you're not - you're just displaying a message).

    <The reason I know this is that on my code under each line I have a MsgBox displayed to show the answer >

    Then your question makes no sense ... if the MsgBox is being shown, then you've answered your own question. I'd STRONGLY encourage you to not make assumptions regarding your data values, but to use the Immediate window to verify that your If statement is correct ... in many cases, what you assume is happening is not, in fact, what's going on. Your MsgBox would show a textual version of the calculation, which may or may not represent the actual computation. Using the Immediate window, and verifying whether this statement is actually True will provide solid proof.

    Author Comment

    Do you mean the If statement calculation for selecting the Minimum value. If so, I have done this:

    Dim iMyMinMaxValue1 As Integer

    iMyMinMaxValue1 = iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9

    But, its not accepting the syntax

    can you help me with that?
    LVL 44

    Assisted Solution

    iMyMinMaxValue1 = IIF(iContractvalueRemainingMax < iContractValueRemaining, CDbl(iContractvalueRemainingMax) * 0.9, CDbl(iContractValueRemaining) * 0.9)

    or , if you also want to know which of the two values is the larger:

    Dim strMaxValue as String

    strMaxValue = IIF(iContractvalueRemainingMax < iContractValueRemaining, "Max value is ContractRemainingMax, and the value is " & CDbl(iContractvalueRemainingMax) * 0.9, "Max value is ContractRemaining, and the value is " &CDbl(iContractValueRemaining) * 0.9)

    MsgBox "The Max value is " & strMaxValue, vbOkOnly

    MsgBoix is a Function that displays a Message and can return, as the value of the function, an indicator of which button the user clicked, in order to close the displayed message box.  You cannot assign a value to the MsgBox (as in MsgBox = ....)

    IIF is also a function (called the Immediate IF function, which tests the 'truth' of the first statement, and if it is TRUE, returns the value of the second statement, otherwise returns the value of the third statement:

    Value = IIF(TestStatement, ValueIfTrue, ValueIfFalse)

    if TestStatement is TRUE, Value = ValueIfTrue
    if TestStatement is NOT TRUE, then Value = ValueIfFalse

    I hope this clears things up for you.



    Author Comment

    Out of all the questions I have posited, 95% have been answered by your Experts. They are fantastic. Some are extra fantastic like LsmConsulting and Arthur Wood to mention a few of the exceptional people helping us. These experts are extraordinary because they take time to explain. For novices like me this is invaluable. The site itself is PRICELESS. Because of the site I have managed to build an access website with a lot of effects which I could never have dreamt of. Keep it going.....Brill

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    In the previous article, Using a Critera Form to Filter Records (, the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
    The first two articles in this short series — Using a Criteria Form to Filter Records ( and Building a Custom Filter ( — discuss in some detail how a form can be…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    733 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now