Storing Data in code? (relative primes to MOD as example)

I would like to learn how to make my code more sophisticated. Specifically, I want to learn how to store data in my code after processing, only to send the final output to either a cell in a worksheet or someother textbox object in a userform, after the calculations are complete.

Currently, many of the codes I write store interm data/calculations in cells in a worksheet. Then later code grabs those interim numbers, uses them for further calculations, and then finally puts the finished calculation back into, say, range("A1").value.

I do not want to "bounce" calculations off the worksheet. I want to store them (in a collection?) in memory.

Following is code that finds all the relative prime numbers for a given modulus. To run the code you will need to use Range("E1") to input the relavant modulus. For example, MOD 26 has 12 relative prime numbers, 1,3,5,7,9,11,15,17,19,21,23 and 25.

[Side Note-- the code places a "data table" in cells A5:C(x)....ignore this for the purposes of this e-mail...just know that it is completely unnecessary to have this data table put into the cells. I have it there so I can find the multiplicative inverses of each of the relative primes with another sub-routine]

Here is the catch: (A) I want to learn how to store the relative prime outputs (in the code, the "p-1" numbers) in memory while the program runs, INSTEAD of printing them, one at a time, in Range("A1").Value, as the program loops; (B) After the last loop, I want the relative primes stored in memory to be sorted from smallest to largest; and (C) I want to then put all of the numbers (now stored in memory and sorted) into Range("A1").value.

Here is the Code (Remember to put the mod in cell E6):

_______________________________
Sub search_inverse2()
'
'Inverse_Function = (P*N)-D*INT((P*N)/D)
'd = range("e1").value
'
'Basic Mod Formula: MOD(n, d) = n - d*INT(n/d)
'Where n = number, d = modulus
'
Range("A1:C10000").Value = ""
Dim n As Double
Dim d As Double
Dim p As Double
Dim Inverse_function As Double
'
count_num = 3
n = 0
d = Range("e1").Value
Do
Do Until Inverse_function = 1 Or p = d
Inverse_function = (p * n) - d * Int((p * n) / d)
p = p + 1
count_num = count_num + 1
Sheet1.Cells(count_num, 1).Value = p - 1
Sheet1.Cells(count_num, 2).Value = Inverse_function
Loop
If Inverse_function = 1 Then
If Range("A1").Value <> "" Then
Range("A1").Value = Range("A1").Value & ", " & p - 1
Else
Range("A1").Value = p - 1
End If
End If
p = 0
Inverse_function = 0
n = n + 1
Loop Until n = d
End Sub
__________________________

Any help will be appreciated!

Use a collection to store data or an array.

but if you want to make it really OOP Object oriented programming use a class module.
If you want to check class modules or write better VB(A) I reccommend you to download MZ-tools.
It will save you time in error handling and writing classes.

I have been tinkering with collection, but have not had much luck making it work. A simple example of a collection code populated by looping procedure would be greatly appreciated.

By the way, the code I used for finding a single multiplicative inverse of a modulus follows below.

___________________________
Sub Multiplicative_Inverse()
'
Range("A1:B10000").Value = ""
'
'Inverse_Function = (P*N)-D*INT((P*N)/D)
'd = range("e1").value
'n = range("e2").value
'
'Basic Mod Formula: MOD(n, d) = n - d*INT(n/d)
'Where n = number, d = modulus
'5P MOD 26
'
Dim n As Double
Dim d As Double
Dim p As Double
Dim Inverse_function As Double
'
count_num = 3
n = Range("e2").Value
p = 0
d = Range("e1").Value
Do Until Inverse_function = 1 Or p = d

Inverse_function = (p * n) - d * Int((p * n) / d)
p = p + 1
count_num = count_num + 1
Sheet1.Cells(count_num, 1).Value = p - 1
Sheet1.Cells(count_num, 2).Value = Inverse_function
Loop
If Inverse_function = 1 Then
Range("A1").Value = p - 1
Else
Range("A1").Value = "Not 1 to 1."
End If
End Sub



-- Jason

Ok. I solved part of the question I presented above: I now use a scripting dictionary to store the calculation in memory. At the end of the sub-routine, the calculations (in this case all relative primes to a given modulus) are displayed in Column B. Much Faster! This code is good. Four-digit mods take a few seconds, six-digit mods take a long time....my next goal is to make a VBA version of the extended euclidian algorithm which should be much quicker.

I still need help on the second part of my question: Is it possible to sort the calculations from lowest to highest WHILE THEY ARE ITEMS IN THE DICTIONARY? (i.e. while they are in memory) so I can list them in Column B.

Yes -- I know I could sort them after they are in column B. I want to know if items stored in a dictionary can be sorted.

Here is the code (Modulus to be inserted into cell E1, try something like MOD = 4920 for a good example):
____________________________
Sub search_inverse4()
'
Dim i
Dim relative_prime_dictionary As Scripting.Dictionary
Dim n As Double
Dim d As Double
Dim p As Double
Dim Inverse_function As Double
Dim text As String
Set relative_prime_dictionary = New Scripting.Dictionary
'
Range("A1:C10000").Value = ""
'
count_num = 3
n = 0
d = Range("e1").Value
Do
Do Until Inverse_function = 1 Or p = d
Inverse_function = (p * n) - d * Int((p * n) / d)
p = p + 1
count_num = count_num + 1
Loop
If Inverse_function = 1 Then
text = p - 1
relative_prime_dictionary.Add n, text
End If
p = 0
Inverse_function = 0
n = n + 1
Range("A2").Value = Format(n / d, "0.00%")
Loop Until n = d
For i = 1 To relative_prime_dictionary.Count
' Code to sort Items?
Sheet1.Cells(i, 2) = relative_prime_dictionary.Items(i - 1)
Next

End Sub