ChartMaxY + ChartMinY

Calculates the maximum of the chart Y Axis to draw, the shifted one, not exact number, shift is calculated based on the differences between Maxi and Mini
Needed to have multiple charts to all show same max and min y axis range to be consistent among all.

Function ChartMaxY(Maxi, Mini)
 ' Calculates the maximum of the chart Y Axis to draw
 ' To add some space above or below the Max or Min
 Diff = Round(Abs(Maxi - Mini), 1) / 50
 Roo = Abs(Maxi - Mini) / 10
 If Roo > 1 Then
  Roo = Int(Roo) ' Round to integer only if it is greator than 1
 Else
  Roo = Round(Roo, 2) ' for less than 1, do not round to int
 End If
 If Roo = 0 Then Roo = 1
 If Val(Application.Version) >= 14 And Val(SettingRead("Ceiling_Floor_Flag")) = 2 Then ' Excel2010 or later
  ChartMaxY = WorksheetFunction.Ceiling_Precise((Maxi + (Diff / 50)), Roo)
 Else
  If Sgn(Roo) = Sgn(Maxi) Then
   ChartMaxY = WorksheetFunction.Ceiling((Maxi + (Diff / 50)), Roo)
  Else
   ChartMaxY = Sgn(Maxi) * WorksheetFunction.Floor((Abs(Maxi) + (Diff / 50)), Abs(Roo))
  End If
 End If
End Function
Function ChartMinY(Maxi, Mini)
 ' Calculates the minimum of the chart Y Axis to draw
 ' To add some space above or below the Max or Min
 Diff = Round(Abs(Maxi - Mini), 1)
 Roo = Abs(Maxi - Mini) / 10
 If Roo > 1 Then
  Roo = Int(Roo) ' Round to integer only if it is greator than 1
 Else
  Roo = Round(Roo, 2) ' for less than 1, do not round to int
 End If
 If Roo = 0 Then Roo = 1
 If Val(Application.Version) >= 14 And Val(SettingRead("Ceiling_Floor_Flag")) = 2 Then ' Excel2010 or later
  ChartMinY = WorksheetFunction.Floor_Precise((Mini - (Diff / 25)), Roo)
 Else
  If Sgn(Roo) = Sgn(Mini) Then
   ChartMinY = WorksheetFunction.Floor((Mini - (Diff / 25)), Roo)
  Else
   ChartMinY = Sgn(Mini) * WorksheetFunction.Ceiling((Abs(Mini) - (Diff / 25)), Abs(Roo))
  End If
 End If
End Function

Maxi, Mini in both

Views 404 Downloads 134

'Chart', 'axis', 'axes', 'y-axes', 'x-axis', 'draw', 'min', 'minimum', 'max', 'maximum', 'calculate', 'floor', 'ceiling', 'rounddown', 'roundup', 'round'

ANmarAmdeen
324
Print+Reports VBA-Excel
Revisions

v2.0