Confused by CDbl behaviour

R

Risky Dave

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave
 
E

Eduardo

Try
If IsNumeric(Me.FmRiskCost.TextBox9.Value) _
And IsNumeric(Me.FmRiskCost.TextBox10.Value) _
And IsNumeric(Me.FmRiskCost.TextBox11.Value) _
And IsNumeric(Me.FmRiskCost.TextBox12.Value) _
And IsNumeric(Me.FmRiskCost.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
Me.FmRiskCost.TextBox23.Value = Me.dMitCost.Value
End
Click to expand...
 
R

Risky Dave

Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave
 
E

Eduardo

Hi Dave,
what version of excel are you using

Risky Dave said:
Eduardo,

Thanks for the quick response.

I'm not quite sure what you mean with this (I'm not a programmer - just
learnign this stuff as I go along).

If I insert another IF statement around the existing code as per your
posting, XL throws back an error about invalid use of "Me." If I remove the
Me, then the code simply skips past the line intended to capture the input,
ie. the IF statement is false so it is just ignored so I don't get the output
I need.

Obviously, I'm missing something here.

TIA

Dave
Click to expand...
 
E

Eduardo

Hi Dave,
I am not an expert as well but some nice people has helped me a lot and the
code is working beautifully to me, there is only one difference and it is I
don't include the form name and I missed some Me in the totals, sorry, try
this
Try
If IsNumeric(Me.TextBox9.Value) _
And IsNumeric(Me.TextBox10.Value) _
And IsNumeric(Me.TextBox11.Value) _
And IsNumeric(Me.TextBox12.Value) _
And IsNumeric(Me.TextBox13.Value) Then

Me.dMitCost.value = (CDbl(Me.TextBox9.Value) +
 
R

Risky Dave

Eduardo,

Still haven't been able to get it to work so I've just written it as a set
of If statements - really ugly (and inefficient I suspect) but it works
 
R

Ron Rosenfeld

Hi,

Can someone please tell me why this does not work (it throws up a type
mismatch):

Sub Cost_Update()

Dim dMitCost As Double

dMitCost = (CDbl(FmRiskCost.TextBox9.Value) +
CDbl_(FmRiskCost.TextBox10.Value) + CDbl(FmRiskCost.TextBox11.Value) +
CDbl_(FmRiskCost.TextBox12.Value) + CDbl(FmRiskCost.TextBox13.Value))_
FmRiskCost.TextBox23.Value = dMitCost
End

I am trying to capture (up to) five values entered into textboxes on a form,
add them and then send the total value to another text box on the same form.
I can do this by replacing the CDbl with a Val, but the calculation then goes
haywire when lage numbers are inputted.

Is this a variation on the old thing of XL assuming that anything joined by
a '+' sign is a string not a number? If so, how do I get around it?

Hope this makes some sense!

TIA

Dave
Click to expand...

I would assume that one of the values is text that cannot be coerced to
represent a numeric value.
--ron
 
Top