Rounding in VBA

[xbunsenx]

Hello,

I am working on a spreadsheet that I have a goal seek function to find the optimal number of units based on certain parameters. My problem is that I need to round these numbers up or down depending on which one it is. I found a solution that mostly works but sometimes it does not act like I need it to. Most of the time it will round up or down properly, but certain numbers will not. I need these numbers to be whole numbers after each goal seek so that the next goal seek can operate correctly. I have included the code below which operates without errors. Any suggestions are very welcome.

Function RDown(Amount As Double, digits As Integer) As Double
    RDown = Int((Amount + (1 / (10 ^ (digits + 1)))) * (10 ^ digits)) / (10 ^ digits)
End Function

Function RUp(Amount As Double, digits As Integer) As Double
    RUp = RDown(Amount + (10 / (10 ^ (digits + 1))), digits)
End Function

Private Sub CommandButton1_Click()
If Range("G2").Value = Range("V4") Then

 'Goal Seek System Width
 Range("C20").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C9")
  
 Dim wsnumber As Double
 wsnumber = RDown(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = wsnumber

 'Goal Seek System Vol.
 Range("C30").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C10")
     
 Dim vwsnumber As Double
 vwsnumber = RUp(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = vwsnumber
 
ElseIf Range("G2").Value = Range("V5") Then
 
 'Goal Seek System Length
 Range("C21").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C10")
  
 Dim lsnumber As Double
 lsnumber = RDown(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = lsnumber

 'Goal Seek System Vol.
 Range("C30").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C9")
     
 Dim vlsnumber As Double
 vlsnumber = RUp(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = vlsnumber
 
ElseIf Range("G2").Value = Range("V2") Then
 
 'Goal Seek System Width
 Range("C13").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C9")
  
 Dim wnumber As Double
 wnumber = RDown(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = wnumber

 'Goal Seek System Vol.
 Range("C28").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C10")
     
 Dim vwnumber As Double
 vwnumber = RUp(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = vwnumber
 
ElseIf Range("G2").Value = Range("V3") Then
 
 'Goal Seek System Length
 Range("C14").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C10")
  
 Dim lnumber As Double
 lnumber = RDown(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = lnumber

 'Goal Seek System Vol.
 Range("C28").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C9")
     
 Dim vlnumber As Double
 vlnumber = RUp(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = vlnumber
 
End If
 
End Sub

 

[joeu2004]

In general, Goal Seek and Solver do not work well when formulas round values or otherwise create "discontinuities" in the calculations.

When it seems to work, it is just an accident of implementation.

Generally, these algorithms make small changes to the "by changing" cells, and they expect that causes appropriate changes to the "target" cell.

When we round calculations, the target cell might not change "appropriately" because many values might round to the same discrete value.

 

[xbunsenx]

I understand that Goal Seek does not work well with the Round function in Excel. That is why I am performing the rounding in the VBA code after the goal seek has completed because I can't have a partial unit in the output. I need it to round up or down to the nearest whole number. It is working properly most of the time, but there are sometimes where the RUp and RDown VBA functions are not giving me perfect results. Sometimes the RDown function ends up rounding up due to the function's method. I was looking to see if there was a better, more reliable way to round up and down in VBA. BTW, I am not an expert in VBA code.

 

[SpillerBD]

?You don't need to call the value for the "V" cells for the IF statements to work as expected?
If the errors you see are mostly with negative values (if that's applicable to your calculations), maybe you should be using FIX instead of INT.

 

[joeu2004]

I understand that Goal Seek does not work well with the Round function in Excel. That is why I am performing the rounding in the VBA code after the goal seek has completed because I can't have a partial unit in the output. I need it to round up or down to the nearest whole number.

Sorry for the misdirection.

If you want to round "to the nearest whole number", use the (Excel) ROUND function. In VBA, write WorksheetFunction.Round(value,0).

If you want to explicitly round up or round down to a whole number, use the (Excel) ROUNDUP and ROUNDDOWN functions. In VBA, write WorksheetFunction.RoundUp(value,0) and WorksheetFunction.RoundDown(value,0).

Aside.... There is a VBA function called Round; write Round(value,0). However, VBA rounds based on "banker's rounding", which differs from Excel.

All of these rounding functions round away from zero (round up) or toward zero (round down). In other words, for example, they behave like RoundUp(sign(value)*abs(value),0).

 

[xbunsenx]

Boom!

OK, I was definitely doing it the hard way. Thank you joeu2004. I tried the worksheet function early on and it wasn't working. I just replaced the RDown and RUp lines with the worksheet function and the issues disappeared. I am still learning VBA syntax so it is frustrating at times, but couldn't do it without this site and all of the helpful people.

Private Sub CommandButton1_Click()
If Range("G2").Value = Range("V4") Then

 'Goal Seek System Width
 Range("C21").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C9")
  
 Dim wsnumber As Double
 'wsnumber = RDown(Cells(9, 3).Value, 0)
 wsnumber = WorksheetFunction.RoundDown(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = wsnumber
 
 'Goal Seek System Vol.
 Range("C31").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C10")
     
 Dim vwsnumber As Double
 'vwsnumber = RUp(Cells(10, 3).Value, 0)
 vwsnumber = WorksheetFunction.RoundUp(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = vwsnumber
 
ElseIf Range("G2").Value = Range("V5") Then
 
 'Goal Seek System Length
 Range("C22").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C10")
  
 Dim lsnumber As Double
 'lsnumber = RDown(Cells(10, 3).Value, 0)
 lsnumber = WorksheetFunction.RoundDown(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = lsnumber

 'Goal Seek System Vol.
 Range("C31").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C9")
     
 Dim vlsnumber As Double
 'vlsnumber = RUp(Cells(9, 3).Value, 0)
 vlsnumber = WorksheetFunction.RoundUp(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = vlsnumber
 
ElseIf Range("G2").Value = Range("V2") Then
 
 'Goal Seek System Width
 Range("C14").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C9")
  
 Dim wnumber As Double
 'wnumber = RDown(Cells(9, 3).Value, 0)
 wnumber = WorksheetFunction.RoundDown(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = wnumber

 'Goal Seek System Vol.
 Range("C29").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C10")
     
 Dim vwnumber As Double
 'vwnumber = RUp(Cells(10, 3).Value, 0)
 vwnumber = WorksheetFunction.RoundUp(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = vwnumber
 
ElseIf Range("G2").Value = Range("V3") Then
 
 'Goal Seek System Length
 Range("C15").GoalSeek Goal:=Range("G4"), _
     ChangingCell:=Range("C10")
  
 Dim lnumber As Double
 'lnumber = RDown(Cells(10, 3).Value, 0)
 lnumber = WorksheetFunction.RoundDown(Cells(10, 3).Value, 0)
 Cells(10, 3).Value = lnumber

 'Goal Seek System Vol.
 Range("C29").GoalSeek Goal:=Range("H4"), _
     ChangingCell:=Range("C9")
     
 Dim vlnumber As Double
 'vlnumber = RUp(Cells(9, 3).Value, 0)
 vlnumber = WorksheetFunction.RoundUp(Cells(9, 3).Value, 0)
 Cells(9, 3).Value = vlnumber
 
End If
 
End Sub

 

[joeu2004]

You're welcome. Another possible rounding issue that I've become sensitive to after answering a question in another forum....

If any of the Cells(...) might reference an Excel cell that is formatted as Currency, Accounting or probably any Custom format that includes a currency symbol, you might want to use the .Value2 property instead of the .Value property, at least as an input reference.

VBA treats Cells(...).Value with any of those formats as type Currency, if the cell value is numeric. That rounds to 4 decimal places. Consequently, for example, the Excel cell value $123.99996 will be input into VBA as $124.0000. So when you round down in VBA, you would get 124 instead of 123.

VBA treats Cells(...).Value2 as type Double, regardless of the cell format (unless the cell value is text). That preserves the full precision of the cell value when it is input into VBA.

 

[Rick Rothstein]

Aside.... There is a VBA function called Round; write Round(value,0). However, VBA rounds based on "banker's rounding", which differs from Excel.

Correct... all rounding done by VBA (your WorksheetFunction calls ask Excel to do the rounding, not VBA) is Banker's Rounding with one single exception... the Format function rounds normally (that is, it does not use Banker's Rounding). So, instead of this (to round to whole numbers)...

RoundedNum = WorksheetFunction.Round(SomeValue, 0)

you could do this...

RoundedNum = Format(SomeValue, "0")

 

[joeu2004]

your WorksheetFunction calls ask Excel to do the rounding, not VBA [....] the Format function rounds normally (that is, it does not use Banker's Rounding).

My understanding is that WorksheetFunction methods emulate (duplicate) Excel implementations, but they execute in the VBA thread, not the Excel thread.

This is difficult to demonstrate empirically. But on my computer, the execution time for WorksheetFunction.Round and Format(...,"0") are about the same.

In contrast, Evaluate(s), where s is "ROUND(...,0)", takes 10 times longer. On the other hand, the execution time for x=Cells(...).Value2 is comparable to WorksheetFunction.Round, albeit 50% longer. Both of those operations do indeed rely on the Excel thread to do the work.

In any case, I would be relunctant to rely on the Format(...,"0") behavior. Although it is not likely to ever change, in deference to backward compatibility, it is an exception to the VBA rules for rounding, as you point out. My guess is: it is an oversight, not conscious design.

In contrast, we can expect that WorksheetFunction methods mimic (or rely on) Excel behavior by design.

Just my two cents. To each his own....

 

[Rick Rothstein]

My understanding is that WorksheetFunction methods emulate (duplicate) Excel implementations, but they execute in the VBA thread, not the Excel thread.

I have no idea about that, but it would surprise me if they went through the trouble of creating new code to emulate function for which code already existed. I would have thought they would simply call the underlying Excel function's code directly (via OLE Automation maybe, or some other such interface).

In contrast, Evaluate(s), where s is "ROUND(...,0)", takes 10 times longer.

That does not surprise me. Where my above assumption would be a direct call to the function's code itself, the Evaluate function is a formula processor and, as such, must have a much higher overhead associated with it (in order to interpret what has to be done from the text argument presented to it) than a direct function call would.

In any case, I would be relunctant to rely on the Format(...,"0") behavior. Although it is not likely to ever change, in deference to backward compatibility, it is an exception to the VBA rules for rounding, as you point out. My guess is: it is an oversight, not conscious design.

I would be willing to bet that it was not an oversight. The Format function has been in Visual Basic (the compiled version which is the root code for VBA) since at least Version 3 that I am aware of (but more than likely even earlier than that) and it has always worked that way. Since VB3 was released in 1993 (some 24 years ago), I am more than confident that Microsoft will not be changing the Format function's underlying operating method.