Excel – How to build magic square with VBA, following Python Mod Mathematics

Some 4 years ago I wrote an article for calculating magic square with python. What the article did was actually checking whether the square is magic, and not calculating a new one. This one is going to show how these squares are built.

Wikipedia says that magic square is a square grid, filled with distinct positive integers in range 1,2, 3… n^2, such that each cell contains a different integer and the sum of the integers per column, row and diagonals is equal. In the general case, building a magic square in VBA requires a few more checks, but as I have recently got a bit deeper into Python, I have noticed that Python has its own way of mathematics, when it comes to negative numbers. (Modulo Operation on Negative Numbers). Long story short, Python claims that:

while in the VBA (and all the C languages), -1 mod 3 and 1 mod 3 would return -1 or 1 . Nothing to do with 2. Which mathematics is “better” is another story, the thing is that the “Python way of doing maths” allows us to write a magic square in quite quick way. If we want to represent the Python mod function in VBA, this is it:

Public Function PythonMod(a, b) As Long
    PythonMod = (((a Mod b) + b) Mod b)
End Function

With this function in hand, building a Magic Square in VBA becomes “a piece of cake”:

    While n <= size * size
        magicArray(i, j) = n        
        n = n + 1
        ii = PythonModule((i - 1), size)
        jj = PythonModule((j + 1), size)        
        If magicArray(ii, jj) Then
            i = i + 1
        Else
            i = ii
            j = jj
        End If
    Wend

With a size big enough to see what is going on, one may notice the logic – build always up and to the left except for the case when the Array((i-1) mod size, (j+1) mod size) is taken:

This is the whole code, allowing you to impress someone. Start with the Main() function and feel free to change the size:

Sub CreateMagicSquare(size As Long)

    Dim ws As Worksheet
    Set ws = Worksheets(1)
    ws.Cells.Delete
    
    Dim magicRange As Range
    Dim magicArray() As Long
    ReDim magicArray(size - 1, size - 1)
    
    With ws
        Set magicRange = .Range(.Cells(1, 1), .Cells(size, size))
    End With
    
    SetColumnWidth ws, 1, size, 2.8
    
    Dim i As Long: i = 0
    Dim j As Long: j = size \ 2
    Dim n As Long: n = 1
    Dim ii As Long
    Dim jj As Long
    
    While n <= size * size
        magicArray(i, j) = n
        magicRange(i + 1, j + 1) = n
        
        n = n + 1
        ii = PythonModule((i - 1), size)
        jj = PythonModule((j + 1), size)
        
        If magicArray(ii, jj) Then
            i = i + 1
        Else
            i = ii
            j = jj
        End If
    Wend
    
End Sub

Public Function PythonModule(a, b) As Long
    PythonModule = (((a Mod b) + b) Mod b)
End Function

Sub SetColumnWidth(ws As Worksheet, fromColumn As Long, toColumn As Long, size As Double)
    
    Dim i As Long
    For i = fromColumn To toColumn
        ws.Columns(i).ColumnWidth = size
    Next i
    
End Sub

Sub Main()
    CreateMagicSquare 9
End Sub

The same code in Python, without the “fancy” excel visualisation looks like this:

import numpy as np

N  = 7
magic_square =  np.zeros((N,N),dtype=int)

n = 1
i = 0
j = N//2

while n <= N * N:
    magic_square[i,j] = n

    n += 1
    ii = (i-1) % N
    jj = (j+1) % N

    if magic_square[ii, jj]:
        i += 1
    else:
        i, j = ii, jj

print(magic_square)
print("\n")
#fancy formatting here:
nice_list = [["%3s" % str(str(j)+"|") for j in i] for i in magic_square]
for line in nice_list:
    print(" ".join(map(str,line)))

Still, the formatting is not that bad:

Cheers!