Number Sequences with MS Excel Visual Basic, Prime Numbers etc...

What is this post all about? I don't really know but it was fun to do and it is what I have been doing so this is what I am writing about! After all this blog is supposed to be about the things I am doing. Enough with the fancy introductions...

  

I really like number sequences, or more specifically integer sequences, because to me they are interesting in some mysterious way. I don't think that I am the only person that feels this way because there is a On-Line Encyclopedia Of Integer Sequences that is full of them here OEIS. Anyway I decided to write some Visual Basic scripts in Excel and play around with them. The first thing I decided to do was make a chart that has the square number sequence (OEIS sequence A000290) going down the page in a diagonal. That is the purple values below:

 

 

This square number sequence is the square of the integers... so start with 1, 2, 3, 4,... and square those and you get (1*1) = 1, (2*2) = 4, (3*3) = 9, (4*4) = 16 etc... What enormous amounts of fun you say!? What could possibly make this any better you ask?  Adding in the missing natural numbers in a way that still preserves the diagonal of squares is the first thing that comes to my mind!

  

To fill in the missing numbers I had to come up with some rules. Actually I can do anything I want without any rules (because in my world I am king) but because I wanted to do this in Visual Basic I needed rules to program. What I decided was to have the natural numbers 'snake' around the squares diagonal, crossing it where the numbers matched like in the picture below.I know you can't actually see that going on in the below picture but trust me it is!

 

 

Have a look at the first picture above (click on it for a better view) if you want to see what I mean. What made sense to me was to start filling in the missing numbers moving up, then turn back down crossing the diagonal. If I have to go up an odd number of numbers, then shift the odd one over and down. Once the numbers cross the diagonal then continue on down (past the diagonal) and come back up to cross again.

Below is 'pseudocode' of what I did and at the end of this post I have the entire Visual Basic Script:

Get the First Diagonal value                (First might be the number 4)

Get the Next Diagonal value                (Next might be the number 9)

Get the Difference of Next - First          (Difference is 9 - 4 = 5)

Is Difference Odd?                              (is Difference odd?)

Start counting, filling in Difference/2     (go up half way)

If Difference is odd move diagonally,    (turn around)

   if not odd then just move over

Continue counting down to the diagonal

Now do this all over again but moving down. (Yeah I know it's boring but this is what I do for fun late at night!). OK once I had all the natural numbers filled in the next obvious thing to do is highlight all the prime numbers. Figuring out if a number is prime isn't easy so I just copied all of them into the spread sheet and looked them up as I went along. Yes I looked them up as I went along so the pseudocode above isn't exactly right... cut me some slack!  Or better yet send me an email ottobelden@yahoo.com or leave a comment.

  

Where the pseudocode above says 'counting' just insert the code "check if the number is prime, if it is highlight it" Back to things that are not easy - highlight cells in Visual Basic isn't easy. I found that out the hard way. So rather than just checking to see if the number is prime then highlighting it, I found it easier to copy the number and it's formatting if it's prime. Then I formatted the list of primes to be a soothing shade of blue. See below:
 
Cells(1, 1).Copy
Cells(2, 2).PasteSpecial xlFormats

What the above piece of code does is Copy the value and formatting of cell A:1 then Pastes the Formatting of the cell to B:2. In VBA the Cells() command uses two inputs of Cells (Row, Column) and they both have to be numbers so you can't just use Cells (1 , A) because A isn't a number. Using numbers to refer to the columns is more of a nuisance than anything else because you can toggle the spreadsheet in Excel to number the Columns rather than use letters to refer to them.

That about sums it all up. I thought that the patterns of prime numbers in Blue above looked interesting and I am probably going to modify the Visual Basic code (below) to do the same thing but with other number sequences, rather than just the squares. If it looks interesting I'll post those pictures too. One thing I noticed is that in the second picture above the prime numbers seem to form straight horizontal groups in 'clusters'. Wow!!! The fun never stops!

While working on this I found a site that has some other interesting pictures (more interesting than mine!) relating to 'patterns' of primes. HERE is a link to that site. Obviously I'm not the first person to mess around with this kind of thing and I'm glad to see that - it makes me feel more 'normal'!!!

The Microsoft Excel Visual Basic Program I wrote:
 

Sub PerfectSquareDiag()

'This code will create a diagonal of perfect squares

'  then fill in all the missing integers following a

'  pattern highlighting al the prime numbers

' The prime numbers start at A:1 and go down

'  the column and are all 'filled blue'

'http://ottobelden.blogspot.com/

' October 2010

'Create a diagonal of Perfect Squares

Dim Row As Integer

Dim Prime As Integer    ' Row Counter for primes
Dim Current As Integer  ' Current cell value
Dim Current1 As Integer ' Next cell in square
Dim Diff As Integer        ' Diff of Current(s)
Dim Offset As Integer    ' Diagonal offset if odd
Dim Height As Integer   ' Height of nums above/below square
Dim Iterate As Integer  ' For the outside loop

'

Row = 3     ' Starting Row and Col
Col = 3

'

' Make the perfect square diagonal

Dim Count1 As Integer
For Count1 = 1 To 100
    Cells(2, 2).Copy
    Cells(Row, Col).PasteSpecial xlFormats
    Cells(Row, Col) = Count1 ^ 2
    Row = Row + 1
    Col = Col + 1
Next Count1
Row = 3     ' Starting Row and Col
Col = 3
Prime = 1   ' Row counter for primes
Application.ScreenUpdating = False

'''''''''''''''''''''''''''''''''''''''''''''''''''''''BEGIN THE ALGO TO MOVE UP AND DOWN

For Iterate = 1 To 600

'Start in the diagonal and begin to fill in numbers

'   moving up

' Get difference between current cell and next cell

Current = Cells(Row, Col)
Current1 = Cells(Row + 1, Col + 1)

' Calculate how high up and if there is an offset

Diff = Int(Current1 - Current)   ' Rounds down the difference
Height = Int(Diff / 2)              ' Height of stack of numbers
Offset = (Current1 - Current) Mod 2 ' Returns 1 if diff is odd

'

' Start a loop moving up checking for primes

' Figure out where to start counting

' Make the stack of numbers moving up

For Counter1 = Current + 1 To (Current + Height)
    ' Set the position
    Row = Row - 1                   ' Move up
    Cells(Row, Col) = Counter1  ' Set the value of the cell

    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
    ' End of prime font change

    '

Next Counter1

'

' Check to see if there is an offset (1) if so

'   then offset the cells right and down

'   check if the cell is prime

'   if not (0) then start moving down

If Offset = 1 Then
    Col = Col + 1
    Row = Row + 1
    Cells(Row, Col) = Counter1  ' Set the value of the cell

    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
        ' End of prime font change

        '

End If

' At this point the first half of the top stack is full

'   the offset (if present) has been entered so

Current = Cells(Row, Col)   ' Set current to the current value

''''''''''''''''''''''''''''''''''''''''''''''''''''''

' Count down to the square number

For Counter1 = Current + 1 To (Current + Height)
    ' Set the position
    Row = Row + 1               ' Move down
    Cells(Row, Col) = Counter1  ' Set the value of the cell

    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
    ' End of prime font change

    '

Next Counter1

'''''''''''''''''''''''''''''''''''''''''''''''''''

' Get difference between current cell and next cell

Current = Cells(Row, Col)
Current1 = Cells(Row + 1, Col + 1)

'

' Calculate how far down to go and if there is an offset

Diff = Int(Current1 - Current)          ' Rounds down the difference
Height = Int(Diff / 2)                     ' Height of stack of numbers
Offset = (Current1 - Current) Mod 2 ' Returns 1 if diff is odd

' Start a loop moving down checking for primes

' Figure out where to start counting

' Make the stack of numbers moving down

For Counter1 = Current + 1 To (Current + Height)
    ' Set the position
    Row = Row + 1                  ' Move down
    Cells(Row, Col) = Counter1  ' Set the value of the cell
    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
    ' End of prime font change

    '

Next Counter1

'

'

' Check to see if there is an offset (1) if so

'   then offset the cells right and down

'   check if the cell is prime

'   if not (0) then start moving down

If Offset = 1 Then
    Col = Col + 1
    Row = Row + 1
    Cells(Row, Col) = Counter1  ' Set the value of the cell

    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
        ' End of prime font change

        '

End If

' At this point the first half of the bottom stack is full

'   the offset (if present) has been entered so

Current = Cells(Row, Col)   ' Set current to the current value

'

' Continue filling in numbers up to Square

'

For Counter1 = Current + 1 To (Current + Height)
    ' Set the position
    Row = Row - 1                   ' Move up
    Cells(Row, Col) = Counter1  ' Set the value of the cell
    '

    ' Check for prime and if it is change font

    If Cells(Row, Col) = Cells(Prime, 1) Then
        Cells(Prime, 1).Copy
        Cells(Row, Col).PasteSpecial xlFormats
        Prime = Prime + 1
        End If
    ' End of prime font change

    '

Next Counter1

''''''''''''''''''''''''''''''''''''''''''''''''''END THE ALGO TO MOVE UP AND DOWN

Next Iterate
Application.ScreenUpdating = True
End Sub
  
After looking at the VBA code above I'm sure you can tell that I am not a programmer nor do I really want to be. I have been looking for a long time to find a programming language that is easy as well as powerful for someone like me who is interested in writing little programs but doesn't want to learn and keep track of all the subtle craziness of a programming language. Visual Basic is not that language but its as close as I have found - other than Python!