Auto Generate Excel Table Using VBA

Question

I should preface this question by stating that I have 0 experience using any of the advanced features of Excel, and now that I need to use them, I have no idea what I am doing.

I need to generate an excel table with the following columns: ID, firstname, lastname, password, and phone in each column. I need N entries in each column, where ID, firstname, and lastname are just random characters, up to X characters, password is random upper case, lower case, and alphanumeric characters, up to Y characters, and phone is a random 10 digit int. I can generate the random strings easy enough, its checking to make sure the content is unique to anything else already in the table and adding it that I have no clue about. I am on a Mac, so this could also be done in bash, creating a csv file, another area I know very little about, but I figured since it is Excel specific, and I am going to need similar skills going forward, I should start learning VBA.

Answer 1

If I understand correctly, you are worried the ID, say, you generate on row N is the same as the one you generated for one of the rows in the range 2 to N-1.

I do not know of a VBA function that will report if a value matches or fails to match any value in a range. You are using the MAC while I use the PC but I doubt that makes a difference.

You seek a five column table. You must have a number of rows in mind, say Max. You want row 1 to hold a heading line. You say you know how to generate the random values for each column.

I know there are subtle differences between MAC and PC VBA but I assume the following is common.

I would generate the table in an array which will be much faster than accessing worksheet cells.

Dim Rng as Range
Dim WkshtValue as Variant

With Worksheets("xxxx")
  Set Rng = range of interest
  WkshtValue = .Rng.Value
End With

This above will convert WkshtValue to an array of the required size and copy to it all the values within the range of interest. Because WkshtValue is Variant, the entries within the array will be strings, integers, dates or whatever they were within the worksheet.

This array will be one based regardless of the position of the range within the worksheet so it hurts my brain less if the range is one based.

The first dimension of WkshtValue will be for rows and the second will be for columns. This is not the normal convention but it means WkshtValue(Row, Col) matches .Cells(Row, Col).Value.

It is marginally more difficult to do the opposite but you can define an array, fill it with values and then write is to the worksheet:

Dim Rng as Range
Dim WkshtValue() as Variant

ReDim WkshtValue(1 To Max + 1, 1 To 5)

' Fill row 1 - WkshtValue(1, 1) to WkshtValue(1, 5) - with the header row values.
' Fill rows 2 to Max+1 with appropriate randon values.

With Worksheets("xxxx")
  Set Rng = .Range(.Cells(1, 1), .Cells(Max + 1, 5))
End With
Rng.Value = WkshtValue

I would code a function RanVal(ColCrnt) As String that returned a random value appropriate for column ColCrnt.

I would then have something like:

For RowCrnt = 2 to Max + 1
  For ColCrnt = 1 To 5
    Do While True
      ValPoss = RanVal(ColCrnt)
      MatchFound = False
      For RowTemp = 2 to RowCrnt - 1
        If WkshtValue(RowTemp, ColCrnt) = ValPoss Then
          MatchFound = True
          Exit For
        End If
      Next
      If Not MatchFound Then
        Exit Do
      End If
    Loop
    WkshtValue(RowCrnt, ColCrnt) = ValPoss  
  Next
Next

The above code was typed straight into the answer and not tested. I cannot see any syntax errors but no promises. Come back if I have made a mistake you cannot correct.

Hope this gives you some ideas.