應用程式 Visual Basic/偽隨機字元表

摘要

此程式碼模組適用於 MS Excel。它在 Sheet1 上建立了一個偽隨機字元、整數和大寫字母表。每次執行該過程時，都會建立一個新的不同表格。

表格

將程式碼複製到 Excel 中的標準 VBA 模組中，然後執行過程 MakePseudoRandomTable() 來建立表格。如所示，Sheet1 將被覆蓋。
輸出使用等寬字型 Consolas，以獲得最清晰的佈局和型別。除了確保垂直和水平的整齊佈局之外，等寬表格允許對角線讀取序列，從而極大地擴充套件了它們的實用性。
透過更改程式碼標題中的值 nRows 和 nCols 來調整表格的大小，如果需要，可以插入要使用的表的名稱。該程式碼將新增編號的行和列標題，並將這些標題新增到顯示或列印的每個頁面。
如果需要確切的列數和行數，請調整工作表的頁邊距，以及可能調整字型大小，直到獲得所需的結果。
整數與大寫字母的比例僅為 10/36，但只需稍加努力即可輕鬆更改程式碼中的比例。

VBA 程式碼模組

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

另請參閱

外部連結