You have a simple accounting calculation, sum of the invoices should be presented in another currency. The currency rate is presented with several decimal places. As the values in B2:B5 are with two decimals, also the result should be with two decimals.

The result is not in line with input values, as input values have only two decimals.

One option is to decrease the number of decimals in the interface. Select home ribbon, then press decrease decimals. That does not change the value of the =SUM(B2:B5)/D5. In the screen it looks like the value is with two decimals.

Still if you calculate the value in the cell D2, the Excel takes into account all the decimals even though the decimals are not visible in the interface.

Another option, for me a better option, is to add an embedded ROUND function.

Now the result of sentence in D2 is with two decimals.

You just need to add ROUND function in the D2 cell. That is an easy and fast way. But if you repeat this process several times a day, this process might feel complex and time consuming. If you work in accounting and results are shown with two decimals, you might need to do this very often.

One solution is to create a macro which would add embedded ROUND around the original sentence.

Sub z_roundwrap()

a = ActiveCell.Formula

ActiveCell.Formula = “=ROUND(” & Mid(a, 2) & “,2)”

End Sub

The advantage of this solution is that it consists of only two lines. First, the sentence in the active cell is stored in a variable. The second line is reformulating the formula. When we start the sentence with equal sign, we need to exclude the equal sign from variable a. Otherwise, we would have two equal signs which caused an error. We take with MID function the formula in the active cell, starting with 2^nd digit till the end. Then we add =ROUND( and ,2) around the original sentence without the equal sign.

The variable a is the formula of the current cell. We need to leave the equal sign out and take other digits.

Place the cursor in a cell, you want to add ROUND function and execute the macro.

The result:

The sentence looks similar to as if you had written it manually. When you use macro, you don’t have to adjust the cell manually but just execute the macro.

Note, that MID in Visual Basic is different than MID in Excel application. In Excel, MID requires three arguments: text, starting digit and number of digits. However, VBA MID requires only two arguments: text and starting digit. It is assumed that all the digits from starting digit till end of the string are captured.

Also note that I have semicolon as separator in Excel. Still, in VBA separator is always comma.

VBA: “=ROUND(” & Mid(a, 2) & “,2)”

Excel application:  =ROUND(SUM(B2:B5)/D5;2)

Even though VBA is an old language, sometimes a very short macro might save time significantly, when you repeat a process constantly. If you need to always present results with two decimals, you need to add often ROUND function to existing sentences.