in de wiskunde is het factoriseren of het ontbinden in factoren van een getal het herschrijven van dat getal in kleinere delen, die met elkaar vermenigvuldigd weer het oorspronkelijke getal opleveren. Die kleinere delen heten de factoren van het originele getal. Kun je op pen en papier doen maar het kan sneller in Excel met VBA code.

We hebben in onderstaande code 3 onderdelen verwerkt:

• PrimeFactors (returns a formatted factorization string)
• ListAllFactors (schrijft alle factoren op het werkblad)
• QuickSort (sorteert de factoren van klein naar groot)

1. Kopieer de onderstaande code middels Ctrl + C
2. Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
3. Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
4. Druk op M om een standaard module in te voegen
5. Daar waar de cursor knippert voeg je de code in middels Ctrl + V
6. Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
7. Druk op de toetscombinatie ALT + F8 om de Macro Dialoog te tonen. Dubbelklik op de macro naam om te starten.

In [A1] zet je een getal bijvoorbeeld: 4294967295

Vervolgens kies je voor View | Macros | View macros en klik op: ListAllFactors
om de macro te starten. Eindresultaat staat op Sheet1.

Option Explicit

' ==========================================================
' Utility Module: FactorizationTools
' Provides:
'   - PrimeFactors(number): returns prime factorization as string
'   - ListAllFactors(): lists all factors of A1 into column A
'   - QuickSort(): helper to sort factor arrays efficiently
' ==========================================================

' -----------------------------
' Returns prime factorization of number as string
' Example:  360 ? "2^3 × 3^2 × 5"
' -----------------------------
Public Function PrimeFactors(ByVal number As Double) As String
    Dim factors As String
    Dim i As Double
    Dim n As Double
    Dim cnt As Long
    
    factors = ""
    n = number
    
    ' Handle factor 2
    cnt = 0
    Do While n / 2 = Int(n / 2)
        cnt = cnt + 1
        n = n / 2
    Loop
    If cnt > 0 Then
        factors = "2"
        If cnt > 1 Then factors = factors & "^" & cnt
    End If
    
    ' Trial divide by odd numbers up to sqrt(n)
    i = 3
    Do While i * i <= n
        cnt = 0
        Do While n / i = Int(n / i)
            cnt = cnt + 1
            n = n / i
        Loop
        If cnt > 0 Then
            If factors <> "" Then factors = factors & " × "
            factors = factors & i
            If cnt > 1 Then factors = factors & "^" & cnt
        End If
        i = i + 2
    Loop
    
    ' If remainder > 1 it's a prime
    If n > 1 Then
        If factors <> "" Then factors = factors & " × "
        factors = factors & n
    End If
    
    PrimeFactors = factors
End Function

' -----------------------------
' QuickSort implementation for Double arrays
' -----------------------------
Public Sub QuickSort(arr() As Double, ByVal first As Long, ByVal last As Long)
    Dim low As Long, high As Long
    Dim pivot As Double, tmp As Double
    
    low = first
    high = last
    pivot = arr((first + last) \ 2)
    
    Do While low <= high
        Do While arr(low) < pivot
            low = low + 1
        Loop
        Do While arr(high) > pivot
            high = high - 1
        Loop
        If low <= high Then
            tmp = arr(low)
            arr(low) = arr(high)
            arr(high) = tmp
            low = low + 1
            high = high - 1
        End If
    Loop
    
    If first < high Then QuickSort arr, first, high
    If low < last Then QuickSort arr, low, last
End Sub

' -----------------------------
' Lists all factors of number in A1
'   - Outputs factors in col A starting A3
'   - Shows prime factorization in B1
'   - Shows total factor count in B2
' -----------------------------
Public Sub ListAllFactors()
    Dim num As Double
    Dim i As Double
    Dim factorCount As Long
    Dim factors() As Double
    Dim sqrNum As Double
    
    ' Read number from A1
    num = Range("A1").Value
    If num < 1 Or num > 1E+15 Then
        MsgBox "Number must be between 1 and 1,000,000,000,000,000"
        Exit Sub
    End If
    
    sqrNum = Sqr(num)
    
    ' Count factors
    factorCount = 0
    For i = 1 To sqrNum
        If num / i = Int(num / i) Then
            factorCount = factorCount + 1
            If i <> num / i Then factorCount = factorCount + 1
        End If
    Next i
    
    ReDim factors(1 To factorCount)
    
    ' Store factors
    factorCount = 0
    For i = 1 To sqrNum
        If num / i = Int(num / i) Then
            factorCount = factorCount + 1
            factors(factorCount) = i
            If i <> num / i Then
                factorCount = factorCount + 1
                factors(factorCount) = num / i
            End If
        End If
    Next i
    
    ' Sort
    If factorCount > 1 Then QuickSort factors, 1, factorCount
    
    ' Output
    Range("A3:A10000").ClearContents
    For i = 1 To factorCount
        Range("A" & (i + 2)).Value = factors(i)
    Next i
    
    ' Prime factorization & summary
    Range("B1").Value = PrimeFactors(num)
    Range("B2").Value = "Total Factors: " & factorCount
End Sub