Flying the Flag

In the U.S., today is Memoria Day. That means that this is the only post you’ll see from me today.

If found a web site on flag specifications and decided to use that information to make an American flag in Excel. Getting the row heights and column widths to scale properly is pretty hard, but I think it makes a nice rendering.

If you want to try it, paste this sub into a standard module and run it. Everything is based on the variable dFlagHoist which I tried to set that it would size correctly for different screen sizes/resolutions. If it’s too big or too small, change the ‘* 0.5’ to some other number.


Sub MakeFlag()

    Dim sh As Worksheet
    Dim dFlagHoist As Double
    Dim i As Long, j As Long
    Dim Shp As Shape
    
    Const dFLAGFLY As Double = 1.9
    Const dUNIONFLY As Double = 0.76
    Const dUNIONHOIST As Double = 0.5385
    Const dSTARVERT As Double = 0.054
    Const dSTARHORI As Double = 0.063
    Const dSTARDIAM As Double = 0.0616
    Const dSTRIPE As Double = 0.0769
    
    Set sh = ThisWorkbook.Worksheets.Add
    sh.Activate
    
    dFlagHoist = sh.Parent.Windows(1).VisibleRange.Width * 0.5
    
    With sh.Range(“A1?)
        .Resize(13, 2).Interior.Color = vbWhite
        For i = 1 To 13 Step 2
            .Offset(i – 1).Resize(, 2).Interior.Color = vbRed
        Next i
        .Resize(13).RowHeight = dFlagHoist / 13
        .Resize(7).Interior.Color = vbBlue
        .Resize(13, 2).BorderAround , xlThin
        .Offset(, 5).Select
        .Offset(13, 0).Resize(.Parent.Rows.Count – 13).EntireRow.Hidden = True
        .Offset(0, 2).Resize(, .Parent.Columns.Count – 2).EntireColumn.Hidden = True
        For i = 1 To 3
            .ColumnWidth = ((dFlagHoist * dUNIONFLY) / .Width) * .ColumnWidth
            With .Offset(0, 1)
                .ColumnWidth = ((dFlagHoist * (dFLAGFLY – dUNIONFLY)) / .Width) _
                    * .ColumnWidth
            End With
        Next i
    End With

    For i = 1 To 9 Step 2
        For j = 1 To 11 Step 2
            Set Shp = sh.Shapes.AddShape(Type:=msoShape5pointStar, _
                Left:=(dFlagHoist * dSTARHORI * j) – ((dFlagHoist * dSTARDIAM) / 2), _
                Top:=(dFlagHoist * dSTARVERT * i) – ((dFlagHoist * dSTARDIAM) / 2), _
                Width:=dFlagHoist * dSTARDIAM, _
                Height:=dFlagHoist * dSTARDIAM)
            Shp.Placement = xlFreeFloating
        Next j
    Next i
    
    For i = 2 To 8 Step 2
        For j = 2 To 10 Step 2
            Set Shp = sh.Shapes.AddShape(Type:=msoShape5pointStar, _
                Left:=(dFlagHoist * dSTARHORI * j) – ((dFlagHoist * dSTARDIAM) / 2), _
                Top:=(dFlagHoist * dSTARVERT * i) – ((dFlagHoist * dSTARDIAM) / 2), _
                Width:=dFlagHoist * dSTARDIAM, _
                Height:=dFlagHoist * dSTARDIAM)
            Shp.Placement = xlFreeFloating
        Next j
    Next i

    sh.Parent.Windows(1).DisplayHeadings = False
    
End Sub

Posted in Uncategorized

5 thoughts on “Flying the Flag

  1. Hi Dick,

    Thank you so much for this post, and also for all the time and talent you put into this blog. I’ve been learning VBA over the last year and all your examples have helped me a lot!

    I have a question about the for/next loop where you set columnwidths. What does this code do? I stepped through the code trying to see what happens as i goes from 1 to 3, but wasn’t able to tell. Could you please explain this to me?

    Thank you,

    Vince

Leave a Reply

Your email address will not be published. Required fields are marked *