Excel VBA 代码将 x 轴设置为绘制范围内的最小值和最大值

Excel VBA code to set x-axis to minimum and maximum value in plotted range

提问by Kate

I have a VBA code (below) that sets the min and max x-axis values to a specified cell (B4 and B15). However, I have many plots in my workbook, and all need a different min and max x-axis range. I want a VBA code that goes to the plotted x-axis range and then finds the min and max value in that range and sets the axis to those values. How can I alter the code below to do that?

我有一个 VBA 代码（如下），它将最小和最大 x 轴值设置为指定的单元格（B4 和 B15）。但是，我的工作簿中有很多图，并且都需要不同的最小和最大 x 轴范围。我想要一个进入绘制的 x 轴范围的 VBA 代码，然后找到该范围内的最小值和最大值，并将轴设置为这些值。我怎样才能改变下面的代码来做到这一点？

Sub Resize_Fonts()
Dim Sht As Worksheet
Dim Cht As ChartObject

For Each Sht In ActiveWorkbook.Sheets
    For Each Cht In Sht.ChartObjects
        Cht.Chart.ChartArea.Font.Size = 9
        Cht.Chart.ChartArea.Font.Name = "Cambria"
        Cht.Chart.ChartArea.Border.LineStyle = xlNone
        Cht.Chart.Axes(xlValue).MinimumScale = 0
        Cht.Chart.Axes(xlCategory).MinimumScale = Range("B4").Value
        Cht.Chart.Axes(xlCategory).MaximumScale = Range("B15").Value


    Next Cht
Next Sht

End Sub

回答by JiheL

You could add a dim count as longand put your min / max values in adjacent columns. Then increment your countin your For eachloop and use Cells([row], [n+] count).valueto get the cell value. By the way, You can use:

您可以添加 adim count as long并将您的最小值/最大值放在相邻的列中。然后count在For each循环中增加您Cells([row], [n+] count).value的值并用于获取单元格值。顺便说一句，您可以使用：

With cht.Chart 
    [...]
End with

回答by Cyril

Try using the WorksheetFunction.Min for this.

尝试为此使用 WorksheetFunction.Min。

    Cht.Chart.Axes(xlCategory).MinimumScale = WorksheetFunction.Min(Columns(2))
    Cht.Chart.Axes(xlCategory).MaximumScale = WorksheetFunction.Max(Columns(2))

This assumes the x-axis values are in column 2.

这假设 x 轴值在第 2 列中。

回答by CLR

If the min and max are always within the range B14:B15on Shtyou can use:

如果最小值和最大值总是范围B14:B15上Sht可以使用：

Cht.Chart.Axes(xlCategory).MinimumScale = worksheetfunction.Min(Sht.Range("B4:B15"))
Cht.Chart.Axes(xlCategory).MaximumScale = worksheetfunction.Max(Sht.Range("B4:B15"))

回答by Shai Rado

Try to use the With Cht.Chartstatement, it will shorten and clear your coding style.

尝试使用With Cht.Chart语句，它会缩短和清除您的编码风格。

When looking for the Minand Maxvalues in Column B, you need to make sure you fully qualify the Range, by adding Sht.Range.

在 B 列中查找Min和Max值时，您需要Range通过添加Sht.Range.

Code

代码

Sub Resize_Fonts()

Dim Sht As Worksheet
Dim Cht As ChartObject

For Each Sht In ActiveWorkbook.Sheets
    For Each Cht In Sht.ChartObjects
        With Cht.Chart
            .ChartArea.Font.SIZE = 9
            .ChartArea.Font.Name = "Cambria"
            .cartArea.Border.LineStyle = xlNone
            .Axes(xlValue).MinimumScale = 0
            .Axes(xlCategory).MinimumScale = WorksheetFunction.Min(Sht.Range("B1:B" & Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row))
            .Axes(xlCategory).MaximumScale = WorksheetFunction.Max(Sht.Range("B1:B" & Sht.Cells(Sht.Rows.Count, "B").End(xlUp).Row))
        End With
    Next Cht
Next Sht

End Sub

回答by JiheL

After reading your comments, as I first said with my other unregistered account, you can use this method:

看完你的评论后，正如我第一次用我的另一个未注册帐户所说的，你可以使用这个方法：

Sub Resize_Fonts()
Dim Sht As Worksheet
Dim Cht As ChartObject
Dim count as Long 

count = 2 ' For column B
For Each Sht In ActiveWorkbook.Sheets
    For Each Cht In Sht.ChartObjects
        With Cht.Chart
            .ChartArea.Font.Size = 9
            .ChartArea.Font.Name = "Cambria"
            .ChartArea.Border.LineStyle = xlNone
            .Axes(xlValue).MinimumScale = 0
            .Axes(xlCategory).MinimumScale = Sht.Cells(4, count).Value
            .Axes(xlCategory).MaximumScale = Sht.Cells(15, count).Value
        End with
        count = count + n ' with n your "pattern"
    Next Cht
    count = 2 ' reset the count when changing sheet
Next Sht

End Sub

This assume values are always row 4 and row 15.

此假设值始终为第 4 行和第 15 行。

Comment if you have any other queries

如果您有任何其他疑问，请发表评论