Convert a Cell’s Value into a Barcode

The following is an example Excel VBA macro that will generate a barcode by calling B-Coder Pro. The macro uses data in the active cell for the barcode message and then inserts the barcode graphic in the worksheet 100 pixels to the right of the cell containing the data.

 Sub GenerateBarCode()
  Dim BC$
  Dim Chan As Long

  ' get a bar code message from the active cell and put it
  ' in a string
  BC$ = ActiveCell.Value

  ' remove any carriage return from bar code message
  If Right$(BC$, 1) = Chr(13) Then BC$ = Left$(BC$, Len(BC$) - 1)

  ' strip off trailing spaces
  BC$ = RTrim$(BC$)

  ' if no text is selected then quit
  If Len(BC$) = 0 Then
    MsgBox "This macro converts text to a bar code using B-Coder." +       Chr(13) _
      & "To use, select a cell containing some text and run this       macro."
    Exit Sub
  End If

  ' Initiate DDE link to B-Coder
  Chan = DDEInitiate("B-Coder", "System") 

  ' Turn off print warnings and enable invalid message warnings
  DDEExecute Chan, "[PrintWarnings=off]" 
  DDEExecute Chan, "[MessageWarnings=on]"

  ' generate the bar code 
  DDEExecute Chan, "[barcode=" + BC$ + "]" 

  ' save the bar code to a disk file 
  DDEExecute Chan, "[savebarcode(C:barcode.wmf)]"

  ' Terminate the DDE link 
  DDETerminate Chan 

  ' insert the bar code picture from the disk file into the sheet 
  ActiveSheet.Pictures.Insert("C:barcode.wmf").Select

  ' The following two lines demonstrate how to move the bar code picture
  ' to the right and down a number of pixels from the cell containing
  ' the original input data that the bar code was generated from. 
  Selection.ShapeRange.IncrementLeft 100
  ' Selection.ShapeRange.IncrementTop 50
End Sub

Contact Us