Solved: Assign lesser value to variable?

[clhare]

Can someone help me with the following questions re calculations in VBA Word? I have not really worked with calculations much, and my code is not working correctly.

1) What is the best way to do two calculations, then assign whichever result is lower to the variable "intLowestResult"? I tried the following, but it didn't work:

' Determine which result is lower
intResult1 = Item2.Value + 2
intResult2 = Item2.Value * 2 - Item1.Value
If intResult1 < intResult2 Then
intLowestResult = Format(intResult1, "#.#0")
ElseIf intResult2 < intResult1 Then
intLowestResult = Format(intResult2, "#.#0")
End If


2) Is it correct to declare intResult1 and intResult2 as integers or as should I declare them as long variables?

3) How do I round down instead up?

4) How do I add a plus (+) or minus (-) sign in front of the value in intResult?

5) How do I assign a string variable ("pass" or "fail") based on whether the result is greater than zero or not? In other words, if the result is less than zero, I want to assign strResult = "Pass". Otherwise, I want strResult = "Fail".

Any assistance is greatly appreciated!

[Bob Phillips]

1) What is the best way to do two calculations, then assign whichever result is lower to the variable "intLowestResult"? I tried the following, but it didn't work:

' Determine which result is lower
intResult1 = Item2.Value + 2
intResult2 = Item2.Value * 2 - Item1.Value
If intResult1 < intResult2 Then
intLowestResult = Format(intResult1, "#.#0")
ElseIf intResult2 < intResult1 Then
intLowestResult = Format(intResult2, "#.#0")
End If

Can't see a problem with that. This code is shorter, but effectively the same



intresult1 = Item.Value + 2
intresult2 = Item.Value * 2 - Item.Value
intLowestResult = Format(IIf(intresult1 < intresult2, intresult1, intresult2), "#.#0")

But the code seems pointless. intresult1 will always be greater than intresult2, because intresult1 is value + 2, inresult2 is just value.



2) Is it correct to declare intResult1 and intResult2 as integers or as should I declare them as long variables?
Not wrong necessarily, but use long.


3) How do I round down instead up?
How are you rounding up?


4) How do I add a plus (+) or minus (-) sign in front of the value in intResult?
Format(value,"+#.#0;-#.#0")


5) How do I assign a string variable ("pass" or "fail") based on whether the result is greater than zero or not? In other words, if the result is less than zero, I want to assign strResult = "Pass". Otherwise, I want strResult = "Fail".
strResult = IIf(intresult < 0,"Pass", "Fail")

[clhare]

I've rewritten my code using the info provided above, but there are still problems. I've attached a zip file of my template. It just has the user form in it and the code I'm working on is in the "Calculate Results" button. When that button is pressed, I want the labels below the button on the user form to change based on the results of the test.

I've got some values already entered on the form. When I run the form and click on the button:

1) The totals that I get are rounded to whole numbers instead of using 2 decimal places as I've formatted it. I should get the following:

ADP Standard Test: Fails by -1.67%
ACP Standard Test: Fails by -1.00%
ADP Alternate Test: Passes by +1.97%
ADP Alternate Test: Passes by +0.50%

2) If the value is > 0, I should get a plus sign before it, but I don't. I do get a minus sign if the value is < 0.

What am I still doing wrong?

[Bob Phillips]

Well, the first obvious thing is that you have declared all the variables as long. Long is just a long integer, so it cannot hold the decimal portion, declare these as Double.

[Bob Phillips]

Then you are trying to store the formatted value (which is a string) in a Long such as lngStandard_ADP. These should be String variable type.

[Bob Phillips]

And here



lngStandard_ACP = Format(lngCalc1_ACP, "+#.#0;-#.#0")
strPassFailStandard_ACP = IIf(lngStandard_ACP < 0, "Fails", "Passes")


You test the formatted result to see if pass or fail, test the originla number value



lngStandard_ACP = Format(lngCalc1_ACP, "+#.#0;-#.#0")
strPassFailStandard_ACP = IIf(lngCalc1 < 0, "Fails", "Passes")

[clhare]

Cool! This is awesome! Only 1 thing still not quite working right--where the result is less than 1.00, how do I format to get the zero before the decimal point? So instead of ".50" I would get "0.50"?

[Bob Phillips]

Use a format of

"+#0.00;-#0.00"

not

"+#.#0;-#.#0"

[clhare]

This is perfect!!! Thank you so much for all your help!
:cloud9: