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
Great “trick”
Thanks!
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
Very very cool.
Very impressive, Dick. How long did that take you?
About 2 hours of work over a 4 day period.