Using Functions in VBA Programs

Generating a Random Integer from a Range

If you need random numbers not from the interval [0, 1], but from an integer interval (e.g., [1, 6]), you can use the function RndInt(), defined as follows.

Generating a Random Integer

Sub DemoIntegerRnd()
    Dim i As Integer
    For i = 0 To 10
        Debug.Print RndInt(1, 6)
    Next
End Sub

Function RndInt(ByVal lowerbound As Integer, _
                ByVal upperbound As Integer) As Integer
    Randomize
    RndInt = (upperbound - lowerbound) * Rnd() + lowerbound
End Function

Outputting a String Character by Character to the Immediate Window

The Len() function returns the length of a string. For example, the following code outputs a string character by character to the Immediate Window.

Printing a String Character by Character

Sub Lengs()
    Dim i As Integer
    Dim s As String
    s = "Hello, World"
    For i = 1 To Len(s)
        Debug.Print Mid(s, i, 1)
    Next
End Sub

A String Consisting of a Given Number of Spaces

The Space() function returns a string consisting of the specified number of spaces. For example, in the following code, by sequentially adding another string in front of the given string, each time with fewer spaces, a “running line” effect is created.

To test the “running line,” run the procedure LetsGo().

Running Line

Private n As Integer

Sub LetsGo()
    n = 10
    RunString
End Sub

Sub RunString()
    If n >= 0 Then
        Range("Sheet2!A1").Value = Space(n) & "Hello, World!"
        n = n - 1
        Application.OnTime Now + TimeValue("00:00:01"), "RunString"
    End If
End Sub

Determining the Number of Seconds Since Midnight

The Timer() function returns a Single value representing the number of seconds since midnight.

• In Windows, unlike Mac OS X, Timer() returns not just seconds but fractional seconds since midnight.

For example, the following code creates a blinking cell where the background color alternates between red and green for 20 seconds.

To prevent the system from freezing during the loop and allow the application to respond to system events, DoEvents statements are added inside the loops.

Blinking Cell

Sub LetsGo2()
    Dim fl As Boolean
    Dim old As Long
    old = Timer
    Do
        fl = Not fl
        If fl Then
            Range("Sheet3!A1").Interior.Color = RGB(255, 0, 0)
        Else
            Range("Sheet3!A1").Interior.Color = RGB(0, 255, 0)
        End If
        Delay
        DoEvents
    Loop While Timer - old <= 20
    Range("Sheet3!A1").Interior.ColorIndex = xlNone
End Sub

Sub Delay()
    Dim old As Long
    old = Timer
    Do
        DoEvents
    Loop While Timer - old <= 0.5
End Sub