VBA - 不正确的图形代码 [英] VBA - Incorrect graphing code

问题描述

首先，感谢过去的所有帮助，这个网站是一个祝福，你们都是圣人。现在只能做生意了

我有一组测试数据出现问题，其他图形正确，但是，这组数据导致我的图形看起来不对。我想我发现了这个问题，但我不确定如何解决它。当我的代码创建要绘制的范围时，由于数学错误，它不会抓取正确的范围。作为参考， DataLength = 102 ，这就是图形和数据的一部分。我需要绘制CZ但它只会到D，因为 fRemainder = 0 这里是缺少怪人代码。

  Dim iAlpha As Integer，fAlpha As Integer 
 Dim iRemainder As Integer，fRemainder As Integer 
 Dim ConvertToLetter As String 
 Dim fConvertToLetter As String 
 
 iAlpha = Int（（DataLength）/ 26）'26为字母
 fAlpha = Int（（DataLength + 2）/ 26）'对于平均和sd函数，因为它们从C开始不是A 
 iRemainder = DataLength  - （iAlpha * 26）
 fRemainder = DataLength + 2  - （fAlpha * 26）
如果iAlpha> 0然后
 ConvertToLetter = Chr（iAlpha + 64）
 End If 
如果iRemainder> 0然后
 ConvertToLetter = ConvertToLetter& Chr（iRemainder + 64）
 End If 
 If fAlpha> 0然后
 fConvertToLetter = Chr（fAlpha + 64）
 End If 
如果fRemainder> 0然后
 fConvertToLetter = fConvertToLetter& Chr（fRemainder + 64）
结束如果
  

另外参考，这是实际图形代码是。第一行偏离C13，但单元格随每个分组而改变，即。 C13C49C85等。

  ActiveCell。 Offset（-7，-2）。选择
 ActiveCell.Range（A1：& fConvertToLetter&4）。选择
范围（Selection，Selection.End（xlToRight））。 
 Range（Selection，Selection.End（xlToRight））。选择
 ActiveCell.Activate 
 Range（Selection，Selection.End（xlToRight））。选择
 ActiveSheet.Shapes.AddChart 。选择
使用ActiveChart 
 .ChartType = xlLine 
 .Axes（xlCategory）.Select 
 .HasTitle = True 
 .ChartTitle.Text = Range（B& amp ;（12 * x  -  5））价值
以
结束With ActiveChart.Parent 
 .Top = 153 * x + 12.75 * 2'位置单元格高12.75，并且有36个单元格
 .Left = 50'重新定位
结束
  

解决方案

定义用于图表的范围有多种不同的方法。在下面的例子中，我展示了两种方法，一种是制作初始图表，另一种是基于向图表添加新系列。最好删除宏记录器使用的语句，如选择和 .select ，而是定义范围变量。这些范围变量可以根据需要使用 .offset 函数进行微调。按照我在动画.gif中显示的代码逐步完成代码，以帮助理解它的工作原理，然后根据需要为应用程序进行调整。

//i.stack.imgur.com/hVJrN.gifrel =nofollow noreferrer>

  Option Explicit 
 Sub chartRange（）
 Dim r As Range，chObj As ChartObject，ser As Series 
 Set r = ActiveSheet.Range（A1：B5）
 Set chObj = ActiveSheet.ChartObjects.Add（Left：= 100，Width：= 375，Top：= 75，高度：= 225）
使用chObj 
 .chart.ChartType = xlXYScatterLines 
 .chart.SetSourceData源：= r 
 End With 
如果MsgBox（Plot y2 also ？，vbYesNo）Then 
 Set ser = chObj.chart.SeriesCollection.NewSeries 
 Set r = ActiveSheet.Range（A1：A5）
 ser.XValues = r 
 ser.Values = r.Offset（0，2）
 ser.Name =y2
 End If 
 End Sub 
  

First of all, thank you for all the help in the past, this site is a blessing, and you all are saints. Now down to business

I am having trouble with one set of my test data, the other graphs correctly, however, this set of data is causing my graphs to look wrong. I think I found the problem, but I am unsure on how to fix it. When my code creates the range to graph, it is not grabbing the right range due to a mathematical error. For reference, DataLength = 102, and this is what the graph and a portion of the data looks like. I need to graph to "CZ" but it is only going to D because fRemainder = 0 And here is the out of wack code.

    Dim iAlpha As Integer, fAlpha As Integer
    Dim iRemainder As Integer, fRemainder As Integer
    Dim ConvertToLetter As String
    Dim fConvertToLetter As String

    iAlpha = Int((DataLength) / 26) '26 for the letters
    fAlpha = Int((DataLength + 2) / 26) 'for the average and sd functions, since they start at C not A
    iRemainder = DataLength - (iAlpha * 26)
    fRemainder = DataLength + 2 - (fAlpha * 26)
    If iAlpha > 0 Then
       ConvertToLetter = Chr(iAlpha + 64)
    End If
    If iRemainder > 0 Then
       ConvertToLetter = ConvertToLetter & Chr(iRemainder + 64)
    End If
    If fAlpha > 0 Then
       fConvertToLetter = Chr(fAlpha + 64)
    End If
    If fRemainder > 0 Then
       fConvertToLetter = fConvertToLetter & Chr(fRemainder + 64)
    End If

And for additional reference, this is the actual graphing code is. The first line offsets from "C13", however the cell changes with each grouping, ie. "C13" "C49" "C85" etc.

ActiveCell.Offset(-7, -2).Select
ActiveCell.Range("A1:" & fConvertToLetter & "4").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlToRight)).Select
ActiveCell.Activate
Range(Selection, Selection.End(xlToRight)).Select
ActiveSheet.Shapes.AddChart.Select
With ActiveChart
    .ChartType = xlLine
    .Axes(xlCategory).Select
    .HasTitle = True
    .ChartTitle.Text = Range("B" & (12 * x - 5)).Value
End With
With ActiveChart.Parent
     .Top = 153 * x + 12.75 * 2 ' reposition cells are 12.75 high and there are 36 cells between
     .Left = 50   ' reposition
End With

解决方案

There are different ways to define the ranges used for charts. In the example below, I show 2 ways, one to make the initial chart, and then another based upon adding a new series to the chart. It is best to remove statements like selection and .select that the macro recorder uses, and instead define range variables. These range variables can be fine-tuned using the .offset function as needed. Step through the code as I show in the animated .gif to help understand how it works, and then adjust as needed for your application.

Option Explicit
Sub chartRange()
Dim r As Range, chObj As ChartObject, ser As Series
Set r = ActiveSheet.Range("A1:B5")
Set chObj = ActiveSheet.ChartObjects.Add(Left:=100, Width:=375, Top:=75, Height:=225)
    With chObj
    .chart.ChartType = xlXYScatterLines
    .chart.SetSourceData Source:=r
    End With
    If MsgBox("Plot y2 also?", vbYesNo) Then
      Set ser = chObj.chart.SeriesCollection.NewSeries
      Set r = ActiveSheet.Range("A1:A5")
      ser.XValues = r
      ser.Values = r.Offset(0, 2)
      ser.Name = "y2"
    End If
End Sub

这篇关于VBA - 不正确的图形代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！