VBA Needle in the Haystack Testing Module

Option Compare Binary
Option Explicit
Option Base 0

''##############################################################################
''
''  @Author: Taylor Scott
''  @Date:   16 Nov 2019
''
''##############################################################################


''##############################################################################
''
''
''                                   WARNING
''
''
''  Do not run the test method or setup method included herein in a document
''  that contains important or sensitive data. All data on the Activesheet at
''  the time of execution shall be cleared and may not be recovered.
''
''
''##############################################################################


''  test function for needle in haystack algorithms in the vain of
''
''      https://codegolf.stackexchange.com/questions/194352/make-a-haystack-with-a-needle
''
''  specifically, algorithms formualted as by @EngineerToast's answer
''
''      https://codegolf.stackexchange.com/a/194373/61846
''


''  From testing using this sub, it is recommended that the subroutine be
''  changed to
''
''
''      Sub n(w,h,x,y)
''      a=94*Rnd
''      [A1].Resize(h,w)=Chr(a+32)
''      Cells(y,x)=Chr(32+(a+93*Rnd+1)Mod 95)
''      End Sub
''
''  as the current verison of the subrountine does not capture all possible
''  combinations of needle and haystack

Sub test_randomness()

    Dim count(32 To 126, 32 To 126) As Long ''  initialize an array to hold count of the
                                            ''  instances of given combinations of A and B

    Dim a As Byte       ''  the first ascii character num  - the haystack
    Dim b As Byte       ''  the second ascii character num - the needle
    Dim k As Byte       ''  dummy var for an alternate definition of a (k=a-32)
    Dim r As Double     ''  dummy var that allows for switching between random
                        ''      and edge case testing
    Dim iter As Long    ''  Iteration counter

    Call prep_output    '' prep the table
    Call Randomize      '' intitialize RND

    For a = 32 To 126   '' iterate over the possible values of A
        For iter = 1 To 10 ^ 5  ''  iterate over that value 10^5 times to get good
                                ''  sample size out of the random values

            ''''''''''''''''''''''''''''''
            '' Original Function
            ''''''''''''''''''''''''''''''
            'b=(a+62*Rnd)Mod 95+32      ''<- Uncomment this line and comment out the lines
                                        ''   below that define k and R and B to test
                                        ''   the function as it originally appeared

            ''  Compare against


            ''''''''''''''''''''''''''''''
            ''  New Function
            ''''''''''''''''''''''''''''''
            k = a - 32                  '' move the `+32` into the Chr statement
            'r = -(iter Mod 2 = 0)      '' edge case testing. Alternating R=0s and R=1s
            r = Rnd                     '' randomness test
            b = 32 + (k + 93 * r + 1) Mod 95

            ''  from edge case testing, r does not need to go to 0 or 1 to cover all possible
            ''  values, as the Mod operator introduces rounding. ie if r> 0.9947 the value of b
            ''  will be rounded up to the maximum possible value just before the mod operation


            'PPCG style explaination of how this works
            'b=32+(a+93*Rnd+1)Mod 95
            'b=                       Define the ASCII char number of the needle as
            '  32+                    32 plus
            '     (          )Mod 95  the modulus with respect to 95 of
            '      a                  the ASCII number of the hay, minus 32
            '       +93*Rnd           Plus a random shift of between 0 and 93 units
            '              +1         Plus a single unit garunteed shift


            '' Keep count of the total number of occurances of B, given a
            Let count(a, b) = count(a, b) + 1
        Next iter

        '' Dump the counts into the table
        Let [D4].Resize(95, 95) = count

        '' GarbageHandling
        Call VBA.DoEvents

    Next a
End Sub


''  Prepares a table with the ASCII character and the associated numbers on
''  on both the x and y axises
Private Sub prep_output()

    Dim a As Byte, b As Byte, i As Byte '' byte iterators

    '' turn off screen updates to increase execution speed
    Let Application.ScreenUpdating = False

    ''  clear old data
    Call Cells.Clear
    Let Cells.Style = "Normal"

    ''  add in the A and B value axises
    Let [A1].Resize(95, 2) = Array("=row()+28", "=char(A1)")
    Call [1:2].Insert
    Let [C1].Resize(1, 95) = "=column()+28"
    Let [C2].Resize(1, 95) = "=Char(C1)"

    '' add common axis legends
    Let [A1] = "ASCII"
    Let [B2] = "Char"

    ''  add axis title spaces
    Call [1:1].Insert
    Call [A:A].Insert
    Let [A:C,1:3].Font.Bold = True
    Let [A:A].ColumnWidth = 3
    Let [B:C].ColumnWidth = 5

    ''  define A value axis title
    Call [A4].Resize(95).Merge
    Let [A4] = "A value"
    Let [A4].Orientation = 90
    Let [A4].Style = "Heading 1"
    Let [A4].VerticalAlignment = xlCenter
    Let [A:C].HorizontalAlignment = xlCenter

    ''  define B value axis title
    Call [D1].Resize(1, 95).Merge
    Let [D1] = "B value"
    Let [D1].Style = "Heading 1"
    Let [D1].Resize(1, 95).EntireColumn.ColumnWidth = 4.25
    Let [1:3].HorizontalAlignment = xlCenter

    ''  set bulk coloration
    Let [B2].Resize(97, 97).Style = "Accent1"
    Let [D4].Resize(95, 95).Style = "20% - Accent1"

    ''  set accent colors for top left corner
    Let [A1:A3,B1:C1].Interior.Color = rgbWhite
    Let [C2,B3].Interior.Color = 13602409   ' GetAccentAtPercentage("Accent1", 80)
                                        '' (custom function - AccentPercentages Module)

    ''  set style of every other column and row to be slightly darker
    ''  than bulk coloration
    For i = 33 To 126 Step 2
        Let [D4].Offset(i - 32, 0).Resize(1, 95).Style = "40% - Accent1"
        Let [D4].Offset(0, i - 32).Resize(95, 1).Style = "40% - Accent1"
    Next i

    ''  set the points where the darker areas overlap to be darker still
    For a = 33 To 126 Step 2
        For b = 33 To 126 Step 2
            Let [D4].Offset(a - 32, b - 32).Style = "60% - Accent1"
        Next b
    Next a

    ''  set the diagonal of the identity matrix to be dark gray, as any
    ''  value which falls in the region indicates that the test function
    ''  for b has failed
    For a = 32 To 126
        Let [D4].Offset(a - 32, a - 32).Style = "Accent3"
    Next a

    '' add in borders
    Let [D4].Resize(95, 95).Borders.Weight = xlThin
    Call [D4].Resize(95, 95).BorderAround(Weight:=xlMedium)
    Call [B2].Resize(2, 2).BorderAround(Weight:=xlThin)
    Call [A1].Resize(98, 98).BorderAround(Weight:=xlThick)

    '' turn back on screen updates
    Let Application.ScreenUpdating = True
End Sub