VBA Control Button

J. C. Daly
March 1, 2011

A button can be used to execute a Visual Basic subroutine.

Now when you click on the button the countbyn subroutine is exectued.

countbyn Subroutine

Public Sub countbyn()

'    This subroutine counts by an increment, k and displays the
'    values on the spread sheet.

'    For example if the maximum value is 20 and the increment is 5,
'    5 10 15 20  will be displayed on the spreadsheet.

    Dim x As Integer
    Dim xmax As Integer
    Dim k As Integer
    Dim c As Integer
    Dim j As Integer
    Dim i As Integer

    With ActiveSheet
        xmax = .Range("xmax")      ' Read the maximum count from the cell "xmax"
        k = .Range("k")            ' Read the counting increment from the cell "k"
    End With

    c = 0       '    Initilize counters to zero
    j = 0

    For i = 1 To xmax                ' Loop from 1 to the maximum count
        j = j + 1                    ' Increment j counter
        If j >= k Then               ' Write to sheet every k count
            ActiveSheet.Cells(6 + c, 4) = i     ' Write to row 6 + c in column D
            j = 0                    ' Reinitilize the j counter
            c = c + 1                ' Increment c so the next write will be down one row.
        End If
    Next i

End Sub

Figure 3   The result of running the subroutine with a max count of 100 and an increment of 5 is shown. Note that the button label was edited to read RUN. When you click on RUN the count by 5 numbers appear in column D. Cell D2 has been renamed xmax and cell F2 has been renamed k.