单元格区域中的VBA图表空白值 [英] VBA Chart Blank Values in Cell Range

问题描述

购物车应如何显示信息，由于我在单元格B16中添加了零值，因此正确显示了购物车

How the cart should be displayed with information, it is being displayed correctly because I added a zero value in cell B16

错误图表，由于抵押贷款1的单元格中没有零值，因此无法正确显示

The error chart, not displaying properly because mortgage1 doesn't have a zero value in its cell

宏的运行方式以及来自用户表单的信息

How the macro is being run and information from user form

我的信息来自用户表单

我有一个图表，该图表是使用宏为一系列单元格生成的.有时在该范围内会存在一些空白单元格.我在生成带有空单元格值的图表时遇到问题，该图表将在第一个非空单元格处开始绘制，从而导致类别轴无法报告正确.如何使用VBA使图表在图表上显示空白值.我不希望在单元格范围内使用公式.

I have a chart that is generated with a macro for a range of cells. At times some blank cells will exist within that range. I'm facing a problem when generating my chart with an empty cell value, the chart will start plotting at the first non empty cell, causing the category axis to not be reporting correct. How can I make my chart show a blank value on my chart using VBA. I would prefer not to use a formula on my cell range.

这是我的代码:

Sub createchart()

Dim wb As Workbook: Set wb = ThisWorkbook
Dim ws As Worksheet: Set ws = wb.Sheets("contactunder")


Dim CellRow As Integer    

wb.Sheets("ContactsFront").Select  
CellRow = ActiveCell.Row   
Worksheets("samplesheet").Activate
ActiveSheet.Shapes.AddChart2(251, xlBarClustered).Select
ActiveChart.DisplayBlanksAs = xlNotPlotted
ActiveChart.SetSourceData Source:=ws.Range("BY" & CellRow & ",CB" & CellRow & ",CH" & CellRow & ",CJ" & CellRow & ",CL" & CellRow & ",CN" & CellRow & ",CP" & CellRow)
ActiveChart.FullSeriesCollection(1).XValues = "contactunder!$BY$10,contactunder!$CB$10,contactunder!$CH$10,contactunder!$CJ$10,contactunder!$CL$10,contactunder!$CN$10,contactunder!$CP$10"
ActiveChart.SetElement (msoElementDataLabelOutSideEnd)
ActiveChart.SetElement (msoElementPrimaryCategoryGridLinesNone)
ActiveChart.Axes(xlValue).MajorGridlines.Format.Line.Visible = msoFalse
ActiveChart.FullSeriesCollection(1).DataLabels.ShowCategoryName = False

With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = "Analysis for " & ws.Range("D" & CellRow)
ActiveChart.HasAxis(xlValue) = False
ActiveChart.HasLegend = False
End With

End Sub

推荐答案

由于您使用联合范围作为图表的源数据，因此该联合范围内的第一个空白单元格将不在源范围内使用 SetSourceData 将联合范围设置为图表的源数据之后.

Since you are using union range as source data of your chart, first blank cell in that union range will not be into the source after setting that union range as the source data of the chart using SetSourceData.

因此，第一个单元格在该联合范围内不得为空.没有值的单元格必须为#N/A .

So first cell must not be empty in that union range. Cells without values must be #N/A.

您可以在代码中用#N/A 替换空白，如下所示:

You could replacing blank with #N/A within your code like so:

...
Set oChartRange = ws.Range("BY" & CellRow & ",CB" & CellRow & ",CH" & CellRow & ",CJ" & CellRow & ",CL" & CellRow & ",CN" & CellRow & ",CP" & CellRow)
For Each oCell In oChartRange
 If Not IsError(oCell.Value) Then
  If oCell.Value = "" Then oCell.Value = CVErr(2042)
 End If
Next
ActiveChart.SetSourceData Source:=oChartRange
...

当然，您需要记住，在公式中使用该单元格时，该单元格中可能存在错误值.因此，也许您必须然后使用 IFERROR .但是，尽管如此，我还是不会使用0，因为0是一个有价值的数字，它的含义不同于空白.空白表示不可用，而不是0.所以我会在这里真正使用#N/A .

Of course then you need keeping in mind that there are error values possible in that cell when using that cell in formulas. So maybe you must using IFERROR then. But nevertheless I would not using 0 because 0 is a valuable number and it's meaning is different from blank. Blank means not available rather than 0. So I would really using #N/A here.

这篇关于单元格区域中的VBA图表空白值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！