With the example for DigitSum(Number), we leave geometry behind for a short time and turn to arithmetic. This example explains the following:
How to use loops
How to use VBA functions
How to handle variables
To calculate the sum of the digits of a number, you add up the digits one by one:
All programming languages include elements that allow you to run tasks repeatedly. The generic term for this is a loop.
VBA recognizes several loop types:
For...Next
Do While...Loop/Do...Loop While
Do Until...Loop/Do...Loop Until
Note
The two versions of the While and Until loops differ in when each determines whether the loop is to be performed. The Do While loop checks at the beginning of the loop, and the Do Until loop checks at the end.
Each loop consists of three parts:
The first statement handles the entry into the loop: This is the loop header.
The statements in the middle are processed during the loop iterations: This is the loop body.
The last statement indicates the end of the loop: This is the loop footer.
In the For...Next loop, the number of iterations is set by a counter. The While loop runs as long as a condition (specified after While) is true. The Until loop ends if the condition specified after Until is true.
You can use the For...Next loop to calculate the sum of digits, because the number of iterations is defined by the number of digits. The condition to end the loop is not determined during runtime but is set from the outset.
The following code shows the basic form of the For...Next loop:
Subtotal = 0
For i = 1 To 10
Subtotal = Subtotal + i
Next iThe first statement sets the Subtotal variable to zero. Subtotal is a variable that represents a number value that can be changed during runtime. These variables are used purely within the function evaluation code and are not visible in the worksheet. It is also good practice to declare a variable at the beginning of a function block and set the data type. The statement
Dim Subtotal As Long
specifies the data type Long for the variable Subtotal; that is, Subtotal is an integer in the range from –2,147,483,648 through 2,147,483,647. The loop header consists of the statement
For i = 1 To 10
The i is the counter that counts the number of iterations. This loop is iterated 10 times, and the counter i has the values 1 through 10 successively.
In the loop body, the counter also has another purpose: Each time the function loops, the counter is added to the current value of the subtotal.
Subtotal = Subtotal + i
The loop footer with the Next keyword increases the counter by 1. If the end value indicated in the loop header is not exceeded, a new iteration starts. Otherwise, the iteration continues with the next statement after the loop.
These are all important elements that can be incorporated into the DigitSum() function.
Public Function DigitSum(Number As Double) As Long
Dim i As Integer
Dim S As String
DigitSum = 0
S = Str(Number)
For i = 1 To Len(S)
DigitSum = DigitSum + Val(Mid(S, i, 1))
Next i
End FunctionThe DigitSum() function is declared with the data type Long and the argument Number in the standard Excel number format. The next two statements declare two variables. The first variable i is the counter variable for the For...Next loop. The maximum value of this variable corresponds to the number of digits.
The second variable, S, is used as a string variable. This variable is going to be used to convert the number into a string by using the VBA function Str() so that you can determine the length of the string with the help of the VBA function Len() and pick out each digit from the number individually.
Within the loop, the VBA function Mid() returns the next digit in the number as a string. The VBA function Val() converts this back to a number, and this number can then be added to the DigitSum variable.
The loop cycles around, adding each digit of the number in turn.
The beginner might find the detour through the string variable and the use of the associated string functions Str(), Len(), Mid(), and Val() confusing. However, this is still the simplest solution and demonstrates how to use loops, variables, and VBA functions with only a few lines.