Easy One - do math with Textbox values

[Marty]

Hello:

I'm trying to do some simple math using the values in some textboxes, and
I'm getting an error on this statement:

Controls("TextBox" & (Y + 2)).Value = Round((Controls("TextBox" & _
(Y + 1)).Value / 100 * WORSTKG), 0)

The right side of the equation takes a value from a textbox, divides it by
100, multiples it by another variable (WORSTKG - which is a numeric value
from another textbox) and then rounds it off to a whole number and puts the
answer into a third textbox. It's part of a loop which is why the textbox
name contains a variable suffix.

At least that's the plan, but I'm getting a "type mismatch" error.

LATE NOTE: should it make a difference if I set the Controls("TextBox" & (Y
+ 1)).Value expression equal to a variable before doing the math (the way I
did WORSTKG?)? If so, why would it?

Help would be appreciated.

Thanks,
MARTY

 

[Jim Cone]

Marty,
There is no "Round" function in VBA.
However, you can use the Excel worksheet function this way...

Application.Round(... or Application.WorksheetFunction.Round(...
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)


in message
Hello:
I'm trying to do some simple math using the values in some textboxes, and
I'm getting an error on this statement:

Controls("TextBox" & (Y + 2)).Value = Round((Controls("TextBox" & _
(Y + 1)).Value / 100 * WORSTKG), 0)

The right side of the equation takes a value from a textbox, divides it by
100, multiples it by another variable (WORSTKG - which is a numeric value
from another textbox) and then rounds it off to a whole number and puts the
answer into a third textbox. It's part of a loop which is why the textbox
name contains a variable suffix.

At least that's the plan, but I'm getting a "type mismatch" error.

LATE NOTE: should it make a difference if I set the Controls("TextBox" & (Y
+ 1)).Value expression equal to a variable before doing the math (the way I
did WORSTKG?)? If so, why would it?

Help would be appreciated.

Thanks,
MARTY

 

[carlo]

I would always check, if the value of the textbox is numeric or not:

if isnumeric(controls("TextBox" & Y+1).value) then
'your code here
else
Msgbox "TextBox" & Y+1 & " is not numeric
end
end if

that way you can figur out, if it's a problem with the data you
entered.

hope that helps you a little

Carlo

 

[carlo]

Of course there is:

from help:
'-------------------------------------------------
Round Function

Description

Returns a number rounded to a specified number of decimal places.

Syntax

Round(expression [,numdecimalplaces])

The Round function syntax has these parts:

Part Description
expression Required. Numeric expression being rounded.
numdecimalplaces Optional. Number indicating how many places to the
right of the decimal are included in the rounding. If omitted,
integers are returned by the Round function.
'-------------------------------------------------


Carlo

 

[Rick Rothstein \(MVP - VB\)]

There is no "Round" function in VBA.

Sure there is... it works slightly different the the worksheet's function
version, but it does exist. Besides, the OP is getting a Type mismatch
error, not a syntax type error.

Rick

 

[Jim Cone]

Correction...
There is no Round function in XL97. Later versions do have it.
Jim Cone

 

[Dave Peterson]

Just to add to Jim's response.

VBA's Round and excel's =round() behave differently (xl2k and above).

But if the OP wants excel's behavior (or is running xl97), then he could use:

application.round()
in code, like:
msgbox application.round(4.6, 0)