excel - Excel VBA 中的 While 循环问题

Question

我正在尝试编写一个 VBA 宏，它逐行获取给定列中的单元格值，并为我组装一个 SQL 查询来复制粘贴。它基本上将文本片段和变量放在一个单元格中。查询需要卡号和序数作为输入，因此需要变量。

宏几乎准备就绪，但它陷入了无限的 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

我试过 IsEmpty() 而不是 IsNull()，结果是一样的。请让我知道我在这里缺少什么。另外，请随时给我任何使代码更优雅的建议，因为这是我第一次尝试 VBA。预先感谢您的努力。

Answer 1

作为 String , Cardnumber永远不会是Null或 Empty .它只能有零长度，Len(Cardnumber) = 0 .

如果 Cardnumber是 Variant , 你可以使用 IsEmpty测试单元格值是否为空白。
没用IsNull , 因为 Excel 中的单元格值永远不会是 Null .即使是 Null从数据库中获取，Excel 会将其替换为 Empty .

回答您的下一个问题:我会将该代码重构为:

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