Excel 2007 VBA tutorial-loop to read and count records


VBA loop: read and count records

In this example, We use VBA while loop statement to read information of books from the current worksheet to put in a ListBox. The program also displays the number of books at the bottom of the Form.

To run this example VBA code, you will need a Form, a CommandButton, a ListBox and a Label. In addition, you may copy and paste the book information below to your current worksheet.

BookID Title
B001 VBA Programming for Excel 2007
B002 C++ Programming for Beginners
B003 Learn Java in 60 hours
B004 C# Programming for Java Developer
B005 C++ Graphics and Game Programming


VBA for Excel: VBA code example of using VBA while loop to read information from worksheet and put in the listbox

VBA code in this example:


Option Explicit 'Alert when errors occur
  Dim i As Integer ' global declaration

  Private Sub CommandButton1_Click()
    i = 1
   While Cells(i, 1) <> ""
      ListBox1.AddItem Cells(i, 1) & Space(10) & Cells(i, 2) 'populate list
      i = i + 1
  Wend

  Label1.Caption = "Number of Books:" & i - 2 'show number of books
End Sub



Comments





This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.