XL2000: How to Get Prime Numbers or Factors

• Microsoft Excel 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

http://www.microsoft.com/support/supportnet/overview/overview.asp

1. Save and close any open workbooks, and then create a new workbook.



2. Start the Visual Basic Editor (press ALT+F11), and on the Insert menu, click Module.



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.

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:

Q226118 OFF2000: Programming Resources for Visual Basic for Applications

Q212536 OFF2000: How to Run Sample Code from Knowledge Base Articles

Additional query words: vba XL2000

Keywords : kbdta kbdtacode OffVBA KbVBA
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto