VBA: adding random numbers to a grid that arent already in the grid

Question

Sub FWP()

Dim i As Integer
Dim j As Integer
Dim n As Integer
n = Range("A1").Value

For i = 1 To n
  For j = 1 To n

    If Cells(i + 1, j) = 0 Then
        Cells(i + 1, j).Value = Int(((n ^ 2) - 1 + 1) * Rnd + 1)
    ElseIf Cells(i + 1, j) <> 0 Then
        Cells(i + 1, j).Value = Cells(i + 1, j).Value
    End If
  Next j
Next i

I am trying to do a part of a homework question that asks to fill in missing spaces in a magic square in VBA. It is set up as a (n x n) matrix with n^2 numbers in; the spaces I need to fill are represented by zeros in the matrix. So far I have some code that goes through checking each individual cell value, and will leave the values alone if not 0, and if the value is 0, it replaces them with a random number between 1 and n^2. The issue is that obviously I'm getting some duplicate values, which isn't allowed, there must be only 1 of each number.

How do I code it so that there will be no duplicate numbers appearing in the grid? I am attempting to put in a check function to see if they are already in the grid but am not sure how to do it

Thanks

I would first scan the matrix and store the values in an array or dictionary, and then, before filing the blank with the random number, would check if the newly generated number is in array (or dictionary). — CMArg, Apr 22 '16 at 14:13
ok so I've put the matrix in an array, but am still not sure how I would code it to check if the newly generated number was in that array. — HWhite, Apr 22 '16 at 14:32
Hi and welcome to StackOverflow. Please include your code directly in the question, thus people will be able to help you more easily. — Michaël Azevedo, Apr 22 '16 at 14:33
Easiest way, Add a reference to the Microsoft Scripting Runtime Library and use the Dictionary Object. It has a .Exists() method — serakfalcon, Apr 22 '16 at 14:48
I would use Dictionary, as @serkfalcon suggested. If not, try [here](https://msdn.microsoft.com/en-us/library/office/ff835873.aspx) — CMArg, Apr 22 '16 at 14:54
Did you get it figured out, ultimately? What approach did you use? — C. White, Apr 28 '16 at 20:28

C. White · Answer 1 · 2016-05-12T21:45:07.770

There are a lot of approaches you can take, but @CMArg is right in saying that an array or dictionary is a good way of ensuring that you don't have duplicates.

What you want to avoid is a scenario where each cell takes progressively longer to populate. It isn't a problem for a very small square (e.g. 10x10), but very large squares can get ugly. (If your range is 1-100, and all numbers except 31 are already in the table, it's going to take a long time--100 guesses on average, right?--to pull the one unused number. If the range is 1-40000 (200x200), it will take 40000 guesses to fill the last cell.)

So instead of keeping a list of numbers that have already been used, think about how you can effectively go through and "cross-off" the already used numbers, so that each new cell takes exactly 1 "guess" to populate.

Here's one way you might implement it:

Class: SingleRandoms

Option Explicit

Private mUnusedValues As Scripting.Dictionary
Private mUsedValues As Scripting.Dictionary

Private Sub Class_Initialize()
  Set mUnusedValues = New Scripting.Dictionary
  Set mUsedValues = New Scripting.Dictionary
End Sub

Public Sub GenerateRange(minimumNumber As Long, maximumNumber As Long)
  Dim i As Long
  With mUnusedValues
    .RemoveAll
    For i = minimumNumber To maximumNumber
      .Add i, i
    Next
  End With
End Sub

Public Function GetRandom() As Long
  Dim i As Long, keyID As Long
  Randomize timer
  With mUnusedValues
    i = .Count
    keyID = Int(Rnd * i)
    GetRandom = .Keys(keyID)
    .Remove GetRandom
  End With
  mUsedValues.Add GetRandom, GetRandom
End Function

Public Property Get AvailableValues() As Scripting.Dictionary
  Set AvailableValues = mUnusedValues
End Property

Public Property Get UsedValues() As Scripting.Dictionary
  Set UsedValues = mUsedValues
End Property

Example of the class in action:

Public Sub getRandoms()
Dim r As SingleRandoms
Set r = New SingleRandoms
With r
  .GenerateRange 1, 100
  Do Until .AvailableValues.Count = 0
    Debug.Print .GetRandom()
  Loop
End With
End Sub

Using a collection would actually be more memory efficient and faster than using a dictionary, but the dictionary makes it easier to validate that it's doing what it's supposed to do (since you can use .Exists, etc.).

I like it. Why not provide an idea of how to achieve what you suggest? — findwindow, Apr 22 '16 at 16:21
I've been trying to resist doing that for the last two hours, but will provide an example shortly. — C. White, Apr 22 '16 at 17:19
My fault. When I said idea, I meant architecture. Something like create random number generator and store each result into array then fill in matrix using said array. Not to actually write code XD — findwindow, Apr 22 '16 at 20:44

score 0 · Answer 2 · answered Apr 22 '16 at 14:47

Nobody is going to do your homework for you. You would only be cheating yourself. Shame on them if they do.

I'm not sure how picky your teacher is, but there are many ways to solve this.

You can put the values of the matrix into an array. Check if a zero value element exists, if not, break. Then obtain your potential random number for insertion. Iterate through the array with a for loop checking each element for this value. If it is not present, replace the zero element.

VBA: adding random numbers to a grid that arent already in the grid

2 Answers2