Excel VBA 中的 While 循环问题
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6466395/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Problem with a While loop in Excel VBA
提问by Andrew
I'm trying to write a VBA macro that takes the cell values in a given column, row by row, and assembles an SQL query for me to copypaste. It basically puts together text snippets and variables in a cell. The query needs cardnumbers and ordinal numbers as input, hence the variables.
我正在尝试编写一个 VBA 宏，它逐行获取给定列中的单元格值，并为我组装一个 SQL 查询以进行复制粘贴。它基本上将文本片段和变量放在一个单元格中。查询需要卡号和序数作为输入，因此需要变量。
The macro is almost ready, but it gets stuck in an infinite While loop.
宏几乎准备好了，但它卡在无限的 While 循环中。
Sub Query()
Dim Row As Integer
Row = 8
Dim Cardnumber As String
Cardnumber = Range("D" & Row)
Dim Number As Integer
Number = 1
Range("E30").Select
ActiveCell.Value = "SELECT cardnumber, first_name || ' ' || last_name FROM ( SELECT cardnumber, first_name, last_name, c.OrderNo FROM ag_cardholder ch, (SELECT '%" & Cardnumber & "%' cardmask, " & Number & " OrderNo from dual "
While IsNull(Cardnumber) = False
Row = Row + 1
Number = Number + 1
Cardnumber = Range("D" & Row)
ActiveCell.Value = ActiveCell.Value & "UNION ALL SELECT '%" & Cardnumber & "%', " & Number & " OrderNo from dual "
Wend
ActiveCell.Value = ActiveCell.Value & ") c WHERE ch.cardnumber LIKE c.cardmask ORDER BY c.OrderNo ) t"
End Sub
I've tried IsEmpty() instead of IsNull(), the result is the same. Please let me know what I'm missing here. Also, feel free to give me any advice for making the code more elegant as this is my first try at VBA. Thank you in advance for your efforts.
我试过 IsEmpty() 而不是 IsNull()，结果是一样的。请让我知道我在这里缺少什么。另外，请随时给我任何建议，使代码更优雅，因为这是我第一次尝试 VBA。预先感谢您的努力。
回答by GSerg
Being a String, Cardnumberwill never be Nullor Empty. It can only have zero length, Len(Cardnumber) = 0.
作为一个String，Cardnumber永远不会是Null或Empty。它只能有零长度，Len(Cardnumber) = 0。
If Cardnumberwas Variant, you could use IsEmptyto test if a cell value is blank.
There is no point to use IsNull, as a cell value in Excel is never Null. Even if a Nullis fetched from a database, Excel will replace it with Empty.
如果Cardnumber是Variant，您可以IsEmpty用来测试单元格值是否为空。
没有必要使用IsNull，因为 Excel 中的单元格值永远不会Null。即使 aNull是从数据库中提取的，Excel 也会将其替换为Empty.
Responding to your next question: I would refactor that code to:
回答您的下一个问题：我将该代码重构为：
Sub Query()
Dim InnerSelect As String
Dim CurCell As Range: Set CurCell = ActiveSheet.Range("D8")
Dim Number As Long: Number = 1
Do
Dim Cardnumber As String
Cardnumber = CurCell.Value
If Len(Cardnumber) = 0 Then Exit Do
If Len(InnerSelect) = 0 Then
InnerSelect = "SELECT '%" & Cardnumber & "%' cardmask, " & Number & " OrderNo from dual "
Else
InnerSelect = InnerSelect & "UNION ALL SELECT '%" & Cardnumber & "%', " & Number & " OrderNo from dual "
End If
Number = Number + 1
Set CurCell = CurCell.Offset(1, 0)
Loop
Range("E30").Value = _
"SELECT cardnumber, first_name || ' ' || last_name FROM ( SELECT cardnumber, first_name, last_name, c.OrderNo FROM ag_cardholder ch, (" & _
InnerSelect & _
") c WHERE ch.cardnumber LIKE c.cardmask ORDER BY c.OrderNo ) t"
End Sub
