Friday, July 20, 2018

Hexagonal Map Macro for Excel



How to make a hexagonal map very easily with Excel

Following recent discussions on A6 solo notebooks, the subject of hexagonal maps was addressed. Todd Zircher, whose excellent site I recommend http://www.tangent-zero.com/ gave me the tips to make a hexagonal map with Excel.


So I made a simple macro to draw hexagons. Simply select the area where you want the map to appear and launch the macro.



Here's the code:

Sub HexMap()
Cells.Clear
Dim rng As Range:
Set rng = Selection
Dim cel As Range

trig = 0
i = 0

For Each cel In rng.Cells
    If cel.Row <> a Then
        trig = trig + 1:
                i = 0
        If trig > 2 Then
            trig = 1
        End If
    End If

    i = i + 1
    If i > 4 Then i = 1


    Select Case trig
        Case Is = 1
            Select Case i
                Case Is = 1
                    cel.Borders(xlDiagonalUp).LineStyle = xlContinuous
                Case Is = 2
                    cel.Borders(xlEdgeTop).LineStyle = xlContinuous
                Case Is = 3
                    cel.Borders(xlDiagonalDown).LineStyle = xlContinuous
                Case Is = 4
                    cel.Borders(xlEdgeBottom).LineStyle = xlContinuous
             End Select
        Case Is = 2
            Select Case i
                Case Is = 1
                    cel.Borders(xlDiagonalDown).LineStyle = xlContinuous
                Case Is = 2
                    cel.Borders(xlEdgeBottom).LineStyle = xlContinuous
                Case Is = 3
                    cel.Borders(xlDiagonalUp).LineStyle = xlContinuous
                Case Is = 4
                    cel.Borders(xlEdgeTop).LineStyle = xlContinuous
             End Select
    End Select
    a = cel.Row

Next cel
End Sub





For those who do not know vba, when you are in Excel:

  • Open the VBA editor (alt+F11)
  • Select the current workbook
  • Create a new module
  • Copy and paste the macro below into the editor.



Then, you just have to select the area to draw and launch the macro (put the cursor anywhere inside the macro and press F5)

2 comments:

  1. will it work in Open Office?

    ReplyDelete
    Replies
    1. Good morning,
      At first glance open office vba does not work exactly like Excel vba.
      Some informations here: https://forum.openoffice.org/fr/forum/viewtopic.php?f=8&t=5220

      Delete