VBA Do loop until 0 or ""

Question

Below code is counting a final amount of students in a school and average amount of students in a class.

It's running the code until the user types "0".

How can I ensure that the "0 class" won't be counted as an additional class? Currently I am achieving it by subtracting -1 from B, but it's not an elegant solution - calculation is correct, but the class is still listed in final MsgBox.

Btw, if I wanted to end the loop when user is leaving the cell empty, what should I do? Simple Loop Until Zak = "" doesn't work.

Many thanks,

Sub D1()

    Dim Zak As Byte
    Dim B As Byte, C As Byte
    Dim kzak As String
    Dim ktrid
    Dim trid As Byte
    Dim k, l As Integer

    B = 0
    kzak = ""

    Do
        Zak = InputBox("Amount of students")
        B = B + 1
        kzak = kzak & Str(B) & (" class") & ("            ") & _
            ("Students ") & Str(Zak) & Chr(10)
        k = k + Zak
    Loop Until Zak = 0
    C = (B - 1)
    l = k / C

    MsgBox kzak & Chr(10) & ("At school is ") & Str(k) & (" students") & _
        (", on avarage ") & Str(l) & (" in a class")

End Sub

Not sure if this will help but if you use `Application.InputBox` you can restrict the data type that can be entered. For example setting `Type:=1` in the optional parameters makes it so the input box will only take numbers. You can read more about it [here](https://learn.microsoft.com/en-us/office/vba/api/excel.application.inputbox) — Jchang43, Nov 05 '18 at 21:54
Note that `Dim k, l As Integer` only declares `l As Integer` but `k As Variant` you must specify a type for **every** variable `Dim k As Long, l As Long`. Also I recommend [always to use Long instead of Integer](https://stackoverflow.com/a/26409520/3219613) in VBA since there is no benefit in `Integer` at all. — Pᴇʜ, Nov 06 '18 at 07:36

score 0 · Answer 1 · answered Nov 05 '18 at 19:47

A common approach to this is to ask first, and then test with a Do While instead of a Loop Until, and then ask again. Something like this:

Zak = InputBox("Amount of students")
Do While Zak <> 0
    ...
    ...
    Zak = InputBox("Amount of students")
Loop

score 0 · Accepted Answer · answered Nov 05 '18 at 20:35

This is a late post, but here is code to accomplish all the requirements you stated with just a few tweaks to your existing code:

Public Sub D2()
   Dim Zak As String
   Dim B As Integer
   Dim kzak As String
   Dim k As Integer
   Dim l As Integer

   B = 0
   kzak = ""

   Do
      Zak = InputBox("Amount of students")

      If Val(Zak) > 0 Then
         B = B + 1
         kzak = kzak & Str(B) & (" class") & ("            ") & ("Students ") & Zak & Chr(10)
         k = k + Val(Zak)
      End If
   Loop Until Zak = ""

   If B > 0 Then
      l = k / B
      MsgBox kzak & Chr(10) & ("At school is ") & Str(k) & (" students") & (", on avarage ") & Str(l) & (" in a class")
   End If
End Sub

Notice some of the changes I made.

First, I declared the variables more appropriately. Also, with your code k would have been a variant.

Second, I was able to remove the B - 1 hack while also assuring `B' had a value to avoid a divide by zero error.

Third, this code handles Cancel from the InputBox.

VBA Do loop until 0 or ""

2 Answers2