How to Get Prime Numbers or Factors

Article translations Article translations
Article ID: 202782 - View products that this article applies to.
This article was previously published under Q202782
This article also applies to:
  • Microsoft Excel for Windows 95 7.0|7.0
  • Microsoft Excel for Windows 95 7.0a|7.0a
  • Microsoft Excel for Windows 5.0|5.0
  • Microsoft Excel for Windows 5.0c|5.0c
  • Microsoft Excel for the Macintosh 5.0|5.0
  • Microsoft Excel for the Macintosh 5.0a|5.0a
Expand all | Collapse all

SUMMARY

A prime number is any integer that can be divided evenly only by itself and one. A factor of a number is an integer that can be evenly divided into that number. This article contains Microsoft Visual Basic for Applications macros for Excel that you can use to determine all the prime numbers within a range, or to derive all of the factors of a number.

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. To create the macros, follow these steps:

  1. Save and close any open workbooks, and then create a new workbook.
  2. Create a new module:

    In Microsoft Excel 97, start the Visual Basic Editor by pressing ALT+F11.

    In Microsoft Excel 5.0, or 7.0, click Macro and Module on the Insert menu.
  3. Type the following code in the module:
       Sub GetFactors()
          Dim Count As Integer
          Dim NumToFactor As Single 'Integer limits to < 32768
          Dim Factor As Single
          Dim y As Single
          Dim IntCheck As Single
       
          Count = 0
          Do
             NumToFactor = _
                Application.InputBox(Prompt:="Type integer", Type:=1)
             'Force entry of integers greater than 0.
             IntCheck = NumToFactor - Int(NumToFactor)
             If NumToFactor = 0 Then
                Exit Sub
                'Cancel is 0 -- allow Cancel.
             ElseIf NumToFactor < 1 Then
                MsgBox "Please enter an integer greater than zero."
             ElseIf IntCheck > 0 Then
                MsgBox "Please enter an integer -- no decimals."
             End If
             'Loop until entry of integer greater than 0.
          Loop While NumToFactor <= 0 Or IntCheck > 0
          For y = 1 To NumToFactor
             'Put message in status bar indicating the integer being checked.
             Application.StatusBar = "Checking " & y
             Factor = NumToFactor Mod y
             'Determine if the result of division with Mod is without _
                 remainder and thus a "factor".
             If Factor = 0 Then
                'Enter the factor into a column starting with the active cell.
                ActiveCell.Offset(Count, 0).Value = y
                'Increase the amount to offset for next value.
                Count = Count + 1
             End If
          Next
          'Restore Status Bar.
          Application.StatusBar = "Ready"
       End Sub
       
       Sub GetPrime()
          Dim Count As Integer
          Dim BegNum As Single  'Integer limits to < 32768
          Dim EndNum As Single
          Dim Prime As Single
          Dim flag As Integer
          Dim IntCheck As Single
          Count = 0
       
          Do
             BegNum = _
                Application.InputBox(Prompt:="Type beginning number.", Type:=1)
             'Force entry of integers greater than 0.
             IntCheck = BegNum - Int(BegNum)
             If BegNum = 0 Then
                Exit Sub
                'Cancel is 0 -- allow Cancel.
             ElseIf BegNum < 1 Then
                MsgBox "Please enter an integer greater than zero."
             ElseIf IntCheck > 0 Then
                MsgBox "Please enter an integer -- no decimals."
             End If
             'Loop until entry of integer greater than 0.
          Loop While BegNum <= 0 Or IntCheck > 0
       
          Do
             EndNum = _
                Application.InputBox(Prompt:="Type ending number.", Type:=1)
             'Force entry of integers greater than 0.
             IntCheck = EndNum - Int(EndNum)
             If EndNum = 0 Then
                Exit Sub
                'Cancel is 0 -- allow Cancel.
             ElseIf EndNum < BegNum Then
                MsgBox "Please enter an integer larger than " & BegNum
             ElseIf EndNum < 1 Then
                MsgBox "Please enter an integer greater than zero."
             ElseIf IntCheck > 0 Then
                MsgBox "Please enter an integer -- no decimals."
             End If
             'Loop until entry of integer greater than 0.
          Loop While EndNum < BegNum Or EndNum <= 0 Or IntCheck > 0
       
          For y = BegNum To EndNum
             flag = 0
             z = 1
             Do Until flag = 1 Or z = y + 1
                'Put message into Status Bar indicating the integer and _
                    divisor in each loop.
                Application.StatusBar = y & " / " & z
                Prime = y Mod z
                If Prime = 0 And z <> y And z <> 1 Then
                   flag = 1
                End If
                z = z + 1
             Loop
       
             If flag = 0 Then
                'Enter the factor into a column starting with the active cell.
                ActiveCell.Offset(Count, 0).Value = y
                'Increase the amount to offset for next value.
                Count = Count + 1
             End If
          Next y
          'Restore Status Bar.
          Application.StatusBar = "Ready"
       End Sub
    					
  4. On the File menu, click Close and Return to Microsoft Excel. Switch to the worksheet on which you want to run the macro, and click a cell in which you want to create the column of factors or prime numbers.
  5. On the Tools menu, point to Macro, and then click Macros.
  6. Click GetFactors, and then click Run to get the factors.

    -or-

    Click GetPrime, and then click Run to get the list of prime numbers.
NOTE: Although these macros can find prime numbers for a large range of numbers and factors for large numbers, running such code may take a very long time. For this reason, the status bar shows the progress while the macro runs.

REFERENCES

For more information about getting help with Visual Basic for Applications, please click the article number below to view the article in the Microsoft Knowledge Base:
163435 VBA: Programming Resources for Visual Basic for Applications
226118 OFF2000: Programming Resources for Visual Basic for Applications
For more information about running sample code, please click the article number below to view the article in the Microsoft Knowledge Base:
173707 OFF97: How to Run Sample Code from Knowledge Base Articles
212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

Properties

Article ID: 202782 - Last Review: October 10, 2006 - Revision: 2.2
APPLIES TO
  • Microsoft Excel 97 Standard Edition
Keywords: 
kbhowto KB202782
Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com