压缩If然后压缩ElseIf

Question

在Excel VBA中压缩以下代码有什么好的技巧吗？我试图在图表中设置网格线(主要和次要(，但这取决于源数据的最大值。感谢您的投入！(来自新手(

If MaxY < 0.1 Then
MajGridY = 0.1 / 5
MinGridY = 0.1 / 10
ElseIf MaxY < 1 Then
MajGridY = 1 / 5
MinGridY = 1 / 10
ElseIf MaxY < 10 Then
MajGridY = 10 / 5
MinGridY = 10 / 10
ElseIf MaxY < 100 Then
MajGridY = 100 / 5
MinGridY = 100 / 10
ElseIf MaxY < 1000 Then
MajGridY = 1000 / 5
MinGridY = 1000 / 10
ElseIf MaxY < 10000 Then
MajGridY = 10000 / 5
MinGridY = 10000 / 10
ElseIf MaxY < 100000 Then
MajGridY = 100000 / 5
MinGridY = 100000 / 10
ElseIf MaxY < 1000000 Then
MajGridY = 1000000 / 5
MinGridY = 1000000 / 10
ElseIf MaxY < 10000000 Then
MajGridY = 10000000 / 5
MinGridY = 10000000 / 10
ElseIf MaxY < 100000000 Then
MajGridY = 100000000 / 5
MinGridY = 100000000 / 10
End If

Answer 1

Dim factor As Long
If MaxY < 1 Then
factor = Fix(Application.WorksheetFunction.Log10(MaxY))
Else
factor = Application.WorksheetFunction.RoundUp(Application.WorksheetFunction.Log10(MaxY), 0)
End If
If 10 ^ factor = MaxY Then factor = factor + 1

MajGridY = 10 ^ factor / 5
MinGridY = 10 ^ factor / 10