Using Barcodes from the Dynamics GP Report Writer
A few months ago, I worked on a support case where we needed to add a barcode to a Sales Order Processing transaction print out. We were able to achieve what we needed using Visual Basic for Applications (VBA) to handle some of the scripting for us. I have been meaning to write a blog post on how to make a customization like this for a really long time and have finally got around to it.
This post is a good follow up to the Dynamics Report Writer is the Best Report Writer in the World post by demonstrating another cool customization using Report Writer with VBA. I will even use the same style to format the post.
Note: There are a number of different types of barcodes used, this example will use a Code 128 barcode subtype C (aka 128C), however, it does include the code needed for 128A and 128B as well.
The Situation
The customer wanted to use Code 128 barcodes on their SOP Packing Slips for the Item Number so they could be scanned at the warehouse before sending the order out. The customer had already purchased barcode True Type fonts from a vendor (in our case BarCodeWiz.com).
The Problem
Using a barcode font is not just a matter of printing the Item Number using a different font. Barcodes have additional characters added for Quiet Zones and Check Digits (see Wiki post). There is an algorithm required to convert your string into the barcode characters which can then be displayed using the barcode font.
Luckily, the barcode font vendor had provided some example BASIC code designed to be used with Microsoft Office Excel's Visual Basic for Applications (VBA). As the VBA in Office is almost identical to the VBA in Microsoft Dynamics GP, we were able use their code with almost no changes.
The Solution
So, we added a couple of blank string calculated fields to return our barcode characters to. One we left as the standard font (for testing purposes) and the other was changed to use one of the barcode fonts installed previously. Next we used the Tools menu to add the report to VBA, then selected the Item Number and the two newly added calculated fields and added them to VBA.
The screenshot below shows the calculated fields added onto the report layout.
Report Layout showing added calculated fields.
The Code
The code is quite simple. We just need to call one of the provided conversion functions and return our results to the added calculated fields.
Code from SOPBlankPackingSlipForm Module
Option Explicit
Private Sub Report_BeforeAH(ByVal Level As Integer, SuppressBand As Boolean)
Dim BarCode As String
Select Case Level
Case 5:
BarCode = BCW_Code128A(sItemNumber)
VBABarcode = BarCode
VBABarcodeText = BarCode
Case Else
End Select
End Sub
' Copyright © Microsoft Corporation. All Rights Reserved.
' This code released under the terms of the
' Microsoft Public License (MS-PL, https://opensource.org/licenses/ms-pl.html.)
' Code below provided by BarCodeWiz.com and used with permission.
Private Function C128ToBars(ascVal As Integer) As String
' This function creates the check character bar-by-bar
' in order to hide the human readable text
Const c As String = "212222222122222221121223121322" & _
"131222122213122312132212221213221312" & _
"231212112232122132122231113222123122" & _
"123221223211221132221231213212223112" & _
"312131311222321122321221312212322112" & _
"322211212123212321232121111323131123" & _
"131321112313132113132311211313231113" & _
"231311112133112331132131113123113321" & _
"133121313121211331231131213113213311" & _
"213131311123311321331121312113312311" & _
"332111314111221411431111111224111422" & _
"121124121421141122141221112214112412" & _
"122114122411142112142211241211221114" & _
"413111241112134111111242121142121241" & _
"114212124112124211411212421112421211" & _
"212141214121412121111143111341131141" & _
"114113114311411113411311113141114131" & _
"3111414111312114122112142112322331112"
Dim posString, ckDigitString As String
posString = Mid(c, 6 * ascVal + 1, 6)
Dim i As Integer
For i = 1 To 6
If i Mod 2 = 0 Then 'space
Select Case Mid(posString, i, 1)
Case "1": ckDigitString = ckDigitString & Chr(184)
Case "2": ckDigitString = ckDigitString & Chr(185)
Case "3": ckDigitString = ckDigitString & Chr(186)
Case "4": ckDigitString = ckDigitString & Chr(187)
End Select
Else 'bar
Select Case Mid(posString, i, 1)
Case "1": ckDigitString = ckDigitString & Chr(180)
Case "2": ckDigitString = ckDigitString & Chr(181)
Case "3": ckDigitString = ckDigitString & Chr(182)
Case "4": ckDigitString = ckDigitString & Chr(183)
End Select
End If
Next
C128ToBars = ckDigitString
End Function
Public Function BCW_Code128A(ByVal inputString As String) As String
If Len(inputString) = 0 Then
Exit Function
End If
Dim currentPos, chkDigitPos, chkDigitTotal As Integer
currentPos = 1
chkDigitTotal = 103 'Start Code 128 A
While currentPos <= Len(inputString)
Dim asciiVal As Integer
asciiVal = Asc(Mid(inputString, currentPos, 1))
Dim bcVal As Integer
If asciiVal >= 32 And asciiVal <= 95 Then
bcVal = asciiVal - 32
ElseIf asciiVal >= 0 And asciiVal <= 31 Then
bcVal = asciiVal + 64
Else
BCW_Code128A = ""
Exit Function
End If
chkDigitTotal = chkDigitTotal + bcVal * currentPos
currentPos = currentPos + 1
Wend
chkDigitTotal = chkDigitTotal Mod 103
Dim bcChkDigitAscii As Integer
If chkDigitTotal = 0 Then
bcChkDigitAscii = 232
ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then
bcChkDigitAscii = chkDigitTotal + 32
ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then
bcChkDigitAscii = chkDigitTotal + 132
ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
bcChkDigitAscii = chkDigitTotal + 100
End If
inputString = Replace(inputString, " ", Chr(232))
BCW_Code128A = Chr(203) & inputString & _
C128ToBars(chkDigitTotal) & Chr(206)
'Chr (bcChkDigitAscii) & Chr(206)
End Function
Public Function BCW_Code128B(ByVal inputString As String) As String
If Len(inputString) = 0 Then
Exit Function
End If
Dim currentPos, chkDigitPos, chkDigitTotal As Integer
currentPos = 1
chkDigitTotal = 104 'Start Code 128 B
While currentPos <= Len(inputString)
Dim asciiVal As Integer
asciiVal = Asc(Mid(inputString, currentPos, 1))
Dim bcVal As Integer
If asciiVal >= 32 And asciiVal <= 127 Then
bcVal = asciiVal - 32
Else
BCW_Code128B = ""
Exit Function
End If
chkDigitTotal = chkDigitTotal + bcVal * currentPos
currentPos = currentPos + 1
Wend
chkDigitTotal = chkDigitTotal Mod 103
Dim bcChkDigitAscii As Integer
If chkDigitTotal = 0 Then
bcChkDigitAscii = 232
ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then
bcChkDigitAscii = chkDigitTotal + 32
ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then
bcChkDigitAscii = chkDigitTotal + 132
ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
bcChkDigitAscii = chkDigitTotal + 100
End If
inputString = Replace(inputString, " ", Chr(232)) 'replace spaces
BCW_Code128B = Chr(204) & inputString & _
C128ToBars(chkDigitTotal) & Chr(206)
'Chr (bcChkDigitAscii) & Chr(206)
End Function
Public Function BCW_Code128C(ByVal inputString As String) As String
On Error GoTo Err
If Len(inputString) = 0 Then
Exit Function
End If
' If the number of digits is not even, then
' add a zero to the beginning of the string
If Len(inputString) Mod 2 <> 0 Then
inputString = "0" & inputString
End If
Dim currentPos, chkDigitPos, chkDigitTotal As Integer
currentPos = 1
chkDigitPos = 1
chkDigitTotal = 105 'Start Code 128 C
Dim encodeToAscii As Integer
While currentPos < Len(inputString)
Dim twoChars As String
twoChars = Mid(inputString, currentPos, 2)
Dim curVal As Integer
curVal = CInt(twoChars)
chkDigitTotal = chkDigitTotal + curVal * chkDigitPos
If curVal = 0 Then
encodeToAscii = 232
ElseIf curVal >= 1 And curVal <= 94 Then
encodeToAscii = curVal + 32
ElseIf curVal >= 95 And curVal <= 99 Then
encodeToAscii = curVal + 132
ElseIf curVal >= 100 And curVal <= 103 Then
encodeToAscii = curVal + 100
End If
Dim outputString As String
outputString = outputString & Chr(encodeToAscii)
chkDigitPos = chkDigitPos + 1
currentPos = currentPos + 2
Wend
chkDigitTotal = chkDigitTotal Mod 103
If chkDigitTotal = 0 Then
encodeToAscii = 232
ElseIf chkDigitTotal >= 1 And chkDigitTotal <= 94 Then
encodeToAscii = chkDigitTotal + 32
ElseIf chkDigitTotal >= 95 And chkDigitTotal <= 99 Then
encodeToAscii = chkDigitTotal + 132
ElseIf chkDigitTotal >= 100 And chkDigitTotal <= 103 Then
encodeToAscii = chkDigitTotal + 100
End If
outputString = Chr(205) & outputString & _
C128ToBars(chkDigitTotal) & Chr(206)
'Chr (encodeToAscii) & Chr(206)
BCW_Code128C = outputString
Exit Function
Err:
BCW_Code128C = ""
End Function
The Result
Below is a screenshot of the resulting report. Note the additional characters added to the barcode text:
Example screen output showing populated barcode fields
More Information
The links below provide mode information on the Barcode code 128 and its subtypes as well as a link to the barcode font supplier used by this customer. The barcode functions included in the VBA code for this example were provided by BarCodeWiz.com and are used in this example with their permission.
I hope you find this example of how barcodes can be used on Dynamics GP reports useful.
David