Microsoft Office Forums - View Single Post - How to create barcode in Excel spreadsheet using Microsoft Office 365 ProPlus

ArviLaanemets · 11-19-2019, 05:04 AM

I'm not sure how this will work as I made it and it was used some 10 or more years ago (MS Office 2010 and Windows XP probably), but at least it can give you some ideas.

I downloaded from some site barcode font (font file name was Code128b, in Format window it was named Code 128) and installed it on computer.

In Excel workbook module, I created a function

Code:

Public Function Code128X(parString As String) As String
    Dim varChar As String
    Dim varSum As Long
    Dim varCheck As Integer
    Dim varValue As Integer
    Dim i As Integer
    
    If Len(Trim(parString)) = 0 Then
        Code128X = ""
    Else
        ' as I did find out hard way, barcode readers don't read less than 3 character codes
        If Len(parString) < 3 Then
            parString = Right("   " & parString, 3)
        End If
        varSum = 104
        For i = 1 To Len(parString)
            varChar = Mid(parString, i, 1)
            varValue = Asc(varChar)
            If varValue = 128 Or varValue = 32 Then
                varValue = 0
            ElseIf varValue >= 33 And varValue <= 126 Then
                varValue = varValue - 32
            Else
                varValue = varValue - 50
            End If
            varSum = varSum + i * varValue
        Next i
        varSum = varSum Mod 103
        If varSum = 0 Then
        varCheck = 128
        ElseIf varSum >= 1 And varSum <= 94 Then
            varCheck = varSum + 32
        Else
            varCheck = varSum + 50
        End If
        Select Case varCheck
        Case 128
            varCheck = 8364
        Case 145
            varCheck = 8216
        Case 146
            varCheck = 8217
        Case 147
            varCheck = 8220
        Case 148
            varCheck = 8221
        Case 149
            varCheck = 8226
        Case 150
            varCheck = 8211
        Case 151
            varCheck = 8212
        Case 152
            varCheck = 732
        End Select
        Code128X = ChrW(353) & Replace(parString, " ", ChrW(8364)) & ChrW(varCheck) & ChrW(339)
    End If
End Function

I set the font for cell where I wanted the barcode to "Code 128", and entered there the formula

Code:

=Code128X($A$1)

(Assumed the value is read from cell $A$1).

NB! The rules for calculating check value and start/end characters depend on font you are using. It means with different barcode font you have to find those and adapt the function code.

Edit: Probably the font "Code 128" I see currently in my computer is not the one I used in past (Code128b), and is an later addition to default Windows fonts. This is probably the reason the old workbook can display only part of barcode lines currently.