CODEWITHSUNDEEP
excelcountdigitsvba
jneary19
jneary19

Reputation: 21

VBA rounding values

I am currently working on an excel spreadsheet that has hundreds of different graphs. My macro chooses the y axis values based on the minimum and maximum values of the data series. I am trying to round the min and max to numbers that make sense. For example if the minimum is 926.43 round to 900 and a max of 1223.21 round to 1300. It needs to be able to work for ranges of data where the min and max are single digits or they are in the millions. This is my code so far and it works in excel but Length always ends up being 6 whenever I run it in VBA. Any suggestions?

    Set srs = ActiveChart.SeriesCollection(1)
    Min = WorksheetFunction.Min(srs.Values)
    Max = WorksheetFunction.Max(srs.Values)

    Digits = Int(Max)
    Length = Len(Digits) - 2
    MinRound = WorksheetFunction.RoundDown(Min, -Length)
    MaxRound = WorksheetFunction.RoundUp(Max, -Length)

Upvotes: 2

Views: 1204

Answers (2)

ShamBhagwat
ShamBhagwat

Reputation: 99

I am using two custom functions as substitute to worksheetfunction.RoundUp and worksheetfunction.RoundDown. Place these two functions in a module.

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 + (5 / (10 ^ (digits + 1))), digits)
End Function

RDown(262704615421,-9) = 262000000000 i.e. minimum Rup(262704615421,-9) = 263000000000 i.e. maximum

Upvotes: 0

MatthewD
MatthewD

Reputation: 6761

MRound is what you are looking for.

Dim i as integer
i = 1076
i = Application.worksheetFunction.MRound(i, 100)

That will produce i = 1100

You can use it to go to the nearest 10, 100, 1000, whatever.

Upvotes: 1

Related Questions