Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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) "Marty" wrote 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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 On Dec 20, 11:43 am, Marty wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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 On Dec 20, 11:52 am, "Jim Cone" wrote: 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, USAhttp://www.realezsites.com/bus/primitivesoftware (Excel Add-ins / Excel Programming) "Marty" wrote 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
Correction... There is no Round function in XL97. Later versions do have it. Jim Cone |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Easy One - do math with Textbox values
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) Jim Cone wrote: Correction... There is no Round function in XL97. Later versions do have it. Jim Cone -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Easy One - Syntax for variables in TextBox & Label names | Excel Programming | |||
How do I add TextBox.values? | Excel Discussion (Misc queries) | |||
How do I add TextBox.values? | Excel Worksheet Functions | |||
Clearing Form Values the easy Way??? | Excel Programming | |||
easy question : textbox | Excel Programming |