current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 1 down vote favorite

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.

share|improve this question

1 Answer 1

up vote 3 down vote accepted

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
share|improve this answer
    
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? –  Andrew Jun 24 '11 at 10:34
    
@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. –  GSerg Jun 24 '11 at 14:57
    
thank you very much again, the code is now ready to use. I'm extremely grateful for your help. Take good care. –  Andrew Jun 27 '11 at 7:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.