Problem with a While loop in Excel VBA

Question

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.

The macro is almost ready, but it gets stuck in an infinite While loop.

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.

GSerg · Accepted Answer · 2011-06-24 15:05:52Z

3

Being a String, Cardnumber will never be Null or Empty. It can only have zero length, Len(Cardnumber) = 0.

If Cardnumber was Variant, you could use IsEmpty to 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 Null is fetched from a database, Excel will replace it with 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

edited Jun 24, 2011 at 15:05

answered Jun 24, 2011 at 10:17

GSerg

78.8k18 gold badges173 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Andrew Over a year ago

thank you very much for clearing things up for me. I've tried While Len(Cardnumber) > 0, the only problem is it runs one extra time resulting in a line without a cardnumber. Could you please give me a hand with this?

GSerg Over a year ago

@Andrew This is because in your loop you are checking the > 0 condition before assigning Cardnumber a new value. You should remove the condition from the While clause, replace the While - Wend with Do - Loop and put the condition into the loop body right after assigning a new value to Cardnumber: If Len(Cardnumber) = 0 Then Exit Do.

Andrew Over a year ago

thank you very much again, the code is now ready to use. I'm extremely grateful for your help. Take good care.

Collectives™ on Stack Overflow

Problem with a While loop in Excel VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related