Userform Calculation

[Paul Mullan]

Hi -

I have a series of text boxes containing numbers that I want to
performa a simple percent difference calculation on in a user form.
The code I am using is:

txtBicTest = ((Val(txtBic2) - Val(txtBic1)) / Val(txtBic1)) * 100
txtSupTest = ((Val(txtSup2) - Val(txtSup1)) / Val(txtSup1)) * 100
txtIllTest = ((Val(txtIll2) - Val(txtIll1)) / Val(txtIll1)) * 100
txtAbdTest = ((Val(txtAbd2) - Val(txtAbd1)) / Val(txtAbd1)) * 100
txtThiTest = ((Val(txtThi2) - Val(txtThi1)) / Val(txtThi1)) * 100
txtCalTest = ((Val(txtCal2) - Val(txtCal1)) / Val(txtCal1)) * 100

In some instances not all text boxes will be complete, but this is
where I am coming up against problems. It sems that I hit a "runtime
error '6' - overflow" error for any calculation that involves a cell
that is blank. I have tried having the textboxes carry a default zero
value but the same problem occurs.

Effectively I am looking fir a way for the userform to ignore blank
cells,

ANy pointers?

Thanks

 

[GS]

Try something like...

If Len(txtBic1) Then txtBicTest = _
Format(((Val(txtBic2) - Val(txtBic1)) / Val(txtBic1)), "0.00%")

...where the divisor is tested before doing the calc. You could also use
the Else clause to specify a message for fields that don't calc.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[GS]

Try something like...

If Len(txtBic1) Then txtBicTest = _
Format(((Val(txtBic2) - Val(txtBic1)) / Val(txtBic1)), "0.00%")

..where the divisor is tested before doing the calc. You could also
use the Else clause to specify a message for fields that don't calc.

Note also that you might want to check that the divisor is not zero!
:-Z

If Len(txtBic1) And Val(txtBic1) <> 0 Then...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

 

[Paul Mullan]

Fantastic! Thanks so much. The second example worked a treat! Please
bear with me I am new to this so I have a question to try and help my
understanding for the future

I wasn't aware of the 'Len' function before. From what I read this
validates the length ?? however I haven't stated a desired length for
it to validate against. What is this actually validating in the
example

If Len(txtBic1) Then txtBicTest =

Thanks again

 

[GS]

Fantastic! Thanks so much. The second example worked a treat!

Please bear with me I am new to this so I have a question to try and
help my understanding for the future

I wasn't aware of the 'Len' function before. From what I read this
validates the length ?? however I haven't stated a desired length for
it to validate against. What is this actually validating in the
example

If Len(txtBic1) Then txtBicTest =

Thanks again

If it has length then it's not empty! If it's empty it has no length!
The latter returns zero which VB[A] interprets as 'False', while
anything other than zero is returned as 'True'!

So what the does is validate that the divisor is not zero since the
code following will only execute if it has length.

HTH

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion