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)
- Kopieer de onderstaande code middels Ctrl + C
- Druk op de toetscombinatie ALT + F11 om de Visual Basic Editor te openen
- Druk op de toetscombinatie ALT + N om het menu Invoegen te openen
- Druk op M om een standaard module in te voegen
- Daar waar de cursor knippert voeg je de code in middels Ctrl + V
- Druk op de toetscombinatie ALT + Q om de Editor af te sluiten en terug te keren naar Excel
- 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