I was asked by a student to assist writing some VBA code. The assignment, as per the student:
- Write a sub that asks the user for an integer such as 100 and stores it in the variable "upperLimit".
- It then asks the user for another integer such as 3 and stores it in the variable "factor".
- It then creates an array called "multiple", of the appropriate size, that contains all the multiples of factor no greater than upperLimit.
- Finally, it uses a loop to sum all of the elements in the array and reports this sum with an appropriate message in a message box.
- For example, if the two inputs are 100 and 3, it should report "The sum of multiples of 3 no greater than 100 is 1683".
There are 2 ways to solve this, the usual iterative way and a mathematical away. The mathematical way is smarter.
In the first:
- create an array of [upper limit] / [factor] elements, called upper bound or ubound, in this case 33
- iterate through the array from array(1) to array (ubound), summing the elements e.g, 3 + 6 + …. + 99, to get 1683
The second is more like an equation:
- Calculate the number or elements, e.g., the integer value of [upper limit] / [factor], or 33
- Calculate the highest value, 99
- Sum the highest and lowest values, 102
- multiply the sum times 1/2 the number of elements, 16.5, to get 1683
Included in the ZIP file is both the code as text file, as well as an Excel 2007 file that uses the functions in equations accessible to the user.
Right-click to Save Target As: VBA_ArraySummation_Examples.zip