将一系列趋势线方程式获取到形状文本框 [英] Getting a series trend line equation to a shape text box

问题描述

我正在尝试从图表中的第一个系列中获得趋势线方程式，然后将其放置在工作表中其他位置的形状文本框中-但是，当我逐步执行代码时，只能使文本框正确填充逐行-在运行时无效:

I'm attempting to get the trend line equation from the first series in my chart to a shape text box placed elsewhere on the worksheet - however, I can only get the textbox to populate correctly when I'm stepping through the code line by line - during run-time it has no effect:

For Each chtObj In ActiveSheet.ChartObjects

    Set cht = chtObj.Chart

    For Each srs In chtObj.Chart.SeriesCollection
        srs.Trendlines(1).DisplayEquation = True 'Display the labels to get the value
        ThisWorkbook.Worksheets("MyDataSheet").Shapes(slopetextboxes(k)).TextFrame.Characters.Text = srs.Trendlines(1).DataLabel.Text
        srs.Trendlines(1).DisplayEquation = False 'Turn it back off
        Exit For
    Next srs

    k = k + 1 ' for the slope textboxes

Next chtObj

请注意，slopetextboxes是一个包含〜6个形状文本框名称的数组.

Note that slopetextboxes is an array containing the names of ~6 shape text boxes.

据我所知，没有停止显示趋势线数据标签的方法.我试过先将它存储在字符串中，DoEvents，然后再打开Application.ScreenUpdating，全部无济于事.我被困在这里.

As far as I know there's no way to get the trend line data label without stopping to display it. I've tried storing it in a string first, DoEvents, and turning Application.ScreenUpdating back on, all to no avail. I'm stumped here.

看来，通过将DoEvents放在.DisplayEquation = True之后，我可以正确填充一些形状，但不是全部.似乎仍然是某种运行时问题.

It appears that by placing DoEvents after .DisplayEquation = True I'm able to have some of my shapes populate correctly, but not all. Still appears to be some kind of run-time issue.

赏金:我已经向前移动了一个公式，但该公式本身已进入数据本身，但我仍然不理解为什么我无法获取图表的.DataLabel.Text在运行时.我可以在逐步执行时(而不是在运行时)抓住它.它似乎只是采用PREVIOUS系列坡度并将其放置为形状(或像元一样，甚至在目标位置也不重要). DoEvents放在不同的位置会产生不同的结果，因此必须进行某些操作.

BOUNTY I've moved ahead to grab the slopes with a formula ran into the data itself, but I still don't understand why I can't grab the chart's .DataLabel.Text during run-time. I can grab it when stepping through, not during run-time. It appears to just take the PREVIOUS series slope and place it in the shape (or a cell, it doesn't even matter where the destination is). DoEvents placed in different spots yields different outcomes, so something must be going on.

推荐答案

我已将其记录为一个错误-BrakNicku发现了唯一的解决方法，即在读取Text之前先Select DataLabel属性:

I've written this off as a bug - The only workaround was discovered by BrakNicku which is to Select the DataLabel before reading its Text property:

srs.Trendlines(1).DataLabel.Select

不是一个足够的解决方案(因为这可能会在运行时引起一些问题)，但是唯一有效的方法.

Not a sufficient solution (since this can cause some issues during run-time), but the only thing that works.

这篇关于将一系列趋势线方程式获取到形状文本框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！