I was reading about seven segment displays over at Sparkfun and thought it would be a fun exercise in Excel. I’m sure it’s been done a million times, but not by me. The first one was VBA based. Type a number into a cell and this code fills cells to display the number as a seven segment display.

`Public Sub ShowSevenSegment(ByVal lInput As Long)`

```
``` Dim sValue As String

Dim i As Long, j As Long

Dim aDigits(0 To 9) As Variant

Dim aRange() As String

Dim aRow(0 To 6) As Long, aCol(0 To 6) As Long

Dim rSeg As Range

Const lDISPCNT As Long = 4

Const lON As Long = vbBlack

Const lOFF As Long = vbWhite

'Hold the top left cell for each display

ReDim aRange(1 To lDISPCNT)

'Set the on/off for each digit. The order is top, left top,

'right top, middle, left bottom, right bottom, bottom

aDigits(0) = Array(lON, lON, lON, lOFF, lON, lON, lON)

aDigits(1) = Array(lOFF, lOFF, lON, lOFF, lOFF, lON, lOFF)

aDigits(2) = Array(lON, lOFF, lON, lON, lON, lOFF, lON)

aDigits(3) = Array(lON, lOFF, lON, lON, lOFF, lON, lON)

aDigits(4) = Array(lOFF, lON, lON, lON, lOFF, lON, lOFF)

aDigits(5) = Array(lON, lON, lOFF, lON, lOFF, lON, lON)

aDigits(6) = Array(lON, lON, lOFF, lON, lON, lON, lON)

aDigits(7) = Array(lON, lOFF, lON, lOFF, lOFF, lON, lOFF)

aDigits(8) = Array(lON, lON, lON, lON, lON, lON, lON)

aDigits(9) = Array(lON, lON, lON, lON, lOFF, lON, lON)

'Set the offset from the top left cell for each of the

'seven segments

aRow(0) = 0: aCol(0) = 1

aRow(1) = 1: aCol(1) = 0

aRow(2) = 1: aCol(2) = 2

aRow(3) = 2: aCol(3) = 1

aRow(4) = 3: aCol(4) = 0

aRow(5) = 3: aCol(5) = 2

aRow(6) = 4: aCol(6) = 1

'Set the top left cell for each display

For i = 1 To lDISPCNT

aRange(i) = Sheet1.Range("B2").Offset(0, (i - 1) * 4).Address

Next i

'Truncate and pad the value as necessary

If lInput > (10 ^ lDISPCNT) - 1 Then

sValue = Left$(lInput, lDISPCNT)

Else

sValue = Format(lInput, String(lDISPCNT, "0"))

End If

'Clear everything

Sheet1.Range(aRange(1)).Resize(5, 15).Interior.Color = lOFF

'Loop though the digits

For i = 1 To Len(sValue)

'Loop through the on/offs for that digit

For j = LBound(aDigits(CLng(Mid$(sValue, i, 1)))) To UBound(aDigits(CLng(Mid$(sValue, i, 1))))

'get the segment range and set the color

Set rSeg = Sheet1.Range(aRange(i)).Offset(aRow(j), aCol(j))

rSeg.Interior.Color = aDigits(CLng(Mid$(sValue, i, 1)))(j)

'color the corners

If aDigits(CLng(Mid$(sValue, i, 1)))(j) = lON Then

'for horizontal segments, fill left and right

If rSeg.Width > rSeg.Height Then

rSeg.Offset(0, -1).Interior.Color = lON

rSeg.Offset(0, 1).Interior.Color = lON

Else

'for vertical segments, fill up and down

rSeg.Offset(-1, 0).Interior.Color = lON

rSeg.Offset(1, 0).Interior.Color = lON

End If

End If

Next j

Next i

`End Sub`

OK, it’s really a 13 segment display – the seven segments and six connecting cells. Next, I did the same thing with conditional formatting. I tried to make the conditional formatting formula consistent across the cells, but I just couldn’t. The TRUEs and FALSEs change for each cell depending on if that cell is lit for that number.

Here’s the CF formula for cell H3.

`=CHOOSE(MID(TEXT($C$9,"0000"),(COLUMN()+MOD(MOD(MOD(16-COLUMN(),12),8),4))/4,1)+1,TRUE,TRUE,TRUE,TRUE,TRUE,FALSE,FALSE,TRUE,TRUE,TRUE)`

H3 is lit for every number except 5 and 6. There’s data validation on the input cell to keep it under five digits. The CF formula is a CHOOSE function with nine TRUEs/FALSEs. To determine which character to represent, I use a MID function after padding the text to four digits. The starting position (second argument of MID) is determine by this:

`(COLUMN()+MOD(MOD(MOD(16-COLUMN(),12),8),4))/4,1)`

Column |
16-Column |
Mod 12 |
Mod 8 |
Mod 4 |
Column+ |
/4 |

2 |
14 |
2 |
2 |
2 |
4 |
1 |

3 |
13 |
1 |
1 |
1 |
4 |
1 |

4 |
12 |
0 |
0 |
0 |
4 |
1 |

6 |
10 |
10 |
2 |
2 |
8 |
2 |

7 |
9 |
9 |
1 |
1 |
8 |
2 |

8 |
8 |
8 |
0 |
0 |
8 |
2 |

10 |
6 |
6 |
6 |
2 |
12 |
3 |

11 |
5 |
5 |
5 |
1 |
12 |
3 |

12 |
4 |
4 |
4 |
0 |
12 |
3 |

14 |
2 |
2 |
2 |
2 |
16 |
4 |

15 |
1 |
1 |
1 |
1 |
16 |
4 |

16 |
0 |
0 |
0 |
0 |
16 |
4 |

You can download SevenSegment.zip