Visual Basic for Applications/A Pseudo Random Character Table

Summary

This code module is intended for MS Excel. It makes a pseudo random table of characters, integers and capitals in this case, on Sheet1. A new and different table is made each time the procedure is run.

The Table

• Copy the code into a standard VBA module in Excel, and run the procedure MakePseudoRandomTable() to make a table. As shown, Sheet1 will be overwritten.
• The output uses a monospaced font, Consolas, for the clearest layout and type. In addition to ensuring a neat layout vertically and horizontally, monospaced tables allow the reading of sequences on a diagonal, so greatly extend their usefulness.
• Adjust the size of the table by changing the values nRows, and nCols in the code heading, and if necessary insert the name of the sheet to use. The code will add numbered row and column headings and will add these to each page that is displayed or printed.
• If an exact number of columns and rows is needed, adjust the margins for the sheet, and perhaps the font size until the required result is obtained.
• The proportion of integers to capitals is just 10/36, but is easily changed in code with a little effort.

The VBA Code Module

Option Explicit

Sub MakePseudoRandomTable()
    ' Makes a pseudo random table of integers and capitals
    ' using VBA internal function Rnd().
    
    'NOTES
    ' User should set narrow margins for best use of page.
    ' This will give about 47 rows by 35 cols
    ' Numbered headings are set to repeat on each printed page.
    ' Set number of rows and columns below.
    ' Integers to capitals ratio approx 10:26 = 0.385.
    ' Enter "0-127" in VBA Help for link to ASCI code numbers.
    
    Dim sht As Worksheet, sStr As String
    Dim nX As Integer, nAsc As Integer
    Dim nRows As Long, nCols As Long
    Dim nR As Long, nC As Long
       
    'set required table size and worksheet name here
    nRows = 100 'number of rows
    nCols = 100 'number of columns
    Set sht = ThisWorkbook.Worksheets("Sheet1")
    sht.Activate
    
    'clear and format worksheet
    With sht.Columns
        .ClearContents
        .ClearFormats
        .HorizontalAlignment = xlCenter
        .Font.Name = "Consolas" 'monospaced
        .Font.Size = 12
        .ColumnWidth = 2
    End With
    
    Randomize Timer 'seed system timer
    For nR = 1 To nRows     'row loop
        For nC = 1 To nCols 'col loop
            'allow break commands
            DoEvents
            'choose integer between 1 and 36 (total number of characters)
            nX = Int((36 - 1 + 1) * Rnd + 1)
            'make asci numbers in a decided proportion
            'set nX<=18 And nX>=1 here for equal integers and capitals
            If nX <= 10 And nX >= 1 Then 'for 10:26
                nAsc = Int((57 - 48 + 1) * Rnd + 48) 'integers 48 to 57
            Else
                nAsc = Int((90 - 65 + 1) * Rnd + 65) 'capitals 65 to 90
            End If
            'convert asci number to string
            sStr = Chr(nAsc)
            'print single string character per cell
            sht.Cells(nR, nC).Value = sStr
        Next nC
    Next nR
        
    'add numbers to column headings
    For nC = 1 To nCols
        sht.Cells(1, nC) = nC
    Next nC
    'set size and orientation of column headings
    With sht.Rows(1)
        .Font.Size = 12
        .Orientation = 90 'vertical
    End With
    
    'add numbers to row headings
    For nR = 1 To nRows
        sht.Cells(nR, 1) = nR
    Next nR
    'set size of row headings
    With sht.Columns(1)
        .Font.Size = 12
    End With
    
    
    'print row and col headings on every page
    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = "$1:$1"
        .PrintTitleColumns = "$A:$A"
    End With
    Application.PrintCommunication = True
    
    'select first cell
    sht.Cells(1, 1).Select

End Sub