Visual Basic for Applications/A Pseudo Random Character Table
Jump to navigation Jump to search
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.
- 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