0

I have a barcode generator macro running in excel, but I keep running in an "invalid character" when I run the macro because the text includes a space.

IE: Smith, John

I'd like to be able to generate a barcode in excel and then scan the barcode into our learner management system, but to include the spaces so it's easier for us to search for our students. 

Sub Main()

    'Initialize variables
    Dim i, j, last_row, temp
    Dim start_a, start_frame, stop_frame, check_digit
    Dim ascii_column, ascii_value, ascii_length, ascii_code
    Dim barcode_column, barcode_value

    'Define variables
    ascii_column = 1
    barcode_column = 2
    check_digit = 0
    barcode_value = ""
    start_a = 103  'Start A Code (in Code 128 format)
    start_frame = 153  'Start A Code (in ASCII format)
    stop_frame = 156  'Stop A Code (in ASCII format)

    'Begin

    'Get last row
       Cells(1, ascii_column).Select
       Selection.End(xlDown).Select
        last_row = ActiveCell.Row
    If last_row > 10000 Then
    Range("A2").Select
    MsgBox ("There doesn't appear to be anything to do. Try again."), vbOKOnly
        Exit Sub
    End If

    'Setup barcode column
    Columns("B:B").Select
    With Selection.Font
    .Name = "Code128bWin"
    .Size = 20
    End With
    Rows("1:1").Select
    With Selection.Font
    .Name = "Arial"
    .Size = 10
    End With

    'Go to each row
    For i = 2 To last_row

    'Get user value and obtain length
    Cells(i, ascii_column).Select
    ascii_value = Trim(ActiveCell.Value)
    ascii_length = Len(ascii_value)

    'Calculate check digit
    For j = 1 To ascii_length

        'Convert value to ASCII
        temp = Asc(Mid(ascii_value, j, 1))

        'Convert ASCII to CODE128
        Select Case temp

            Case 128
                ascii_code = 0

            Case 33 To 126
                ascii_code = temp - 32

            Case 127 To 156
                ascii_code = temp - 50

            Case Else
                MsgBox ("Invalid character detected. Check input and try again."), vbOKOnly
                Exit Sub

        End Select

        'Aggregate check digit
        check_digit = check_digit + (ascii_code * j)

    Next j

    'Add start frame to check digit and mod 103
    check_digit = (start_a + check_digit) Mod 103

    'Convert CODE128 value back to ASCII
    If check_digit = 0 Then
        check_digit = 128

    ElseIf check_digit <= 94 Then
        check_digit = check_digit + 32

    Else
        check_digit = check_digit + 50

    End If

    'Combine guard bars, value, and check digit
    barcode_value = Chr(start_frame) & ascii_value & Chr(check_digit) & Chr(stop_frame)

    'Write out barcode value
    Cells(i, barcode_column).Select
    ActiveCell.Value = barcode_value

    'Reset values
    barcode_value = "": check_digit = 0

Next i

Range("A2").Select

End Sub
BigBen
  • 46,229
  • 7
  • 24
  • 40
Lorinda Inouye
  • 1
  • 1

1 Answers1

2

This forum is directed at those with programming experience, who have code that is not working. Probably superuser would have been a better forum.

You also need to be aware that the <space> character is NOT part of the ISO specification for the 128 barcode font. But at lease one implementation uses 194 for that.

If the implementation you are using does that, you can use this modified Select Case segment, to allow for translation of the <space> character: (ASCII Code 32)

Select Case temp

    Case 128
        ascii_code = 0

    Case 33 To 126
        ascii_code = temp - 32

    Case 127 To 156
        ascii_code = temp - 50

    Case 32
        ascii_code = 194

    Case Else
        MsgBox ("Invalid character detected. Check input and try again."), vbOKOnly
        Exit Sub

End Select

If your implementation does not, you will need to either find the correct code for the <space>, or write your own.

Ron Rosenfeld
  • 53,870
  • 7
  • 28
  • 60
  • hey! I did it correctly! But I have a whole column of names and the barcode is only generated for the first student name. – Lorinda Inouye Feb 05 '20 at 21:14
  • Thank for that information. I will definitely add myself to the right place, but after googling for two days, I figured I'd try to reach out to actual experts. I did have a code but I didn't know how to edit it as I'm super unfamiliar with this particular venue. I do sincerely appreciate your help and your time in clarifying and providing assistance. – Lorinda Inouye Feb 05 '20 at 21:18
  • @LorindaInouye The way the code is written, (and it is pretty sloppy), if A1 is empty, you will get that result. So put something in A1 and see what happens. – Ron Rosenfeld Feb 05 '20 at 21:27
  • @LorindaInouye To improve the code, and also for your edification, suggest you read [How to avoid using Select in Excel VBA](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) and also [Error in finding last used cell in Excel with VBA](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) – Ron Rosenfeld Feb 05 '20 at 21:29
  • I went with what was given to me, but thank you. You've been a huge help to me and my sanity. – Lorinda Inouye Feb 05 '20 at 21:32