vba Do While Not IsEmpty(Range("A1")) 条件评估失败

Do While Not IsEmpty(Range("A1")) condition evaluation failure

提问by Ando Jurai

I have a problem with this code, which I made specifically to "load" data in an already create graph, from 3 associated columns like this:

我对这段代码有一个问题，我专门为从 3 个相关列中“加载”已经创建的图表中的数据而编写的，如下所示：

Data1 X1 Y1
Data2 X2 Y2
Data3 X3 Y3

Its intended behavior is very simple, It select the active cell, and enters a loop: select the first graph of the sheet, evaluate if active cell is not empty, if it is, it should go outside the loop and end the macro, if it isn't, it then adds new series to it, puts Y as abscissa value and puts X as ordinate value, increments a tracking value which allows to increase the number of the next series in a graph (it was initialized outside the loop as 1), changes the row of the active cell with the one immediately below.

它的预期行为非常简单，它选择活动单元格，然后进入一个循环：选择工作表的第一个图形，评估活动单元格是否不为空，如果是，它应该跳出循环并结束宏，如果不是，然后向其添加新系列，将 Y 作为横坐标值并将 X 作为纵坐标值，增加跟踪值，允许增加图中下一个系列的数量（它在循环外初始化为1), 将活动单元格的行更改为紧接在其下方的行。

The problem is that it never go outside of the loop (and eventually crashes because you can't add more that 256 series to a graph, which I improved the code in another version with a if condition to make it better, it works; but still adds empty series; which are very tedious to delete and may mess with things if left there).

问题是它永远不会超出循环（并最终崩溃，因为您无法将更多的 256 系列添加到图形中，我在另一个版本中使用 if 条件改进了代码以使其更好，它可以工作；但是仍然添加了空系列；删除它们非常繁琐，如果留在那里可能会弄乱东西）。

It seems that the condition: Do While Not IsEmpty(Range("A1")) fails to evaluate correctly. I tried to use Do While Not IsEmpty(Range("A1").value) instead, or Do While IsEmpty(Range("A1")); Do While Not IsEmpty(Selection) (which send back an error about global), I tried a lot of other things but I think my type of value is ok, it is a boolean, so it should work as is. I also tried on a new workbook with a new sheet and only one line to be sure nothing had messed with cells before and would have changed their state so they would not be empty for the program, but actually while I thought about the most evident mistakes, I still fail to obtain the correct behavior.

似乎条件：Do While Not IsEmpty(Range("A1")) 未能正确评估。我尝试使用 Do While Not IsEmpty(Range("A1").value) 代替，或 Do While IsEmpty(Range("A1")); Do While Not IsEmpty(Selection)（返回一个关于全局的错误），我尝试了很多其他的东西，但我认为我的值类型没问题，它是一个布尔值，所以它应该按原样工作。我还尝试了一个带有新工作表和只有一行的新工作簿，以确保之前没有任何东西弄乱单元格并且会改变它们的状态，因此它们不会为程序为空，但实际上，当我想到最明显的错误时，我仍然无法获得正确的行为。

Sub ScatterSeries()

    ActiveCell.Offset(0, 0).Range("A1").Select
    Serie = 1
    Do While Not IsEmpty(Range("A1"))
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.PlotArea.Select
        ActiveChart.SeriesCollection.NewSeries
        ActiveChart.SeriesCollection(Serie).Name = ActiveCell
        ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2)
        ActiveChart.SeriesCollection(Serie).Values = ActiveCell.Offset(0, 1)
        Serie = Serie + 1
        ActiveCell.Offset(1, 0).Range("A1").Select
    Loop

End Sub

I read few references like that https://www.udemy.com/blog/excel-vba-do-while/or http://www.fontstuff.com/ebooks/free/fscodeloops.pdfbut I fail to see what I am doing differently, which would cause the program to be wrong. Could anyone please give me insights about that? I feel really stupid now. Thanks.

我读了一些像https://www.udemy.com/blog/excel-vba-do-while/或http://www.fontstuff.com/ebooks/free/fscodeloops.pdf这样的参考文献，但我看不到什么我的做法不同，这会导致程序出错。任何人都可以请给我有关的见解吗？我现在真的觉得自己很傻。谢谢。

(I must say that I am a beginner with vba, so it is possible I made very obvious mistakes, I just runned some actions to see what would appear in a macro, then tweaked this with scraps of code read in examples here and there (I still plan to learn properly the language but I had to solve the problem for today...:/)

（我必须说我是 vba 的初学者，所以我有可能犯了非常明显的错误，我只是运行了一些操作来查看宏中会出现什么，然后用在这里和那里的示例中读取的代码片段进行调整（我仍然打算好好学习这门语言，但我今天必须解决这个问题...:/)

回答by Sam

IsEmpty() is used in VBA to check if a variable has been assigned a property, not to check if a cell is blank. Also your offset method isn't properly declared.

IsEmpty() 在 VBA 中用于检查变量是否已分配属性，而不是检查单元格是否为空。此外，您的偏移方法未正确声明。

Use something like this:

使用这样的东西：

Sub ScatterSeries()

Range("A1").Activate

Serie = 1

While Not ActiveCell = ""

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.NewSeries
    ActiveChart.SeriesCollection(Serie).Name = ActiveCell.Value
    ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2).Value
    ActiveChart.SeriesCollection(Serie).Values = ActiveCell.Offset(0, 1).Value

    Serie = Serie + 1

    ActiveCell.Offset(1, 0).Activate
Wend

End Sub

Also, you should always specify the .Value property of the range object (Yes, I know it's the default method of a Range object) but when you are testing a condition on the value, you want to be sure that it's definitely looking at the value and nothing else.

此外，您应该始终指定 range 对象的 .Value 属性（是的，我知道它是 Range 对象的默认方法）但是当您测试该值的条件时，您要确保它确实在查看价值，没有别的。

回答by Dave Brown

Tried this as a comment but it was too long.

尝试将此作为评论，但它太长了。

It looks like the problem is an infinite loop. The cell A1, I assume, has something in it (likely the text Data1?) and nothing in your code ever looks anywhere else or changes the value in this cell.

看起来问题是一个无限循环。我假设单元格 A1 中包含一些内容（可能是文本 Data1？），并且您的代码中没有任何内容可以查看其他任何地方或更改此单元格中的值。

Do While Not IsEmpty(Range("A1"))

You already have an integer "counter" named Serie, so I would suggest just altering the Do While to be

您已经有一个名为 Serie 的整数“计数器”，因此我建议将 Do While 更改为

Do While Not IsEmpty(Range("A1").Offset(Serie-1,0))

The reason for the minus one is Serie is set to 1 initially and you actually want offset zero in your first pass.

减号的原因是 Serie 最初设置为 1，而您实际上希望在第一次通过时偏移为零。

If this doesn't solve your problem post again and I can take a better look later in the day.

如果这不能再次解决您的问题，我可以在当天晚些时候更好地查看。

回答by Ando Jurai

Would have commented your answers but it's way too long.

会评论你的答案，但它太长了。

Thanks S OYou were right about IsEmpty, I read lots of code about using IsEmpty for evaluating empty cells, but that won't work while just changing 'Do While Not IsEmpty(Range("A1")) to Do While Not ActiveCell = "" in my previous code.

由于SO你是对的IsEmpty，我阅读了大量的代码如何使用的IsEmpty评估空单元格，但不会工作，而只是改变“你虽然不为IsEmpty（范围（‘A1’））做虽然不ActiveCell =“ "在我之前的代码中。

I actually just changed this condition, because the one you provided didn't work for me*, and it made it work without problem. THANKS!

我实际上只是改变了这个条件，因为你提供的那个对我不起作用*，它让它毫无问题地工作。谢谢！

• your code send me back error 1004, "error defined by application or object", it doesn't seem to like the line: ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2).Value. I also read that Wend was "obsolete" syntax, so I didn't use that.

• 您的代码向我发送了错误 1004，“由应用程序或对象定义的错误”，它似乎不喜欢以下行：ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2).Value。我还读到 Wend 是“过时”的语法，所以我没有使用它。

But I retain the lesson about syntax improvement about value I'll take these gladly, as I said I am only a tweaker regarding VBA, I didn't learn syntax properly. I just have now to understand why these won't work for me there.

但是我保留了关于价值的语法改进的课程我很乐意接受这些，因为我说我只是一个关于 VBA 的调整者，我没有正确学习语法。我现在必须明白为什么这些对我不起作用。

Dave Brownsolution also works nearly flawlessly with the original code, it adds only one empty series in the end since it won't go out of the loop on the first empty cell but only on the second. Which is something that I can correct by setting "Series" at 0 at first and changing the way/moment it is incremented**. You were right about the infinite loop, for me it was quite evident it was one but I didn't mentionned that because I believed the while evaluation was somewhat wrong, while it only evaluated the wrong thing. On the other hand, I failed at first to understand why, since I did believe that "Range ("A1")" was changed by the fact of changing the active cell. Which is not the case. Beginner failure.

Dave Brown解决方案也几乎可以完美地与原始代码一起使用，它最后只添加了一个空系列，因为它不会在第一个空单元格上退出循环，而只会在第二个空单元格上退出循环。这是我可以通过首先将“系列”设置为 0 并改变它递增的方式/时刻来纠正的事情**。你对无限循环的看法是对的，对我来说很明显它是一个，但我没有提到这一点，因为我认为 while 评估有点错误，而它只评估了错误的东西。另一方面，我起初不明白为什么，因为我确实相信“范围（“A1”）”被改变活动单元格的事实改变了。事实并非如此。初学者失败。

**but changing Dave Brownsolution to

**但将Dave Brown解决方案更改为

Serie = 0
Do While Not IsEmpty(Range("A1").Offset(Serie, 0))
    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection.NewSeries
    Serie = Serie + 1
    ActiveChart.SeriesCollection(Serie).Name = ActiveCell
    ActiveChart.SeriesCollection(Serie).XValues = ActiveCell.Offset(0, 2)
    ActiveChart.SeriesCollection(Serie).Values = ActiveCell.Offset(0, 1)
    ActiveCell.Offset(1, 0).Range("A1").Select
Loop

Still makes the same mistake...? It should not, since it evaluate the current cell at start, and "Series" is now equal to current total vertical offset...

还是犯同样的错误……？它不应该，因为它在开始时评估当前单元格，并且“系列”现在等于当前总垂直偏移......

In the end, I came up with another solution which is : Do While Not IsEmpty(ActiveCell) which also solves the problem (but as S Opointed it out, it is not perfect as improper use of "IsEmpty" is made)

最后，我想出了另一个解决方案：Do While Not IsEmpty(ActiveCell) 也解决了这个问题（但正如SO指出的那样，它并不完美，因为“IsEmpty”的使用不当）