Intermediate Exercise
Create a Function that counts the number of times a prime number appears in cells. Check the result with a Sub that sets the background color to green in each occurrence.
Remember that in most cases there is more than one way to solve the exercise.
Hint: Prime numbers are only divisible by themselves and by 1.
We start by defining the function's arguments and the output data type
Function PrimeCount(Search As Range) As Long
We declare the variables
Dim i As Integer
Dim Cell As Range
Dim CellVal As Integer
Dim Divisor As Integer
Dim Prime As Integer
We create a For Each loop that runs through each cell of the function's "Range" argument. Then, we define the CellVal variable as the value of an element of the "Range" of the loop, and we assign 0 the Divisor variable
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0
We create a For Next loop within the previous loop.
For i = 1 To CellVal
Within this loop we introduce a If Else conditional to calculate the number of divisors of the number. If CellVal Mod i = 0 Then Divisor = Divisor + 1
We use a If Else conditional to push the loop forward if the number of divisors reaches three (thus optimizing execution time).
If Divisor = 3 Then
Exit For
End If
End If
Next i
In another conditional, if the number has only two divisors, we add a unit to the counter Prime and move on to the next cell
If Divisor = 2 Then
Prime = Prime + 1
End If
Next Cell
We define the variable Prime as the total amount of prime numbers occurrences once the For Each loop ends
PrimeCount = Prime
Function PrimeCount(Search As Range) As Integer
Dim i As Integer
Dim Cell As Range
Dim CellVal As Integer
Dim Divisor As Integer
Dim Prime As Integer
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0
For i = 1 To CellVal
If CellVal Mod i = 0 Then
Divisor = Divisor + 1
If Divisor = 3 Then
Exit For
End If
End If
Next i
If Divisor = 2 Then
Prime = Prime + 1
End If
Next Cell
PrimeCount = Prime
End Function
Sub Verification()
Dim Search As Variant
Dim i As Integer
Dim Cell As Variant
Dim Divisor As Integer
Dim CellVal As Integer
Set Search = Range("A3:C103")
For Each Cell In Search
CellVal = Cell.Value
Divisor = 0
For i = 1 To CellVal
If CellVal Mod i = 0 Then
Divisor = Divisor + 1
If Divisor = 3 Then
Exit For
End If
End If
Next i
If Divisor = 2 Then
Cell.Interior.Color = RGB(140, 198, 63)
End If
Next Cell
End Sub
SuperExcelVBA.com is learning website. Examples might be simplified to improve reading and basic understanding. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. All Rights Reserved.
Excel ® is a registered trademark of the Microsoft Corporation.
© 2019 SuperExcelVBA | ABOUT
Thank you for contributing. A message was sent reporting your comment.