用于主轴标记的Excel宏 [英] Excel Macro for Primary Axis Labeling

问题描述

我正在尝试编写一个宏以格式化图表以实现一致性.我坚持认为最简单的部分.标记轴.它不会标记主要价值"轴(y轴).我已经在2010年和2013年多次录制了此宏.这是怎么回事?我运行宏，并在第3行进行调试，几乎就像标签不存在一样.仅运行前2行就证明了这一点.有帮助吗?

I'm trying to write a macro to format charts for uniformity. I'm stuck on what I thought would be the easiest part. Labeling the axes. It won't label the Primary Value axis (y-axis). I've recorded this macro many times on 2010 and 2013. What is wrong? I run the macro and it debugs on line 3 almost as if the label didn't exist. Running just the first 2 lines proved this. Any help?

    ActiveChart.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)
    Selection.Format.TextFrame2.TextRange.Characters.Text = "Primary Y-Axis"
      With Selection.Format.TextFrame2.TextRange.Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
      End With
    With Selection.Format.TextFrame2.TextRange.Characters(1, 14).Font
      .BaselineOffset = 0
      .Bold = msoTrue
      .NameComplexScript = "+mn-cs"
      .NameFarEast = "+mn-ea"
      .Fill.Visible = msoTrue
      .Fill.ForeColor.RGB = RGB(0, 0, 0)
      .Fill.Transparency = 0
      .Fill.Solid
      .Size = 10
      .Italic = msoFalse
      .Kerning = 12
      .Name = "+mn-lt"
      .UnderlineStyle = msoNoUnderline
      .Strike = msoNoStrike
    End With

推荐答案

运行时错误的一个明显来源是依赖于记录的宏.他们通常需要一些调整.在您的情况下，我认为问题可能在于轴的 .HasTitle 属性仍设置为 False ，当您尝试访问 .AxisTitle 属性.

An obvious source of runtime errors is relying on the recorded macros. They usually require some tweaking. In your case, I believe the problem may be that the axis' .HasTitle property is still set to False, which would raise that error when you try to access the .AxisTitle properties.

注意::您可以阅读

NOTE: You can read this to learn about why the Select and Activate methods are problematic.

在此代码中，我定义了一些变量以表示图表和轴，并确保 .HasTitle 属性为true.没有遇到错误.:)

In this code, I define some variables to represent the chart and the axis, and make sure that the .HasTitle property is true. No errors encountered. :)

Sub foo()
Dim cht As Chart
Dim ax As Axis

Set cht = Sheet1.ChartObjects(1).Chart '# modify as needed

cht.SetElement (msoElementPrimaryValueAxisTitleAdjacentToAxis)

'# set your axis in a variable
Set ax = cht.Axes(xlValue, xlPrimary)
'# Make sure your axis HAS a title
ax.HasTitle = True
With ax.AxisTitle.Format.TextFrame2.TextRange
    .Characters.Text = "Primary Y-Axis"
    With .Characters(1, 14).ParagraphFormat
        .TextDirection = msoTextDirectionLeftToRight
        .Alignment = msoAlignCenter
    End With
End With

End Sub

这篇关于用于主轴标记的Excel宏的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！