Prime Number

Exercise Solution

1. We start by defining the function's arguments and the output data type

   Function PrimeCount(Search As Range) As Long

2. We declare the variables

   Dim i As Integer
   Dim Cell As Range
   Dim CellVal As Integer
   Dim Divisor As Integer
   Dim Prime As Integer
   

3. 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
   

4. We create a For Next loop within the previous loop.

   For i = 1 To CellVal
   

5. 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

6. 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
   

7. 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
   

8. We define the variable Prime as the total amount of prime numbers occurrences once the For Each loop ends

   PrimeCount = Prime
   

Consolidated Answer

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