Links to stuff on this blog

Use the Site Index of Projects page link above for an easier way to find stuff on my blog that you might be looking for!

Sunday, October 17, 2010

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!

8 comments:

  1. I too like playing around with numbers and trying to find patterns in the prime number sequence and various other sequences.

    But here's a formula I found that I think would help you out in conditionally formatting prime numbers:

    =OR(A1=2,A1=3,ISNA(MATCH(TRUE,A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))=INT(A1/ROW(INDIRECT("2:"&INT(SQRT(A1))))),0)))

    Don't ask me how it works, I just found it in a Google search, much better than using a lookup. Apply it to cell A1 and than use the Format Painter tool to apply the formatting to all your cells without affecting their formulas.

    Also, if you are looking for a powerful but easy language to do things with, I'd recommend Java if your willing to download the SDK and an IDE like NetBeans. If not, C# is almost identical to Java, and its available in the Visual Studio Suite. VB is weird with all those unnecessary keywords (Dim, Me, Is, End If, Next, Sub)...yuck.

    ReplyDelete
  2. codemann8

    Thanks for checking out my blog. I'm glad you found it interesting and I apologize for my lack of coding ability. I am a mechanical engineer and writing software is something that I 'have' to do to do the things I want to do!
    I'll give that formula a try - thanks! I googled it and there are some discussions about it. Looks like it iteratively divides a number by sequential integers looking for remainders...

    What types of things have you done with prime numbers or sequences? I'd be interested in seeing what you are doing.

    Thanks - Otto

    ReplyDelete
  3. I haven't really done much. I dabble around here and there, it's hard thinking of ideas on what to try out. A lot of times I find myself eventually reducing my complex beginnings down to things that have already been thought of, most often it's variations Pascal's triangle/tetrahedron.

    But I've been interested in things like factorials, prime numbers, Fibonacci sequence, Pi, number systems (other than base 10), Towers of Hanoi, fractals, really anything number related.

    I myself am a software developer so I was taught to code and really just that. So I can do just about anything with a set of data. But, I don't have much of any math knowledge. It makes it especially tough because every time I look something up about a certain postulate or some math proof, I don't have any clue how to read math notation beyond the basic common sense.

    But I do find it interesting that even though I'm finding things that are already known. It's kinda cool to see that I was also able to come to the same conclusion as some other historical mathematicians, that is without looking to see how they did it.

    Excel is a perfect tool for iteratively making calculations while maintaining all references. I don't know, while something storms in my mind, it's too easy to jam stuff into Excel and quickly figure something out, and that's what I mainly do.

    ReplyDelete
  4. codemann8,

    I agree some of the math relating to number theory can be challenging. I wasn't taught that type of math but I do find it interesting.

    My problem is I'm not a software person and coding really isn't all that fun for me. If I have an idea and try to code it in C or python even ExcelVB I end up with more heartache debugging my code when all I really want to do is try some idea. In other words I get too bogged down in the coding and sometimes never make it to trying out what I want to do with the data. I have even given up trying things because I had no way to test them or try them out.

    It would be nice if there were a 'spreadsheet' for lack of a better word that had a more intuitive interface and wan't limited like Excel VB is. I have some ideas on that, maybe I'll write a blog post about it.

    Do you think it would be hard to write something like that or is my description too vague?

    ReplyDelete
  5. I look at languages like C, python, and VB and shutter, and like I said I am a coder. I try to avoid any languages that I spend more time looking up syntax, keywords, and built-in functions; those above are among these type.

    Like I said in my previous post though, I think if you look into C# or Java, I think you would find it to be more intuitive, especially if you are dealing with simple computations with numbers. It's just a question of if you are willing/interested enough to learn about it. Some people are, some people aren't. But I understand that you don't "like" to code, it's just your only means to finding any kind of an answer.

    The problem with ExcelVB is that it requires you to write your code with retrospect to cell references, which makes thinking about it more harder than it needs to be. This is something much better and more simply done in Java or C#. You define an array of your initial numbers, you loop through each one and perform a calculation (and maybe store that information in another array), and print out the resulting numbers.

    It's funny you mentioned a more intuitive spreadsheet. Unfortunately, computers and programs can only do what it is told to do and it cannot do any thinking for you. Excel is built around a set of rules that works for the majority of people. They also built in this VB addon so anyone who wanted more out of it they could write a script to do it.

    I have however thought of the idea of making a program to house different variations of spreadsheets, like a 3D spreadsheet, or a 2D isometric, or 3D isometric tetrahedral, etc. I envision it being where you're looking at a spreadsheet 2D style and then you hit a certain key-combination to graphically show a 3D view of it and you use your mouse to move it around and have the ability to switch back to 2D but on a different "surface", but also able to maintain a way to view into deeper layers. It would be the same as Excel with references to cells, but with more of the intention being to recursively compute number sequences.

    But I'll tell you first hand, doing something like this is far from easy. There is a certain aspect of converting a graphical idea into a 2D program that is incredibly tough. A lot of programming languages have all that stuff built-in (which is why all Windows programs have the same-looking menus, same buttons, same text fields), they did all the work behind the scenes to provide a framework where it "converts" the idea of a text box into a 2D array of screen pixel RGB (red/green/blue) values and display it. Making a spreadsheet as I described above entails a whole different design entirely, because there is nothing like it, and it would be from scratch. I know my fair share of programming but there plenty of areas where my knowledge is weak in the matter. But I'm always challenging myself with new ideas but graphics has always been something that has eluded me.

    ReplyDelete
  6. codemann8,

    Your spreadsheet idea sounds intriguing, I think that would be really cool from what you describe.For rendering numbers in 3D I have used a mechanical CAD package called SolidWorks. Being mechanical I use that software all day in my job. You can create lists of values and load them into the software to create a point field in 3D. I have done some experiments that way with various number sequences. What you describe sounds much better and more flexible. SolidWorks is a mechanical design package and not really well suited to doing what I believe you want (and what I want for sure!). What you describe sounds really cool but I'd imagine it's a very tricky thing to do, debug and get running.

    I'll have a look at the languages you mention and see if I understand them well enough to play with. Honestly I love Excel but it isn't the right tool for what I want to do. It's like 90% of what I want but doing things like binary operations, parsing data and modular operations are either non existant (even in VBA) or really bassackwards to implement. The graphical 'see it on the screen' right away aspect of it can't be beat in my opinion. Doing calculations in other languages I tend to have to write the data out to a text file then import it into Excel. Play with it then save it into a text file and read it back into a home made program etc...

    I guess I will have to think a bit about what it is I am really after then look for the right tools to get there.

    Otto

    ReplyDelete
  7. I am a 42 year-old businessman with zero math training. However, I have been interested in number sequences for some time. I visit the Sloane database often. I have also written a C++ program that derives the function for a sequence of numbers. For example, if you input a sequence of triangular numbers, it will derive function = n(n+1)/2. It will do this for most known sequences. If you want the C++ source code, send me a note at nick.schmelzer@gmail.com. Thanks for sharing your efforts. I am glad there are others !

    ReplyDelete
  8. Nick,
    Thanks for checking out my blog. I'm also glad to hear there are other number sequence enthusiasts out there! I would appreciate the code, I'll send you an email. Are you familiar with the prime number spirals, like the Ulam Spiral?
    http://en.wikipedia.org/wiki/Ulam_spiral
    These arrangements of numbers really fascinate me.
    Otto

    ReplyDelete