The concept of looping through Controls in a user form is a savior. The problem is how to ensure that every text box is filled with valid data.

User has two enter non-numeric data in each and every box before he/she hits “Execute” command button. To simplify this, let’s say we have number of Text Box. By controls, I mean TextBox, Radio Buttons, Selection Box etc. Suppose we have a user-form and it has quite good number of controls. To read and know more about user form follow these link1 and link2. StandardRound = Round(pValue + QValue, pDecimalPlaces)Īnd then call the StandardRound function instead of using the Round function.This is quite interesting and goes well with user-form. ' Add a 1 to the end of the value (For example, if pValue is 12.65 ' (Remove the following 3 lines if you require 'Round Up' rounding) ' Symmetric rounding is commonly desired so if the value is If pDecimalPlaces 0) And (LNumDecimals > 0) And (LNumDecimals > pDecimalPlaces) Then ' Return an error if the decimal places provided is negative

Public Function StandardRound(pValue As Double, pDecimalPlaces As Integer) As Variant

' built-in VBA Round function to provide true (symmetric) numeric Rounding If you want to avoid bankers rounding, you can create your own custom function as follows: ' This function overcomes the bankers Rounding that occurs in the In this example, the variable called LNumber would now contain the value of 210.7. Result: 210.66 'example of bankers rounding Here are some examples of what the ROUND function would return: The ROUND function can be used in VBA code in Microsoft Excel. So please be careful before using this function. As a reminder, the ROUND function uses something commonly referred to as bankers rounding. In Excel's VBA environment, the ROUND function returns a number rounded to a specified number of decimal places. So, be sure to only use the ROUND function if this is your desired result. In these cases, the last digit after rounding is always an even number.

If the expression that you are rounding ends with a 5, the ROUND function will round the expression so that the last digit is an even number. The ROUND function utilizes round-to-even logic. So before using this function, please read the following: It is very important to note that the VBA ROUND function behaves a little peculiar and uses something commonly referred to as bankers rounding. Please read our ROUND function (WS) page if you are looking for the worksheet version of the ROUND function as it has a very different syntax. As a VBA function, you can use this function in macro code that is entered through the Microsoft Visual Basic Editor. It can be used as a VBA function (VBA) in Excel. The ROUND function is a built-in function in Excel that is categorized as a Math/Trig Function. The Microsoft Excel ROUND function returns a number rounded to a specified number of digits.

Excel solver function in vba 2013