Decomposition in Prime Factors

**philippe1** · 07-03-2011, 03:37 PM

Hi,

I am looking for a script to decompose a list of numbers into their prime factors.

Attached is a file with an example of what I am looking for.

Thanks for your help,

Philippe

**arthurbr** · 07-03-2011, 03:43 PM

Google search on "decomposition in prime factors +excel" yielded more than 100 k answers -

**shg** · 07-03-2011, 03:50 PM

Function Factors(ByVal dNum As Double, _
                 Optional bRevOrder As Boolean = False) As Variant
    ' shg 2006-0923
    '     2006-0604 - added bRevOrder argument
    ' Returns a comma-delimited string of the factors of dNum

    Dim vFac        As Variant
    Dim asFac()     As String
    Dim iFac        As Long
    
    vFac = FirstFactor(dNum)

    Select Case VarType(vFac)
        Case vbError, vbString
            Factors = vFac

        Case vbBoolean
            Factors = dNum
            
        Case vbDouble
            ReDim asFac(1 To 50)    ' 2^50 > 1E+15
            
            Do Until dNum = 1
                iFac = iFac + 1
                asFac(iFac) = vFac
                dNum = dNum / vFac
                vFac = FirstFactor(dNum)
            Loop
            
            ReDim Preserve asFac(1 To iFac)
            If bRevOrder Then ArrRev asFac
            Factors = Join(asFac, "*")
    End Select
End Function

Private Function ArrRev(ByRef av As Variant)
    Dim i           As Long
    Dim j           As Long
    Dim vTmp        As Variant

    i = LBound(av)
    j = UBound(av)

    Do While i < j
        vTmp = av(i)
        av(i) = av(j)
        av(j) = vTmp
        i = i + 1
        j = j - 1
    Loop
End Function

Function FirstFactor(dNum As Double) As Variant
    ' shg 2006-0923
    '     2009-1003 fixed bug so if dNum Mod 15 = 0, returns 3 as a factor, not 5

    ' Returns:      if dNum is:
    '   #VALUE!       < 2
    '   "Too big!"    > 1E+15
    '   #VALUE!       <> Int(dNum)
    '   1st factor    otherwise

    Dim dQuo        As Double
    Dim dFac        As Double

    If Int(dNum) <> dNum Then
        FirstFactor = CVErr(xlErrValue)

    ElseIf dNum > 1E+15 Then
        FirstFactor = "Too big!"

    ElseIf Int(dNum / 2#) = dNum / 2 Then
        FirstFactor = 2#
    Else
        For dFac = 3# To Int(Sqr(dNum)) Step 2#
            dQuo = dNum / dFac
            If Int(dQuo) = dQuo Then
                FirstFactor = dFac
                Exit Function    '------------------------------------->
            End If
        Next dFac

        FirstFactor = dNum
    End If
End Function

E.g.,

      -A- ---B--- ------------C------------
  1    56 2*2*2*7 B1 and down: =Factors(A1)
  2    22 2*11                             
  3    36 2*2*3*3                          
  4    84 2*2*3*7                          
  5   236 2*2*59                           
  6   457 457                              
  7   826 2*7*59

**philippe1** · 07-03-2011, 05:37 PM

Hi,

I tried this code but got this error message: Argument not optional

Any idea of what is wrong?

Philippe

**shg** · 07-03-2011, 05:54 PM

"Tried it" in what way?

**philippe1** · 07-03-2011, 06:05 PM

I created a module with the code.

Then pressed alt f8 and made the Factors function run.

This is when I received the error message.

**shg** · 07-03-2011, 06:12 PM

You used a formula on a worksheet, or wrote code to test it? If the latter, post the code.

**Captain Pedro** · 05-31-2012, 11:31 AM

I clicked select code copied the code then opened a new module in the vb editor and pasted the code in. then selected "close and return to excel" on the file menu. I selected a column of values and pressed Alt+F8 to view macros the macro doesn't appear on the list. So I looked at the code, didn't find "sub Marco_name()" or "end sub" so I deleted that module created a new one and typed "sub prime_factors()" and Enter. Then I pasted the code in between exited to excel and tried to run the macro by selecting a range of values typing "Alt+F8" and got the error "expecting endsub" what am I doing wrong please

**arlu1201** · 05-31-2012, 11:32 AM

Captain Pedro,

Welcome to the Forum, unfortunately:

Your post does not comply with Rule 2 of our Forum RULES. Don't post a question in the thread of another member -- start your own thread. If you feel it's particularly relevant, provide a link to the other thread. It makes sense to have a new thread for your question because a thread with numerous replies can be off putting & difficult to pick out relevant replies.